Page 1 of 2
SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Wed Oct 03, 2012 9:33 am
by jonnyfortis
hello i have a select list displaying sizes from a mySQL db and it doesnt work, can someone look at my code and tell me where i have gone wrong
Code: Select all
$var1_rsProdList = "-1";
if (isset($_GET['recordID'])) {
$var1_rsProdList = $_GET['recordID'];
}
mysql_select_db($database_poochie, $poochie);
$query_rsProdList = sprintf("SELECT * FROM poochieProd, poochieCat WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = %s", GetSQLValueString($var1_rsProdList, "int"));
$query_limit_rsProdList = sprintf("%s LIMIT %d, %d", $query_rsProdList, $startRow_rsProdList, $maxRows_rsProdList);
$rsProdList = mysql_query($query_limit_rsProdList, $poochie) or die(mysql_error());
$row_rsProdList = mysql_fetch_assoc($rsProdList);
if (isset($_GET['totalRows_rsProdList'])) {
$totalRows_rsProdList = $_GET['totalRows_rsProdList'];
} else {
$all_rsProdList = mysql_query($query_rsProdList);
$totalRows_rsProdList = mysql_num_rows($all_rsProdList);
}
$totalPages_rsProdList = ceil($totalRows_rsProdList/$maxRows_rsProdList)-1;
$var3_Recordset1 = "-1";
if (isset($_GET['ProdID'])) {
$var3_Recordset1 = $_GET['ProdID'];
}
mysql_select_db($database_poochie, $poochie);
$query_Recordset1 = sprintf("SELECT * FROM poochieProd, poochieStock, poochieSizes WHERE poochieStock.sizeID = poochieSizes.SizeID AND poochieProd.ProdID = %s", GetSQLValueString($var3_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $poochie) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
and this is where the select list is
Code: Select all
<select name="os0" class="text" id="selectSize">
<option value="Select Size">Select Size</option>
<?php
$query2 = sprintf("
SELECT DISTINCT
stock.stockID, size.Size
FROM
poochieProd AS prod
LEFT JOIN pochieStock AS stock ON prod.ID = stock.ID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE
prod.ID = '%s' AND stock.Stock > 0
ORDER BY
size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int"));
$results2 = mysql_query($query2);
while($row2 = mysql_fetch_array($results2)){
?>
<option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
<?php
}
?>
</select>
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Wed Oct 03, 2012 10:36 am
by jonnyfortis
i have run the script and i am getting the following error in the output
<b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/users1/jonfort/public_html/designfreak/pooch/product-detail.php on line 132
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Wed Oct 03, 2012 12:30 pm
by Mordred
echo mysql_error() after the query call?
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Wed Oct 03, 2012 12:35 pm
by jonnyfortis
i have echo out
<?php $results2 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error()); ?>
and got the following 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 Prod.ID = '-1' AND stock.stock > 0 ORDER BY size.SizeID ASC. Unknown table 'Prod' in where clause
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Wed Oct 03, 2012 12:58 pm
by Mordred
prod and Prod are two different names, follow the same capitalization. Interestingly, in your original posting they seem fine
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Wed Oct 03, 2012 1:04 pm
by jonnyfortis
this is what i was thinking but dont know what one goes where
the tables are as follows
poochieCat
CatID
poochieProd
ProdID
CatID
poochieProd
ProdID
CatID
poochieSizes
SizeID
Size
poochieStock
stockID
ProdID
sizeID
stock
Code: Select all
<select name="os0" class="text" id="selectSize">
<option value="Select Size">Select Size</option>
<?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
prod.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)){
?>
<option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
<?php
}
?>
</select>
i have been looking but cant seem to see
thanks in advance
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Thu Oct 04, 2012 2:46 am
by Mordred
Pick a text editor with a case sensitive search and use it, the mysql_error output would tell you what to search for.
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Thu Oct 04, 2012 3:51 am
by jonnyfortis
ok i have (i think) corrected the statement
Code: Select all
<select name="os0" class="text" id="selectSize">
<option value="Select Size">Select Size</option>
<?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.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)){
?>
<option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
<?php
}
?>
</select>
there are now no errors being shown using
<?php $results2 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error()); ?>
however no sizes are being shown either
can you give me some more advice??
thanks so much in advance
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Thu Oct 04, 2012 5:06 am
by Mordred
Copy the query to phpMyAdmin and see if it gives any results there - thus you'll know if the problem is in the query, the database or the output code
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Thu Oct 04, 2012 7:59 am
by jonnyfortis
Hello
I copied the below query to the phpMyAdmin
Code: Select all
<?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.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 it returned the following
Code: Select all
Error
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 @ 7
STR: <?
SQL: <?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.ProdID = '%s' AND stock.stock > 0
ORDER BY size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int")); <?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.ProdID = '%s' AND stock.stock > 0
ORDER BY size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int")); <?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.ProdID = '%s' AND stock.stock > 0
ORDER BY size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int")); <?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.ProdID = '%s' AND stock.stock > 0
ORDER BY size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int"));
SQL query:
<?php $query2 = sprintf(" SELECT DISTINCT stock.stockID, size.Size FROM poochieProd AS prod LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID WHERE prod.ProdID = '%s' AND stock.stock > 0 ORDER BY size.SizeID ASC", GetSQLValueString($var3_Recordset1, "int"));
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 '<?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
' at line 1
i take it this means theres lots wrong?
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Thu Oct 04, 2012 8:27 am
by jonnyfortis
sorry i have done this again CORRECTLY
and got the following results
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.ProdID = '%s' AND stock.stock > 0
ORDER BY size.SizeID ASC
and replaced the %s with a 5 because i know there is a product id in the DB with the value of 5
i got these results
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.ProdID = '5'
AND stock.stock >0
ORDER BY size.SizeID ASC
LIMIT 0 , 30
so its saying its empty even though there is a record in there?
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Thu Oct 04, 2012 10:12 am
by Mordred
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.stockID
should be
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.ProdID
?
Also, your naming conventions are inconsistent - fix that while your codebase is small.
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Thu Oct 04, 2012 4:46 pm
by jonnyfortis
hello
i have changed my SQL to
Code: Select all
<?php
$query2 = sprintf("
SELECT DISTINCT stock.stockID, size.Size
FROM poochieProd AS prod
LEFT JOIN poochieStock AS stock ON prod.ProdID = stock.ProdID
LEFT JOIN poochieSizes AS size ON stock.SizeID = size.SizeID
WHERE prod.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)){
?>
but am still not getting the sizes?. with regards to my naming convention do you mean just change everything to lowercase?
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Fri Oct 05, 2012 5:02 am
by jonnyfortis
sorted it and started from scratch
just joined all the table in one query and echoed out what i needed too.....i was over complicating things
Code: Select all
$query_rsProdList = sprintf("SELECT * FROM poochieProd, poochieCat, poochieSizes, poochieStock WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = poochieStock.ProdID AND poochieSizes.SizeID = poochieStock.sizeID AND poochieProd.ProdID = %s ", GetSQLValueString($var1_rsProdList, "int"));
<select name="os0" class="text" id="selectSize">
<option value="Select Size">Select Size</option>
<?php do { ?>
<option value="<?php echo $row_rsProdList['SizeID']; ?>"><?php echo $row_rsProdList['Size']; ?></option>
<?php } while ($row_rsProdList = mysql_fetch_assoc($rsProdList)); ?></select>
thanks for everybodys help
Re: SELECT DISTINCT LEFT JOIN select list ERROR
Posted: Fri Oct 05, 2012 5:05 am
by jonnyfortis
WAIT.....the thing i need to add though is if the stock = 0 dont display size or show a "sold out" next to the size
thanks in advance