Page 1 of 1

Fetching data from two different tables

Posted: Sun Mar 29, 2009 10:05 pm
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? :)

Re: Fetching data from two different tables

Posted: Mon Mar 30, 2009 12:05 am
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.

Re: Fetching data from two different tables

Posted: Mon Mar 30, 2009 3:52 am
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

Re: Fetching data from two different tables

Posted: Mon Mar 30, 2009 8:22 am
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

Re: Fetching data from two different tables

Posted: Mon Mar 30, 2009 11:51 pm
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

Re: Fetching data from two different tables

Posted: Tue Mar 31, 2009 9:33 am
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

Re: Fetching data from two different tables

Posted: Tue Mar 31, 2009 1:00 pm
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

Re: Fetching data from two different tables

Posted: Sat Apr 11, 2009 8:58 pm
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?

Re: Fetching data from two different tables

Posted: Tue Apr 21, 2009 2:09 pm
by JKM
Anyone? :-)

Re: Fetching data from two different tables

Posted: Tue Apr 21, 2009 3:11 pm
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

Re: Fetching data from two different tables

Posted: Mon Apr 27, 2009 9:38 am
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.