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

i am going round in circles. Maybe a fresh set of eyes can help
i have the following table joined

prod table

ID (prod)
name
CatID

Size Table

SizeID
Size

Stock Table

StockID
ID (prod)
SizeID
Stock

i am trying to populate a select list with sizes from a product




i dont know where i am going wrong,

thanks in advance for any help

You need to be more specific. Post some output or something that will help us understand the problem. What is wrong or missing? What error messages are you getting? Did you echo $query2 and then copy and paste that into a command line session?

P.S. You should consider changing to PDO rather than use the depreciated MySQL library.

ok the Output i got from the select list was

<b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/usr/users1/ort/public_html/ch/product-detail.php</b> on line <b>132</b>


and on line 132 the code is

while($row2 = mysql_fetch_array($results2)){

the full out from the select list is



<select name="os0" class="text" id="selectSize">
<option value="Select Size">Select Size</option>
<br />
<b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/usr/users1/ort/public_html/ch/product-detail.php</b> on line <b>132</b><br />
</select>

Try debugging the warning with this:

this is helping..it gave me an error regarding a spelling error

pochieStock

i corrected this to read poochieStock then it has given an 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 Prod.ID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Unknown table 'Prod' in where clause


but i am unsure what table 'Prod' the error is referring to?

You must not have a table named 'Prod' in your database (show tables). What is that particular column (Prod.ID) supposed to be?

the tables that i have and the joins are

poochieCat
CatID
name

poochieStock
stockID
ProdID
sizeID
stock

poochieSizes
SizeID
Size

poochieProd
ProdID
Prodname
CatID

the tables i included in the first post are not the correct ones from this post, i incorrectly added them so the ones on my previous ones are the correct one

I think you want to remove the dot (.).

i have changes the query to

<?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
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 am now getting the results

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 prodID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Column 'prodID' in where clause is ambiguous

so i changed this to prod.prodID and it returned

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 prod.prodID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Unknown table 'Prod' in on clause

Yes because table 'Prod' does not exist. What table do you want 'ProdID' to be taken from? It exists in more than 1.

i presume in need it to come from the poochieStock table.

i thought the poochieProd AS prod

was telling the next part to see poochieProd as prod so

WHERE prod.ProdID would see this?

Nope. Use poochieProd.ProdID