bsamson Posted January 12, 2008 Share Posted January 12, 2008 I have a DB and I am trying to find a way in php/mysql to grab the field w/ the most current date ... Fields: idno date info Values: 2 04/25/07 info goes here 3 04/26/07 info goes here In this case I would like to grab the ID # and info from IDNO 3. How would I do this? Thanks! Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 SELECT * FROM table ORDER BY date DESC LIMIT 0,1 But you need to store it in a valid date format in your DB. Quote Link to comment Share on other sites More sharing options...
bsamson Posted January 12, 2008 Author Share Posted January 12, 2008 Perhaps I am a bit confused. I need to find the date closest to todays. How do I select the closest date and also specify the ID# I want ?? Thanks for any help! Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 The date closest to todays would be the newest right or are there entries after today? Quote Link to comment Share on other sites More sharing options...
phpSensei Posted January 12, 2008 Share Posted January 12, 2008 Perhaps I am a bit confused. I need to find the date closest to todays. How do I select the closest date and also specify the ID# I want ?? Thanks for any help! If the date in id 3 is 5 days ago, and the date in id 2 is 4 days ago, then the database will return the date 4 days ago in revraz's script. Also, your DATE column needs to be a DATETIME column or a TIMESTAMP for the ORDER BY to work on the query. Quote Link to comment Share on other sites More sharing options...
bsamson Posted January 12, 2008 Author Share Posted January 12, 2008 yes I am sorry ... I need to select the newest for a particiular ID #. For example if I want ID #5 ... there are muliples entries for the ID ... So I need to find the newest or latest entry ... Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 What if there are 3 that are the same day, do you also store the time? Quote Link to comment Share on other sites More sharing options...
phpSensei Posted January 12, 2008 Share Posted January 12, 2008 What if there are 3 that are the same day, do you also store the time? i don't know what you mean, or if your talking to me lol. I think the seconds are never the same in a TIMESTAMP as it constantly changes WHEN the script is running, or the query sorts by the ID if there are 3 of the same dates... Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 Thanks for the reply, but I was asking how he stores it not how it works. I am familiar with how timestamps work, but if he just stores mm/dd/yy then it doesn't do any good right? Trying to understand his setup so we can suggest a better way and give him the results he needs. Quote Link to comment Share on other sites More sharing options...
bsamson Posted January 12, 2008 Author Share Posted January 12, 2008 Actually I just adjusted the date field in my date column to the DATETIME format and adjusted the script accordingly. that being said lets say I wanted to select the most recent date with ID # 5 ... How would I do this? Thanks for all your help! Quote Link to comment Share on other sites More sharing options...
phpSensei Posted January 12, 2008 Share Posted January 12, 2008 yes I am sorry ... I need to select the newest for a particiular ID #. For example if I want ID #5 ... there are muliples entries for the ID ... So I need to find the newest or latest entry ... <?php $query = mysql_query("SELECT * FROM `table `ORDER BY `id` DESC LIMIT 1"); // OR $query = mysql_query("SELECT * FROM `table` ORDER BY `date`DESC LIMIT 1"); ?> That does the trick, the first one gets the latest ID, and so does the Next Query that follows it. Quote Link to comment Share on other sites More sharing options...
bsamson Posted January 12, 2008 Author Share Posted January 12, 2008 Thanks! But here's the thing ... What if I want to specify the ID #? How would I narrow the search by the ID # first ... Then by the date? Thanks for your patience! Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 Use the WHERE clause WHERE id = 5 Quote Link to comment Share on other sites More sharing options...
phpSensei Posted January 12, 2008 Share Posted January 12, 2008 Thanks! But here's the thing ... What if I want to specify the ID #? How would I narrow the search by the ID # first ... Then by the date? Thanks for your patience! <?php $query = mysql_query("SELECT * FROM `table` WHERE `id` = '1' "); // OR $id = mysql_real_escape_string(strip_tags($_GET['id'])); $query = mysql_query("SELECT * FROM `table` WHERE `id` = '$id' "); ?> Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 $query = mysql_query("SELECT * FROM `table` WHERE id = 5 ORDER BY `date`DESC LIMIT 1"); Quote Link to comment Share on other sites More sharing options...
phpSensei Posted January 12, 2008 Share Posted January 12, 2008 $query = mysql_query("SELECT * FROM `table` WHERE id = 5 ORDER BY `date`DESC LIMIT 1"); There is only 1 id that is 5, don't need a ORDER BY. Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 He said there are mutiple IDs with the same number. Nothing said they were unique. He asked a specific question How would I narrow the search by the ID # first ... Then by the date? For example if I want ID #5 ... there are muliples entries for the ID ... So I need to find the newest or latest entry ... Quote Link to comment Share on other sites More sharing options...
phpSensei Posted January 12, 2008 Share Posted January 12, 2008 He said there are mutiple IDs with the same number. Nothing said they were unique. He asked a specific question How would I narrow the search by the ID # first ... Then by the date? For example if I want ID #5 ... there are muliples entries for the ID ... So I need to find the newest or latest entry ... Oh i didn't read it properly, but this would mean he has a AUTO INCREMENT field with a second ID field. Quote Link to comment Share on other sites More sharing options...
bsamson Posted January 12, 2008 Author Share Posted January 12, 2008 LOL DUH ... I'm sorry for the stupid follow up ... I am obviously still learning PHP & MySQL and the one thing I always find is that I go through and find a very long way to accomplish something that PHP could do in one line of code. HAHA .. Thanks again for everything! Quote Link to comment Share on other sites More sharing options...
revraz Posted January 12, 2008 Share Posted January 12, 2008 No problem, but now we should talk about having more than 1 id for a field Quote Link to comment Share on other sites More sharing options...
bsamson Posted January 12, 2008 Author Share Posted January 12, 2008 I actually simplified the fields a bit ... that table has 15+ fields. The ID number I was speaking of is not actually an ID number but a store # that's stored in another DB and is stored in a session. I wasn't sure what the MYSQL query would look like is all. Thanks again and have a GREAT weekend! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.