[SOLVED] Query over my head

[sciencebear]

I've been trying to run a pretty intense query, and I feel a little bit over my head. Let me try and explain as best as I can what I am attempting.

 

First, the query finds friends of the user logged in. This seems to work fairly well. Then, the query runs on an table called "updates" and tries to find all the updates from the user's friends and order them by most recent to oldest. There is one problem. There are two types of updates, text and picture. The table was not built by me, so it is a little strange. For picture updates, there is a column in the table that has an identifier unique to the picture. For text, it only identifies the user. So I am able to easily work displaying the pictures, but for text, I have to run yet again another query on a table containing the usernames and content of the text updates. This unfortunately produces a problem: where the output should be all of the text updates from a user from newest to oldest, it only displays the most recent update for each instance where the user updated.

 

I know this probably sounds really confusing, but if anyone can help me, I'll do my best to clarify.

[GKWelding]

can you post your queries so we can work with them? More info needed really. Also, look in to MySQL Views.

[sciencebear]

$friends = "SELECT * FROM updates ORDER BY id DESC";

$qryfriends = mysql_query($friends);

while($friendsrowb = mysql_fetch_array($qryfriends)) {

$upuser=$friendsrowb['username'];

$resultt = mysql_query("SELECT * FROM friends WHERE (friendname = '$username') OR (username = '$username')");

$numRowsresultt = mysql_num_rows($resultt);

for($countb = 1; $countb <= $numRowsresultt; $countb++){

$friendsrow=mysql_fetch_array($resultt);

$friendsrowfull = array_diff($friendsrow, array($username));

$friendida=$friendsrowfull['username'];

$friendidb=$friendsrowfull['friendname'];

if($upuser==$friendida || $upuser==$friendidb) {

if($friendsrowb['type']=="status"){

$page=1;

$friendsstatus = "SELECT * FROM  posts WHERE (username = '$friendida') OR (username = '$friendidb') ORDER BY id DESC LIMIT 1";

$status = mysql_query($friendsstatus);

while($statusrow = mysql_fetch_array($status)){

echo '<b>' . $friendsrowb['username'] . '</b>'." ".'<a href="/test/profile.php?username=' . $friendsrowb['username'] . '&page='.$page.'">' .$statusrow['body']. '</a> ';

echo "<br />";

}

}

if($friendsrowb['type']=="picture"){

$picpath=$friendsrowb['path'];

$friendspic = "SELECT * FROM  userpics WHERE path='$picpath'";

$picquery = mysql_query($friendspic);

$page=1;

$picrow = mysql_fetch_array($picquery );

$will = getimagesize($picrow['path']);

$width = $will[0];

$height = $will[1];

if($width < 75){

    echo '<b>' . $friendsrowb['username'] . '</b> uploaded a picture! <a href="/test/userimgdisplay.php?picid=' . $picture['id'] . '&username=' . $friendsrowb['username'] . '&page=1"><img src=' .$picrow['path'] . ' border="0"></a><br />';

} else {

$f = $width/75;

$w = 75;

$h = $height/$f;

echo '<b>' . $friendsrowb['username'] . '</b> uploaded a picture! <a href="/test/userimgdisplay.php?picid=' . $picrow['id'] . '&username=' . $friendsrowb['username'] . '&page=1"><img src=' . $picrow['path'] . ' width="' . $w . '" height="' . $h .'" border="0"></a><br />';

}

}

}

}

}

 

$numrun = 0;

$numrun2 = 0;

$friendsget = "SELECT * FROM friends WHERE (username='$username') OR (friendname='$username')";

$friendsrun = mysql_query($friendsget);

