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