displaying recoirds if 2 tables

[jeff5656]

I want to display a bunch of records from table 1.  Table 1 has a field called "user_id".

In table 2 i have id and name.

 

Hoiw do I query table 1 and then display the name of the user?

 

I have

$query = "SELECT * from table1 where status = '$status' ORDER by ????";
$results = mysql_query($query);
while $row = $mysql_fetch_array($results){
echo "status is "$row['status']." and work type is ".$row['work_type']. "for the user" $row['name']."<br>"";
}

Obviosuly thius wouldnt work but basically I want to pull everything fromt able 1, but sort by the name which is located in table 2.  The only thing I need from table two is the name associated with user_id.

Oh I ALSO want to keep all records from table1 with the same user_id on 1 line (users in table2 are unique)!

 

[turkman]

This is a good reference, think the example it gives solves your exact problem.

 

http://www.w3schools.com/sql/sql_join.asp

 

[jeff5656]

No Join doesn't really solve the problem because in table 1 I have bob smith and table 2 I have 20 records with the user_id associated with Bob smith. So if I did the JOIN query and spit out the records in a while loop I would get:

Bob smith info from table2

Bob Smith more info from table 2

etc for 20 lines

 

instead of:

 

Bob smith - info1...all the way to 20

John Higgins - info on john higgins from table 2.

 

Hope that is clear?  A join puts all the queried records on a separate line, even if the info from table 1 is unique (i.e. "one to many" I think is the term?)

[wildteen88]

Yes, join is the correct way. You need to add the logic within your while loop to stop if from outputting Both Smith all the time. If you require a code example I'll post one

[jeff5656]

"If you require a code example I'll post one"

Yes please!  :-):-)

 

 

[wildteen88]

For demonstration I created two tables, users and users_info

 

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `users_info` (
  `info_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `item` varchar(100) NOT NULL,
  PRIMARY KEY (`info_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

And some summy data

INSERT INTO `users` (`user_id`, `name`) VALUES
(1, 'name1'),
(2, 'name2');

INSERT INTO `users_info` (`info_id`, `user_id`, `item`) VALUES
(1, 1, 'item1'),
(2, 1, 'item2'),
(3, 1, 'item3'),
(4, 2, 'item1'),
(5, 2, 'item2'),
(6, 2, 'item3'),
(7, 1, 'item4'),
(8, 1, 'item5');

 

And the PHP code

$query = "SELECT u.user_id,
                 u.name,
                 ui.info
          FROM users u 
          RIGHT JOIN users_info ui
            ON ui.user_id = u.user_id
          ORDER BY u.name";
          
$results = mysql_query($query) or die($query . '<br >' . mysql_error());

if($results)
{
    while($row = mysql_fetch_assoc($results))
        $data[$row['name']][] = $row['item'];
    
    foreach($data as $name => $items)
    {
        echo "<p><b>$name</b>: " . implode(', ', $items) . "</p>";
    }
}

[jeff5656]

Wow you're awesome (and quick)!  Thank you.  I never would have figured out to use an array within the while loop - I will apply and modify it it to my own table and hope it works.