while ($friendsfetch = mysql_fetch_array($friendsrun)){

$fri1 = $friendsfetch['username'];

$fri2 = $friendsfetch['friendname'];

if ($fri1 == $username){

$updatesget = "SELECT * FROM updates WHERE username='$fri2'";

$updatesrun = mysql_query($updatesget);

$updatesfetch = mysql_fetch_array($updatesrun);

$type = $updatesfetch['type'];

if ($type=="status") {

$page=1;

$friendsstatus = "SELECT * FROM  posts WHERE username='$fri2' ORDER BY id DESC LIMIT $numrun, 1";

$status = mysql_query($friendsstatus);

while($statusrow = mysql_fetch_array($status)){

echo '<b>' . $updatesfetch['username'] . '</b><a href="/test/profile.php?username=' . $updatesfetch['username'] . '&page='.$page.'">' .$statusrow['body']. '</a> ';

echo "<br />";

}

$numrun = $numrun++;

}

if($type=="picture"){

$picpath=$updatesfetch['path'];

$friendspic = "SELECT * FROM  userpics WHERE path='$picpath'";

$picquery = mysql_query($friendspic);

$page=1;

while ($picrow = mysql_fetch_array($picquery)){

$will = getimagesize($picrow['path']);

$width = $will[0];

$height = $will[1];

if($width < 75){

echo '<b>' . $updatesfetch['username'] . '</b> uploaded a picture! <a href="/test/userimgdisplay.php?picid=' . $picrow['id'] . '&username=' . $updatesfetch['username'] . '&page=1"><img src=' . $picrow['path'] . ' border="0"></a><br />';

} else {

$f = $width/75;

$w = 75;

$h = $height/$f;

echo '<b>' . $updatesfetch['username'] . '</b> uploaded a picture! <a href="/test/userimgdisplay.php?picid=' . $picrow['id'] . '&username=' . $updatesfetch['username'] . '&page=1"><img src=' . $picrow['path'] . ' width="' . $w . '" height="' . $h .'" border="0"></a><br />';

}

}

}

} elseif ($fri2 == $username){

$updatesget = "SELECT * FROM updates WHERE username='$fri1'";

$updatesrun = mysql_query($updatesget);

$updatesfetch = mysql_fetch_array($updatesrun);

$type = $updatesfetch['type'];

if ($type=="status") {

$page=1;

$friendsstatus = "SELECT * FROM  posts WHERE username='$fri1' ORDER BY id DESC LIMIT $numrun2, 1";

$status = mysql_query($friendsstatus);

while($statusrow = mysql_fetch_array($status)){

echo '<b>' . $updatesfetch['username'] . '</b><a href="/test/profile.php?username=' . $updatesfetch['username'] . '&page='.$page.'">' .$statusrow['body']. '</a> ';

echo "<br />";

}

$numrun2 = $numrun2++;

}

if($type=="picture"){

$picpath=$updatesfetch['path'];

$friendspic = "SELECT * FROM  userpics WHERE path='$picpath'";

$picquery = mysql_query($friendspic);

$page=1;

while ($picrow = mysql_fetch_array($picquery)){

$will = getimagesize($picrow['path']);

$width = $will[0];

$height = $will[1];

if($width < 75){

echo '<b>' . $updatesfetch['username'] . '</b> uploaded a picture! <a href="/test/userimgdisplay.php?picid=' . $picrow['id'] . '&username=' . $updatesfetch['username'] . '&page=1"><img src=' . $picrow['path'] . ' border="0"></a><br />';

} else {

$f = $width/75;

$w = 75;

$h = $height/$f;

echo '<b>' . $updatesfetch['username'] . '</b> uploaded a picture! <a href="/test/userimgdisplay.php?picid=' . $picrow['id'] . '&username=' . $updatesfetch['username'] . '&page=1"><img src=' . $picrow['path'] . ' width="' . $w . '" height="' . $h .'" border="0"></a><br />';

}

}

}

}

}

 

These are two of the more successful queries I've tried. I didn't write both of them, but neither one work as they should.

[Alt_F4]

i would start with trying to reduce the number of times you go into the database by using joins where possible, even stored procedures may be worth looking into.

 

