Trouble with date_format in mysql

[Steven]

So I have a long query and I'm trying to format the date (the "datein" column is date type). Here's my query:

"SELECT j.*, c.*, DATE_FORMAT(datein, '%M %d, %Y') AS datein FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE j.jobid = $id"

It responds with this error:

Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement in C:xampphtdocsmos-logviewjob.php on line 29

So I figured what was happening, was "datein" was being pulled twice, conflicting with my prepared statement. So I tried changing my query so all columns from the jobs table were SELECTED before the date_format except for "datein". Well it still gave me the error.

 

I've been rummaging the mysql manual and stackoverflow, but haven't found anything that helps me understand what's going on.

[davej]

What does this do?

"SELECT * FROM jobs j, clients c WHERE j.clientid = c.id AND j.jobid = $id"

[justsomeguy]

The error message says that you are trying to bind parameters to a query that has no parameters. You should probably replace that $id variable with a parameter.

[Steven]

What does this do?

"SELECT * FROM jobs j, clients c WHERE j.clientid = c.id AND j.jobid = $id"

 

What it does, is pulls data from two tables: Jobs and Clients. The $id variable comes from a GET, which is pulling the job id from the url. The page this is being used on is a viewjob page, that displays a single job (from the jobs table) and all the details of that job.

 

Here is a bigger picture of the code:

<?php	$id = $_GET['jobid'];	// prepare statement	if ($stmt = mysqli_prepare($con, "SELECT j.*, c.*, DATE_FORMAT(datein, '%M %d, %Y') AS datein FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE j.jobid = $id")) {		mysqli_stmt_execute($stmt);		// bind variables to prepared statement		// list all* columns in order of tables selected!		mysqli_stmt_bind_result($stmt, $clientid, $jobid, $datein, $description, $id, $name);		// fetch values		while (mysqli_stmt_fetch($stmt)) {			echo '<h2><span class="fontweight500">Job-'.$jobid.'</span> — '.$name.'</h2>';			echo '<p>Ordered: '.$datein.'</p>';			echo '<p>Notes: '.$description.'</p>';		}	// close statement		mysqli_stmt_close($stmt);	}?>

"datein" is a column from the jobs table. Here are all the columns:

 

"Jobs" Table: clientid, jobid, datein, description

"Client" Table: id, name

[justsomeguy]

One of the major reasons of using prepared statements is so that you avoid SQL injection attacks. SQL injection attacks happen when you take user-supplied data and just put it right into a query without any kind of validation or sanitation. You're putting the value of $_GET['jobid'] right into your query. That defeats the purpose of using prepared statements. Use a placeholder, and bind the parameter.

[Steven]

What do you mean exactly by placeholder?

[justsomeguy]

In the examples in the manual, the placeholders are the question marks.http://www.php.net/manual/en/mysqli-stmt.bind-param.php

[Steven]

I'm binding results, though, not params. I haven't seen anything about placeholders on the bind_result page. I know full well my knowledge is pretty thin, so I'm not trying to argue a point. But I haven't seen anything saying bind_result needs placeholders, at least I haven't seen them used in any examples.

 

http://us2.php.net/manual/en/mysqli-stmt.bind-result.php

[davej]

What it does, is pulls data from two tables: Jobs and Clients. The $id variable comes from a GET, which is pulling the job id from the url. The page this is being used on is a viewjob page, that displays a single job (from the jobs table) and all the details of that job.

 

Yes, but what I was trying to ask is whether a simple query works correctly? You can format the date in Php.

 

Also see...

 

http://www.php.net/manual/en/security.database.sql-injection.php

[Steven]

Ah, yes, the query itself without the date_format does work. What's the best practice for formatting a date type (it isn't integer) with PHP? The research I've done hasn't come up with much.

 

Thanks for the link.

[justsomeguy]

I'm binding results, though, not params.

I know, that's the problem. Binding results is fine and doesn't require anything special in the query. Putting user-supplied data (anything from $_POST, $_GET, $_COOKIE, $_REQUEST, etc) directly into a query is a problem. You're putting the value of $_GET['jobid'] right in the query instead of using it as a parameter. I've been trying to explain that this whole time, it's the entire reason to use prepared statements in the first place. What happens if I send a request to that page and use this as the value for jobid:1 OR 1=1Is that text a valid job ID? What happens if you run this query, which is what happens right now if you use that text as jobid:SELECT j.*, c.*, DATE_FORMAT(datein, '%M %d, %Y') AS datein FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE j.jobid = 1 OR 1=1

[Steven]

After some fiddling I tweaked it like this:

		$getid = $_GET['jobid'];		// prepare statement		if ($stmt = mysqli_prepare($con, "SELECT j.*, c.*, date_format(datein, '%M %d, %Y') AS dates FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE j.jobid = ? ")) {			mysqli_stmt_execute($stmt);			// bind param			mysqli_stmt_bind_param($stmt, 'i', $getid);			// execute			mysqli_stmt_execute($stmt);			// bind result			mysqli_stmt_bind_result($stmt, $clientid, $jobid, $datein, $description, $id, $name, $dates);			while (mysqli_stmt_fetch($stmt)) {				echo '<h2><span class="fontweight500">Job-'.$jobid.'</span> — '.$name.'</h2>';				echo '<p>Ordered: '.$dates.'</p>';				echo '<p>Notes: '.$description.'</p>';			}			// close stmt			mysqli_stmt_close($stmt);		}

The placeholder seems to be working. Thanks.

[Steven]

I'm still confused as to why my date_format isn't printing. When I run the SQL syntax in phpmyadmin, it returns the formatted date under a new column, "dates," just fine. But in the PHP it returns nothing...

[thescientist]

what's the value of date you are getting from the form?

[Steven]

It's a date type, YYYY-MM-DD. I'm trying to print it as "Mar 28, 2014" for example.

[thescientist]

Are you sure? What is an actual sample of the value coming over the wire? what if you just try with a unix timestamp just as a test?

 

Personally, I would store dates as unix timestamps and then just format them on the way out.

[Steven]

I'm sure,

// prepare statementif ($stmt = mysqli_prepare($con, "SELECT jobs.*, clients.* FROM jobs JOIN clients WHERE clientid=id ORDER BY jobs.jobid DESC")) {	mysqli_stmt_execute($stmt);	// bind variables to prepared statement	// list all* columns in order of tables selected!	mysqli_stmt_bind_result($stmt, $clientid, $jobid, $datein, $description, $id, $name);	// fetch values	while (mysqli_stmt_fetch($stmt)) {		echo "<tr>";		echo '<td><a href="viewjob.php?jobid='.$jobid.'">'.$jobid.'</a></td>';		echo "<td>$clientid</td>";		echo '<td><a href="viewclient.php?clientid='.$clientid.'">'.$name.'</a></td>';		echo "<td>$datein</td>";		echo "<td>$description</td>";		echo "</tr>";	}	echo "</table>";	// close statement	mysqli_stmt_close($stmt);}

Returns what is in the attached image.

 

(and yes, this particular page doesn't have the placeholder in the prepared statement yet, haven't updated all the pages yet)

 

But the SQL works, I was just hoping one of you would have an easy answer as to why it isn't working in the PHP.

 

(and all those entries that have "0000-00-00" are from when I was doing a bunch of tests and I had the date set as an integer and not a string)

Edited March 28, 2014 by Steven

[Steven]

It's working now...

 

Must have been a cache issue or something.