-1

I have a written a PHP program that is meant to query a MySQL database to populate drop down lists, then offer choices to insert values into a third table. It almost works, but instead of inserting the values in one record, it inserts them separately, even though it's one query. How do I remedy this?

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>
<body>
<?php // add-order.php
date_default_timezone_set("Australia/Adelaide");
$datetry= date("Y/m/d");
echo "today's date is ".$datetry."<br>";
require_once 'login.php';
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);
$query="SELECT * FROM customers";
$result=$conn->query($query);
if (!$result) die($conn->error); 
$rows = $result->num_rows;
for ($j = 0 ; $j < $rows ; ++$j)
{
$result->data_seek($j);
$row = $result->fetch_array(MYSQLI_NUM);   
$Customers[$j][0] = $row[0];
$Customers[$j][1]= $row[1];
$Customers[$j][2]= $row[2];
}
?>
<form action="add-order.php" method ="post">
<select name="choice">
<option selected="selected">Choose a customer</option>
<?php
for ($j = 0 ; $j < $rows ; ++$j){
?>
<option value="<?php echo $Customers[$j][0]; ?>"><?php echo $Customers[$j][1]; ?></option>
<?php
}
?>
</select>
<input type="submit" value="submit">
</form>
<?php
$result->close(); 
//products array
$queryp="SELECT * FROM products";
$resultp=$conn->query($queryp);
if (!$resultp) die($conn->error);
$rows = $resultp->num_rows;
for ($j = 0 ; $j < $rows ; ++$j)
{
$resultp->data_seek($j);
$row = $resultp->fetch_array(MYSQLI_NUM);   
$Products[$j][0] = $row[0];
$Products[$j][1]= $row[1];
}
?>  
<form action="add-order.php" method ="post">
<select name="choice1">
<option selected="selected1">Choose a product</option>
<?php
for ($j = 0 ; $j < $rows ; ++$j){
?>
<option value="<?php echo $Products[$j][0]; ?>"><?php echo $Products[$j][1];
?></option>
<?php
}
?>
</select>
<input type="submit" value="submit">
</form>
<?php
$resultp->close();
$PresentCustomer = $_POST['choice'];
echo $PresentCustomer;
$PresentProduct = $_POST['choice1'];
echo $PresentProduct; 
$queryO    = "INSERT INTO orders VALUES('','$PresentCustomer', '$PresentProduct','$datetry')";
$resultO   = $conn->query($queryO);
if (!$resultO) echo "INSERT failed: $query<br>" .
$conn->error . "<br><br>";
$resultO->close();
$conn->close();
?>     
</body>
</html>
Campbell Menzies
  • 5
  • 2
  • You never specify the columns the data should be inserted to. `INSERT INTO table (cola, colb, etc) VALUES ('val1', 'val2', etc)` – Xorifelse Nov 09 '16 at 01:58
  • @Xorifelse you do not need to specify the columns if the values list contains values for all columns in their nominal order. – Shadow Nov 09 '16 at 02:30
  • @Shadow Quite correct, but what happens if that's not the case? – Xorifelse Nov 09 '16 at 02:44
  • [Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – [Jay Blanchard](http://stackoverflow.com/users/1011527/jay-blanchard) – Jeff Puckett Nov 09 '16 at 02:45
  • @Xorifelse then you get a syntax error and the insert is not executed, which is not what happens based on the OP's description. – Shadow Nov 09 '16 at 02:52
  • Good comments guys. I have values for all columns, so that is not an issue. SQL injection attacks are not an issue as I am operating in an off-line environment (localhost with server on a USB) – Campbell Menzies Nov 09 '16 at 05:19

1 Answers1

0

The two dropdown boxes are in separate html forms with separate submit buttons. My guess is that you select the customer, press the submit button, then select a product and press the other submit button. These are 2 separate submits, therefore 2 separate inserts will be executed.

Not to mention that you do not check user inputs before the insert, so the insert will execute even when you load the page, and your code is also completely vulnerable to sql injection attacks.

Solution:

  1. Place the 2 dropdown menus in a single html form with a single submit button.
  2. Check if there was a form submission at all using isset() function before you do the insert.
  3. Validate the user inputs before the insert or use prepared statement.
Shadow
  • 33,525
  • 10
  • 51
  • 64