Data not being inserted into SQL

Beginners - Learning PHP
#1

Hi can someone please look over this and tell me why the data isnt being entered into SQL… it was so all the connections are fine and i can view the data. I just cant create new nor can i edit.

<?php
/*
Allows the user to both create new records and edit existing records
*/

// connect to the database
include("connect.php");

// creates the new/edit record form
// since this form is used multiple times in this file, I have made it a function that is easily reusable
function renderForm($item1 = '', $cost1 ='',  $size1 ='', $catagory1 ='',$error = '', $id = '')
{ ?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>
<?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?>
</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
</head>
<body>
<h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1>
<?php if ($error != '') {
echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
. "</div>";
} ?>

<form action="" method="post">
<div>
<?php if ($id != '') { ?>
<input type="hidden" name="id" value="<?php echo $id; ?>" />
<p>ID: <?php echo $id; ?></p>
<?php } ?>

<strong>ITEM: *</strong> 
<input type="text" name="item" value="<?php echo $item1; ?>"/><br/>

<strong>COST: *</strong> 
<input type="text" name="cost" value="<?php echo $cost1; ?>"/><br/>

<strong>SIZE: *</strong> 
<input type="text" name="size" value="<?php echo $size1; ?>"/><br/>

<strong>CATAGORY: *</strong> 
<input type="text" name="catagory" value="<?php echo $catagory1; ?>"/><br/> 
<!-- My attempt in trying to use a selection box which failed.
<select name="catagory">
    <option value="Steaks">Steaks</option>
    <option value="<?php echo $catagory1; ?>"/>Burger Sausages Bacon Mince Chops</option>
    <option value="<?php echo $catagory1; ?>"/>Chicken</option>
    <option <?php if (isset($catagory) && $catagory=='Frozen Goods') echo 'checked';?> value="Frozen Goods">Frozen Goods</option>
    <option <?php if (isset($catagory) && $catagory=='Fruit and Veg') echo 'checked';?> value="Fruit and Veg">Fruit and Veg</option>
    <option <?php if (isset($catagory) && $catagory=='Other Things') echo 'checked';?> value="Other Things">Other Things</option>
    <option value="<?php echo $catagory1; ?>">Snacks</option>
    <option <?php if (isset($catagory) && $catagory=='Beers and Ciders') echo 'checked';?> value="Beers and Ciders">Beers and Ciders</option>
    <option <?php if (isset($catagory) && $catagory=='Fortified Wines') echo 'checked';?> value="Fortified Wines">Fortified Wines</option>
    <option <?php if (isset($catagory) && $catagory=='Liqueurs') echo 'checked';?> value="Liqueurs">Liqueurs</option>
    <option <?php if (isset($catagory) && $catagory=='Soft Drinks') echo 'checked';?> value="Soft Drinks">Soft Drinks</option>
    <option <?php if (isset($catagory) && $catagory=='Spirits') echo 'checked';?> value="Spirits">Spirits</option>
    <option <?php if (isset($catagory) && $catagory=='Wines') echo 'checked';?> value="Wines">Wines</option>
</select>
-->

<p>* required</p>


<input type="submit" name="submit" value="Submit" />
</div>
</form>
</body>
</html>

<?php }










/*

EDIT RECORD

*/
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$item = htmlentities($_POST['item'], ENT_QUOTES);
$cost = htmlentities($_POST['cost'], ENT_QUOTES);
$size = htmlentities($_POST['size'], ENT_QUOTES);
$catagory = htmlentities($_POST['catagory'], ENT_QUOTES);


// check that firstname and lastname are both not empty
if ($item == '' || $cost == '' || $size == '' || $catagory == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($item, $cost, $size, $catagory, $error, $id);
}
else
{
// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE products SET item = ?, cost = ?, size = ?, Catagory = ? 
WHERE id=?"))
{
$stmt->bind_param("ssi", $item, $cost, $size, $catagory, $id);
$stmt->execute();
$stmt->close();
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}

// redirect the user once the form is updated
header("Location: adminconsole.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];

// get the recod from the database
if($stmt = $mysqli->prepare("SELECT * FROM products WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();

$stmt->bind_result($id, $item, $cost, $size, $catagory);
$stmt->fetch();

// show the form
renderForm($item, $cost, $size, $catagory, NULL, $id);

$stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the adminconsole.php page
else
{
header("Location: adminconsole.php");
}
}
}










/*

NEW RECORD

*/
// if the 'id' variable is not set in the URL, we must be creating a new record
else
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// get the form data
$item = htmlentities($_POST['item'], ENT_QUOTES);
$cost = htmlentities($_POST['cost'], ENT_QUOTES);
$size = htmlentities($_POST['size'], ENT_QUOTES);
$catagory = htmlentities($_POST['catagory'], ENT_QUOTES);
// check that firstname and lastname are both not empty
if ($item == '' || $cost == '' || $size == '' || $catagory == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($item, $cost, $size, $catagory, $error);
}
else
{
// insert the new record into the database
if ($stmt = $mysqli->prepare("INSERT products (item, cost, size, catagory) VALUES (?, ?, ? ,?)"))
{
$stmt->bind_param("ss", $item, $cost, $size, $catagory);
$stmt->execute();
$stmt->close();
}
// show an error if the query has an error
else
{
echo "ERROR: Could not prepare SQL statement.";
}

// redirec the user
header("Location: adminconsole.php");
}

}
// if the form hasn't been submitted yet, show the form
else
{
renderForm();
}
}

// close the mysqli connection
$mysqli->close();
?>

original script credit to “falkencreative”

#2

You should add some error handling to your db code.

Here you give three types (ssi) but bind 4 variables.

Same issue here

Also I would not do this before inserting into the DB. It’s great to escape data but it’s much better to escape data on output instead of storing “mangled” data in the database. When you escape on output you can use an escape method that suits the output format.

$item = htmlentities($_POST['item'], ENT_QUOTES);
$cost = htmlentities($_POST['cost'], ENT_QUOTES);
$size = htmlentities($_POST['size'], ENT_QUOTES);
$catagory = htmlentities($_POST['catagory'], ENT_QUOTES);
1 Like
#3

perfect thank you for pointing me in the right direction, it is now working.

#4

To be honest, there is nothing to credit. That code is poorly written and needs a complete re-write.

#5

good or bad, its still 90% someone elses so i feel i still should. :slight_smile:

Im doing this to learn as a complete novice so to me this is really technicaly. Hopefully I will be at your guys level and be able to critique it myself :slight_smile:

#6

Since you have a good example of how not to do it, here are some things that should be changed…

The function renderForm should go. The header part should be in its own file, header.php and included in your main file, say index.php

The inline CSS should go in its own CSS file, styles.css and included in the header.php

The form action should be completely removed.

The input values need to be checked that they exist before you try to use them.

The category select is a mess.We can get to that later.

Submit button doesnt need a name.

You need to check the REQUEST METHOD, not hope the name of a button is submitted in order for the code to work.

Create a function for htmlentites. Too much repetition

You need to kill the script after a header redirect or the script will keep running

is_numeric is not the right function for checking an ID

There is no need to manually close the DB connection. Php will do it automatically.

Sponsor our Newsletter | Privacy Policy | Terms of Service