Problem with Selecting specific rows from a table based on the size of a number

[Andrew777]

Hi Guys,

I'm am trying to figure out the logic to do this, hopefully someone can help me out…

 

I'm working on a small social networking site. One feature is that whenever a user updates certain items on their profile an entry gets placed in the "updates_all" table.

For example:

auto_id member id text date_time querydate

1 Bill 154787 has updated his bio 2011-03-17 1103171953

2 Jeff 568946 has updated his profile pic 2011-03-18 1103172000

3 Sue 445185 has created an ad 2011-03-18 1103172043

4 Bill 154787 has deleted an ad 2011-03-19 1103172045

etc...

 

Then I have a table called "subscriptions", where a user's id gets entered along with another user's id, when the user subscribes to a user's profile. So they can keep updated on what that other user does…  So one user can have multiple subscriptions.

 

Table: subscriptions

auto_id memberid profileid date

1 154787 568946 Apr 1, 2011 (Bill has subscribed to Jeff's Profile)

2 154787 445185 Apr 2, 2011 (Bill has subscribed to Sue's Profile)

3 445185 568946 Apr 2, 2011 (Sue has subscribed to Jeff's Profile)

4 568946 445185 Apr 5, 2011 (Jeff has subscribed to Sue's Profile)

etc…

 

 

So far, I have coded a page called subscriptions.php, that once a member is logged in and goes to that page, the code gets an array of members that the logged in user is subscribed to from the subscriptions table,

 

$subscripPID = array();
$sql_findsubs = "SELECT * FROM subscriptions WHERE memberid='$id' ";
$rs_findsubs = mysql_query($sql_findsubs);
while($rowfs = mysql_fetch_array($rs_findsubs)) { //THIS GETS THE PROFILES THIS USER IS SUBSCRIBED TO.
$subscripPID[] =$rowfs['profileid']; //THIS PUTS THOSE PROFILES IDS INTO AN ARRAY
echo $rowfs['profileid'];
echo '<br>';
}

 

then Selects the rows from the updates_all table based on that array and shows the updates in a list as follows…

 

$sql="SELECT * FROM updates_all WHERE id='$subscripPID' ";
$rs=mysql_query($sql);
while($row=mysql_fetch_array($rs)) {
echo $row['member'].'<br>';
echo '<hr>';
}

$sql_ups = "SELECT * FROM updates_all ORDER BY querydate DESC";
$rs_ups = mysql_query($sql_ups);
while($rowups = mysql_fetch_array($rs_ups)) { 

$id = $rowups['id'];
if (in_array($id, $subscripPID)){
echo $rowups['member'].' - ';
echo $rowups['id'];
echo '  '.$rowups['date_time'].'  |  '.$rowups['querydate'];
echo '<br />';

}
}

 

Bill is logged in and goes to his subscriptions page: (Bill is subscribed to Sue and Jeff)

25 Sue - 445185  has updated her blah blah… 2011-09-09 20:40:24 PM  |  1109092040  **

24 Sue - 445185  has updated her blah blah… 2011-09-09 20:40:02 PM  |  1109092040

23 Jeff - 568946  has updated his blah blah… 2011-09-09 19:35:40 PM  |  1109091935  **

21 Sue - 445185  has updated her blah blah… 2011-09-02 22:17:55 PM  |  1109022217

19 Jeff - 568946  has updated his blah blah… 2011-07-14 20:38:07 PM  |  1107142038

17 Jeff - 568946  has updated his blah blah… 2011-07-14 20:26:45 PM  |  1107142026

10 Sue - 445185  has updated her blah blah… 2011-07-14 20:26:14 PM  |  1107142026

 

 

1. My problem is that I want to First SELECT Only the most recent update from one of those members based on the updates querydate which the largest number… (The two rows marked with **)

 

So Bill has subscribed to Sue and Jeff (for example)

and Sue has 10 updates in the table, and the most recent update has a larger querydate than the rest,

so how do I select that row… And the same for Jeff.

 

2. And how do I put that info into an array (the auto_id's  for those 2 rows), which I then want to use to create a While

loop on the page so in this instance shows Sue at the top, and Jeff underneath that. And then so

if Jeff then makes a new update, and Bill Logs into his Subscription page, Jeff then shows up on top above Sue.

Once I can get that organization done for the main While Loop, I can create an internal while loop to show

Jeff's list of updates only next to his name and only Sue's specific updates next to her name.

 

 

Hopefully this makes sense... any help is really appreciated...

I've spent a few days on this and still don't get how I can get this working.

Thanks

 

[Nethox]

 

EDIT : Actually I think I found a much better solution, lemme work on it.

 

EDIT2 : Try changing this line :

 

$sql_ups = "SELECT * FROM updates_all ORDER BY querydate DESC";

 

with this :

 

$sql_ups = "SELECT * FROM updates_all ORDER BY querydate DESC LIMIT 1";

 

[Andrew777]

Thanks Nethox, but I tried the "LIMIT 1" yesterday and for some reason I get no results on the page when I add that to the Select statement.

I have no idea why that happens.

 

 

[Nethox]

What do you get if you try this :

$sql="SELECT * FROM updates_all WHERE id='$subscripPID' ORDER BY querydate DESC LIMIT 1";
$rs=mysql_query($sql);
while($row=mysql_fetch_array($rs)) {
echo $row['member'].'<br>';
echo '<hr>';
//}

//$sql_ups = "SELECT * FROM updates_all ";
//$rs_ups = mysql_query($sql_ups);
//while($rowups = mysql_fetch_array($rs_ups)) { 

$id = $row['id'];
//if (in_array($id, $subscripPID)){
echo $row['member'].' - ';
echo $row['id'];
echo '  '.$row['date_time'].'  |  '.$row['querydate'];
echo '<br />';

//}
}

[Andrew777]

If I run it with your modification from your post (as in the code below) the second loop doesn't show any results at all....

btw, I took out the "//commented out" lines...

 

$subscripPID = array();
$sql_findsubs = "SELECT * FROM subscriptions "; 
$rs_findsubs = mysql_query($sql_findsubs);
while($rowfs = mysql_fetch_array($rs_findsubs)) { 
$subscripPID[] =$rowfs['profileid']; 
echo $rowfs['profileid'];
echo '<br>';
}
echo '<br>';

$sql="SELECT * FROM updates_all WHERE id='$subscripPID' ORDER BY querydate DESC LIMIT 1";
$rs=mysql_query($sql);
while($row=mysql_fetch_array($rs)) {
echo $row['member'].'<br>';
echo '<hr>';

echo $row['member'].' - ';
echo $row['id'];
echo '  '.$row['date_time'].'  |  '.$row['querydate'];
echo '<br />';
}

 

I tried some modifications (code below), and I got the output to show the members a person is subscribed to, and I can get the select statement to get the most recent row/per member based on the 'querydate' BUT I can't get the order that is output to put the member with the most recent update on top. I realized that the output is being effected by the order of the initial while loop that gathers the member ID's from the subscriptions table, so the order they were added to that table is being put into the array, which I am using to to find and Output my final list of members, so the sorting by most recent querydate doesn't work, uggggh!!!. (hope that makes sense :-)

Any help figuring that out would be appreciated....

 

$subscripPID = array();
$sql_findsubs = "SELECT * FROM subscriptions WHERE memberid='$id' ORDER BY auto_id DESC"; //SO THE PROBLEM IS IN THE ORDER HERE
$rs_findsubs = mysql_query($sql_findsubs);
while($rowfs = mysql_fetch_array($rs_findsubs)) { 
$subscripPID[] =$rowfs['profileid']; 
echo $rowfs['profileid'];
echo '<br>';
}
echo '<br>';


$sql_ups = "SELECT * FROM `updates_all` GROUP BY member ORDER BY querydate ASC ";
$rs_ups = mysql_query($sql_ups);
while($rowups = mysql_fetch_array($rs_ups)) 
{ 
$id = $rowups['id'];
if (in_array($id, $subscripPID))	
{
$sql = " SELECT * FROM updates_all WHERE id='$id' ORDER BY querydate DESC LIMIT 1 ";
$rs = mysql_query($sql);
while($row=mysql_fetch_array($rs)) {
	echo $row['member'];
	echo ".....";
	echo $row['querydate'];
	echo "<br>";
	}
}
}

[Buddski]

Give this a try.. it should (untested of course) replace the whole second half of your script.

$sql_ups = "SELECT
MAX(`querydate`),
`member`,
`id`,
`text`
FROM `updates_all` 
WHERE `id` IN (".join(',',$subscripPID).")
GROUP BY `id` ORDER BY `member` ASC";

$rs_ups = mysql_query($sql_ups);
while ($row=mysql_fetch_array($rs_ups)) {
echo $row['member'];
echo ".....";
echo $row['querydate'];
echo "<br>";
}

Let me know how you get on.

[Andrew777]

Hi Buddski,

Thanks for your help. Your modification (I made a couple of tweeks) seems to somewhat help BUT now it's giving me rows based on the first querydate entered per/member in that table, not the most recent row with the higher querydate entered per/member....

 

So if "Bob" is in the "updates_all" table like so.

9 - Bob .......1103181620 (most recent)

2 - Bob .......1103181619

1 - Bob .......1103172045 (first, oldest)

 

The result will return

Row "1", not row "9"..

 

Here is the code as it is now....

$id = $_SESSION['memberid'];
$subscripPID = array();
$sql_findsubs = "SELECT * FROM subscriptions WHERE memberid='$id' ";
$rs_findsubs = mysql_query($sql_findsubs);
while($rowfs = mysql_fetch_array($rs_findsubs)) { 
$subscripPID[] =$rowfs['profileid']; 
echo $rowfs['profileid']; //This line is irrelevant.
echo '<br>';
}
echo '<br>';

$sql_ups = "SELECT MAX(`querydate`),
`member`,
`id`,
`auto_id`,
`querydate` 
FROM `updates_all` 
WHERE `id` IN (".join(',',$subscripPID).") 
GROUP BY `id` ORDER BY `querydate` DESC";

$rs_ups = mysql_query($sql_ups);
while ($row=mysql_fetch_array($rs_ups)) {

echo $row['auto_id'].'.....'.$row['member'].'('.$row['id'].')'.' - '.$row['querydate'];
echo "<br>";
}

 

 

Thanks for any more help....