Page 1 of 1

Query for 3 tables - help please - probably quick

Posted: Tue Oct 03, 2006 12:20 am
by waradmin
Here is my query, that obviously isnt working and I knew it wouldnt. I understand how to query 2 tables to get information, but what about 3?

I first need to search the friends table for ALL matches where global_id matches the global id in the address, then I need to grab friend_global_id from those matching fields. Then i need to get the information from TABLE profile_photo in FIELD profile_photo_url that matches the friend_global_id looked up in the friends table, then lastly i need to get from the TABLE loginphp FIELDS Fname and Lname that have the global_id that matches up with the friend_global_id from the friends table.

Heres my query (doesnt work), how would I fix it:

Code: Select all

$query = "SELECT
                        friends.global_id, friends.friend_id, profile_photo.global_id, profile_photo.profile_photo_url, loginphp.global_id, loginphp.Fname, loginphp.Lname
                FROM
                        friends, profile_photo
                JOIN
                        loginphp
                ON
                        friends.friend_id=loginphp.global_id
                WHERE
                        friends.global_id='". mysql_real_escape_string($_GET[id]) . "'";
Thanks in advance,
steve

Posted: Tue Oct 03, 2006 12:37 am
by daedalus__

Posted: Tue Oct 03, 2006 5:37 pm
by waradmin
I have my query set as:

Code: Select all

SELECT
                        friends.global_id, friends.friend_global_id, profile_photo.global_id, profile_photo.profile_photo_url, loginphp.global_id, loginphp.Fname, loginphp.Lname
                FROM
                        friends
                JOIN
                        loginphp
                ON
                        friends.friend_global_id=loginphp.global_id 
				JOIN
						profile_photo
				ON
						loginphp.global_id=profile_photo.global_id 
                WHERE
                        friends.global_id='". mysql_real_escape_string($_GET[id]) . "'
but I do think the query is quite right because it doesnt display any information besides some broken images.

The query is supposed to match up all the friend_global_id's (2nd field) that have the global_id (1st field) pulled from the address.

Example, user is at profile.php?id=123456, user 123456 is friends with 987654 and 876543

The query is supposed to go into the friends table, find all entries where global_id is 123456 (there are 2) and get the friend_global_id (the second column) which are in this case 987654 and 876543. Then using those, it will go into the profile_photo table and match those up with the global_id in that table and display the profile_photo_url associated with that id.

Basicly to try to sum it up: It needs to take the id set in the address, find their friend_global_id's, then use those id's to match up with the global_id in the profile_photo table to get the profile_photo_url as well as match that friend_global_id up with the global_id in loginphp to get Fname and Lname.

Whats wrong with my query? Any help would be great.

-Steve

Posted: Tue Oct 03, 2006 6:31 pm
by volka
Can you provide sample data (as valid sql statements if possible)?

Posted: Tue Oct 03, 2006 6:41 pm
by waradmin
FRIENDS table:

Field | type | extra
----------------------------
id | int(11) | auto_increment
global_id | int(11)
friend_global_id | int(11)

Data:
id global_id friend_global_id
----------------------------------------------------
1 8511657 8511657
(note for this example I just said the user was their own friend, but friend_global_id will usualy differ from global_id)


PROFILE_PHOTO table:

Field | type | extra
----------------------------
id | int(11) | auto_increment
global_id | int(11)
profile_photo_url | text

Data:
id global_id profile_photo_url
1 8511657 images/12058170848.jpg

LOGINPHP table:

Field | type | extra
----------------------------
id | int(11) | auto_increment
global_id | int(11)
Fname | varchar(30)
Lname | varchar(30)

Data:
id Uname Email Fname Lname Pword global_id
3 steve uberamd@gmail.com Steve Morrissey ********* 8511657

So I want to view profile.php?id=8511657 which then needs to go into the friends table, and select EACH friend_global_id where global_id = $_GET['id']. THEN it needs to go into the profile_photo table and select the profile_photo_url that matches up with the friend_global_id, THEN it needs to go into table loginphp, and match the global_id with friend_global_id then grab the Fname and Lname from there.

