[SOLVED] Help!! with MySQL 5.0.45 - JOIN nightmare!

[banacan]

Hi All,

 

Noobie here, and I really need help. 

 

I want to create a product_list page where all of the details of each record are visible so I can check that they were entered properly.  The problem is that 4 of the 10 fields in the table are foreign keys and their values were set using drop-down lists. I can show the value of each field but the foreign keys are numeric and I want to show their  text value from their respective tables.  Below is my original SQL which shows the numeric values.

 

> SELECT *

> FROM products

> ORDER BY products.mod_num

 

I'm afraid this is way beyond my ability right now, therefore I hope someone will be able to help me create this seemingly impossible join.  Below is the full SQL as I have it now.

 

> SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name

> FROM products, `group`, category, divisions, segment

> WHERE products.group_id = `group`.group_id AND products.cat_id = category.cat_id AND products.div_id = divisions.div_id AND products.seg_id = segment.seg_id

> ORDER BY products.mod_num

 

This works except for the fields that allow null values. Plus, the above SQL has the AND in the WHERE clause which means it only shows records in which all are present:

 

Following are the Tables and their fields:

 

PRODUCTS  NULL  DIVISIONS    GROUP          SEGMENT        CATEGORY

 

prod_id    n    div_id        group_id      seg_id          cat_id                                mod_num    n    div_name      group_name    seg_name        cat_name            div_id      n

group_id    n

seg_id      y

cat_id      y

title      y

desc        y

thick      y

width      y

height      y

project    y

image      n

 

As you can see, of the foreign keys, only div_id and group_id are not null, seg_id and cat_id allow null.  I tried to create a left join but it didn't work because I need to join more than two tables:

 

> SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name

> FROM products left join `group`, category, divisions, segment

> ON products.group_id = `group`.group_id AND products.cat_id = category.cat_id AND products.div_id = divisions.div_id AND products.seg_id = segment.seg_id

> ORDER BY products.mod_num

 

Any help would be greatly appreciated.

 

NOTE: I don't know how to get the table definitions to align properly so it may require adjusting the browser window.  Sorry.

[revraz]

You may want to use a LEFT JOIN instead.

[banacan]

Thanks for the quick reply.  I did try a LEFT JOIN as seen in the last SQL statement, but it didn't work.  Can you tell me what I need to do to make it work?

[revraz]

I may be wrong, but I don't think you can group your tables like that with one JOIN.  Also, you should rename the GROUP fieldname, it will cause you headaches.

 

Try something like

 

SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name

FROM products LEFT JOIN `group` ON (products.group_id = `group`.group_id) LEFT JOIN category ON (products.cat_id = category.cat_id) LEFT JOIN divisions ON (products.div_id = divisions.div_id) LEFT JOIN segment ON (products.seg_id = segment.seg_id) ORDER BY products.mod_num;

 

[banacan]

revraz,

 

Thanks again for your quick response.

 

First, I noticed that `group` and `desc` are the only field names that have the backtick.  Why is that?  Are they special in some way?

 

Second,  I tried the SQL as you suggested

 

SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name

FROM products LEFT JOIN `group` ON (products.group_id = `group`.group_id) LEFT JOIN category ON (products.cat_id = category.cat_id) LEFT JOIN divisions ON (products.div_id = divisions.div_id) LEFT JOIN segment ON (products.seg_id = segment.seg_id) ORDER BY products.mod_num;

 

but I got the following error:

 

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 '; LIMIT 0, 20' at line 1

 

What does that mean?

 

[revraz]

That wouldn't be an error on that querry, since there is no LIMIT on it.

 

Backticks go around fieldnames to prevent problems with reserved names.  GROUP is a reserved name so it requires backticks unless you change the name.

[banacan]

I'll change the name from GROUP to GRP.  Thanks for the heads-up.

 

That is exactly the SQL query, what else could it mean?

[revraz]

Paste the code a few lines before and after the query.  Sounds like something sneaking in there.

[banacan]

revraz,

 

Thanks.  Here's what I've got.

 

mysql_select_db($database_belleadmin, $belleadmin);

$query_listProducts = "SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name FROM products LEFT JOIN `group` ON (products.group_id = `group`.group_id) LEFT JOIN category ON (products.cat_id = category.cat_id) LEFT JOIN divisions ON (products.div_id = divisions.div_id) LEFT JOIN segment ON (products.seg_id = segment.seg_id) ORDER BY products.mod_num;";

$query_limit_listProducts = sprintf("%s LIMIT %d, %d", $query_listProducts, $startRow_listProducts, $maxRows_listProducts);

$listProducts = mysql_query($query_limit_listProducts, $belleadmin) or die(mysql_error());

$row_listProducts = mysql_fetch_assoc($listProducts);

[revraz]

Your query is calling the wrong variable

 

$listProducts = mysql_query($query_limit_listProducts, $belleadmin) or die(mysql_error());

 

It uses $query_limit_listProducts, not $query_listProducts.

 

And that only contains

 

$query_limit_listProducts = sprintf("%s LIMIT %d, %d", $query_listProducts, $startRow_listProducts, $maxRows_listProducts);

 

Which is why it errors.

[banacan]

Thanks revraz,

 

I think I did not copy enough of the code to show you what was happening.  Here is more of the code surrounding the SQL:

 

$maxRows_listProducts = 20;

$pageNum_listProducts = 0;

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

  $pageNum_listProducts = $_GET['pageNum_listProducts'];

}

$startRow_listProducts = $pageNum_listProducts * $maxRows_listProducts;

 

mysql_select_db($database_belleadmin, $belleadmin);

$query_listProducts = "SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name FROM products LEFT JOIN `group` ON (products.group_id = `group`.group_id) LEFT JOIN category ON (products.cat_id = category.cat_id) LEFT JOIN divisions ON (products.div_id = divisions.div_id) LEFT JOIN segment ON (products.seg_id = segment.seg_id) ORDER BY products.mod_num;";

$query_limit_listProducts = sprintf("%s LIMIT %d, %d", $query_listProducts, $startRow_listProducts, $maxRows_listProducts);

$listProducts = mysql_query($query_limit_listProducts, $belleadmin) or die(mysql_error());

$row_listProducts = mysql_fetch_assoc($listProducts);

 

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

  $totalRows_listProducts = $_GET['totalRows_listProducts'];

} else {

  $all_listProducts = mysql_query($query_listProducts);

  $totalRows_listProducts = mysql_num_rows($all_listProducts);

}

$totalPages_listProducts = ceil($totalRows_listProducts/$maxRows_listProducts)-1;

 

The LIMIT 0, 20 is part of recordset paging, so I don't know why it is conflicting with the listProducts query.  Isn't that a separate query? 

 

My brain is too fried after a long day.  I think I'll sleep on it and give it another try tomorrow.

 

Thanks for all of your help.  At least I feel I have a better understanding of how to construct a proper LEFT JOIN, which I clearly didn't know before.  I appreciate your help.

[revraz]

$query_limit_listProducts = sprintf("%s LIMIT %d, %d", $query_listProducts, $startRow_listProducts, $maxRows_listProducts);

echo $query_limit_listProducts;

exit;

$listProducts = mysql_query($query_limit_listProducts, $belleadmin) or die(mysql_error());

 

Add these two lines then see what echo's out.  You are doing a query with the LIMIT first.

 

[banacan]

revraz,

 

Thank you so much for your help.  Somehow this morning when I came in and tried the query again it worked!  Maybe Apache was tired yesterday too, but all is now right with the world.

 

Thanks again for teaching me the proper way to construct a LEFT JOIN.  I can now use this with confidence.