Hi everyone, I have the following template file that displays a whole heap of form elements so someone can enter in the details for new items in my items table: <?php include_once(__ROOT__ . "/includes/helpers.inc.php"); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <title><?php htmlout($pagetitle); ?></title> <meta http-equiv="content-type" content="text/html; charset=utf-8"/> <style type="text/css"> textarea { display: block; width: 100%; } </style> </head> <body> <h1><?php htmlout($pagetitle); ?></h1> <form action="?<?php htmlout($action); ?>" method="post"> <div> <label for="title">Title:</label> <input type="text" name="title" id="title" value="<?php htmlout($itemTitle); ?>"/> </div> <div> <label for="itemSKULadies">Ladies SKU:</label> <input type="text" name="itemSKULadies" id="itemSKULadies" value="<?php htmlout($itemSKULadies); ?>"/> </div> <div> <label for="itemSKUMen">Men's SKU:</label> <input type="text" name="itemSKUMen" id="itemSKUMen" value="<?php htmlout($itemSKUMen); ?>"/> </div> <div> <label for="itemDescLadies">Ladies Description:</label> <textarea id="itemDescLadies" name="itemDescLadies" rows="3" cols="40"><?php htmlout($itemDescLadies); ?></textarea> </div> <div> <label for="itemDescMen">Men's Description:</label> <textarea id="itemDescMen" name="itemDescMen" rows="3" cols="40"><?php htmlout($itemDescMen); ?></textarea> </div> <div> <label for="sizeLadies">Ladies Sizes:</label> <select name="sizeLadies" id="sizeLadies"> <option value="">Select one</option> <?php foreach ($sizesLadies as $size): ?> <option value="<?php htmlout($size['sizeLadiesID']); ?>"<?php if ($size['sizeLadiesID'] == $sizeLadiesID) echo ' selected="selected"'; ?>><?php htmlout($size['size']); ?></option> <?php endforeach; ?> </select> </div> <div> <label for="sizeMen">Men's Sizes:</label> <select name="sizeMen" id="sizeMen"> <option value="">Select one</option> <?php foreach ($sizesMen as $size): ?> <option value="<?php htmlout($size['sizeMenID']); ?>"<?php if ($size['sizeMenID'] == $sizeMenID) echo ' selected="selected"'; ?>><?php htmlout($size['size']); ?></option> <?php endforeach; ?> </select> </div> <div> <label for="itemPrice">Price:</label><input type="text" name="itemPrice" id="itemPrice" value="<?php htmlout($itemPrice); ?>"/> </div> <div> <label for="itemColours">Colours:</label><input type="text" name="itemColours" id="itemColours" value="<?php htmlout($itemColours); ?>"/> </div> <div> <label for="category">Category:</label> <select name="category" id="category"> <option value="">Select one</option> <?php foreach ($categories as $category): ?> <option value="<?php htmlout($category['catID']); ?>"<?php if ($category['catID'] == $catID) echo ' selected="selected"'; ?>><?php htmlout($category['category']); ?></option> <?php endforeach; ?> </select> </div> <div> <label for="supplier">Supplier:</label> <select name="supplier" id="supplier"> <option value="">Select one</option> <?php foreach ($suppliers as $supplier): ?> <option value="<?php htmlout($supplier['supplierID']); ?>"<?php if ($supplier['supplierID'] == $supplierID) echo ' selected="selected"'; ?>><?php htmlout($supplier['supplier']); ?></option> <?php endforeach; ?> </select> </div> <div> <label for="itemType">Item type:</label> <select name="itemType" id="itemType"> <option value="">Select one</option> <?php foreach ($itemTypes as $itemType): ?> <option value="<?php htmlout($itemType['itemTypeID']); ?>"<?php if ($itemType['itemTypeID'] == $itemTypeID) echo ' selected="selected"'; ?>><?php htmlout($itemType['itemType']); ?></option> <?php endforeach; ?> </select> </div> <fieldset> <legend>Subcategories:</legend> <?php foreach ($subcategories as $subcategory): ?> <div><label for="subcategory<?php htmlout($subcategory['subcatID']); ?>"><input type="checkbox" name="subcategories[]" id="subcategory<?php htmlout($subcategory['subcatID']); ?>" value="<?php htmlout($subcategory['subcatID']); ?>"<?php if ($subcategory['selected']) { echo ' checked="checked"'; } ?>/><?php htmlout($subcategory['subcategory']); ?></label></div> <?php endforeach; ?> </fieldset> <fieldset> <legend>Site Section:</legend> <?php foreach ($siteSections as $siteSection): ?> <div><label for="siteSection<?php htmlout($siteSection['siteSectionID']); ?>"><input type="checkbox" name="siteSections[]" id="siteSection<?php htmlout($siteSection['siteSectionID']); ?>" value="<?php htmlout($siteSection['siteSectionID']); ?>"<?php if ($siteSection['selected']) { echo ' checked="checked"'; } ?>/><?php htmlout($siteSection['siteSection']); ?></label></div> <?php endforeach; ?> </fieldset> <div> <input type="hidden" name="itemID" value="<?php htmlout($itemID); ?>"/> <input type="submit" value="<?php htmlout($button); ?>"/> </div> </form> </body> </html> PHP: The above template file is controlled by the following controller file: <?php if ($_SERVER['HTTP_HOST'] != "new_site.com") { define ('__ROOT__', $_SERVER['DOCUMENT_ROOT'] . '/new_site'); } else { define ('__ROOT__', $_SERVER['DOCUMENT_ROOT']); } include_once(__ROOT__ . "/includes/magicquotes.inc.php"); if (isset($_GET['add'])) { $pagetitle = 'New item'; $action = 'addform'; $itemTitle = ''; $itemSKULadies = ''; $itemSKUMen = ''; $itemDescLadies = ''; $itemDescMen = ''; $itemPrice = ''; $itemColours = ''; $itemcategory; $itemID = ''; $supplierID = ''; $itemTypeID = ''; $catID = ''; $sizeLadiesID = ''; $sizeMenID = ''; $button = 'Add item'; include(__ROOT__ . "/includes/dbAdmin.inc.php"); // Build the list of suppliers $sql = "SELECT supplierID, supplier FROM suppliers"; $result = mysqli_query($link, $sql); if (!$result) { $error = 'Error fetching list of suppliers.'; include 'error.html.php'; exit(); } while ($row = mysqli_fetch_array($result)) { $suppliers[] = array('supplierID' => $row['supplierID'], 'supplier' => $row['supplier']); } // Build the list of item types $sql = "SELECT itemTypeID, itemType FROM itemTypes"; $result = mysqli_query($link, $sql); if (!$result) { $error = 'Error fetching list of item types.'; include 'error.html.php'; exit(); } while ($row = mysqli_fetch_array($result)) { $itemTypes[] = array('itemTypeID' => $row['itemTypeID'], 'itemType' => $row['itemType']); } // Build the list of categories $sql = "SELECT catID, category FROM categories"; $result = mysqli_query($link, $sql); if (!$result) { $error = 'Error fetching list of categories.'; include 'error.html.php'; exit(); } while ($row = mysqli_fetch_array($result)) { $categories[] = array('catID' => $row['catID'], 'category' => $row['category']); } // Build the list of ladies sizes $sql = "SELECT sizeLadiesID, size FROM sizesLadies"; $result = mysqli_query($link, $sql); if (!$result) { $error = 'Error fetching list of ladies sizes.'; include 'error.html.php'; exit(); } while ($row = mysqli_fetch_array($result)) { $sizesLadies[] = array('sizeLadiesID' => $row['sizeLadiesID'], 'size' => $row['size']); } // Build the list of men's sizes $sql = "SELECT sizeMenID, size FROM sizesMen"; $result = mysqli_query($link, $sql); if (!$result) { $error = 'Error fetching list of men\'s sizes.'; include 'error.html.php'; exit(); } while ($row = mysqli_fetch_array($result)) { $sizesMen[] = array('sizeMenID' => $row['sizeMenID'], 'size' => $row['size']); } // Build the list of site sections $sql = "SELECT siteSectionID, siteSection FROM siteSections"; $result = mysqli_query($link, $sql); if (!$result) { $error = 'Error fetching list of site sections.'; include 'error.html.php'; exit(); } while ($row = mysqli_fetch_array($result)) { $siteSections[] = array( 'siteSectionID' => $row['siteSectionID'], 'siteSection' => $row['siteSection'], 'selected' => FALSE); } // Build the list of subcategories $sql = "SELECT subcatID, subcategory FROM subcategories"; $result = mysqli_query($link, $sql); if (!$result) { $error = 'Error fetching list of subcategories.'; include 'error.html.php'; exit(); } while ($row = mysqli_fetch_array($result)) { $subcategories[] = array( 'subcatID' => $row['subcatID'], 'subcategory' => $row['subcategory'], 'selected' => FALSE); } include 'form.html.php'; exit(); } if (isset($_GET['addform'])) { include(__ROOT__ . "/includes/dbAdmin.inc.php"); $title = mysqli_real_escape_string($link, $_POST['title']); $itemSKULadies = mysqli_real_escape_string($link, $_POST['itemSKULadies']); $itemSKUMen = mysqli_real_escape_string($link, $_POST['itemSKUMen']); $itemDescLadies = mysqli_real_escape_string($link, $_POST['itemDescLadies']); $itemDescMen = mysqli_real_escape_string($link, $_POST['itemDescMen']); $sizeLadies = mysqli_real_escape_string($link, $_POST['sizeLadies']); $sizeMen = mysqli_real_escape_string($link, $_POST['sizeMen']); $itemPrice = mysqli_real_escape_string($link, $_POST['itemPrice']); $itemColours = mysqli_real_escape_string($link, $_POST['itemColours']); $category = mysqli_real_escape_string($link, $_POST['category']); $supplier = mysqli_real_escape_string($link, $_POST['supplier']); $itemType = mysqli_real_escape_string($link, $_POST['itemType']); if ($sizeLadies == '') { $error = 'You must choose a ladies size for this item. Click ‘back’ and try again.'; include 'error.html.php'; exit(); } elseif ($sizeMen == '') { $error = 'You must choose a men\'s size for this item. Click ‘back’ and try again.'; include 'error.html.php'; exit(); } elseif ($category == '') { $error = 'You must choose a category for this item. Click ‘back’ and try again.'; include 'error.html.php'; exit(); } elseif ($supplier == '') { $error = 'You must choose a supplier for this item. Click ‘back’ and try again.'; include 'error.html.php'; exit(); } elseif ($itemType == '') { $error = 'You must choose an item type for this item. Click ‘back’ and try again.'; include 'error.html.php'; exit(); } $sql = "INSERT INTO items SET itemTitle='$title', itemSKULadies='$itemSKULadies', itemSKUMen='$itemSKUMen'; itemDescLadies='$itemDescLadies', itemDescMen='$itemDescMen', itemPrice='$itemPrice', itemColours='$itemColours', catID='$category', itemTypeID='$itemType', supplierID='$supplier'"; if (!mysqli_query($link, $sql)) { $error = 'Error adding submitted item: ' . mysqli_error($link); include 'error.html.php'; exit(); } $itemID = mysqli_insert_id($link); if (isset($_POST['subcategories'])) { foreach ($_POST['subcategories'] as $subcategory) { $subcategoryid = mysqli_real_escape_string($link, $subcategory); $sql = "INSERT INTO item_to_subcat SET itemID='$itemID', subcategoryid='$subcategoryid'"; if (!mysqli_query($link, $sql)) { $error = 'Error inserting joke into selected subcategory.'; include 'error.html.php'; exit(); } } } if (isset($_POST['siteSections'])) { foreach ($_POST['siteSections'] as $siteSection) { $siteSectionid = mysqli_real_escape_string($link, $siteSection); $sql = "INSERT INTO item_to_siteSection SET itemID='$itemID', siteSectionid='$siteSectionid'"; if (!mysqli_query($link, $sql)) { $error = 'Error inserting joke into selected site section.'; include 'error.html.php'; exit(); } } } header('Location: .'); exit(); } PHP: When the submit button in the template file is pressed, the controller file detects the query string in the action code (action="?<?php htmlout($action); ) and starts executing at the following point in the controller file: if (isset($_GET['addform'])) After filling in the fields in the template file and pressing the submit button I get the following error output to the browser: Error adding submitted item: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; itemDescLadies='asdf', itemDescMen='asdf', itemPrice='40', itemCol' at line 4 The above error is referring to the following SQL code in the controller file: $sql = "INSERT INTO items SET itemTitle='$title', itemSKULadies='$itemSKULadies', itemSKUMen='$itemSKUMen'; itemDescLadies='$itemDescLadies', itemDescMen='$itemDescMen', itemPrice='$itemPrice', itemColours='$itemColours', catID='$category', itemTypeID='$itemType', supplierID='$supplier'"; PHP: I've taken a look but I can't see what's wrong with it, so I wondered if anyone could see what I've done wrong? Really appreciate any help.
If you do echo $sql; PHP: what does it show? More than likely you're not setting a variable. Also, after the mysql_query is run echo out the mysql_error()
Thanks for the reply, When I echo $sql; I get: INSERT INTO items SET itemTitle='adfa', itemSKULadies=';lkj;LKJ', itemSKUMen='l;kj'; itemDescLadies='klj;lkj', itemDescMen=';lkj', itemPrice='30', itemColours='kjhgkhj', catID='1', itemTypeID='1', supplierID='1' I tried echoing the mysql_error() as follows: if (!mysqli_query($link, $sql)) { $error = 'Error adding submitted item: ' . mysqli_error($link); include 'error.html.php'; exit(); } echo mysql_error(); PHP: but nothing was output.
Also, in the original error: Error adding submitted item: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; itemDescLadies='asdf', itemDescMen='asdf', itemPrice='40', itemCol' at line 4 I don't know why it's referring to line 4 as the SQL is not anywhere near line 4?
Thats not how you do an insert. You want something like INSERT INTO items VALUES ('adfa', ';lkj;LKJ', 'l;kj'; 'klj;lkj', ';lkj', '30', 'kjhgkhj', '1', '1', '1') You syntax looks like you are confuse with an update query.
No That method also works Kaizoku http://milov.nl/2836 You need to mysqli_escape_string all values you're inserting, as they're causing the query to break
It works just as well as a normal insert, only less confusing to read what values are assigned to which columns. The manual also has it as the second example of INSERT usage, so why you say it's not standard and should be avoided I don't know
Ok so the query now looks like this: $sql = "INSERT INTO items VALUES (itemTitle='$title', itemSKULadies='$itemSKULadies', itemSKUMen='$itemSKUMen'; itemDescLadies='$itemDescLadies', itemDescMen='$itemDescMen', itemPrice='$itemPrice', itemColours='$itemColours', catID='$category', itemTypeID='$itemType', supplierID='$supplier')"; PHP: I'm still getting the same error though. This has already been done further up in the code, ie. $title = mysqli_real_escape_string($link, $_POST['title']); $itemSKULadies = mysqli_real_escape_string($link, $_POST['itemSKULadies']); $itemSKUMen = mysqli_real_escape_string($link, $_POST['itemSKUMen']); $itemDescLadies = mysqli_real_escape_string($link, $_POST['itemDescLadies']); $itemDescMen = mysqli_real_escape_string($link, $_POST['itemDescMen']); $sizeLadies = mysqli_real_escape_string($link, $_POST['sizeLadies']); $sizeMen = mysqli_real_escape_string($link, $_POST['sizeMen']); $itemPrice = mysqli_real_escape_string($link, $_POST['itemPrice']); $itemColours = mysqli_real_escape_string($link, $_POST['itemColours']); $category = mysqli_real_escape_string($link, $_POST['category']); $supplier = mysqli_real_escape_string($link, $_POST['supplier']); $itemType = mysqli_real_escape_string($link, $_POST['itemType']); PHP: Then the variables are referenced in the SQL query. Any other ideas welcome.
I've heard this before, and find it a nonsense argument. if you're using MySQL chances are you will stick with MySQL. People changing their database type is rare to say the least. That system works fine for MySQL, and also the code is running mysqli, so of course it needs to be valid for mysql but not for others. If PDO was being used then yes it should be altered, but it isn't, and short of the OP rewriting the whole code, it'll stay as mysql
By the way I understand now about the two INSERT methods but I just need to work out why it's not inserting.
I've found the error - there was a semi-colon at the end of the following line instead of a comma: itemSKUMen='$itemSKUMen'; So with that error gone, when I press submit I now get another error as follows; Error adding submitted item: Column count doesn't match value count at row 1 Do you know what this means?
When I changed the SQL syntax back to what I had it originally, ie. by using SET instead of VALUES, the error went away and inserted the data into the table successfully, so this is solved now. Thanks for the help.