drop down list to sql database

Hi All,

I have a drop down list which gets its data from a table from a mysql table and i now want the selected option to be put into a different table when the user presses submit. Here is my code for pulling the data into the drop down list:

<?

$mysqli = new mysqli('localhost','root','newr00t');

$mysqli->select_db('orders');

$result = $mysqli->query("SELECT * FROM user");

echo '<SELECT name=category>';

while($row1 = $result->fetch_assoc()) {

echo '<OPTION>'.$row1['name'].'</option>';

}

echo '</select>';

$result->close();

?>

Thanks in advance for your help 

Hi,

Change your Option statement to

echo '<OPTION VALUE=.'$row1['name'].'>'.$row1['name'].'</option>'; 

Then pick the value from the $_POST array and use an update statement to add it to your DB.

HTH

Dave

I haven't tested this but you get the idea

Btw I didn't correct your OPTION tag. Dave's already done it.

<?php
      
     if($_POST['submitted'])
     {
          $select_value = $_POST['category'];
          // TODO: now insert $select_value into the database using insert
     }



      $mysqli = new mysqli('localhost','root','newr00t');
      $mysqli->select_db('orders');

      $result = $mysqli->query("SELECT * FROM user");

      ?>
      <form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
      <?php

      echo '<SELECT name=category>';
      while($row1 = $result->fetch_assoc()) {
      echo '<OPTION>'.$row1['name'].'</option>';   
      }
      echo '</select>';

      echo "<input type=\"hidden\" name=\"submitted\" value=\"TRUE\" />";
      echo "</form>";

      $result->close();

      ?>

Hi Dave,

If i replace my option with the one you suggested i get the following error:

Parse error: syntax error, unexpected T_VARIABLE, expecting ',' or ';' in c:\webs\test\neworder.php on line 51

Line 51 is what you suggested.

Any Ideas???

I do have other things such as text boxes which do insert into my database its just the results from my dropdown list.

Cheers,

Mike

Sorry mate there is a typo in my line

<OPTION VALUE='.$row1['name'].'>'.$row1['name'].'</option>

is better!

It's a small  typo thats all 

Change:

echo '<OPTION VALUE=.'$row1['name'].'>'.$row1['name'].'</option>'; 

To

$name = $row1['name'];
echo "<option value=".$name.">" . $name .  "</option>";

Does that help?

I don't know why people query whole table when they just need one column.

Here is my code now and it still doesn't work.

<?

$mysqli = new mysqli('localhost','root','newr00t');

$mysqli->select_db('orders');

$result = $mysqli->query("SELECT * FROM user");

echo '<SELECT name=category>';

while($row1 = $result->fetch_assoc()) {

$name = $row1['name'];

echo "<option value=".$name.">" . $name .  "</option>";

}

echo '</select>';

$result->close();

?>

I am fairly new at this and dont fully understand what it was that cowfish suggested earlier.

Thanks for your help,

Mike

I have this at the top of my page

<?php

$OrderRef = $_POST["OrderRef"];

$OrderID = $_POST["OrderID"];

$Description1 = $_POST["Description1"];

$Quantity1 = $_POST["Quantity1"];

$UnitPrice1 = $_POST["UnitPrice1"];

$Comments1 = $_POST["Comments1"];

$Total1 = $_POST["Total1"];

$Delivery = $_POST["Delivery"];

$GrandTotal = $_POST["GrandTotal"];

$name = $_POST["$name"];

if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form

?>

What i mentioned in my previous post is what populates the dropdown and the following is my insert statement:

<?

} else {

$host = "localhost";

$username = "root";

$password = "newr00t";

$database = "orders";

$mysqli = mysqli_connect('localhost','root','newr00t');

$mysqli->select_db('orders');

$OrderRef = addslashes($OrderRef);

$Description1= addslashes($Description1);

$Quantity1 = addslashes($Quantity1);

$UnitPrice1= addslashes($UnitPrice1);

$Comments1= addslashes($Comments1);

$Total1= addslashes($Total1);

$Delivery = addslashes($Delivery);

$GrandTotal = addslashes($GrandTotal);

$name = addslashes($name);

if(!$mysqli)

{

echo " Error: could not connect to database.";

exit;

}

$sql="INSERT INTO `orders`(`orderref`,`orderedby`,`description`,`quantity`,`unitprice`,`comments`,`total`,`delivery`,`grandtotal`)

VALUES ('".$OrderRef."','".$name."','".$Description1."','".$Quantity1."','".$UnitPrice1."','".$Comments1."','".$Total1."','".$Delivery."','".$GrandTotal."')";

$result = mysqli_query($mysqli, $sql, MYSQLI_USE_RESULT);

if($result)

{

echo mysqli_affected_rows($mysqli)." .Order Submitted Successfully.";

}

}

?>

Cheers again,

Mike

<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">

      <?php

      echo '<SELECT name=category>';

      while($row1 = $result->fetch_assoc()) {

      echo "<OPTION>'.$row1['name'].'</option>"; 

      }

      echo '</select>';

Note dont use similar quotes many times in the echo statement.If you use small at beginning then use its another while closing only.Similarly for the double quotes.

Hello,

I think the problem might be the line: if (!isset($_POST['submit']))

Normally I include a hidden field in the form like this

echo "<input type=\"hidden\" name=\"submitted\" value=\"TRUE\" />";

That just creates an invisible form element with the sole purpose of passing a value back to the page, after you submit the form.

In this case, the hidden field is used to see if you have submitted the form or not.

To illustrate:

Once the form is submitted, you will be able to access $_POST['submitted']

Then you do if($_POST['submitted']) to check if the form has been submitted. You could also do the inverse with if(!$_POST['submitted']) {  display form  }

NB: if($_POST['submitted']) is equivalent to typing if($_POST['submitted'] == TRUE) , but you don't have to type the whole thing out.

Hope that helps! Sorry I'm not a really good explainer. 

Hi Guys,

i finally got it working using the following:

<?

$mysqli = new mysqli('localhost','root','newr00t');

$mysqli->select_db('orders');

$result = $mysqli->query("SELECT * FROM user");

echo "<SELECT name='user'>\n";

while($row = $result->fetch_assoc()) {

echo "<option value='{$row['userid']}'>{$row['name']}</option>\n";

}

echo "</select>\n";

$result->close();

?>

thanks for all your help,

Mike