Jump to content
Balderick

updating column with increment in mysql

Recommended Posts


In phpmyadmin I did the following query and had it output in php like this:
$sql = "UPDATE `table` SET count = count + 1 WHERE unique_nr = 5175781";
this all worked well.

though when I tried to implement it in my script I had an error:

 

script:

		<?php  
		// code to establish dbase connection:

		var_dump($unique_nr);




		 
		// $stmt = $conn->prepare('UPDATE table SET count = ? WHERE unique_nr = ? ');
		$stmt = $conn->prepare('UPDATE table SET count = count + 1 WHERE unique_nr = ? ');

		$stmt->bind_param("is", $count, $unique_nr);


		$stmt->execute();


		?>

resulting in an error message.

I also tried this way of writing for bind_param:

$stmt->bind_param("s", $unique_nr);

but that didnt update the column, though there wasnt an error message anymore

Share this post


Link to post
Share on other sites

What error message shows up?

Share this post


Link to post
Share on other sites

But I also changed this code:

    $stmt = $conn->prepare('UPDATE table SET count = count + 1 WHERE unique_nr = ? ');

in

$count= 1;
 
$stmt = $conn->prepare('UPDATE table SET count = count + ? WHERE unique_nr = ? ');

the result here is that I have no error message, but there is nothing added to the column.

Share this post


Link to post
Share on other sites

The original error was because you only had once placeholder in the query but you were trying to bind 2 parameters. After you fix that, are you sure that unique_nr is a string or should that be a number?

Share this post


Link to post
Share on other sites

I tried these variations:

     $count = 1
     $stmt = $conn->prepare('UPDATE table SET count = count + ? WHERE unique_nr = ? ');

and:

   

    $stmt = $conn->prepare('UPDATE table SET count = count + 1 WHERE unique_nr = ? ');

bind_param is set to:

    $stmt->bind_param("ii", $count, $unique_nr);

I even made a mysqli routine but this also didnt work.

 

What did work was the mysql query input in the console:

mysql> UPDATE table SET count = count + 1 WHERE unique_nr = 267334;

The thing is with the console input is that when the value of count was NULL I wasnt able to update the column but once I first set it at 1 I could use the above query.

 

What is the reason for that?

 

And do you have any clue how to solve it?

 

Edit:

and is there a way to auto_increment 2 columns in one table?

Edited by Balderick

Share this post


Link to post
Share on other sites

You can't add 1 to NULL because NULL is not a number. When a row is created you should be setting the field to a specified numeric value.

 

Assuming the count field is not null, this query will work:

$stmt = $conn->prepare('UPDATE table SET count = count + 1 WHERE unique_nr = ? ');
$stmt->bind_param("i", $unique_nr);

You have to bind exactly the number of parameters that are being used in the SQL.

 

If that's not working, show exactly what error message you got.

Share this post


Link to post
Share on other sites

what do bind_param and execute do actually?

How should the parameters for these functions be used?

 

I'm looking for good and extended tutorial info online, but about this subject there's not much, a lot of tuts seem to be outdated, use other coding not related to OOP.

This is rather sad but for programmers but good info (especially for security matters) would be welcome.

Share this post


Link to post
Share on other sites

Explanations of all the functions are in the PHP manual:

http://php.net/mysqli_bind_param

http://php.net/mysqli_stmt_execute

 

And there's a manual page explaining the concept of prepared statements in general. You might call it a tutorial:

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

 

 

PHP is a well-documented language.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×