Update with mySQLi Prepared Statement

Question

I have written the code below and it is supposed to update a selected entry in the database when the user presses the update button. When I press the update button I just get a blank page. I cannot figure out what is wrong with my code. Any help would be appreciated.

        <?php
    //Open a new connection to the MySQL server
    $mysqli = new mysqli('localhost','some directory','some password','some user ');

    //Output any connection error
    if ($mysqli->connect_error) {
        die('Connection failed : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }

 // check if the 'id' variable is set in URL, and check that it is valid
     if (isset($_GET['cd']) && is_numeric($_GET['cd']))

     // get id value
     $id = $_GET['cd'];


$firstname  = $_POST['firstname']; 
$lastname   = $_POST['lastname'];
$phonenumber    = $_POST['phonenumber'];    
$city       = $_POST['city'];
$state      = $_POST['state'];
$zipcode    = $_POST['zipcode'];
$dob        = $_POST['dob'];
$doi        = $_POST['doi'];
$adjustername   = $_POST['adjustername'];
$claimrefnumber = $_POST['claimrefnumber'];
$providernature = $_POST['providernature'];
$created    = $_POST['created'];
$language   = $_POST['language'];
$client     = $_POST['client'];
$amountauthorized = $_POST['amountauthorized'];
$active     = $_POST['active'];
$invoiceformat  = $_POST['invoiceformat'];


$query = ("UPDATE tabele SET firstname, lastname, phonenumber, city, state, zipcode, dob, doi, adjustername, claimrefnumber, providernature, created,language, client, amountauthorized, active, invoiceformat, WHERE id)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

$statement = $mysqli->prepare($query);

$statement->bind_param('isssssssssssssssss', $id, $firstname, $lastname, $phonenumber, $city, $state, $zipcode, $dob, $doi, $adjustername, $claimrefnumber, $providernature, $created, $language, $client, $amountauthorized, $active, $invoiceformat);

if($statement->execute()){
 header("some location");
    }else{
        die('Error : ('. $mysqli->errno .') '. $mysqli->error);
    }
    $statement->close();
?>

blank page = something blew up and all debug options are disabled. turn on `display_errors` and `error_reporting` at the php.ini level and try again. — Marc B, Jul 09 '15 at 16:48
but your SQL does have a syntax error. an insert query can NOT have a `where` clause. `... invoiceformat, WHERE id) ...` — Marc B, Jul 09 '15 at 16:49
you don't. inserts can't be made conditional... if you want to decide whether to insert or not, you have to do that in client-side code. — Marc B, Jul 09 '15 at 16:58
@MarcB I'm not trying to do a conditional I need to update the entry where the id is equal to the value being passed in the url. — Marquise R, Jul 09 '15 at 17:12

Marquise R · Accepted Answer · 2015-07-10T00:51:01.927

I figured it out, I finally found an example that works although I had to tweak some parts. The code below will grab the id passed from the url, do a prepared statement to update the selected id, and then redirect to a url location. The only thing missing is a Limit statement which I haven't figured out how to make work.

The code will also work to delete will be nearly identical with a few minor tweaks.

<?php
     // check if the 'id' variable is set in URL, and check that it is valid
     if (isset($_GET['id']) && is_numeric($_GET['id']))

     // get id value
     $id = $_GET['id'];

    $results = $id;

    $firstname  = $_POST['firstname']; 
    $lastname   = $_POST['lastname'];
    $phonenumber    = $_POST['phonenumber'];    
    $city       = $_POST['city'];
    $state      = $_POST['state'];
    $zipcode    = $_POST['zipcode'];
    $dob        = $_POST['dob'];
    $doi        = $_POST['doi'];
    $adjustername   = $_POST['adjustername'];
    $claimrefnumber = $_POST['claimrefnumber'];
    $providernature = $_POST['providernature'];
    $created    = $_POST['created'];
    $language   = $_POST['language'];
    $client     = $_POST['client'];
    $amountauthorized = $_POST['amountauthorized'];
    $active     = $_POST['active'];
    $invoiceformat  = $_POST['invoiceformat'];

   $connection = new mysqli("localhost", "some directory", "some password", "some user");
   $statement = $connection->prepare("update table set firstname = ?, lastname = ?, phonenumber = ?, city = ?, state = ?, zipcode = ?, dob = ?, doi = ?, adjustername = ?, claimrefnumber = ?, providernature = ?, created = ?,language = ?, client = ?, amountauthorized = ?, active = ?, invoiceformat = ? where id = ?");
   $statement->bind_param("sssssssssssssssssi", $firstname, $lastname, $phonenumber, $city, $state, $zipcode, $dob, $doi, $adjustername, $claimrefnumber, $providernature, $created, $language, $client, $amountauthorized, $active, $invoiceformat, $id);

   $statement->execute();
    if($statement->execute()){
       header("some location");
    }else{
        die('Error : ('. $mysqli->errno .') '. $mysqli->error);
    }
    $statement->close();
?>

Update with mySQLi Prepared Statement

1 Answers1