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>» <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>» <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>» <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?

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>» <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!
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.