Page 1 of 1

Help With PHP to MYSQL Query

Posted: Mon Sep 25, 2006 6:12 pm
by greyday
I have a query that is looking at three tables, let's say those three tables have minimal data:

TABLE_1
ID
100
101

TABLE_2
ID Name
100 Sample_1
101 Sample_2

TABLE_3
ID Stock
101 5

My query looks like this:

Code: Select all

$updates = "select p.ID, pd.Name, ps.Stock from " . TABLE_1 . " p, " . TABLE_2 . " pd, " . TABLE_3 . " ps where p.ID = pd.ID and p.ID = ps.ID group by p.ID";
Displaying the results in a datarow where I want to show the ID, the Name and the Stock would show the following:

ID Name Stock
101 Sample_2 5

The problem I run into is that I want it to show ID 100 as well, but because ID 100 isn't in TABLE_3 it skips it. I would like the resulting data to look like this:

ID Name Stock
100 Sample_1
101 Sample_2 5

If ID 100 is not in table three, I would like to still display the rowdata, but have a blank value for the Stock (I am using this blank value in an If statement based on whether or not there is a stock value).

Can someone explain to me the proper syntax in the query to get this accompished? I've searched and searched and am not sure how to do this. Thanks in advance!!

(Let me know if this is confusing or you'd like more info, please, I need to get this solved)

Posted: Mon Sep 25, 2006 11:46 pm
by shneoh
2 simple ways to solve:

1. Add an initial value to Table3.
2. Split your query into 2.

Posted: Tue Sep 26, 2006 10:54 am
by greyday
Shneoh,

Thanks for the response!!

I considered number 1. Adding values of Stock 0 in Table 3 for ID's that don't show there, but that causes a large problem in an entirely different area.

I'm pretty green when it comes to this stuff, can anyone give me an example of how I can split my query into 2?

Thanks again.

Posted: Tue Sep 26, 2006 11:24 pm
by shneoh
It is just a matter of work around. :)

option 1: Just a will to know how many items you wish to display on a view... If it is only few hundred rows, I didn't see there is any problem. :) If you are consider on the stored bytes, use vchar instate of char or text. It will save you lots of space when you have a huge variant of stock value.

option 2: Query table1 and table2 then get the result from the first query to match with table3. In simple words: use 2 mysql_query. :oops:

Posted: Wed Sep 27, 2006 5:21 am
by shneoh
Oh, I just see a better option in PHP Code forum... Maybe it helps. :)

viewtopic.php?t=56160

Posted: Wed Sep 27, 2006 9:58 am
by Mordred

Code: Select all

FROM table1, table2, table3
This is a CROSS JOIN, which afaik is grossly inefficient. Use LEFT JOIN on TABLE_2 and TABLE_3 and don't mention TABLE_1 at all, you don't seem to need it (at least in this oversimplifed version you show).