World Outside World Outside - 18 days ago 7
MySQL Question

Php Prepared Statement Fails to insert data into database through form

I'm trying to insert data into a database, the script executes all the way down and redirect the user to the page but no record gets inserted. I've been trying to figure out why for hours. I keep creating new scripts but i'm missing something apparently.

init.php

<?php
/*for error 1045 config.inc.php*/
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "databasename");
$db = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
if(mysqli_connect_errno()){
echo "database failed to connect with following errors:". mysqli_connect_error();
die();
}

require_once $_SERVER['DOCUMENT_ROOT'].'/ecommerce/config.php';
require_once BASEURL.'helpers/helpers.php';


form.php

<?php
ob_start();
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once $_SERVER['DOCUMENT_ROOT'].'/HeleneQuirion/core/init.php';
include 'includes/header.php';

if(isset($_GET['add'])){
$parentQuery = $db->query("SELECT * FROM categories WHERE parent= 0" );

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

// prepare and bind
$stmt = $db->prepare("INSERT INTO product (prod_name, categories, list_price, price,prod_width,prod_depth,prod_height,prod_material,quantity,image_1,image_2,image_3,image_4,description,care_instructions) VALUES (?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?)");
$stmt->bind_param("ssiiiiisissssss" ,$prod_name, $child,$list_price,$price,$prod_width,$prod_depth,$prod_height,$prod_material,$quantity,$image_1,$image_2,$image_3,$image_4,$description,$care_instructions);

// set parameters and execute
if(isset($_POST['prod_name']))
{
$prod_name = sanitize($_POST)['prod_name'];
}

if(isset($_POST['child']))
{
$categories = sanitize($_POST)['child'];
}

if(isset($_POST['list_price']))
{
$prod_name = sanitize($_POST)['list_price'];
}

if(isset($_POST['price']))
{
$price = sanitize($_POST)['price'];
}

if(isset($_POST['prod_width']))
{
$prod_width = sanitize($_POST)['prod_width'];
}

if(isset($_POST['prod_depth']))
{
$prod_depth = sanitize($_POST)['prod_depth'];
}

if(isset($_POST['prod_height']))
{
$prod_height = sanitize($_POST)['prod_height'];
}

if(isset($_POST['prod_material']))
{
$prod_material = sanitize($_POST)['prod_material'];
}

if(isset($_POST['quantity']))
{
$quantity = sanitize($_POST)['quantity'];
}

if(isset($_POST['care_instructions']))
{
$care_instructions = sanitize($_POST)['care_instructions'];
}

if(isset($_POST['image_1']))
{
$image_1 = $_FILES['image_1'];
}

if(isset($_POST['image_2']))
{
$image_2 = $_FILES['image_2'];
}

if(isset($_POST['image_3']))
{
$image_3 = $_FILES['image_3'];
}

if(isset($_POST['image_4']))
{
$image_4 = $_FILES['image_4'];
}

if(isset($_POST['description']))
{
$description = sanitize($_POST)['description'];
}
$stmt->execute() or die(mysqli_error($db));
header('Location: products.php');

$stmt->close();
$conn->close();
}
?>


<form action="products.php?add=1" method="POST" enctype="multipart/form-data">

<div class='container_12'>
<div class="form-group col-md-3">
<label for="prod_name">Product Name*:</label>
<input type="text" name="prod_name" id="prod_name" class="form-control" value="<?=((isset($_POST['prod_name']))?sanitize($_POST['prod_name']):' ');?>">


</div>
<div class="form-group col-md-3">
<label for="parent">Parent Category*:</label>
<select class="form-control" id="parent" name="parent">
<option value=""<?=((isset($_POST['parent']) && $_POST['parent'] == '')?'selected':'');?>></option>
<?php while($parent = mysqli_fetch_assoc($parentQuery)): ?>
<option value=" <?=$parent['id'];?>"<?=((isset($_POST['parent']) && $_POST['parent'] == $parent['id'])?' select':'');?>><?=$parent['category_name'];?></option>
<?php endwhile; ?>
</select>
</div>
<div class='form-group col-md-3'>
<label for='child'>Second Category*:</label>
<select id='child' name='child' class='form-control'></select>
</div>
</div>

