Problem with coding MySQL query


Posted on 16th Feb 2014 07:03 pm by admin

I'm having heaps of trouble getting one of my PHP/MySQL queries to work for some reason (and the funny thing is there are plenty that are identical to it all the way through the site) so I'm wondering if someone can spot the problem. (I've basically torn all the code apart trying to work it out and have changed values that are being submitted, etc to try and get it to work, so I'm tearing my hair out now!)

Code: $staffID = $_POST['staffID'];
$date = date("Y-m-d h:i:s");

$sql2 = "INSERT INTO Order (date, staffID)
VALUES ('$date', '$staffID')";
$result2 = mysqli_query($cxn, $sql2)
or die ("Couldn't execute insert into order query.");
As it's not working, I keep getting "Couldn't execute insert into order query."

The MySQL database is named Order, and has orderID, date and staffID. orderID is int(8 ), unsigned zerofill and autoincrement. date is datetime, and staffID is int(4) unsigned zerofill. Staff members enter the zeros in their staffID when putting their details into the form (so would enter 0004).

Here's the full code:

createorder.php
Code: <?php

include("credentials.inc");

switch (@$_POST['do']) {

case "neworder":

$cxn = mysqli_connect($host, $user, $password, $dbname)
or die ("Connection failed.");

$staffID = $_POST['staffID'];
$sku = $_POST['sku'];
$quantity = $_POST['quantity'];

$sql = "SELECT staffID FROM Staff
WHERE staffID = '$staffID'";
$result = mysqli_query($cxn, $sql)
or die ("Couldn't execute staff query.");
$num = mysqli_num_rows($result);

if ($num > 0) { //staff member found

$date = date("Y-m-d h:i:s");

$sql2 = "INSERT INTO Order (date, staffID)
VALUES ('$date', '$staffID')";
$result2 = mysqli_query($cxn, $sql2)
or die ("Couldn't execute insert into order query.");

$sql3 = "SELECT orderID FROM Order
WHERE date = '$date'";
$result3 = mysqli_query($cxn, $sql3)
or die ("Couldn't execute select from order query.");
$rowname = mysqli_fetch_assoc($result3);
extract ($rowname);

$sql4 = "INSERT INTO ItemsOrdered (orderID, sku, quantity)
VALUES ('$orderID', '$sku', '$quantity')";
$result4 = mysqli_query($cxn, $sql4)
or die ("Couldn't execute insert into ItemsOrdered query.");
header("Location: success4.php");
}
else {
$message = "Staff member does not exist.<br />";
include("createorder.inc");
}


break;

default:

include("createorder.inc");

}

?>createorder.inc
Code: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Adelaide Books</title>
<link href="style.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>

<body>
<div id="body">
<table id="main" cellspacing="0px">
<tr><td id="logo" colspan="3">
<img src="images/logo.gif" alt="Adelaide Books"/></td>
<td class="space">&nbsp;</td>
<td class="right">&nbsp;</td></tr>
<tr><td class="left">&nbsp;</td>
<td class="space">&nbsp;</td>
<td id="text">
<h1>Create New Order</h1>

<form action="createorder.php" method="POST">
<table width="250" border="0" align="center" cellpadding="2" cellspacing="2">

<?php

if (isset($message)) {
echo "<tr><td style='color:red' colspan='2' >$message <br /></td></tr>";
}
?>

<tr>
<td width="75px" align="right">Staff ID:</td>
<td><input type="text" name="staffID" size="25" maxsize="50"></td>
</tr>
<tr>
<td width="75px" align="right">SKU:</td>
<td><input type="text" name="sku" size="25" maxsize="50"></td>
</tr>
<tr>
<td width="75px" align="right">Quantity:</td>
<td><input type="text" name="quantity" size="10" maxsize="20"></td>
</tr>
<input type="hidden" name="do" value="neworder">
<tr>
<td colspan="2" align="center"><input type="submit" name="neworder" value="Submit"></td>
</tr>
</table>
</form>

&nbsp;<br/>&nbsp;<br/>&nbsp;<br/>&nbsp;<br/><a href="orders.php"><img src="images/back.gif" alt="Back" border="0" /></a></td>
<td class="space">&nbsp;</td>
<td class="right">&nbsp;</td></tr>
</table>
</div>
</body>
</html>
Hope someone can help!

No comments posted yet

Your Answer:

Login to answer
221 Like 6 Dislike
Previous forums Next forums
Other forums

Filtering an Array Based on Value
I have a very simple script set up that pulls data from a database and is output using this code:

Save username into DB
OkaY so I got my blog to actually save the posts and whatever, all I need now is for it to keep the

Get Total From While Loop
I'm trying to get the total for each product and add them for a Grand Total to list outside the loop

random generation
hi

i need help with generating this

numbers from 1-12

i want to generate in ran

images aren't rendering
I'm trying to call a JPG file from within PHP (in an effort to hide the actual JPG folder). The imag

Need to write a cookie with a url var
Not sure if its possible, but I need to write a cookie with a url.

So this would write 2232 a

Edit MySQL Row Using PHP and HTML Form
Hello,

Here's what I'm trying to do. Build a page where a user enters a MySQL row number in.

Snapshot from video
anyone knw how to make a snapshot maker from avi n mkv format.
for example if i give a direct lin

POST into Array problem
Hi Chaps,

I have a repeat region, displaying rows of data: jobid, fromtable, translatorcharge

values not being entered into table
hi. I;ve created a form, so that when a user enters data into it, it gets added to a table in a data

Sign up to write
Sign up now if you have flare of writing..
Login   |   Register
Follow Us
Indyaspeak @ Facebook Indyaspeak @ Twitter Indyaspeak @ Pinterest RSS



Play Free Quiz and Win Cash