just to start with - where is $username set?

$resultt = mysql_query("SELECT * FROM friends WHERE (friendname = '$username') OR (username = '$username')"); 

[sciencebear]

$username is set whenever someone is logged in. It's a commonly used variable in my set and hasn't yet caused any problems. I'm definitely going to try some joins and hope that works.

[GKWelding]

jesus, I would definately recommend some joins there, without a doubt. That's a lot of database calls.

[Alt_F4]

ok so i had a stab at rewriting the first query  :-\

It may not be exactly what you are after cause i dont know what you database is structured like or exactly what data you want from it, but it should give you a base to start from (hopefully)

excuse any errors that there may be - its 1:30am here so im not exactly A1 at the moment.

 

<?php
$sql = "SELECT t1.username,t1.type,t1.path,t3.body,t4.id FROM update t1 
	JOIN friends t2 ON t1.username=t2.username 
	JOIN posts t3 ON t1.username=t3.username
	JOIN userpics t4 ON t1.path=t4.path
	WHERE t2.friendname = '$username' OR t2.username = '$username'
	ORDER BY t1.id DESC";

$query = mysql_query($sql);
$num_rows = mysql_num_rows($query);

while($result = mysql_fetch_array($query)) {
//not sure what the below is for - so i commented it out
/*$friendsrowfull = array_diff($friendsrow, array($username));
    $friendida=$friendsrowfull['username'];
    $friendidb=$friendsrowfull['friendname'];*/

if($result['type'] == 'status') {
	$page=1;
	echo '<b>'.$result['username'].'</b>'." ";
	echo '<a href="/test/profile.php?username='.$result['username'].'&page='.$page.'">'.$result['body'].'</a>';
        echo "<br />";
} elseif($result['type'] == 'picture') {
	$dim = getimagesize($result['path']);
	       
        if($dim[0] < 75){
		echo '<b>'.$result['username'].'</b> uploaded a picture!';
		echo '<a href="/test/userimgdisplay.php?picid='.$result['id'].'&username='.$result['username'].'&page=1">';
		echo '<img src='.$result['path'].' border="0"></a><br />';
        } else {			
		$height = $dim[1]/($dim[0]/75);
		echo '<b>'.$result['username'].'</b> uploaded a picture!';
		echo '<a href="/test/userimgdisplay.php?picid='.$result['id'].'&username='.$result['username'].'&page=1">';
		echo '<img src='.$result['path'].' width="75" height="'.$eight.'" border="0"></a><br />';
        }
}

}
?>

[sciencebear]

That gets pretty close. I still have the same basic problem. Let me try and explain what is happening.

 

Imagine user1 is friend with user2, user3 and user4. They have made updates like this:

 

user2 update 1

user3 update 2

user4 update 3

user2 update 4

user2 update 5

user3 update 6

 

Instead of returning that, the query returns something like:

 

user2 update 1

user2 update 4

user2 update 5

user3 update 2

user3 update 6

user4 update 3

user2 update 1

user2 update 4

user2 update 5

user2 update 1

user2 update 4

user2 update 5

user3 update 2

user3 update 6

 

Any suggestions?

[Alt_F4]

maybe try just running the following query in your database and see what you get

 

SELECT t1.username,t1.type,t1.path FROM update t1 ORDER BY t1.id DESC

[sciencebear]

That query shows all the users and only shows one status per instance.

 

Also, I should note the original suggested query returned an empty set so I changed them to left joins. It now looks like this, and this was returning what I said when I stated it was getting pretty close.

 

SELECT t1.username,t1.type,t1.path,t3.body,t4.id FROM updates t1 
      LEFT JOIN friends t2 ON t1.username=t2.username 
      LEFT JOIN posts t3 ON t1.username=t3.username
      LEFT JOIN userpics t4 ON t1.path=t4.path
      WHERE t2.friendname = '$username' OR t2.username = '$username'
      ORDER BY t1.id DESC