Fetching data from two different tables

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
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Fetching data from two different tables

Post by JKM »

I there!

I'm having some problems with getting a username from the table "users" when I'm having the userid from the table "ranking". The echo is empty.

Code: Select all

<?php
$sql = mysql_query("SELECT userid, score FROM ranking ORDER BY id desc LIMIT 10"); // finding the top 10 ranked users
$query = $sql or die(mysql_error());
    while($fetch = mysql_fetch_array($query)) {
        $user_sql = mysql_query("SELECT user_name FROM users WHERE userid='".$fetch['userid']."'"); // finding the username by echoing the userid (that I'm getting from the other table - ranking.
        $user_query = $user_sql or die(mysql_error());
        $user_fetch = mysql_fetch_array($user_query);
    ?>
    <tr>
        <td>&raquo; <a target="_blank" href="profile.php?user=<?php echo $fetch['userid'];?>"><?php echo $user_fetch['user_name']; ?></a></td>
    </tr>
<?php } ?>
Any help? :)
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Fetching data from two different tables

Post by jaoudestudios »

Code: Select all

 
<?php
$sql = mysql_query("SELECT userid, score FROM ranking ORDER BY id desc LIMIT 10"); // finding the top 10 ranked users
$query = $sql or die(mysql_error());
    while($fetch = mysql_fetch_array($query)) {
        $user_sql = mysql_query("SELECT user_name FROM users WHERE userid='".$fetch['userid']."'"); // finding the username by echoing the userid (that I'm getting from the other table - ranking.
        $user_query = $user_sql or die(mysql_error());
        $user_fetch = mysql_fetch_array($user_query);
    ?>
    <tr>
        <td>&raquo; <a target="_blank" href="profile.php?user=<?php echo $fetch['userid'];?>"><?php echo $user_fetch['user_name']; ?></a></td>
    </tr>
<?php } ?>
 
You can combine those queries into 1 using a JOIN. It will make your life easier.
max529
Forum Commoner
Posts: 50
Joined: Sat May 19, 2007 4:10 am

Re: Fetching data from two different tables

Post by max529 »

May be this is not answer to your question,but there is a MySQL wrapper Class ,CRDB. that can shrink the code you sent to the following

Code: Select all

 
<?php
foreach(  $crdb->ranking()->desc_order_by()->id->limit(0,10) as $v)
  {
  $user_row=$crdb->users->userid($v->userid);
   echo $user_row->userid;
   echo $user_row->user_name;   
  }
?>
 
Download CRDB from http://www.phpclasses.org/browse/package/5221.html
Last edited by max529 on Tue Mar 31, 2009 4:59 am, edited 1 time in total.
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Fetching data from two different tables

Post by JKM »

jaoudestudios wrote:

Code: Select all

 
<?php
$sql = mysql_query("SELECT userid, score FROM ranking ORDER BY id desc LIMIT 10"); // finding the top 10 ranked users
$query = $sql or die(mysql_error());
    while($fetch = mysql_fetch_array($query)) {
        $user_sql = mysql_query("SELECT user_name FROM users WHERE userid='".$fetch['userid']."'"); // finding the username by echoing the userid (that I'm getting from the other table - ranking.
        $user_query = $user_sql or die(mysql_error());
        $user_fetch = mysql_fetch_array($user_query);
    ?>
    <tr>
        <td>&raquo; <a target="_blank" href="profile.php?user=<?php echo $fetch['userid'];?>"><?php echo $user_fetch['user_name']; ?></a></td>
    </tr>
<?php } ?>
 
You can combine those queries into 1 using a JOIN. It will make your life easier.
Could you please show me how? :D
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Fetching data from two different tables

Post by jaoudestudios »

Code: Select all

