left join help or alternatives

Report · Oct 03, 2012

let me explain what i have set up

i have a product detail page that shows the product details and the sizes, the sizes a pulled in from a table

i have the following table joined

prod table

ID (prod)

name

CatID

Size Table

SizeID

Size

Stock Table

StockID

ID (prod)

SizeID

Stock

i have a select list that gets the sizes for each product

<option value="Select Size">Select Size</option>

<?php

$query2 = sprintf("

SELECT DISTINCT

stock.StockID, size.Size

FROM

beauProd AS prod

LEFT JOIN beauStock AS stock ON prod.ID = stock.ID

LEFT JOIN beauSizeList AS size ON stock.SizeID = size.SizeID

WHERE

prod.ID = '%s' AND stock.Stock > 0

ORDER BY

size.SizeID ASC", GetSQLValueString($var1_Recordset1, "int"));

$results2 = mysql_query($query2);

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

?>

<?php

}

?>

</select>

i want to know if there is another way of getting this acheived. The reason i ask is because someone else done this code and need to understand it or recreate it using this or another method.

thanks in advance

Report · Oct 03, 2012

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

Report · Oct 03, 2012

>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

Not being a PHP expert, my guess is that the query is empty. What is the database datatype of prod.ID ?

Report · Oct 03, 2012

Not being a PHP expert, my guess is that the query is empty. What is the database datatype of prod.ID ?

ProdID	int(11)			No		auto_increment

in the poochieProd table

and

	ProdID	varchar(80)	latin1_swedish_ci		No

in the poochieStock table

Report · Oct 03, 2012

Look at your WHERE clause

>WHERE poochieProd.ProdID = '-1'

The poochieProd.ProdID is defined as an integer, yet you have wrapped the value in quotes. At one point, MySQL supported implicit conversions but not sure if it's still allowed. You're always better off to use the correct datatype syntax. Also, is there a direct relationship between poochieProd.ProdID and poochieStock.ProdID ? And you still have not answered my question about using a LEFT join.

Report · Oct 03, 2012

sorry...Does your LEFT table contain unmatched rows in the RIGHT table? That does not sound likely.

how do you mean?

Report · Oct 03, 2012

Do you understand the difference between an inner join and an outer join ? You need to use the correct join for the situation. If you don't know the difference, you can start here: http://en.wikipedia.org/wiki/Join_%28SQL%29

Report · Oct 03, 2012

>Do you need an outer join at all? Does your LEFT table contain unmatched rows in the RIGHT table?

no i dont need an out join. LEFT JOIN is correct

Report · Oct 03, 2012

Jon, a LEFT join IS an OUTER Join. Please read the link I sent.

Report · Oct 03, 2012

i have read it. and have used the join statement from previous code with the same table structure so am sure this is correct, i think the problem is due to the capitalization of some of the code

Report · Oct 03, 2012

i see what you mean do i need any join?

like i say i am using code that has worked from the same principle as before, you think maybe i should start from scratch?

Report · Oct 03, 2012

Jon, to be clear, I do not think your current problem has anything to do with OUTER vs INNER joins. I'm just trying to inform you that you always need to use and understand the correct join syntax for a given situation. Looking at your query, it seems to me that a LEFT (outer) join is not necessary.

At this point, I'm not even sure what your current problem is since you've posted several error messages. Please post the code you are currently using and the problem you are currently experiencing (error messages, wrong results, etc).

Report · Oct 03, 2012

hi Bregent, looking at it im not sure i need this.

as stated previously i have the tables

poochieCat

CatID

poochieProd

ProdID

CatID

poochieProd

ProdID

CatID

poochieSizes

SizeID

Size

poochieStock

stockID

ProdID

sizeID

stock

i need to get the sizes in a select list for each product and display it on a detail page

the code i am currently using is

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$maxRows_rsProdList = 18;

$pageNum_rsProdList = 0;

if (isset($_GET['pageNum_rsProdList'])) {

$pageNum_rsProdList = $_GET['pageNum_rsProdList'];

}

$startRow_rsProdList = $pageNum_rsProdList * $maxRows_rsProdList;

$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 the select list is

<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.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)){

?>

<?php

}

?>

</select>

i think i am over complicating things

Report · Oct 03, 2012

>i think i am over complicating things

Probably, and you didn't tell us what your current problem is. Does this page work at all now?

Your queries have some problems so let's look at each one. Please read this and make sure you understand and answer every question (?) completely.

1) What is your current problem?

2) The first query that produces the $rsProdList recordset looks fine. It uses a filter value passed in the querystring. Is that correct - you are using a querstring value called recordID?

