-1

Hello! Stack overflow newbie here. I'm in the middle of this assignment and i got stuck on this part of it. I need to update the DB but im getting " Invalid parameter number: number of bound variables does not match number of tokens" error. I tried looking through the forum and although i can find some people with similar problems i still havent resolved it.

Heres my code:

<?php
// Get the product data
$category_id = filter_input(INPUT_POST, 'category_id', FILTER_VALIDATE_INT);
$code = filter_input(INPUT_POST, 'code');
$name = filter_input(INPUT_POST, 'name');
$price = filter_input(INPUT_POST, 'price', FILTER_VALIDATE_FLOAT);
$product_id = filter_input(INPUT_POST, 'productID');

// Validate inputs
if ($category_id == null || $category_id == false ||
        $code == null || $name == null || $price == null || $price == false) {
    $error = "Invalid product data. Check all fields and try again.";
    include('error.php');
} else {
    require_once('database.php');

    // Add the product to the database  
    $query = 'UPDATE products SET categoryID = :category_id,  productCode = :code, productName = :name, listPrice = :price WHERE productID = :product_id';
   
   // $db->exec($query);
   
   $statement = $db->prepare($query);
   $statement->bindValue(':category_id', $category_id, PDO::PARAM_INT);
   $statement->bindValue(':code', $code, PDO::PARAM_INT);
   $statement->bindValue(':name', $name, PDO::PARAM_INT);
   $statement->bindValue(':price', $price, PDO::PARAM_INT); //line 27
   $statement->execute();
   $statement->closeCursor();

    // Display the Product List page
    include('index.php');
}
?>
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
datguyJP
  • 1
  • 5

1 Answers1

-1

In your SQL you are using a parameter in the where clause, ":product_id", but it seems you forgot to bind it. Add the bind statement.

Sudhir
  • 113
  • 8
  • I tried that, it did get rid of the error, but my database is still not updating. I have an Add function which seems to work perfectly adding columns but editing them is not happening. – datguyJP Apr 30 '22 at 06:21
  • Then try adding "echo $statement->rouCount();" and this will print the number of rows affected, which should be 1 in this case. Remember though, if the values you are trying to update are same as old, nothing will happen. Further, try adding "print_r($statement->errorInfo()); " which will print any error reported by PDO – Sudhir Apr 30 '22 at 06:29
  • on a different note I just realized that at the start of your code you are filtering input variables, but for 3 of them you have not specified the third parameter and this will result in no filtering at all for $code, $name and $product_id – Sudhir Apr 30 '22 at 06:33
  • I thought of it updating the same values regardless of different input, but rowCount shows 0 in this case. Getting rid of the filters didn't resolve the issue either. – datguyJP Apr 30 '22 at 06:51
  • Thats right, rowCount is 0 because you didnt really update anything. Try different values and the rowCount will be 1 and then you can see changes reflected in database – Sudhir Apr 30 '22 at 06:58
  • I tried to but no luck, i took the values out and put completely different ones but still no luck. I tried to get the values with GET instead of POST and still nothing. – datguyJP Apr 30 '22 at 07:15
  • what did you get in rowCount? – Sudhir Apr 30 '22 at 07:17
  • i got 0, I tried using rowCount on the Add page and if i enter the same values of course it shows 0. I think my problem comes from getting the values. Its not getting my values from the input, instead its getting them from the existing column in the DB and therefore inputting the same. I dont know how to go about fixing that. – datguyJP Apr 30 '22 at 07:23
  • there is nothing to fix, everything WORKS – Your Common Sense Apr 30 '22 at 07:28
  • so if the original question about parameters is answered guess you can accept the answer :-) – Sudhir Apr 30 '22 at 07:37
  • thanks for accepting the answer. Can you show the code for data input fields? There could be some minor mistake there – Sudhir Apr 30 '22 at 08:12
  • No problem. I tried editing the post and added the code from the other page but it seems that they dont want me doing that. What is the best way to show it? Im new here sorry. – datguyJP Apr 30 '22 at 08:25
  • Yes, it got closed because the original question already had answer in other thread and I cant reopen it. But maybe you can post the code (not screenshot) in comment using backticks like this: ` – Sudhir Apr 30 '22 at 08:29
  • well i echoed all the parameters it's using but it seems like product_id is not passing through. Honestly i dont even know if the WHERE param should pass or not. but that is the only thing not passing through the form. Ill troubleshoot further see what i can do. – datguyJP Apr 30 '22 at 08:44
  • Check your webform if you have a field with "name='product_id'". For any input or select field if you omit the "name" attribute then its not sent down the line. So ensure that you have all required fields in the web form and that all of them have a proper "name=" set – Sudhir Apr 30 '22 at 08:47
  • There is no input field for "product_id", it should not be an input value. When im at the form it echoes the ID number but not when i submit the form. – datguyJP Apr 30 '22 at 08:58
  • well well... thats why your sql is not updating anything. If not an input value, maybe you need to get it by some other means like from session. you need that in the where clause or else you will update all records. So you need to find a way to pass the product_id to PHP code, maybe as a hidden field – Sudhir Apr 30 '22 at 08:59
  • ` ` I have this in my index page. still nothing. Ill try adding it to the form – datguyJP Apr 30 '22 at 09:02
  • try changing type="hidden" to type="text" so that you can see if this field has any value. If not, check where you are creating the $product array. Also ensure that you are not leaving unwanted whitespaces inside the "echo" part – Sudhir Apr 30 '22 at 09:05
  • That helped, it wasnt passing through when i added that piece of code because i had the value assigned to ` echo $product_id['productID']; ` , i changed it to ` echo $product_id ` and now it works as it should. Even though the deadline passed and i submitted it partially, i really wanted to fix it. Thank you so much for the help man you're one of a kind! – datguyJP Apr 30 '22 at 09:13
  • You are welcome. Just remember that had you posted relevant code including index page we could have avoided such a long thread through comments. – Sudhir Apr 30 '22 at 09:15
  • I would have posted all three of them had they not been taken down. I thought the Invalid parameter number was the only thing holding me back from completing it hence why i only posted that part. I never knew this forum is so strict on such things. – datguyJP Apr 30 '22 at 09:21