mysqli update not working

[laflair13]

I am trying to convert my site to mysqli and I cannot get the databse to update or the results to show on the site.

 

If you could look at my code and please advise to what I could be doing wrong I would greatly appreciate it.

 

<?php


$db = new mysqli("localhost","admin","password","database"); 


if(!$db) {
    die('sorry we are having some problbems');
}


if ($_POST['submit']) {

$id = $_POST['id'];
$itemname = $_POST['itemname'];
$manufacture = $_POST['manufacture'];
$model = $_POST['model'];


//below are checkboxes
$showmanu = $_POST['showmanu'];
$showmodel = $_POST['showmodel'];
$showserial = $_POST['showserial'];


$query = "UPDATE new_equip SET itemname=?, manufacture=?, model=?, showmanu=?, showmodel=?, showserial=? WHERE id=?  LIMIT 1";
$conn = $db->prepare($query);
$conn->bind_param("sssiii", $item, $manufacture, $model, $showmanu, $showmodel, $showserial, $id);


if ($conn->execute()) {
header('location: inventory.php?Msg=Update');
}
$db->close();
}


?>

This is the tutorial and code I was using as reference.

http://coderlearner.com/PHP_MySQLi_Example_Update_Record

[Barand]

Your problem is most likely the checkboxes. If a checkbox is not checked the no data is posted so you need to check

$showmodel - isset($_POST['showmodel']) ? 1 : 0;   // set value if isset

[laflair13]

I have the checkboxes so that if they are checked they show on the frontend. Thats a whole other issue I cant solve.

 

But it still didnt work even if I checked the box.

 

After doing some research, I seen that another way of doing it was like below. This where I am confused, Seems there are different ways to do this.

$query = "UPDATE new_equip SET `itemname`=?, `manufacture`=?, `model`=?, `showmanu`=?, `showmodel`=?, `showserial`=? WHERE `id`=? LIMIT 1";
$conn = $db->prepare($query);
$conn->bind_param('sssiii', $_POST['item'], $_POST['manufacture'], $_POST['model'], $_POST['showmanu'], $_POST['showmodel'], $_POST['showserial']);

[Barand]

Also when you bind_params you have 'sssiii' and 7 params and 7 placeholders. (In your second version only 6 params - you forgot the id)

[laflair13]

It still isnt working. Here is what I have for the query.

 

$query = "UPDATE new_equip SET `itemname`=?, `manufacture`=?, `model`=?, `serial`=?, `year`=?, `condition`=?, `category`=?, `desc`=?, `dimension`=?, `location`=?, `price`=?, `purchase`=?, `addedby`=?, `notes`=?, `ran`=?, `electrical`=?, `owner`=?, `featured`=?, `showmanu`=?, `showmodel`=?, `showserial`=?, `showyear`=?, `showdem`=?, `showelec`=?, `showran`=?, `showloca`=?, `showown`=?, `showpur`=?, `showsale`=? WHERE id=?  LIMIT 1";
$conn = $db->prepare($query);
$conn->bind_param("sssssssssssssssssiiiiiiiiiiiii", $itemname, $manufacture, $model, $serial, $year, $condition, $category, $desc, $dimension, $location, $price, $purchase, $addedby, $notes, $ran, $electrical, $owner, $featured, $showmanu, $showmodel, $showserial, $showyear, $showdem, $showelec, $showran, $showloca, $showown, $showpur, $showsale, $EditID);

if ($conn->execute()) {
            header('location: inventory.php?Msg=Update');
        } 

$db->close();
}

I count 30 on both the placeholder and params

 

And when I click submit it is just showing a blank EditPost.php

Edited March 5, 2015 by laflair13

[Barand]

Ask it what the error is.

if ($conn->execute()) {
            header('location: inventory.php?Msg=Update');
        } 
else echo $conn->error;

[laflair13]

I tried that, nothing comes up, just a blank EditPost.php

[Barand]

Have you got php error reporting turned on?

[laflair13]

If you mean by adding this code to the .htaccess, it makes my site have a 500 error

 

ini_set('display_startup_errors',1);

ini_set('display_errors',1);

error_reporting(-1);

 

So I created a php.ini file and added the code above to that. But still all I am getting is a blank EditPost.php page.

Edited March 5, 2015 by laflair13

[Barand]

.htaccess?

 

That code should be at the top of your script or, better, set in the ini file.

 

phpinfo() will tell you where your current ini file is.

[laflair13]

Edited last post. I had to create one, but it is still coming up blank.

[Barand]

An ini file is *not* a php file and doesn't contain code. It should look like this in the ini file

