PHP/MYSQL. Combine results from 2 tables?

Let's say that I have an uploaded video.

The video has [uid] which is a users ID within the site.

The variable [uid] is from another table called 'users'

And what if I want to display a video and the users name with that video?

This is the code I use for displaying latest videos

<?php

// Create a connection to your database.

// Query database and select the last 10 entries.
$data = mysql_query("SELECT * FROM video ORDER BY id DESC LIMIT 50");
while($row = mysql_fetch_array($data))
{

echo "

".$row[title]."

";

}
?>

Code (markup):

so my videos are stored in a table called video

that table has the number in field [uid] which refers to a users ID number that is stored in the database 'users'

and I need that users name, but all I have is his ID from the query above.

any ideas? I tried some UNION strings, but didn't work.

This forum is great btw for beginning developers, I really appreciate all the help I'm getting from this board, you're awesome.

 

inner join will work for you

SELECT * FROM videos a INNER JOIN users b WHERE a.uid = b.uid

 

Thanks a lot, that query works

SELECT * FROM video a INNER JOIN users b WHERE a.uid = b.id ORDER BY a.id DESC LIMIT 5

This displays the 5 latest videos in the mysql table with the usernames I need.

But now I can't seem to make it display within the php code

I tried both ".$row[username]." and ".$row[a.username]." - no difference.

 

that seems odd, try putting quotes around your result vars

echo $row["username"] or $row['username']

 

oh crap, use the field names that you want to get instead of *

ex, SELECT a.uid, b.username, field, field etc

just append the alias of the table beside each field to make sure you dont get an ambigouous field error

 

Thanks!

Now I can combine results from any database.

I hope this topic helps others too

 

Hello,

If you have Id of that user than you can make another function which returns the user name from the user table

Try this

example this is the uId fromthe video tables
then

$usrName = getUserName($uId);

fuction getUserName($uId)
{
$selectQuery = "SELECT username from user where id='$uId'";
$resultQuery = mysql_query($selectQuery);
$rowQuery = @mysql_fetch_array($resultQuery);
$usernam = $rowQuery['username'];
return $usernam;

}

PHP:

This will do it..

Regards
Stylesofts Developing Team

 

Less cluttered function:

fuction getUserName($uId)
{
$rowQuery = mysql_fetch_array(mysql_query("SELECT username from user where id='$uId'"),MYSQL_BOTH);
$user = $rowQuery['username'];
return $user;
}

PHP: