[SOLVED] Join syntax question

[KevinM1]

I'm trying to do an inner join with three tables.  Each contain computer peripherals, but they all represent a different category of peripheral.  Unfortunately, as I've been building the database as I go, there are some duplicates across tables, which makes printing out the entire list slightly more complicated than it should be.  I figured the easiest way for me to exclude the duplicates would be to just not include the items that have the same name across tables (although, thinking about it more, I dunno if that would actually work).  Unfortunately, I'm getting a PHP error stating that my query isn't generating a valid MySQL resource to work with.

 

My table structure is the following (same for all three tables, with the exception of their respective id names):

table_name_id: TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
name: VARCHAR(40) NOT NULL
price: FLOAT
availability: CHAR(1)
description: TEXT
pic_url: TEXT

 

The query I'm trying to execute is:

<?php

$query = "SELECT * FROM gaming_peripherals AS gp, office_peripherals AS op, peripherals AS p WHERE gp.name != p.name AND op.name != p.name AND availability='y' ORDER BY price ASC";
$result = mysql_query($query);

//HTML output for a few lines here

if(mysql_num_rows($result) == 0){
   echo "All items of this category are currently out of stock.  Please check here again at a later time for product availability.<br />We apologize for any inconvenience this may cause.";
}

?>

 

And the error message is:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/thinkin8/public_html/test.php on line 46

 

Is my syntax valid?  Or could I be missing something else?  This is the only query I make to the database, so there's no cross-contamination, so to speak.

[Wildbug]

I usually check the result resource first before using mysql_num_rows() or any other function that requires it in order to be certain the resource is valid.

 

I.e.,

 

if ($result && mysql_num_rows($result) == 0) { ....

 

It looks as if your query is not executing (duh!).  I would guess it is because "availability" is ambiguous.  You could change it to p.availability or otherwise eliminate the ambiguity as to which "availability" column you mean.  For further debugging you could also try typing your query into the mysql client directly if you have that ability/option.  Alternatively, for debugging purposes, you could check for errors and print the MySQL error message.  I.e.,

 

if (mysql_errno()) echo "<pre>".mysql_error()."</pre>\n";  // Or something to that effect....

[KevinM1]

I usually check the result resource first before using mysql_num_rows() or any other function that requires it in order to be certain the resource is valid.

 

I.e.,

 

if ($result && mysql_num_rows($result) == 0) { ....

 

It looks as if your query is not executing (duh!).  I would guess it is because "availability" is ambiguous.  You could change it to p.availability or otherwise eliminate the ambiguity as to which "availability" column you mean.  For further debugging you could also try typing your query into the mysql client directly if you have that ability/option.  Alternatively, for debugging purposes, you could check for errors and print the MySQL error message.  I.e.,

 

if (mysql_errno()) echo "<pre>".mysql_error()."</pre>\n";  // Or something to that effect....

 

That removed my syntax error, but now I have a rather large logic error.  I've modified my query to be:

SELECT * FROM gaming_peripherals AS gp, office_peripherals AS op, peripherals AS p WHERE gp.name != p.name AND op.name != p.name AND gp.availability='y' AND op.availability='y' AND p.availability='y' ORDER BY gp.price, op.price, p.price ASC;

 

Instead of removing duplicate entries, I have each item in the tables represented dozens of times.  So, for instance, the keyboard/mouse combo we offer, which only has, at most, two entries in the entire database (one in gaming_peripherals, one just in peripherals), is displayed over 30 times as the result of this join.

 

Any ideas on where I'm going wrong?  Because I figure my 'AND' conditions would limit what should be shown, not increase what is shown.

 

EDIT: This is odd...I've used a test query that just says

SELECT * FROM gaming_peripherals AS gp, office_peripherals AS op, peripherals AS p;

 

Now, I'd think it would just return the 46 total items that are in those tables.  Instead, I get 2,898 items returned to me.  What the heck??

[KevinM1]

Unfortunately, it looks like I can't edit my previous post, so forgive this bump.

 

It appears as though my joins are multiplying the number of rows from one table with another.  In other words, when I try a simple join like:

SELECT * FROM gaming_peripherals, office_peripherals;

 

Instead of getting 23 rows back (14 from the first table, 9 from the second), I get 126 back (14 x 9).

[KevinM1]

Damned cross joins....topic solved!