Tables Joins - Processing and Displaying the Resulting Array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
chrispynic99
Forum Newbie
Posts: 9
Joined: Sat Sep 12, 2009 10:06 pm

Tables Joins - Processing and Displaying the Resulting Array

Post by chrispynic99 »

Howdy,

I am having trouble wrapping my brain around joins and processing the resulting array. Here are the tables with some sample data:

table 1
-------------------------------------------
| title | description |
-------------------------------------------
| Best | This is a really good one |
| Good | This one is good too |
-------------------------------------------

table 2
--------------------------
| ID | Name |
--------------------------
| 21 | Best |
| 22 | Best |
| 23 | Best |
| 24 | Best |
| 41 | Good |
| 42 | Good |
| 43 | Good |
--------------------------

table 3
----------------------
| e_id | value |
----------------------
| 21 | 1100 |
| 22 | 1200 |
| 23 | 1100 |
| 24 | 1000 |
| 41 | 550 |
| 42 | 570 |
| 43 | 560 |
----------------------

Here is the desired output:

Best - This is a really good one - 1200
Good - This one is good too - 570

Notice that 1200 and 570 are the max values from table 3 WHERE table1.title = table2.Name AND table2.ID = table3.e_id

I just can't figure out how to display this correctly. Here is what I have so far if this helps, or you can suggest another method:

Code: Select all

 
 $result = mysql_query(
            " SELECT * from table1, table2, table3 WHERE table1.title = table2.Name AND table2.ID = table3.e_id; ");
  if (!$result) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }
 
 // Display
  while ( $row = mysql_fetch_array($result) ) {
    echo( $row["title"]." - ".$row["description"]. " - " );
  }
 
?>
 
 
Thanks in advance for your help.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Tables Joins - Processing and Displaying the Resulting Array

Post by jackpf »

Do you not just want to select `table1`.*?

What does what you've got display?
chrispynic99
Forum Newbie
Posts: 9
Joined: Sat Sep 12, 2009 10:06 pm

Re: Tables Joins - Processing and Displaying the Resulting Array

Post by chrispynic99 »

I need to pull the max value from table 3, so I can't just select table 1.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Tables Joins - Processing and Displaying the Resulting Array

Post by califdon »

Use the SQL function MAX() http://www.tizag.com/mysqlTutorial/mysqlmax.php

But even before that, your table structure doesn't make any sense to me. What entity is represented by tables 2 and 3? If you don't have clear definitions of what every table represents, you will get absolutely nowhere in relational databases. The reason you are having trouble figuring out the joins is almost surely that your tables don't represent distinct entities, as required by the relational model on which SQL and all relational database operations are based.
Post Reply