<div class='container_12'>

<div class='form-group col-md-3'>
<label for='list_price'>List Price(OPTIONAL): </label>
<input type="text" id="list_price" name="list_price" class="form-control" value="<?=((isset($_POST['list_price']))?sanitize($_POST['list_price']):'');?>">
</div>

<div class="form-group col-md-3">
<label for="price">Price*:</label>
<input type="text" id="price" name="price" class="form-control" value="<?=((isset($_POST['price']))?sanitize($_POST['price']):'');?>">
</div>

<div class='form-group col-md-3'>
<label for='prod_width'>Width* (in inches):</label>
<input type="text" id="prod_width" name="prod_width" class="form-control" value="<?=((isset($_POST['prod_width']))?sanitize($_POST['prod_width']):'');?>">
</div>

<div class='form-group col-md-3'>
<label for='prod_depth'>Height*(in inches):</label>
<input type="text" id="'prod_depth" name="'prod_depth" class="form-control" value="<?=((isset($_POST['prod_depth']))?sanitize($_POST['prod_depth']):'');?>">
</div>
</div>

<div class='container_12'>
<div class='form-group col-md-3'>
<label for='prod_height'>Depth*(in inches):</label>
<input type="text" id="prod_height" name="prod_height" class="form-control" value="<?=((isset($_POST['prod_height']))?sanitize($_POST['prod_height']):'');?>">
</div>

<div class='form-group col-md-3'>
<label for='prod_material'>Construction Material:</label>
<input type="text" id="prod_material" name="prod_material" class="form-control" value="<?=((isset($_POST['prod_material']))?sanitize($_POST['prod_material']):'');?>">

</div>

<div class='form-group col-md-6'>
<label>Quantity * :</label>
<input type="text" id="quantity" name="quantity" class="form-control" value="<?=((isset($_POST['quantity']))?sanitize($_POST['quantity']):'');?>">

</div>
</div>

<div class='container_12'>
<div class="form-group col-md-3"> <label for="image_1">Product Photo #1:</label>
<input type="file" name="image_1" id="image_1" class="form-control">
</div>
<div class="form-group col-md-3"> <label for="image_2">Product Photo #2:</label>
<input type="file" name="image_2" id="image_2" class="form-control">
</div>
<div class="form-group col-md-3"> <label for="image_3">Product Photo #3:</label>
<input type="file" name="image_3" id="image_3" class="form-control">
</div>
<div class="form-group col-md-3"> <label for="image_4">Product Photo#4:</label>
<input type="file" name="image_4" id="image_4" class="form-control">
</div>

</div>



<div class='container_12'>
<div class="form-group col-md-6">
<label for="description">Description:</label>
<textarea id="description" name="description" class="form-control" rows="6"><?=((isset($_POST['description']))?sanitize($_POST['description']):'');?></textarea>
</div>



<div class="form-group col-md-6">
<label for="care_instructions">Care Instructions*:</label>
<textarea id="care_instructions" name="care_instructions" class="form-control" rows="6"><?=((isset($_POST['care_instructions']))?sanitize($_POST['care_instructions']):'');?></textarea>
</div></div>

<div class='container_12'>
<div class="form-group pull-right">
<input type='submit' name='submit' value='Add Product' class='form-control btn-success pull-right'>
</div></div>
</form>

Answer

This is just to clarify one of the points in the comments above... So I knocked up some test code to check what is going on...

<?php

$_POST['prod_name'] = 'fred';

function sanitize($thing){
    var_dump("Inside Sanitize- ", $thing);
    return $thing;
}
// Original Code - sends an array to sanitize
if(isset( $_POST['prod_name'] )) {
    $prod_name = sanitize($_POST)['prod_name'];
}
var_dump('Original Version '.$prod_name);

// New Code 1 - Sends a String to sanitize
if(isset( $_POST['prod_name'] )) {
    $prod_name = sanitize($_POST['prod_name']);
}

// New Code 2 - Should use this one, or could make this a function.
$prod_name = isset($_POST['prod_name'])? sanitize($_POST['prod_name']):'';

var_dump($prod_name);