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