Help With PHP to MYSQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
greyday
Forum Newbie
Posts: 5
Joined: Mon Sep 25, 2006 6:07 pm

Help With PHP to MYSQL Query

Post 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)
shneoh
Forum Commoner
Posts: 38
Joined: Mon Sep 25, 2006 2:23 am
Location: Malaysia

Post by shneoh »

2 simple ways to solve:

1. Add an initial value to Table3.
2. Split your query into 2.
greyday
Forum Newbie
Posts: 5
Joined: Mon Sep 25, 2006 6:07 pm

Post 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.
shneoh
Forum Commoner
Posts: 38
Joined: Mon Sep 25, 2006 2:23 am
Location: Malaysia

Post 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:
shneoh
Forum Commoner
Posts: 38
Joined: Mon Sep 25, 2006 2:23 am
Location: Malaysia

Post by shneoh »

Oh, I just see a better option in PHP Code forum... Maybe it helps. :)

viewtopic.php?t=56160
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post 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).
Post Reply