SELECT from 2 tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

SELECT from 2 tables

Post 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.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Post 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?
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post 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 } ?>
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post 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.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Post 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.
Last edited by William on Fri Dec 16, 2005 12:37 am, edited 2 times in total.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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:
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

change mysql_fetch_array to mysql_fetch_assoc
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post 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.
korto
Forum Commoner
Posts: 36
Joined: Thu Aug 18, 2005 6:30 am
Location: Greece
Contact:

Post by korto »

check the while statement , shouldn' t there be double equal symbols ? (==) I think by using one you are actually assigning a value
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

Nope the double == doesnt solve it, just causes it to not display anything.
Post Reply