SQL help

[nobody]

So I suck at (My)SQL. I'm trying to learn the language beyond just really basic SELECTS, INSERTS, and UPDATES, but being inherently stupid makes that a little rough.

What I want to do is select only the rows that happen to fall into a certain month and year. Each row has a timestamped column, so the information is already there. I just really don't know how to do this. I figure I could do it by using the timestamp as a string and ripping it apart, but I feel like MySQL has something built in for this sort of thing.

Ultimately I'd like to make it so my PHP code just magically creates a seperate query for each month so I can mess with all that data separately, but I couldn't even begin to think of how that's possible. So hints on that would be solid too, but the first thing is more important at the moment.

Oh and I have screwed around a bit in the MySQL documentation and I know the basics of the language, so I'm not completely oblivious to this, just want to get into the intermediate stuff and am thoroughly lost.

Thanks tigers and female tigers. (Tigers are automatically dudes unless otherwise noted)

[bwh2]

What I want to do is select only the rows that happen to fall into a certain month and year.
SELECT * FROM myTable
WHERE MONTH(timestamp_col) = 11
AND YEAR(timestamp_col) = 2006

Ultimately I'd like to make it so my PHP code just magically creates a seperate query for each month so I can mess with all that data separately, but I couldn't even begin to think of how that's possible.there are 2 approaches to take.
1) use a separate query for each month
2) write one query and separate the results in PHP to an associative array

i prefer 2 because it requires less DB calls.

[nobody]

Wow that's super awesome on the first one!

The issue with the second deal is that I want everything to just kind of magically happen as time progresses. Obviously I could just hard code in the months and years as they near and all, or even just make a really large list of months and years and check for existence and go from there, but I think there's a better way.

I guess that's what you're suggesting with the associative array. I honestly don't know much about creating associative arrays, I know what they are and how they work, but I've never intentionally created one short of using mysql_fetch_assoc(). That's a great stepping stone though. Thanks a ton for the information, I was hoping you'd be the one to answer :beam:

[bwh2]

with the associative array, i was thinking something like this. i would only do this if you want all the results on one page. otherwise, you would add conditions in your WHERE to trim down the result set.


<?php

/* connect to db. define vars first */
$link = mysql_connect( 'localhost', $username, $password );
mysql_select_db( $db, $link );

/* the query */
$sql = "SELECT *,
MONTH(timestamp_col) as mon,
YEAR(timestamp_col) as yr
FROM myTable";

/* run query */
$result = mysql_query( $sql );

/* array to hold our result data */
$data = array();

/* loop through resultset */
while( $row = mysql_fetch_array($result) ) {

/* YYYYMM */
$period = $row['yr'].sprintf('%02d', $row['mon']);

/* check if there is already an array element in $data for this period */
if( !$data[$period] ) {
$data[$period] = array();
}

/* add results to $data */
array_push( $data[$period], array(
'id' => $row['id'],
'time' => $row['timestamp']
)
);
}

/* print out full associative array */
print_r( $data );

?>

[CriTiCeRz]

with the associative array, i was thinking something like this. i would only do this if you want all the results on one page. otherwise, you would add conditions in your WHERE to trim down the result set.


<?php

/* connect to db. define vars first */
$link = mysql_connect( 'localhost', $username, $password );
mysql_select_db( $db, $link );

/* the query */
$sql = "SELECT *,
MONTH(timestamp_col) as mon,
YEAR(timestamp_col) as yr
FROM myTable";

/* run query */
$result = mysql_query( $sql );

/* array to hold our result data */
$data = array();

/* loop through resultset */
while( $row = mysql_fetch_array($result) ) {

/* YYYYMM */
$period = $row['yr'].sprintf('%02d', $row['mon']);

/* check if there is already an array element in $data for this period */
if( !$data[$period] ) {
$data[$period] = array();
}

/* add results to $data */
array_push( $data[$period], array(
'id' => $row['id'],
'time' => $row['timestamp']
)
);
}

/* print out full associative array */
print_r( $data );

?>
Wow... that's nice. :)

[Seb Hughes]

^Does that go though each row and check if there is one like it if nto add to array? or

[nobody]

That's pretty intense bw2, I'll fiddle with that soon. Thanks a lot for your help :)

[bwh2]

@CriTiCeRz: it looks even better OOP. i'll post it later if i get a chance.

@seb: no. think of it as a grouping by period (YYYYMM). the group name is an associative array key ('period') and the value is another array ($row).

@28: no problem. let me know if you have any questions.

[CriTiCeRz]

^
Is OOP object-oriented programming?

[bwh2]

yes.

[CriTiCeRz]

^
How would it look with OOP? lol...