datetime compare

jefterwil

I need help and I'm an amateur (but not a newbie) with php and mysql.

Scenario:

I have a dating profiles website. There are two datetime fields, regdate and editdate. The homepage displays 10 thumbnail pics descending by date of members in two categories: 1) New Members and 2) Updated Profiles (6 pics each).

New Members SQL:

"SELECT * FROM {table}WHERE pic1 != '' order by regdate DESC limit 10"

Updated Profiles SQL:

"SELECT * FROM {table}WHERE pic1 != '' AND editdate != regdate order by editdate DESC limit 10"

I need the output to say:

Display newest 10 pics by regdate.

Display newest 10 pics by editdate not already displayed by regdate.

I need a few lines of code in the right direction to help on my way -- How to compare arrays? Use mysql SUBTIME ?

HELP!!!!

stevesweetland

you may consider getting php to crunch the info - this may not be the best way of doing it.... but

$regdatearray = array();

$sql = "SELECT * FROM {table}WHERE pic1 != '' order by regdate DESC limit 10" // get all the regdate row ids
loop db results {
  $regdatearray[] =db_result("id"); // add rowid to your array
};

$editdatearray = array();

$sql = "SELECT * FROM {table}WHERE pic1 != '' AND editdate != regdate order by editdate";
for ($i = 0;$i != 10; $i++){ // we'll need to be sure we get 10 rows
  $rowid = db_result("id"); 

  if ($rowid == ""){ //if the rowid is blank..
    $i = 10;  // we need a failsafe, just in case there isnt enough database rows to end the for loop by finding 10 results... youll probably want to come up with a neater bit of code for this.
  };

  if (!inarray($rowid,$regdatearray)){ // if the row id ISNT in the regdate array...
    $editdatearray[] = db_result("id"); // add the row id to the editdate array
    $i++; // increment the $i value.
  };
}

so, get all your regdate rows first, and throw the id of the rows in an array.

create a new array, and get all the editdate row id's. you can then do code along these lines for EACH array.

foreach ($arrayname as $rowid){
  $sql = "SELECT *FROM {table} WHERE id = '$rowid'";// get the data for this row id
  // print the db results 
  print db_result("thefield");
};

NOTE - i dont do a limit on the second sql statement - we're filtering on the results we get, if we limit the mysql results, we may end up with less than ten (IE the ids that are in the regdatearray() are ignored).

yelvington

Jefterwil, to do this neatly and cleanly in SQL you need subselects, which are not supported in MySQL < 4.0, and are buggy in the versions that support them when you're referencing a single table.

You might be able to fake it by using a temporary table and a join, but I haven't tried that route.

I ran into a similar problem recently using mysql 3.x, and I resorted to solving it with brute force and PHP. I implemented the equivalent of DISTINCT and LIMIT in PHP.

You have to build an array of stuff you've already seen and don't want to see again (while looping through your first query).

Then remove LIMIT from your second query -- or set it reasonably high.

Loop through the second result set by using an explicit counter variable.

Check each retrieved ID to see if it's in the saved array (there's your DISTINCT) . If it's not, do your output stuff and increment the counter (there's your LIMIT).

I think that's what stevesweetland is trying to do with his example.

jefterwil

Thanks for the input. I'll have to go over it a few times to totally understand. I do have a question about this line:

loop db results {
$regdatearray[] =db_result("id");
}

Is the string "loop db results {" just a comment? Or is it some function I'm not finding on php.net?

Thanks again for you speedy replies.

stevesweetland

no, i intend 'loop db results' as pseudo (IE fake) code...

you might say

SELECT all the fields FROM the database
loop the db results{
  print a hyperlink with the db field 'id' and the db field 'name';
};

more of a pointer, rather than telling you exactly what code to use. my reasoning here is that there are different approaches to looping database results, use your preferred one here.