0

I am writing a query in a mysql db website and need a little help on the select statement syntax. I need to retrieve info from a database containing user input from a web-form at my site. There are a large number of over-the-road drivers who need to check in reporting their location with this form. The goal is to have a concise grid display table on the resulting web page that shows only the latest entry for all the drivers with only the 3 criteria on each row, (– name – location – date/time). The code included below does return results for all the drivers, but in the form of a long list of all entries for each driver on multiple pages instead of just updating location for each driver. It seems like I need to use “DISTINCT” and/or “LIMIT” in the string, but my attempts at this haven’t produced the desired results. Thanks, Mike

$myquery = "select name, location, recordtime from ".$config->site_db."form_db order by recordtime DESC";
Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
user641472
  • 1

1 Answers1

0

If I understand correctly you want to select the record with the highest recordtime for each name:

$myquery = "select name, location, max(recordtime) from ".$config->site_db."form_db group by name";

sestevez
  • 21
  • 2
  • Thanks sestevez, the "GROUP BY" worked fine, but for some reason the the "MAX" changes date/time for all rows displayed to the time the query was run (all today's date). As I played with other areas of the code and removed some modifications in date/time formatting that have been working fine on all versions, date/time disappeared entirely from the display (while using MAX in the select statement). I tried removing MAX and adding "SORT BY" along with the "GROUP BY", but that resulted in error. – user641472 Mar 03 '11 at 14:43
  • MAX(recordtime) will return the record (row) with the highest recordtime in the table. When you group by name it returns the records with the highest recordtime for each name. Check the contents of the table. Today's date must be in there if it's getting selected. – sestevez Mar 03 '11 at 15:49