Jump to content


Photo

datestamp and mysql


  • Please log in to reply
11 replies to this topic

#1 domagoj

domagoj
  • New Members
  • Pip
  • Newbie
  • 7 posts
  • LocationCroatia

Posted 02 March 2003 - 01:50 AM

I have a table with datetime column in MySQL database. If I wanted to select all rows with the newest month how I would do it?
For example, table ids:
+----+---------------------+
| id | mytime |
+----+---------------------+
| 1 | 2003-03-01 21:03:34 |
| 2 | 2003-01-01 20:33:34 |
| 3 | 2003-01-01 21:03:34 |
| 4 | 2003-03-01 02:03:34 |
| 5 | 2002-03-01 15:13:34 |
+----+---------------------+
from which I want to select all ids from March 2003.

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 02 March 2003 - 11:34 AM

you should use date_format(mytime, \"format\")

See documentation for the date_format function in mysql.

Then use

select * form table where date_format(...) = \'03\';

\'03\' is march (should be obvious).

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 domagoj

domagoj
  • New Members
  • Pip
  • Newbie
  • 7 posts
  • LocationCroatia

Posted 02 March 2003 - 11:55 AM

you should use date_format(mytime, \"format\")
See documentation for the date_format function in mysql.
Then use
select * form table where date_format(...) = \'03\';
\'03\' is march (should be obvious).
P.


That\'s OK but \'03\' is not a constant, so i need to make a subquery which MySQL doesn\'t support. I think the beginning of the SQL code should be (maybe :lol: ) something like this:

DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table (mjgod VARCHAR(20)) TYPE=HEAP;
INSERT INTO tmp_table SELECT DATE_FORMAT(max(time), \'%M, %Y\') FROM clanci;

The second thing - did someone try to store several QUERIES in one PHP string and then execute? I\'ve got no results from database.

#4 NL_Johan_UK

NL_Johan_UK
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 02 March 2003 - 02:41 PM

How about selecting with Limit = 0,1 and sort by mytime desc. This way you have the latest date, you can substract the month out of it like explained above and use that in the rest of the code / queries.

#5 domagoj

domagoj
  • New Members
  • Pip
  • Newbie
  • 7 posts
  • LocationCroatia

Posted 02 March 2003 - 08:49 PM

maybe something like this:

select * from ids where extract(month from time) = extract(month from current_date) and extract(year from time) = extract(year from current_date);



#6 NL_Johan_UK

NL_Johan_UK
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 03 March 2003 - 12:11 AM

That is sort of what I meant indeed, but I dont know if mysql gonna have that. Just do the extraction in PHP and save the results in variables you can use for the second query...

#7 domagoj

domagoj
  • New Members
  • Pip
  • Newbie
  • 7 posts
  • LocationCroatia

Posted 03 March 2003 - 01:32 AM

That is sort of what I meant indeed, but I dont know if mysql gonna have that. Just do the extraction in PHP and save the results in variables you can use for the second query...


it works fine inside the mysql, but the answer is here:

$query = "

    	DROP TABLE IF EXISTS tmp_table;

	CREATE TEMPORARY TABLE tmp_table (mjgod VARCHAR(20)) TYPE=HEAP;

	INSERT INTO tmp_table  SELECT DATE_FORMAT(max(time), \'%M, %Y\') FROM clanci;

	SELECT clanci.*, tmp_table.* as najnoviji from clanci, tmp_table WHERE DATE_FORMAT(clanci.time,\'%M, %Y\')=tmp_table.mjgod;

    	";

    	$result = mysql_query($query, $link) or die("No results for bulletin.");

The only problem is that this code works inside mysql prompt, but when I try to execute multiple SQL statement string (above) from PHP it returns no results.

Does anybody know is it possible to execute some kind of SQL procedure like above one from PHP?

#8 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 03 March 2003 - 07:57 AM

Hi,

It is possible... just do them one at a time...

The result you want will be in the last one...

1. DROP TABLE IF EXISTS tmp_table;
2. CREATE TEMPORARY TABLE tmp_table (mjgod VARCHAR(20)) TYPE=HEAP;
3. INSERT INTO tmp_table SELECT DATE_FORMAT(max(time), \'%M, %Y\') FROM clanci;
4. SELECT clanci.*, tmp_table.* as najnoviji from clanci, tmp_table WHERE DATE_FORMAT(clanci.time,\'%M, %Y\')=tmp_table.mjgod;

You need 4 queries. (Well actually three):


1. DROP TABLE IF EXISTS tmp_table;
2. CREATE TEMPORARY TABLE tmp_table AS SELECT DATE_FORMAT(max(time), \'%M, %Y\') FROM clanci;
3. SELECT clanci.*, tmp_table.* as najnoviji from clanci, tmp_table WHERE DATE_FORMAT(clanci.time,\'%M, %Y\')=tmp_table.mjgod;

But I don\'t understand what you\'re doing. If you just want all the most recent month you could use date_format(now()) for the current date...

Is it not possible to use the max(time) in a normal sql statement ?

like

select * from table where date_format(mytime) = date_format(max(mytime)) ?? Is this only possible with temporary tables?

(I\'m still asleep, monday morning - maybe I\'m way off here).

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#9 domagoj

domagoj
  • New Members
  • Pip
  • Newbie
  • 7 posts
  • LocationCroatia

Posted 03 March 2003 - 12:29 PM

it doesn\'t work in where clause, don\'t ask why, I don\'t know. With MSAccess and MSSQL it works, but MySQL doesn\'t support subqueries yet.

#10 domagoj

domagoj
  • New Members
  • Pip
  • Newbie
  • 7 posts
  • LocationCroatia

Posted 03 March 2003 - 12:33 PM

Hi,

select * from table where date_format(mytime) = date_format(max(mytime)) ?? Is this only possible with temporary tables?
P.


This does not work, that\'s why I\'m posting to this forum :lol:

And I didn\'t understand the thing with 3 queries - you thought that I should use
$result = mysql_query($query, $link) three times or just once?
Because I was thinking that this should work if I executed just once the whole \"stored procedure\"

#11 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 04 March 2003 - 08:56 AM

[quote=\"domagoj\"][quote]Hi,

And I didn\'t understand the thing with 3 queries - you thought that I should use
$result = mysql_query($query, $link) three times or just once?
Because I was thinking that this should work if I executed just once the whole \"stored procedure\"[/quote]

Yes, you should do the above three times (or four).

Php and mysql doesn\'t understand the mysql character ;

But I\'m doing it like

1. mysql_query($query, $link);
2. same
3. same
.
.
.
5. $result = mysql_query($query, $link);

When it is only the last SQL statement I need to evaluate or print or whatever....

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#12 domagoj

domagoj
  • New Members
  • Pip
  • Newbie
  • 7 posts
  • LocationCroatia

Posted 04 March 2003 - 10:41 PM

Thnx, I think this should work.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users