Page 1 of 1

Query for Specific Data

Posted: Mon Jun 19, 2006 3:26 pm
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.

Posted: Mon Jun 19, 2006 3:33 pm
by Benjamin
sudo...

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

That work?

Posted: Mon Jun 19, 2006 3:43 pm
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.

Posted: Mon Jun 19, 2006 3:56 pm
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

Posted: Mon Jun 19, 2006 5:41 pm
by tecktalkcm0391
because its not really like that it, but i think i figured it out... thanks!

Posted: Mon Jun 19, 2006 5:42 pm
by tecktalkcm0391
how could i show like the last two questions and answers using what you said...

Posted: Mon Jun 19, 2006 5:46 pm
by Christopher
Beside the curious database design:

Code: Select all

select * from table where date = today order by id desc LIMIT 0,2

Posted: Mon Jun 19, 2006 6:14 pm
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";
?>

Posted: Tue Jun 20, 2006 6:58 am
by mmcoolkid94
hello, i am doing something about the same, how would i display the things, would i use a loop?

Posted: Tue Jun 20, 2006 6:59 am
by Grim...
Sure.

Posted: Tue Jun 20, 2006 7:51 am
by tecktalkcm0391
how could i do the loop, am confused use for or while or what?

Posted: Tue Jun 20, 2006 8:14 am
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

Posted: Tue Jun 20, 2006 8:56 am
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?

Posted: Tue Jun 20, 2006 9:22 am
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.

Posted: Tue Jun 20, 2006 5:37 pm
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!