How to SELECT all fields from two tables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

sounds like mysql_num_rows() returned zero.
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

must be, but don't know why...several rows in each table
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Could you post an export of the two tables' structures?
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

fixed it - it was here:

$query= "select * from `customers`, `orders2` where `orders2`.realname = '$realname' and `customers`.realname = `orders2`.`realname`";


changed to:

$query= "select * from `customers`, `orders2` where `orders2`.realname = `customers`.realname and `customers`.realname = `orders2`.`realname`";

Now I just have to config the HTML output...
boy, thanks a million!
this is a fantastic resource for someone trying to learn the how-to's
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

LAST QUESTION

Post by michlcamp »

Now, what's the best way to search for single orders?
whole new can of worms?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's actually not all that bad.

This should be the same query you just wrote, in a different look

Code: Select all

SELECT
  *
FROM
  `customers`
INNER JOIN
  `orders2`
  ON
  (
    `customers`.`realname` = `orders2`.`realname`
  )
Using that,

Code: Select all

SELECT
  *
FROM
  `customers`
INNER JOIN
  `orders2`
  ON
  (
    `customers`.`realname` = `orders2`.`realname`
  )
WHERE
  `specificField` = 'some value'
is a natural extension.
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

many thanks...I'll give that a try in a minute...right now trying to figure out how to post only those fields that have an entry - or at least to hide the zero values
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Thanks to all

Post by michlcamp »

all is well
many thanks to all who helped me here.
mc
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

strange results

Post by michlcamp »

one thing that's wierd...
when I query for a report, I get duplicates of orders when they're by the same customer - wondering how to configure it so I only get one result for each order...

query I'm using :

Code: Select all

echo "<a href=\"order_admin.php\">Admin</a><br>";
$realname2 = $_POST["realname"];
include("host.php");
$dbh=mysql_connect ($host, $user, $password);
$link = $dbh;
@mysql_select_db($dbname) or die( "Unable to select database");
$sql = "SELECT * FROM `customers`INNER JOIN `orders2` ON (`customers`.`realname` = `orders2`.`realname`) WHERE `customers`.`realname`= '$realname2' ORDER BY orders2.date desc";
$query=$sql;
$result = mysql_query($query) or die(mysql_error());
if (is_resource($result)){
    if (mysql_num_rows($result)){
etc
Last edited by michlcamp on Fri Aug 26, 2005 10:53 am, edited 2 times in total.
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

please use

Code: Select all

or

Code: Select all

tags around your code
Post Reply