Page 1 of 1
SELECT from 2 tables
Posted: Fri Dec 16, 2005 12:10 am
by waradmin
So i have a table called friends that list's a users friends. And i have a login table that has the username of the person, their profile image, etc.
So i first echo the friend_name from the table friends. Associated with that is an image located in the login table. I want to be able to display that profile picture as well as the value from the friend table.
To simplify, i need to display a value from a table, and then display the corresponding image associated with friend_uname where Uname is equal to friend_uname from another table. I need the friend_name to display as text:
Code: Select all
<?php echo $row['friend_uname']; ?>
with the friends image in the other table to display the field mypic where friend_uname (from the friend table) = Uname (from the login table) but im not sure of the SELECT syntax for that.
Thanks.
Posted: Fri Dec 16, 2005 12:12 am
by William
Why not just do 2 querys, get the ID of the user from the first query then make another query using the ID you just requested and getting the image?
Posted: Fri Dec 16, 2005 12:14 am
by waradmin
Here is what i have, and it doesnt work:
Code: Select all
<? include("config.php");
#connect to MySQL
$conn=@mysql_connect("$DBHOST", "$DBUSER", "$DBPASS")
or die("Err:Conn");
#select the specified database
$rs = @mysql_select_db("$DBNAME", $conn)
or die("Err:Db");
#create the query
$sql="SELECT * FROM friends where Uname='$user'";
#execute the query
$rs=mysql_query($sql,$conn);
#write the data
while( $row = mysql_fetch_array($rs) )
{ ?>
<?php $fimguname = $row['friend_uname']; ?>
<? include("config.php");
#connect to MySQL
$conn1=@mysql_connect("$DBHOST", "$DBUSER", "$DBPASS")
or die("Err:Conn");
#select the specified database
$rs1 = @mysql_select_db("$DBNAME", $conn)
or die("Err:Db");
#create the query
$sql1="SELECT * FROM loginphp where Uname='$fimguname'";
#execute the query
$rs1=mysql_query($sql1,$conn1);
#write the data
while( $row1 = mysql_fetch_array($rs1) )
{ ?>
<td><img src="pubimages/thumbs/thumb_<?php echo $row1['mypic']; ?>"><td><a href="template.php?user=<?php echo $row['friend_uname']; ?>"><?php echo $row['friend_uname']; ?></a><br></td>
<?php } ?><?php } ?>
Posted: Fri Dec 16, 2005 12:23 am
by hawleyjr
I'm not sure what you tables look like but try some normalization and/or JOINS
http://en.wikipedia.org/wiki/Database_normalization
http://databases.about.com/od/specificp ... zation.htm
http://dev.mysql.com/tech-resources/art ... ation.html
For instance:
Code: Select all
Table 1: (Users)
USER_ID
NAME
EMAIL
LINK_TO_PHOTO
Code: Select all
Table 2: (friends)
USER_ID
FRIENDS_ID
QUERY:
Code: Select all
SELECT b.NAME,b.EMAIL, b.LINK_TO_PHOTO from table1 as a
LEFT JOIN table2 on a.USER_ID = b.USER_ID
WHERE A.USER_ID = $this_user_id
or
SELECT NAME, EMAIL, LINK_TO_PHOTO from table2
WHERE USER_ID = $this_user_id
Posted: Fri Dec 16, 2005 12:30 am
by waradmin
I mostly understand that. Im not sure where your getting the $this_user_id from.
The tables are:
friends table
-id (auto inc)
-uname (username of the person who added them as a friend)
-friend_uname (username of the person you added as friend, what im trying to display)
loginphp table
-id (auto inc)
-Uname (person whos profile your viewing is template.php?user=Uname but each user has a Uname on here, friend_uname matches this for displaying the image)
-mypic (picture location stored in database for the Uname that matches friend_uname)
Thanks.
Posted: Fri Dec 16, 2005 12:34 am
by William
Code: Select all
<?php
$user = $_GET['user'];
include("config.php");
# Connects to MySQL Server.
$SQLc = @mysql_connect("$DBHOST", "$DBUSER", "$DBPASS") or die("Err:Conn");
# Selects a Data Base from the MySQL Server.
@mysql_select_db("$DBNAME", $conn) or die("Err:Db");
# Create query for MySQL Server.
$SQLd = "SELECT * FROM `friends` WHERE `UNAME` = '".$user."'";
# Send query to MySQL Server.
$SQLq = mysql_query($SQLd);
# Fetch array from $SQLq
while($udata = mysql_fetch_array($SQLq)) {
# Sets $fimguname varible from $data[]
$funame = $udata['friend_uname'];
# Create query for MySQL Server.
$SQLd = "SELECT * FROM `loginphp` WHERE `UNAME` = '".$funame."'";
# Send query to MySQL Server.
$SQLq = mysql_query($SQLd);
# Fetch array from $SQLq
$fdata = mysql_fetch_array($SQLq);
# Sends table data to client.
echo "<td><img src=\"pubimages/thumbs/thumb_".$fdata['mypic']."\"><td><a href=\"template.php?user=".$funame."\">".$funame."</a><br /></td>";
}
?>
I haven't had time to test it but there you go. Remember inserting variables straight into a SQL query isn't very secure.
Posted: Fri Dec 16, 2005 12:35 am
by hawleyjr
waradmin wrote:I mostly understand that. Im not sure where your getting the $this_user_id from.
The tables are:
friends table
-id (auto inc)
-uname (username of the person who added them as a friend)
-friend_uname (username of the person you added as friend, what im trying to display)
loginphp table
-id (auto inc)
-Uname (person whos profile your viewing is template.php?user=Uname but each user has a Uname on here, friend_uname matches this for displaying the image)
-mypic (picture location stored in database for the Uname that matches friend_uname)
Thanks.
$this_user_id is a made up variable. AKA the current users id.
Posted: Fri Dec 16, 2005 12:48 am
by hawleyjr
waradmin wrote:I mostly understand that. Im not sure where your getting the $this_user_id from.
The tables are:
friends table
-id (auto inc)
-uname (username of the person who added them as a friend)
-friend_uname (username of the person you added as friend, what im trying to display)
loginphp table
-id (auto inc)
-Uname (person whos profile your viewing is template.php?user=Uname but each user has a Uname on here, friend_uname matches this for displaying the image)
-mypic (picture location stored in database for the Uname that matches friend_uname)
Thanks.
Check out the normalization links I posted, you really should change your table structure.
Without really looking into it here is how I would do it:
Code: Select all
loginphp table
uid
picurl
username
friends table
id
pid
friends_id
#pid is the id of the person who's friend it is
This way you have an array of query options to choose from:
Code: Select all
//get all users friends
$this_user_id = 1;
SELECT b.username,b.picurl FROM loginphp as a
LEFT JOIN friends as b on b.pid= a.uid
where a.uid = $this_user_id
EDIT: Changed the Query

