Query for Specific Data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Query for Specific Data

Post by tecktalkcm0391 »

How could I do a query for data (in MySQL) that meets this requriements
  • >> Date is the current date
    >> Picks each of these types only once: question, answer
    >> Picks only the last of each type. (EXAMPLE: if i have 2 questions for the same data it picks the latest one (I have an id next to each row))
I just cant figure it out.

Thank you.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

sudo...

select * from table where date = today order by id desc

That work?
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

Great, now how do i display the current (greatest id) question and current (greatest id) answer.
See my database looks like this:
It goes id, info, data, type:

29 ......Af80KGD8fGux07VmkxwYXQxn..... June 19, 2006.... question
30 ......KpMAxWABxsjEyAWyNdnMkUcc..... June 19, 2006..... answer
31 ......fGsFQvG4OiUd9hac5VJTdbvv..... June 19, 2006..... question
32 ......E6b8N1pyPfNCBqwRkoqeSM9i..... June 19, 2006..... answer
And I only want the question on 31 and matching answer which is 32

I know how to do this stuff but not in this way.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

How are you associating the questions and answers?

Why not have something like...

RecordNumber | Question | Answer | TimeStamp

Then you can...

Code: Select all

$Query = "select * from `table` where date='today' order by `RecordNumber` desc"; // pulls the last question on x date

$LinkID = mysql_query($Query, $Connect);

$Data = mysql_fetch_assoc($LinkID);

echo $Data['Question']; // echo's the question
echo $Data['Answer']; // echo's the question
echo $Data['TimeStamp']; // echo's the timestamp
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

because its not really like that it, but i think i figured it out... thanks!
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

how could i show like the last two questions and answers using what you said...
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Beside the curious database design:

Code: Select all

select * from table where date = today order by id desc LIMIT 0,2
(#10850)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Just to clarify the query

Code: Select all

<?php
$sql = "SELECT field1, field2, FROM table WHERE datefield = CURDATE() ORDER BY id DESC LIMIT 0, 2";
?>
mmcoolkid94
Forum Newbie
Posts: 8
Joined: Mon Jun 19, 2006 9:07 pm

Post by mmcoolkid94 »

hello, i am doing something about the same, how would i display the things, would i use a loop?
Last edited by mmcoolkid94 on Tue Jun 20, 2006 7:10 am, edited 1 time in total.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

Sure.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

how could i do the loop, am confused use for or while or what?
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

Code: Select all

if($_GET['amount']>0){
			$amount = var_dump($_GET['amount']);
			$current_date = date("F j, Y");	
			foreach($types as $type){
				$query = mysql_query("SELECT * FROM `codes` WHERE Date='{$current_date}'  ORDER BY ID DESC LIMIT 0, $amount");
									print("The current ". $display['Type'] ." security encryption code is ". $display['Code'] .".<br>"); 
				
			}
			
		}
And I get nothing to show up
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I'd stay away from queries within a loop. It is a bit resource intensive. Are you trying to show data relative to data in another loop?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Just my tuppence worth, but I reckon you'd make this a hell of a lot easier if you changed your database design, to something along the lines of:

Code: Select all

CREATE TABLE question (
id_question INT(5) unsigned not null auto_increment,
de_question VARCHAR(255) not null,
de_answer VARCHAR(255) not null,
PRIMARY KEY (id_question));
This assumes that you can only have one answer to any question.

Then, you can have another table which holds the question by date, and you can grab the last question from there, and get the answer from the "question" table.

It is *usually* a bad idea to store different types of record in the same table. It is also *usually* not a good idea to store related records in the same table... what happens if another process writes to the table and disturbs your number sequence, so that you can't guarantee that the answer to the question in row 31 will be in row 32? You should either associate the answer to the question in the same record or have the questions in one table and the answers in another.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

OK, well i've kinda misrepresented the problem. I was just using question and answer to try to make it simpler but it hasn't really helped.

What I have is 2 security codes that are generated ever 2 hours in cron job. What I wanted to do with something like this:

Code: Select all

if($_GET['amount']>0){ 
                        $amount = var_dump($_GET['amount']); 
                        $current_date = date("F j, Y");  
                        foreach($types as $type){ 
                                $query = mysql_query("SELECT * FROM `codes` WHERE Date='{$current_date}'  ORDER BY ID DESC LIMIT 0, $amount"); 
                                                                        print("The current ". $display['Type'] ." security encryption code is ". $display['Code'] .".<br>"); 
                                
                        } 
                        
                }

Was to display the 2 types of codes whatever the amount was times. So say today at 4:00 the code was first code was 12345 and the second code was qwert.
at 6:00 the first code code changed to 54321 and the second to: 1qazs.

Now a user requests the last two codes of each type. So I want it to show something like this.
Code 1: 12345
Code 2: qwert
-------------------
Code 1: 54321
Code 2: 1qazs
I really just want to make it show how ever many previous codes the user requests of what ever types they request.

Do you get what I am aiming for?
The reason they aren't on the same row is because I have the codes going at different times, and I want them to be called on at different time. Now that I am writting this and thinking about it, I might just do it like that. Oh nevermind cause if I add a code 3 then i would have to make a new row, instead of just adding the code to the array of types to make.

Any help would be great!
Post Reply