#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Shady Grove
    Posts
    96
    Rep Power
    15

    Cannot update database


    Hi, I've got the below code which runs without errors but won't update the database is there something i'm missing?

    Thanks PHP Addict

    PHP Code:
         $DBConnection = new database_connector('MyDB'); //Initialize database connection

         
    if(isset($_POST['Submit']) && $_POST['Submit'] == 'place order'){
             foreach(
    $_POST['OrderQuantity'] as $OrderID => $OrderQuantity){    //Validate form input
                 
    if(!is_int($OrderID)){    //Check for form injection tampering
                     
    die('Error(911): We were unable to process your request');    //Kill script
                 
    }

                 if(!
    ctype_digit($OrderQuantity)){    //User has typed non digits in to the Stock Quantity field
                     
    $UserMessage 'Please ensure only digits 0 - 9 have been entered in to the stock quantity field and resubmit your order';
                     return;
                 }
             }

             
    #insert in to db
             
    $OrderDelivery date('Y-m-d'$Delivery->get_deliveryDate());

             
    $PS1SQL 'INSERT
                         INTO stock_orders (Quantity, Price, StockitemID, DeliveryDate, KitchenID, UserID)
                          VALUES (:Quantity, :Price, :StockitemID, :DeliveryDate, :KitchenID, :UserID)
                          ON DUPLICATE KEY UPDATE
                          Quantity = :Quantity'
    ;
             
    $Statement $DBConnection->prepare($PS1SQL);

             foreach(
    $_POST['OrderQuantity'] as $OrderID => $OrderQuantity){
                 foreach(
    $_SESSION['KitchenOrder'] as $Order){
                     if(
    $OrderID == $Order['ID']){    //Insert in to DB
     /*
                     var_dump($Order); exit;
                     //returns
                     array (size=7)
      'ID' => string '32' (length=2)
      'Description' => string 'Cocoa Cola Can' (length=14)
      'Unit' => string '330ml' (length=5)
      'Case' => string '24' (length=2)
      'Price' => string '4.99' (length=4)
      'Quantity' => string '19' (length=2)
      'SupplierID' => string '14' (length=2)
    */

                         
    $Statement->bindParam(':Quantity'$OrderQuantityPDO::PARAM_INT);
                         
    $Statement->bindParam(':Price'$Order['Price'], PDO::PARAM_INT);
                         
    $Statement->bindParam(':StockitemID'$Order['ID'], PDO::PARAM_INT);
                         
    $Statement->bindParam(':DeliveryDate'$OrderDeliveryPDO::PARAM_STR);
                         
    $Statement->bindParam(':KitchenID'$_SESSION['KitchenID'], PDO::PARAM_INT);
                         
    $Statement->bindParam(':UserID'$_SESSION['UserID'], PDO::PARAM_INT);
                         
    $Statement->execute();
                     }
                 }
             }
             
    #saved to db redirect back to control panel
    if(!$PS1SQL){die('Query failed!!!');}    //Testing only
                                                       
         
    }
         
    $DBConnection null
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,625
    Rep Power
    1811
    I don't do php or PDO, but to my untrained eye you are doing no error checking after each of the insert statements are executed in the inner loop.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70
    Perhaps as a test to double-check, unless that is where you've gotten the commented lines you included, echo out all the values you use when running the bindParam functions. Run the echo just above/below that group, so you know that if() calculated as true and is even running this section.

    You can also place this echo in there to offer you a copy of what is intended to run, and this will echo the MySQL query to screen, and you can run the query manually in phpMyAdmin or such and make sure that string is successful, or see what response you will get with a 'more direct' use of the query.
    PHP Code:
    echo 'INSERT INTO stock_orders (Quantity, Price, StockitemID, DeliveryDate, KitchenID, UserID) 
    VALUES ("' 
    $OrderQuantity '", "' $Order['Price'] . '", "' $Order['ID'] . '", "' $OrderDelivery '", "' $_SESSION['KitchenID'] . '", "' $_SESSION['UserID'] . '") 
    ON DUPLICATE KEY UPDATE Quantity = $OrderQuantity;'

    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Apr 2009
    Posts
    2,232
    Rep Power
    1296
    This is not directly related to your question, but I'd like to bring up a topic which I feel strongly about and will help in your troubleshooting.

    The vast majority of the php coders I've come across use a few coding practices which IMO make the code harder to read and maintain. I'm referring to the unnecessary over use of concatenation and excessively long line lengths.

    IMO (and what I was taught), line lengths should be kept below 80 chars as much as reasonably possible but can go over a little (no more than 100) on occasion.

    Concatenation (and its accompanying additional quotes) should be used only when it's required or logically and syntactically reasonable.

    Adjusting the quoting and vertical/horizontal whitespace will solve both of those problems and make your code more readable and maintainable.

    For example, I'd add a little horizontal white space to your bind param statements to line the vars up vertically (i.e., columnar format).
    PHP Code:

                         $Statement
    ->bindParam(':Quantity',      $OrderQuantityPDO::PARAM_INT);
                         
    $Statement->bindParam(':Price',         $Order['Price'], PDO::PARAM_INT);
                         
    $Statement->bindParam(':StockitemID',   $Order['ID'], PDO::PARAM_INT);
                         
    $Statement->bindParam(':DeliveryDate',  $OrderDeliveryPDO::PARAM_STR);
                         
    $Statement->bindParam(':KitchenID',     $_SESSION['KitchenID'], PDO::PARAM_INT);
                         
    $Statement->bindParam(':UserID',        $_SESSION['UserID'], PDO::PARAM_INT); 
    I'd do the same thing with the echo statement that Triple_Nothing used.
    PHP Code:
    # this can be changed to a heredoc, if you wanted
    echo "
    INSERT INTO stock_orders (
                              Quantity,
                              Price,
                              StockitemID,
                              DeliveryDate,
                              KitchenID,
                              UserID
                              )
    VALUES (
            '
    $OrderQuantity',
            '
    {$Order['Price']}',
            '
    {$Order['ID']}',
            '
    $OrderDelivery',
            '
    {$_SESSION['KitchenID']}',
            '
    {$_SESSION['UserID']}'
            ) 
    ON DUPLICATE KEY UPDATE Quantity = 
    $OrderQuantity;
    "

    This makes it much easier to read, maintain and find errors.

    Comments on this post

    • Triple_Nothing agrees : Nicely displayed. Visual appearance is EXTREMELY helpful in troubleshooting. ;-)

IMN logo majestic logo threadwatch logo seochat tools logo