3) The second query has some problems. It is selecting all columns from 3 tables (poochieProd, poochieStock, poochieSizes) but you are only joining 2 tables (WHERE poochieStock.sizeID = poochieSizes.SizeID). You must include another join to join poochieProd to one of the other tables or you will end up with a cartesian product. You can include that in the WHERE clause as well.

Also, best practice is to always use variables that are meaningful. Your recordset called $Recordset1 does not have a meaningful name and so it makes your code less readible.

What is the purpose of this second query? I don't see it being used anywhere.

This query filter gets its value from a querystring item called ProdID, correct?

This is from a different querystring value than the first query, correct?

The url for this page has two querystring values, correct?

Please cut and paste an example of the url for this page, including the querystring.

4) The 3rd query has this WHERE clause: WHERE prod.ProdID = '%s' AND stock.stock > 0

As I stated earlier, prod.ProdID is an integer value, but you are comparing as a string by using quotes. MySQL will probably implicitly convert, but don't take chances. Change it to :

WHERE prod.ProdID = %s AND stock.stock > 0

Report · Oct 04, 2012

>Probably, and you didn't tell us what your current problem is. Does this page work at all now?

well the when i am running the <?php $results2 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error()); ?>

there are no errors now, the SQL i am using now is

<?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 i am not getting any sizes in the select list still

> The first query that produces the $rsProdList recordset looks fine. It uses a filter value passed in the querystring. Is that correct - you are using a querstring value called recordID? YES

>The second query has some problems. It is selecting all columns from 3 tables (poochieProd, poochieStock, poochieSizes) but you are only joining 2 tables (WHERE poochieStock.sizeID = poochieSizes.SizeID). You must include another join to join poochieProd to one of the other tables or you will end up with a cartesian product. You can include that in the WHERE clause as well.

yes i was using this for the 2 joins and also the poochieProd.ProdID = var3 what for the use of the output in the select list

GetSQLValueString($var3_Recordset1, "int"));

but i guess this is wrong?

Also, best practice is to always use variables that are meaningful. Your recordset called $Recordset1 does not have a meaningful name and so it makes your code less readible. OK

What is the purpose of this second query? I don't see it being used anywhere. as mentioned i was using this to reference the select list

This query filter gets its value from a querystring item called ProdID, correct? yes

This is from a different querystring value than the first query, correct? yes

The url for this page has two querystring values, correct? im not sure what this means

>Please cut and paste an example of the url for this page, including the querystring. you mean a url to show the webpage where this is not working?

>

4) The 3rd query has this WHERE clause: WHERE prod.ProdID = '%s' AND stock.stock > 0

As I stated earlier, prod.ProdID is an integer value, but you are comparing as a string by using quotes. MySQL will probably implicitly convert, but don't take chances. Change it to :

WHERE prod.ProdID = %s AND stock.stock > ok so i will delete the '' around the %s, correct?

thanks so much for you help so far, im sure there is an easier way of doing this and i have over complicated things

Report · Oct 04, 2012

>>What is the purpose of this second query? I don't see it being used anywhere.

>as mentioned i was using this to reference the select list

It is not being used anywhere in the code you posted. The select list has its own query that does not reference any part of the second query.

>>The url for this page has two querystring values, correct?

>im not sure what this means

I mean that the url for this page must contain values for ProdID and recordID for these queries to work

>Please cut and paste an example of the url for this page, including the querystring.

>>you mean a url to show the webpage where this is not working?

Yes. It does not have to be a complete url - you can change the address if you want. But I want to see how the querystring is formatted. It should look something like:

mypage.php?Item1=1&Item2=2

So cut and paste it into this thread.

Report · Oct 05, 2012

Hello again...right....i stepped back and have 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

$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"));

<option value="Select Size">Select Size</option>

<?php do { ?>

<?php } while ($row_rsProdList = mysql_fetch_assoc($rsProdList)); ?></select>

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 for help

Report · Oct 03, 2012

Do you need an outer join at all? Does your LEFT table contain unmatched rows in the RIGHT table? That does not sound likely.

Report · Oct 03, 2012

i have been doing debuggin since my post and am getting the following errors

these are the tables in the DB

poochieCat

CatID

poochieProd

ProdID

CatID

poochieProd

ProdID

CatID

poochieSizes

SizeID

Size

poochieStock

stockID

ProdID

sizeID

stock

Below is the select list contining the query

<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

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)){

?>

<?php

}

?>

</select>

and this is the error that is echoing

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

Report · Oct 03, 2012

>Unknown table 'poochieProd' in where clause

It's unknown because you created an alias for it in the FROM clause. You still have not answered as to why you are using LEFT joins.

Report · Oct 03, 2012

what in the poochieProd AS prod

i tried just changing this to poochieProd and deleting the AS prod

then changing the

WHERE

poochieProd.ProdID = '%s' AND stock.stock > 0