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 :roll:

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.