error_reporting = E_ALL
display_errors = On

[laflair13]

Still comes up a blank page

[laflair13]

Ok, I was able to get this to come up.

Notice: Undefined index: id in /edit-item.php on line 9

This is being used to auto-populate the item data in the fields.

$eid = (int) $_GET['id'];

[Barand]

you may need to restart you server software (Apache, IIS) after changing the ini file.

[Barand]

$_GET ? All the other input comes from $_POST

[laflair13]

That is at the top of edit-item.php. It grabs the item info from the database and pre-fills the fields on the page.

 

So here is what that file looks like.

 

edit-item.php (on the top)

<?PHP
session_start();
if (isset($_SESSION['loggedin']) && $_SESSION['loggedin'] == true) {
    
  } else {
header ("Location: index.php");
}
 $eid = (int) $_GET['id'];

 include_once('../mysql_connect.php');
 if(isset($_POST['marksold']) && isset($_POST['id']))
 {
     mysql_query("UPDATE new_equip SET sold='1' WHERE id='" . mysql_real_escape_string($_POST['id']) . "'");
 }

?>

The form (Some fields removed to save room for post) 

<form method="post" action="EditPost2.php" enctype="multipart/form-data" class="form-horizontal" accept-charset="UTF-8">
    <div class="form-group">
        <label class="col-md-3">Item ID</label>
        <div class="col-md-8">
            <input type="text" name="EditID" value="<?php echo $row['id']; ?>" class="form-control" />
        </div> <!-- /.col -->
    </div> <!-- /.form-group -->
    
    <div class="form-group">
        <label class="col-md-3">Item Name</label>
        <div class="col-md-8">
            <input type="text" name="itemname" value="<?php echo $row['itemname']; ?>" class="form-control" />
        </div> <!-- /.col -->
    </div> <!-- /.form-group -->
    
    <div class="form-group">
        <label class="col-md-3">Manufacture</label>
        <div class="col-md-8">
            <input type="text" name="manufacture" value="<?php echo $row['manufacture']; ?>" class="form-control" />
        </div> <!-- /.col -->
        <input type="checkbox" name="showmanu" value="1" <?php echo ($row['showmanu'] == 1) ? 'checked="checked"' : ''; ?> />
        <span style="float:right; font-size: 10px; margin-top: 4px">Check to show</span>
    </div> <!-- /.form-group --><div class="form-group">
<div class="col-md-7 col-md-push-3">                                                    
<button type="submit" name="submit" class="btn btn-primary" >Save Changes</button>
       
<button type="reset" class="btn btn-default">Cancel</button>
</div> <!-- /.col -->
</div> <!-- /.form-group -->

 

                                                

 

Then of course my EditPost.php (Some fields taken out to save room)

<?php
error_reporting(E_ALL);

$db = new mysqli("localhost","admin","password","database"); 

if(!$db) {
    die('sorry we are having some problbems');
}

if(isset($_POST['submit'])) {

$id = $_POST['EditID'];
$itemname = $_POST['itemname'];
$manufacture = $_POST['manufacture'];

//Below are checkboxes
$showpur = $_POST['showpur'];
$showsale = $_POST['showsale'];

$query = "UPDATE new_equip SET `itemname`=?, `manufacture`=?, `showpur`=?, `showsale`=? WHERE id=?  LIMIT 1";
$conn = $db->prepare($query);
$conn->bind_param("ssiii", $itemname, $manufacture, $showpur, $showsale, $EditID);

if ($conn->execute()) {
            header('location: inventory.php?Msg=Update');
        } 
else echo $conn->error;


$db->close();
}

?>

 

Edited March 5, 2015 by laflair13

[Barand]

that is labeled EditPost.php and form calls EditPost2.php ??

[laflair13]

EditPost2.php is my mysqli test. I am using that until I get it working 100%. EditPost.php is mysql and works good, just trying to convert my site to mysqli

[Barand]

the error

Notice: Undefined index: id in /edit-item.php on line 9

is from edit-item.php. From what I see you try to set a value of $eid from a non-existent value but then it isn't used after that. The query uses a POST variable

[mac_gyver]

I am trying to convert my site ...

 

then you would probably want to take the time to do it in general, so that you don't need to repeat code over and over.

 

you should extend whatever database library you are using (PDO works best for this), to create a generic prepared query method, that you can simply supply the sql statement and an (optional) array of input parameters/types, and it will handle preparing the query, binding any input data, running the query, and returning the result of the query (array of data, even if empty, for select/show queries, true/false status from delete/update queries.)