So its getting the users friend's global_id (friend_global_id in the friends table) and grabing their picture from profile_photo and their first and last name from loginphp (Fname Lname). Then it loops.

Hope it helps.

Posted: Tue Oct 03, 2006 6:44 pm
by volka
As valid sql statements please. I really don't want to copy,paste&edit other peoples sql data anymore.

Posted: Tue Oct 03, 2006 6:59 pm
by waradmin
(I assume you mean the mysql dump from phpmyadmin):

Code: Select all

CREATE TABLE `loginphp` (
  `id` int(11) NOT NULL auto_increment,
  `Uname` varchar(30) default NULL,
  `Email` varchar(30) default NULL,
  `Fname` varchar(30) default NULL,
  `Lname` varchar(30) default NULL,
  `Pword` varchar(30) default NULL,
  `global_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `loginphp`
-- 

INSERT INTO `loginphp` VALUES (1, 'uberamd', 'uberamd@gmail.com', NULL, NULL, '------', 0);
INSERT INTO `loginphp` VALUES (3, 'steve', 'uberamd@gmail.com', 'Steve', 'Morrissey', '------', 8511657);
INSERT INTO `loginphp` VALUES (5, 'test', 'theman@dotthis.com', 'Gordon', 'Freeman', '-----', 7676666);
INSERT INTO `loginphp` VALUES (6, 'steven', 'g@d.com', 'Steven', 'Jackson', '------', 2155761);
INSERT INTO `loginphp` VALUES (7, 'sally', 'sally@d.edu', 'Sally', 'Bojangles', '------', 3769653);

Code: Select all

-- 
-- Table structure for table `friends`
-- 

CREATE TABLE `friends` (
  `id` int(11) NOT NULL auto_increment,
  `global_id` int(11) NOT NULL default '0',
  `friend_global_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `friends`
-- 

INSERT INTO `friends` VALUES (1, 8511657, 8511657);

Code: Select all

-- 
-- Table structure for table `profile_photo`
-- 

CREATE TABLE `profile_photo` (
  `id` int(11) NOT NULL auto_increment,
  `global_id` int(11) NOT NULL default '0',
  `profile_photo_url` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `profile_photo`
-- 

INSERT INTO `profile_photo` VALUES (1, 8511657, 'images/12058170848.jpg');
Sorry about the last post, I wasnt sure (and am still not 100%) about what you mean by valid mysql statements

Thanks volka

Posted: Tue Oct 03, 2006 7:09 pm
by volka
I tried

Code: Select all

SELECT
	friends.global_id, friends.friend_global_id, profile_photo.global_id, profile_photo.profile_photo_url, loginphp.global_id, loginphp.Fname, loginphp.Lname
FROM
	friends
JOIN
	loginphp
ON
	friends.friend_global_id=loginphp.global_id
JOIN
	profile_photo
ON
	loginphp.global_id=profile_photo.global_id
WHERE
	friends.global_id=8511657
with the sample and got

Code: Select all

"global_id";"friend_global_id";"global_id";"profile_photo_url";"global_id";"Fname";"Lname"
8511657;8511657;8511657;"images/12058170848.jpg";8511657;"Steve";"Morrissey"
Maybe there's nothing wrong with the query but with the php code arround?

Posted: Tue Oct 03, 2006 8:03 pm
by waradmin
Well it works. Thanks for the help with this, thus far I have learned quite a bit from your help so thanks for helping my php knowledge.

-Steve

Posted: Tue Oct 03, 2006 10:29 pm
by waradmin
Alright, so it doesnt work as perfectly as I thought.

I added another test friend into the friends table, and instead of displaying that NEW information, it just repeats the old information (doesnt show second friend information, just shows the first friend twice).

Heres the complete code:

Code: Select all

<?php
				  	$query = "SELECT
										friends.global_id, friends.friend_global_id, profile_photo.global_id, profile_photo.profile_photo_url, loginphp.global_id, loginphp.Fname, loginphp.Lname
								FROM
										friends
								JOIN
										loginphp
								ON
										friends.friend_global_id=loginphp.global_id
								JOIN
										profile_photo
								ON
										loginphp.global_id=profile_photo.global_id
								WHERE
										friends.global_id='". mysql_real_escape_string($_GET[id]) . "'";
					$result = mysql_query($query) or die(mysql_error());
					include('functions.php');
					$i = 0;
					$num_rows = mysql_num_rows( $result );
					while($i < $num_rows) {
						$row = mysql_fetch_array( $result );
						if($i ==4){
							$i=0;
							echo "</tr>";
						}
						if($i ==0) {
							echo "<tr>";
						}
						if($i <= 4) {
							$picture = $row['profile_photo_url'];
							list($w, $h) = getimagesize($picture);
							if($w > 50) {
								$scale = imageScale($picture, 50, -1);
								$width = $scale[0];
								$height = $scale[1];
								echo "<td><center><a href=\"profile.php?id=" . $row['global_id'] . "\"><img src=\"$picture\" width=\"$width\" height=\"$height\"><br>" . $row['Fname'] . " " . $row['Lname'] . "</a></center></td>";
								}
								else
								{
									echo "<td><center><a href=\"profile.php?id=" . $row['global_id'] . "\"><img src=\"$picture\"><br>" . $row['Fname'] . " " . $row['Lname'] . "</a></center></td>";
								}
							$i++;
						}
					 }
					?>

Posted: Wed Oct 04, 2006 5:32 am
by volka
And what sql data did you add?
Can there be more than one entry for each global_id in profile_photo?

btw: if global_id is unique you don't need another unique/auto_increment in loginphp. Seems like you're not using it anyway.

Posted: Wed Oct 04, 2006 8:22 am
by waradmin
I updated the friends table by adding another friend to the user:

(and out of habit i always make a id row that is auto_inc, incase I want to sort by registration order or something)

Code: Select all

-- 
-- Table structure for table `friends`
-- 

CREATE TABLE `friends` (
  `id` int(11) NOT NULL auto_increment,
  `global_id` int(11) NOT NULL default '0',
  `friend_global_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `friends`
-- 

INSERT INTO `friends` VALUES (1, 8511657, 8511657);
INSERT INTO `friends` VALUES (2, 8511657, 7676666);

Code: Select all

-- 
-- Table structure for table `profile_photo`
-- 

CREATE TABLE `profile_photo` (
  `id` int(11) NOT NULL auto_increment,
  `global_id` int(11) NOT NULL default '0',
  `profile_photo_url` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `profile_photo`
-- 

INSERT INTO `profile_photo` VALUES (1, 8511657, 'images/12058170848.jpg');
INSERT INTO `profile_photo` VALUES (2, 7676666, 'images/12058170848.jpg');
The new data makes user 8511657 friends with 7676666 and gives user 7676666 a profile picture. However it displays the same image (because im using the same image, that was expected) but it also displays the same Fname and Lname under each of the 2 images (Steve Morrissey) and each image links to the same profile (profile.php?id=8511657), the second image should display name Gordon Freemen and link to profile.php?id=7676666

Thanks.

Posted: Wed Oct 04, 2006 8:59 am
by volka
try

Code: Select all

SELECT
        loginphp.global_id, loginphp.Fname, loginphp.Lname,
        profile_photo.profile_photo_url
FROM
        friends
LEFT JOIN
        loginphp
ON
        friends.friend_global_id=loginphp.global_id
LEFT JOIN
        profile_photo
ON
        loginphp.global_id=profile_photo.global_id
WHERE
        friends.global_id=8511657
volka wrote:Can there be more than one entry for each global_id in profile_photo?

Posted: Wed Oct 04, 2006 9:26 am
by waradmin
Alright that is working perfectly, lastly is it possible to throw a limit of 6 on this (so it only display's the first 6 entries then stops?

Thanks for all the help Volka.

Posted: Wed Oct 04, 2006 9:54 am
by volka
Depends on the database. For mysql you can append LIMIT 6
http://dev.mysql.com/doc/refman/5.0/en/select.html