Joining Tables

[bullbreed]

I have 2 tables in my database that I want to pull the information out and show it on a page relative to s username.

 

What would be the code I need to insert in to the SELECT FROM section of the following code to join a table called 'users' with a table called ''trainingdata' associated by the 'username'

<table>
<tr>
    	<th>Name</th>   
        <th>Username</th>
        <th>Email</th>
        <th>Training Required</th>
        <th>Date Submitted</th> 
    </tr>

<?php
$sql = "SELECT `name`, `username`, `email` FROM users WHERE `userlevel`=1";
$query = mysql_query($sql) or die(myslq_error());

while($row = mysql_fetch_assoc($query)){
?>
<tr>
    	<td><?php echo $row['name']; ?></td>
        <td><?php echo $row['username']; ?></td>
        <td><?php echo $row['email']; ?></td>
        <td><?php echo $row['description']; ?></td>
        <td><?php echo $row['date']; ?></td>
    </tr>
<?php
}
?>
</table>

 

[Buddski]

This isnt really a PHP question its a MySQL question. But since your here..

 

SELECT * FROM `users` Inner Join `trainingdata` USING (`username`) WHERE `userlevel` = 1

The above sql will only grab users that have data in both the users AND traningdata tables.

 

If you want to grab all the users anyway and only get the trainingdata if it exists you can replace the Inner Join with Left Join

 

[bullbreed]

I kind of know what you mean.

 

I have a row of information  in a page called clients.php.

This row shows some of the data held in the 'users' table in the db.

At the end of this row is a 'more' link like this

Name-----------------Username-------------Email-----------------More Info

Bob--------------------Bobby01--------------bob@bob.com------More

 

When the 'More' link is clicked it will go to the clientinfo.php page like this

<?php echo '<a href="clientinfo.php?username='.$row['username'].'">More</a>' ?>

 

I want this new page to get

 

name----username----email

from the users table

 

and

 

description----date

from the trainingdata table

 

Relative to the user in the link.

 

Baffled? I know I am. lol   

[Buddski]

Baffled? No..

$sql = "SELECT 
`users`.`name`,
`users`.`email`,
`users`.`username`,
`trainingdata`.`date`,
`trainingdata`.`description
FROM `users`
Inner Join `trainingdata` USING (`username`)
WHERE `users`.`username`='".$_GET['username']."'
LIMIT 1";
$sql_query = mysql_query($sql) or trigger_error(mysql_error());

if (mysql_num_rows($sql_query) > 0) {
$data = mysql_fetch_assoc($sql_query);
print_r($data);
}

Have a go with that.

[bullbreed]

wow. how good are you. 

 

how do I get that information in a table

 

Heres my code;

<table>
<tr>
    	<th>Name</th>   
        <th>Username</th>
        <th>Email</th>
        <th>Training Required</th>
        <th>Date Submitted</th> 
    </tr>

<?php
$sql = "SELECT `users`.`name`,`users`.`username`,`users`.`email`,`trainingdata`.`description`,`trainingdata`.`date` FROM `users` INNER JOIN `trainingdata` USING (`username`)
WHERE `users`.`username`='".$_GET['username']."' LIMIT 1";
$sql_query = mysql_query($sql) or trigger_error(mysql_error());

if (mysql_num_rows($sql_query) > 0) {
$data = mysql_fetch_assoc($sql_query);

}
?>
<tr>
    	<td><?php echo $row['name']; ?></td>
        <td><?php echo $row['username']; ?></td>
        <td><?php echo $row['email']; ?></td>
        <td><?php echo $row['description']; ?></td>
        <td><?php echo $row['email']; ?></td>
    </tr>

</table>

[bullbreed]

Think I did it woohoo

<table>
<tr>
    	<th>Name</th>   
        <th>Username</th>
        <th>Email</th>
        <th>Training Required</th>
        <th>Date Submitted</th> 
    </tr>

<?php
$sql = "SELECT `users`.`name`,`users`.`username`,`users`.`email`,`trainingdata`.`description`,`trainingdata`.`date` FROM `users` INNER JOIN `trainingdata` USING (`username`)
WHERE `users`.`username`='".$_GET['username']."' LIMIT 1";
$sql_query = mysql_query($sql) or trigger_error(mysql_error());

if (mysql_num_rows($sql_query) > 0) {
$data = mysql_fetch_assoc($sql_query);
}

?>
<tr>
    	<td><?php echo $data['name']; ?></td>
        <td><?php echo $data['username']; ?></td>
        <td><?php echo $data['email']; ?></td>
        <td><?php echo $data['description']; ?></td>
        <td><?php echo $data['email']; ?></td>
    </tr>

</table>

 

[Buddski]

if (mysql_num_rows($sql_query) > 0) {
$data = mysql_fetch_assoc($sql_query);
echo '<tr>
<td>'.$data['name'].'</td>
<td>'.$data['username'].'</td>
<td>'.$data['email'].'</td>
<td>'.$data['description'].'</td>
<td>'.$data['date'].'</td>
</tr>';
} else {
echo '<tr><td colspan="5">Invalid User Data</td></tr>';
}

Would do the trick

 

Edit: Notice my use of the else statement.

[bullbreed]

how cool ws that. Thanks matey.

 

now, that limit=1 you called. What of a user has 2 rows in the trainingdata table

So he had submitted 2 different descriptions against his username

[Buddski]

I would take out the limit..

But you would then have to iterate through your result set to display those details (if there is more than 1)

[bullbreed]

eh?

 

Im a bit dumb sometimes mate and its 2:00am here in the uk. lol

Could you explain a little more please

[Buddski]

Ok lets say "Bob" has 1 entry in the users table but 2 entries in the trainingdata table..

you would need to do something like this..

$sql = "SELECT 
`users`.`name`,
`users`.`email`,
`users`.`username`,
`trainingdata`.`date`,
`trainingdata`.`description
FROM `users`
Inner Join `trainingdata` USING (`username`)
WHERE `users`.`username`='".$_GET['username']."'";
$sql_query = mysql_query($sql) or trigger_error(mysql_error());

if (mysql_num_rows($sql_query) > 0) {
while ($data = mysql_fetch_assoc($sql_query)) {
echo '<tr>
<td>'.$data['name'].'</td>
<td>'.$data['username'].'</td>
<td>'.$data['email'].'</td>
<td>'.$data['description'].'</td>
<td>'.$data['date'].'</td>
</tr>';
}
} else {
echo '<tr><td colspan="5">Invalid User Data</td></tr>';
}

This, however, will produce 2 rows which you dont really want because all the data that is collected from the users table will be repeated.. You need to figure out HOW you would like to display multiple trainingdata rows to fit into your table..

 

[bullbreed]

Thats fantastic, thanks mate.

 

I see what you mean about duplicating the data. It doesn't look so bad but it is unnecessary. Although I dont think that a user will ask for more than 5 training requests.

 

How would I put a line break between the 2 results.