Balderick Posted February 2, 2017 Share Posted February 2, 2017 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 Link to comment Share on other sites More sharing options...
Balderick Posted February 2, 2017 Author Share Posted February 2, 2017 maybe its more a php question ... Link to comment Share on other sites More sharing options...
Ingolme Posted February 2, 2017 Share Posted February 2, 2017 What error message shows up? Link to comment Share on other sites More sharing options...
Balderick Posted February 2, 2017 Author Share Posted February 2, 2017 with the above script I have: Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in ... Link to comment Share on other sites More sharing options...
Balderick Posted February 2, 2017 Author Share Posted February 2, 2017 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted February 2, 2017 Share Posted February 2, 2017 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? Link to comment Share on other sites More sharing options...
Balderick Posted February 4, 2017 Author Share Posted February 4, 2017 it was a number, but changing that doesnt solve the problem. Link to comment Share on other sites More sharing options...
Balderick Posted February 4, 2017 Author Share Posted February 4, 2017 (edited) 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 February 4, 2017 by Balderick Link to comment Share on other sites More sharing options...
Ingolme Posted February 4, 2017 Share Posted February 4, 2017 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. Link to comment Share on other sites More sharing options...
Balderick Posted February 6, 2017 Author Share Posted February 6, 2017 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. Link to comment Share on other sites More sharing options...
Ingolme Posted February 6, 2017 Share Posted February 6, 2017 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now