...lol, and again

Posted: Fri Dec 16, 2005 12:54 am
by waradmin
thanks. That works. How do i make it loop through all the results. I got:
Code: Select all
<table border="1"><tr>
<?php $user = $_GET['user'];
include("config.php");
# Connects to MySQL Server.
$SQLc = @mysql_connect("$DBHOST", "$DBUSER", "$DBPASS") or die("Err:Conn");
# Selects a Data Base from the MySQL Server.
@mysql_select_db("$DBNAME", $conn) or die("Err:Db");
# Create query for MySQL Server.
$SQLd = "SELECT * FROM `friends` WHERE `Uname` = '".$user."'";
# Send query to MySQL Server.
$SQLq = mysql_query($SQLd);
# Fetch array from $SQLq
while($udata = mysql_fetch_array($SQLq)) {
# Sets $fimguname varible from $data[]
$funame = $udata['friend_uname'];
# Create query for MySQL Server.
$SQLd = "SELECT * FROM `loginphp` WHERE `Uname` = '".$funame."'";
# Send query to MySQL Server.
$SQLq = mysql_query($SQLd);
# Fetch array from $SQLq
$fdata = mysql_fetch_array($SQLq);
# Sends table data to client.
?>
<td><img src="pubimages/thumbs/thumb_<?php echo $fdata['mypic']; ?>"></td><td><a href="template.php?user=<?php echo $funame; ?>"><?php echo $funame; ?></a><br></td>
<?php } ?></tr></table>
but it doesnt loop through all the values in the friends table, it just displays the first entry.
Posted: Fri Dec 16, 2005 7:09 am
by shiznatix
change mysql_fetch_array to mysql_fetch_assoc
Posted: Fri Dec 16, 2005 11:38 am
by waradmin
Ive got now:
Code: Select all
<table border="1">
<tr>
<td><?php $user = $_GET['user'];
include("config.php");
# Connects to MySQL Server.
$SQLc = @mysql_connect("$DBHOST", "$DBUSER", "$DBPASS") or die("Err:Conn");
# Selects a Data Base from the MySQL Server.
@mysql_select_db("$DBNAME", $conn) or die("Err:Db");
# Create query for MySQL Server.
$SQLd = "SELECT * FROM `friends` WHERE `Uname` = '".$user."'";
# Send query to MySQL Server.
$SQLq = mysql_query($SQLd);
# Fetch array from $SQLq
while($udata = mysql_fetch_assoc($SQLq)) {
# Sets $fimguname varible from $data[]
$funame = $udata['friend_uname'];
# Create query for MySQL Server.
$SQLd = "SELECT * FROM `loginphp` WHERE `Uname` = '".$funame."'";
# Send query to MySQL Server.
$SQLq = mysql_query($SQLd);
# Fetch array from $SQLq
while( $fdata = mysql_fetch_assoc($SQLq) )
# Sends table data to client.
{?><img src="pubimages/thumbs/thumb_<?php echo $fdata['mypic']; ?>"></td>
</tr>
<tr>
<td><a href="template.php?user=<?php echo $funame; ?>"><?php echo $funame; ?></a><br></td>
</tr><?php }} ?>
</table>
and it only displays one entry. Doesnt loop through them all. To get a better look at what im trying to do go to
http://www.open-blog.org/myopenspace2/ and click register then go to Members and click on uberamd and look at the friends picture on the bottom. Im trying to display all friends, not just one.
Posted: Fri Dec 16, 2005 2:55 pm
by korto
check the while statement , shouldn' t there be double equal symbols ? (==) I think by using one you are actually assigning a value
Posted: Fri Dec 16, 2005 4:41 pm
by waradmin
Nope the double == doesnt solve it, just causes it to not display anything.