This should be easy, please help a newbi

[Anon]

I just want to grab the middle part of a variable and assign it to another variable. Here is what i want to do:

variable $date = 2001-01-10
||
||
||
positon 67

I want to grab the "01" and assign it to something like

variable $january = 01

I am actually selecting a bunch of dates from a database and want to add up how many January, february's, march's and so on.....

I tried using the select MONTH(date) in my sql statement but that didnt work.

Thanks for any help

[Anon]

Try:

SELECT COUNT(DATE_FORMAT(datefield,'%M')) AS no_months FROM <your_table> WHERE month='$date'";

Now if $date was 'January' In theory this should give you the total number of 'January's' in a filed called 'month'

Not tested though..
See: http://www.mysql.com/doc/D/a/Date_and_time_functions.html
for more interesting date+time functions in MySQL.

HTH :-)
Russ

[[deleted]]

Expand that a little furhter then you can get a could of how many times each month occured with this:

SELECT count(*) AS cnt, MONTH(date_column)
FROM table
GROUP BY MONTH(date_column)
ORDER BY 2;

But this only works if your 'date' field has the correct format.
If you have stored all your dates as a string instead of the proper database DATE or DATETIME type, then you are in a little trouble.

[Anon]

its not working, here is my original code:include ("customer_table.inc");

$connection = @mysql_connect("", "", "") or die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection)or die("Couldn't select database.");

$sql = "select month(purchase_date)
FROM $table_name
";

echo $sql;

$result = @($sql,$connection)or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result)) {

$product_purchased = $row['product_purchased'];
$purchase_date = $row['purchase_date'];
echo $purchase_date;

if ($purchase_date == "1") {
$puchasecount1++;
}
else {
$purchasecount2++;
}
echo $purchase_date;
echo $puchasecount1;
echo $puchasecount2;
}

absolutely nothing gets echoed with the above code:

Then i tried yours vincent:

include ("customer_table.inc");
$connection = @mysql_connect("", "", "") or die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection)or die("Couldn't select database.");

$sql = "SELECT count(*) AS cnt, MONTH(purchase_date)
FROM $table_name
GROUP BY MONTH(purchase_date)
ORDER BY 2
";

echo $sql;

here is the results:

SELECT count(*) AS cnt, MONTH(purchase_date) FROM customer_table GROUP BY MONTH(purchase_date) ORDER BY 2 Couldn't execute query.

[Anon]

Brian wrote:

SELECT count(*) AS cnt, MONTH(purchase_date) FROM customer_table GROUP BY MONTH(purchase_date) ORDER BY 2 Couldn't execute query.

Not the most helpful of error messages 🙂

What data type is your purchase_date field? As vincent notes, if it's not being stored as a date, then MONTH(purchase_date) won't work.

[Anon]

Also:

• What was actually printed to the page with echo $sql?
• Try also using or die(mysql_error()) as this will print a useful error message if the query dies.
• Note what both Hayley and Vincent point out about the datatype of your <date_column>

Russ

[[deleted]]

About the "or die"

remove the @ from the script.
It hides all error messages, which is a bad idea if you have no proper error checking.