Page 2 - PHP-General - Cant get a select list working with joined table. HELP

yes i tried that

<?php
$query2 = sprintf("
SELECT DISTINCT
stock.stockID, size.Size
FROM
poochieProd AS prod
LEFT JOIN poochieStock AS stock ON Prod.ID = stock.ID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE
poochieProd.ProdID = '%s' AND stock.stock > 0
ORDER BY
size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int"));
$results2 = mysql_query($query2);
while($row2 = mysql_fetch_array($results2)){
?>

and got the following error?

Error in query: SELECT DISTINCT stock.stockID, size.Size FROM poochieProd AS prod LEFT JOIN poochieStock AS stock ON Prod.ID = stock.ID LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID WHERE poochieProd.ProdID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Unknown table 'poochieProd' in where clause

That is correct, but the table "Prod" doesn't exist because you declared the table as "prod". It's case sensitive so you have to keep it in lowercase throughout the string.

i added the poochieProd.ProdID

and it returned

Unknown table 'poochieProd' in where clause

Post the output from 'show tables' on that database.

sorry how is that done?

thanks

The easiest way is to use the mysql command then type:
use <database name>;
show tables;

just trying how to figure out how to do this

Are you on Windows or *NIX?

can i do that from phpMyAdmin?

windows

Yes you can do that from PHPAdmin.

i opened up the SQL box

Run SQL query

and typed

use <dbpoochie>;
show tables;

but it gave me

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 14
STR: >;
SQL: use <dbpoochie>;use <dbpoochie>;use <dbpoochie>;

SQL query:

use <dbpoochie>;

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<dbpoochie>' at line 1

i have included all the values of the tables if that helps

Table name: poochieCat
CatID - int(11) - auto_increment
name - varchar(200)
image - varchar(200)

Table name: poochieProd
ProdID - int(11) - auto_increment
Prodname - varchar(200)
ProdDesc - text
CatID - varchar(80)



Table name: poochieSizes
SizeID - int(11) - auto_increment
Size - varchar(200)

Table name: poochieStock

stockID - int(11) - auto_increment
ProdID - varchar(80)
sizeID - varchar(80)
stock - varchar(30)
sold - varchar(10)


its that ok?

That's not what I said. Your statement declared the table "poochieProd AS prod" but then in your first join, you called your table "Prod" with a capital "P". If you declare your table as "prod" all in lower case, you need to call your table in all lower case every time, you can't change the case of your table name in the string. Also, once you've declared it as "prod" I believe you have to call it by the name you have declared; you can't go back to the real table name once you've declared it as something different. You're also referencing two different id fields (prod.ID and prod.ProdID) for the poochieProd table and your last post only indicated one. So, based on the last query string you posted, keeping the case the same would look like this.

ok thanks i have changed that and it is now giving me another error

Error in query: SELECT DISTINCT stock.stockID, size.Size FROM poochieProd AS prod LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.ID LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID WHERE prod.ProdID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Unknown column 'stock.ID' in 'on clause'