Need some help with an SQL statement in PHP

PHP
1

Hi all, I’m hoping someone here can help me. It’s probably easy, but I’m still more on the beginner side of things than expert. By far. Exibit A:

I’m trying to do a very simple query that would be much easier if I didn’t set up my date field as varchar (I know, I know…)

Essentially, I’m looking to pull all the results where the month equals the current month. Values are formatted like 01/01/2014. Here’s what I’ve tried. I first got the current month:


$currentMonth = date(m);



$query= "SELECT * FROM cakes WHERE substr($cdate, 0, 2) = $currentMonth";

In theory, that should match up the first two digits of the string in $cdate with the current month. I don’t get an error on that, but I don’t get any results, either (and on my test server, I have two that should match up).

Am I missing something obvious, or am I on the completely wrong track?

2

Regardless of the field type you should store the date as YYYY-MM-DD so you can sort by date etc. Then you can make a query like this.

$sql = "SELECT id,name FROM cakes WHERE MONTH(date) = $currentMonth";

Be sure to add those single quotes on the date month call.

$currentMonth = date('m');
3

Yeah, the problem is that I have a db full of the other format, so I’m stuck with it for now.

4

To expand or fix what Drummin is saying. Store as a Date or DateTime, not string. From there you can run native SQL commands against it such as MONTH or greater/ less than loigic. Trying to parse a string as a date is horrible practice, and can also be horribly slow.

5

If I could do it over, I would certainly do that. But for now, I’m trying to figure out how to make this work with what is currently set up. If it’s possible. The other option is to just query the table and use PHP to give me a monthly total. But I’m not quite sure how to do that, either.

6

I’m not sure what files might be affected by making this change, but you could re-format that date on all your records by doing something like this. Please wait for other opinions on doing this before proceeding.

<?php
$host = "localhost";
//Database user name.	
$login = "";
//Database Password.
$dbpass = "";
//Database name.
$dbname = "";
$PDO = new PDO("mysql:host=localhost;dbname=$dbname", "$login", "$dbpass");

try{
$sql = "SELECT id,date FROM cakes";
$stmt = $PDO->prepare($sql);
	$stmt->execute();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
	
	$id = $row['id'];
	$bad_date = $row['date'];
	$good_date = date('Y-m-d', strtotime($bad_date));
	
	$query = $PDO->prepare("UPDATE cakes SET date = :good_date WHERE id = :id");
	$query->bindParam(":id", $id);
	$query->bindParam(":good_date", $good_date);
	$query->execute();
		
	}
	
}catch (PDOException $e){
	echo "Database error: ".$e->getMessage();
}
?>
7 

$query= "SELECT * FROM cakes WHERE substr($cdate, 0, 2) = $currentMonth";
should be
$query= "SELECT * FROM cakes WHERE substr(cdate, 1, 2) = '$currentMonth'";

Drop the $ from cdate. I’m assuming that table cakes has a column named cdate. Make sure you read the mysql documentation on functions. In mysql, the first character of a substr begins with 1.

Basically, be careful not to mix up sql and php.

Also, learn to use the mysql console or some other tool such as phpmyadmin so you can test your queries before coding them.

8

Ahhh… That worked. I tried the field name with 0 offset, which didn’t work so I was at a loss as to why. Very good.

9

That did it. Thanks so much! I had no idea about those differences. And next time, I know how to avoid this problem altogether.

Much appreciated everyone!