<?php
$sql = mysql_query("SELECT userid, score FROM ranking ORDER BY id desc LIMIT 10"); // finding the top 10 ranked users
$query = $sql or die(mysql_error());
    while($fetch = mysql_fetch_array($query)) {
        $user_sql = mysql_query("SELECT user_name FROM users WHERE userid='".$fetch['userid']."'"); // finding the username by echoing the userid (that I'm getting from the other table - ranking.
        $user_query = $user_sql or die(mysql_error());
        $user_fetch = mysql_fetch_array($user_query);
    ?>
    <tr>
        <td>&raquo; <a target="_blank" href="profile.php?user=<?php echo $fetch['userid'];?>"><?php echo $user_fetch['user_name']; ?></a></td>
    </tr>
<?php } ?>
 
...to...

Code: Select all

 
SELECT ranking.userid, ranking.score, users.user_name 
FROM ranking
JOIN users ON ranking.userid = users.userid
ORDER BY ranking.id DESC LIMIT 10
 
NB: you should use id in each table, so therefore it becomes more logical. i.e. ranking.id, user.id
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Fetching data from two different tables

Post by JKM »

Thanks a lot! It worked great! :D

But how should I solve this one?

Code: Select all

SELECT forum.userid, forum_thread.name, users.user_name
FROM forum
JOIN forum_thread, users ON forum.category_id = forum_thread.post_category AND forum.userid = users.user_id
ORDER BY forum_thread.name DESC LIMIT 10
I've tried both

Code: Select all

forum.category_id = forum_thread.post_category AND forum.userid = users.user_id
and

Code: Select all

forum.category_id = forum_thread.post_category, forum.userid = users.user_id
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Fetching data from two different tables

Post by jaoudestudios »

Glad it worked.

Here is a link to the documentation. Here you will learn all the tricks.
http://dev.mysql.com/doc/refman/5.0/en/join.html
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Fetching data from two different tables

Post by JKM »

Another similar question!

Code: Select all

SELECT <schema>1.<table>1.<field>1, <schema>2.<table>2.<field>2
FROM <schema>1.<table>1
JOIN <schema>1.<table>1, <schema>2.<table>2 ON <schema>1.<table>1.<field>1 = <schema>2.<table>2.<field>2
example:

Code: Select all

SELECT schema1.forum.users, schema2.users.user_info
FROM schema1.forum
JOIN schema1.forum, schema2.users ON schema1.forum.users = schema2.users.user_info
Could anyone please explain how to check if two rows is matching - crossing schemes?
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Fetching data from two different tables

Post by JKM »

Anyone? :-)
FyreHeart
Forum Newbie
Posts: 5
Joined: Tue Feb 17, 2009 12:27 am

Re: Fetching data from two different tables

Post by FyreHeart »

Your example has a redundant JOIN in there. The comma (,) effectively acts like a JOIN.

To match across databases (schemas), you simply prepend the database name to the table. As with same-schema JOINs, there has to be a common field between the two database.tables to JOIN on. So, to expand your example:

Code: Select all

SELECT schema1.forum.users, schema2.users.user_info
FROM schema1.forum INNER JOIN schema2.users
ON schema1.forum.users_id = schema2.users.user_id
Don't let the INNER JOIN confuse you - it's the same as JOIN. I just use full ANSI syntax because I regularly cross database vendors.

-John
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Fetching data from two different tables

Post by JKM »

Hmm, thanks - but how should I solve this issue?

Code: Select all

mysql_connect('xxx','xxx','xxx') or die("mysql error");
mysql_select_db("scheme1") or die("mysql error");
 
$query1 = mysql_query("SELECT id FROM table1 WHERE user_name='".$un."'") or die(mysql_error());
$myrow = mysql_fetch_array($query1);
 
if(mysql_num_rows($query1) > 0) {
    echo 'Already existing username.';
    mysql_close();
    
    mysql_connect('xxx','xxx','xxx') or die("mysql error");
    mysql_select_db("scheme2") or die("mysql error");
    mysql_query("INSERT INTO table (id, value, status) VALUES ('','$value','0')");
} else {
    echo 'Added.';
    mysql_query("INSERT INTO table (id, value) VALUES ('','$value','1')");
}
It doesn't say "Already existing username" - even though it's existing.
Post Reply