Jump to content
Sign in to follow this  
Steven

Trouble with date_format in mysql

Recommended Posts

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.

Share this post


Link to post
Share on other sites

What does this do?

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

post-12664-0-63145700-1396035024_thumb.jpg

Edited by Steven

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...