Can a select box be used for updating a database value?

lanceox · May 17, 2011

Hi guys, is it possible to update a field ina a database using a select box. So far i have populated the select box with database values and it works fine, however i dont know how to update the field properly in a database.

have a look at the code and tell me what you think.

<?php
session_start();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>stock manager</title>
</head>

<body>

<center>
<table>
<td>
    	<table>
<td>
        <form action='stockview.php' method='POST'>
        Please Enter a Stock Name and Stock Value
        
        <table>
            <tr>
            <td>
                Stock Name:
            </td>
            <td>
              <input name="stockname" type="text" /><BR />
            </td>
        </tr>
        <tr>
            <td>
                Stock Qty:
            </td>
            <td>
                <input name="stockqty" type="text" />
            </td>
        </tr>
        
        </table>
         <input name="submit1" type="submit" value="Add New Stock Items" />
        </form>
  	</td>
</table>
</td>

<td>
    
<table>
<td>
	<form action='stockview.php' method='POST' enctype="multipart/form-data">
       	Please Select from the list the item you wish to update
          <table>
            <tr>
            	<td>
               		 Stock Name:
            	</td>
           		 <td>
                
                <?php	
			$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
			mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

			$query=("SELECT id, stockname FROM stocks");
			$result = mysql_query ($query);
			echo "<select name=stock value=''>Edit Stock QTY</option>";

			while($nt=mysql_fetch_array($result))
			{	//Array or records stored in $nt
				echo "<option value=$nt[id]>$nt[stockname]</option>";
				/* Option values are added by looping through the array */
			}


			?>
                
                
            </td>
        	</tr>
        	<tr>
            	<td>
               	 Stock Qty:
            	</td>
            	<td>
                	<input name="stock_qty1" type="text" />
            	</td>
        	</tr>
        </table>
         <input name="submit" type="submit" value="Update stock items" />
        </form>
   	 </td>
</table>
</td>
</table>

<BR /><BR />

<?php
	$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
	mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

	$query=("SELECT stockname, stockqty FROM stocks");
	$result = mysql_query ($query);

	echo "<table border='1'>";
	echo "<tr><th>Stock Name</th> <th>Stock Quantity</th></tr>";

	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		echo "<tr><td>"; 
		echo $row['stockname'];
		echo "</td>";
		echo "<td>";
		echo $row['stockqty'];
		echo "</td>";
	} 
?>

</center>
<table>
<tr>
    	<td>
        </td>
        <td>
        </td>
    </tr>

</body>
</html>

Here is the page that handles the form

<?php
session_start();

$_SESSION['nt'];
$sn = $_SESSION['nt'];



?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>stock</title>
</head>

<body>
<?php
$submit1 =&$_POST['submit1'];
$stockname =&$_POST['stockname'];
$stockqty =&$_POST['stockqty'];

$submit = &$_POST['submit'];
$stockqty1 = &$_POST['stock_qty1'];


if(isset($submit1))
{
if(isset($stockname) && ($stockqty))
{
	$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
	mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

	$query = mysql_query("SELECT * FROM stocks WHERE stockname='$stockname'");
	$numrows = mysql_num_rows($query);

	if ($numrows!=0)
	{
		echo("Item already exists!");?><BR /><BR /><?php echo("Please Add A non-existent Item!");?> <a href="stockmanager.php">Try again!</a><?php

	}
	else
	{
		$queryreg = mysql_query("INSERT INTO `stocks` (stockname, stockqty) VALUES ('$stockname','$stockqty')");
		echo("Stock has been added"); ?><BR /><BR /> <?php echo("Click here to return to stock manager!");?> <a href="stockmanager.php">Click Here!</a><?php

	}
}
else
{
	echo("Please fill in all fields to add stock!");	
}

}

if(isset($submit))
{
	$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
	mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

	$query=("SELECT id, stockname FROM stocks");
	$result = mysql_query ($query);

	while($nt=mysql_fetch_array($result))
	{	//Array or records stored in $nt
		echo "<option value=$nt[id]>$nt[stockname]</option>";
		/* Option values are added by looping through the array */

	}

	$queryreg = mysql_query("SELECT * FROM stocks WHERE stockqty='$stockqty1'");
	$numrows = mysql_num_rows($queryreg);

	$update = mysql_query("UPDATE stocks SET stockqty=$stockqty1 WHERE stockname=$sn'");
	echo("Item has been updated");
}

?>
</body>
</html>

Any help woul;d be really great, i ahve tried everything for days on end now

Thanks

Lance

cmattoon · May 17, 2011

There's probably an easier way to do this, but here's what I do..

Suppose we have a table of stocks:

ID Symbol Name

1 ABC ABC, Inc.

2 XYZ XYZ, Inc.

...

Use the unique identifier (whatever that may be in your case).. in the example i'll use ID

<select name="stockToModify">
while ($row = loop to pick all valid options) {
    echo "<option value=\"".$row[id]."\">".$row[symbol]."</option>";
}
</select>

Then, in your 2nd script (which processes form data):

$id = $_POST['stockToModify'];
$sql = mysql_query("SELECT * FROM stocks WHERE id='$id'");
...perform operation, etc...

Hope that helps!

lanceox · May 18, 2011

Hey, i tried the way you said, however it still wont work, No errors occur however it just doesnt update the values in the database. Can someone have a look at see where i am going wrong.

The form section

<?php
session_start();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>stock manager</title>
</head>

<body>

<center>
<table>
<td>
    	<table>
<td>
        <form action='stockview.php' method='POST'>
        Please Enter a Stock Name and Stock Value
        
        <table>
            <tr>
            <td>
                Stock Name:
            </td>
            <td>
              <input name="stockname" type="text" /><BR />
            </td>
        </tr>
        <tr>
            <td>
                Stock Qty:
            </td>
            <td>
                <input name="stockqty" type="text" />
            </td>
        </tr>
        
        </table>
         <input name="submit1" type="submit" value="Add New Stock Items" />
        </form>
  	</td>
</table>
</td>

<td>
    
<table>
<td>
	<form action='stockview.php' method='POST' enctype="multipart/form-data">
       	Please Select from the list the item you wish to update
          <table>
            <tr>
            	<td>
               		 Stock Name:
            	</td>
           		 <td>
                
                <?php	
			$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
			mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

			$query=("SELECT id, stockname FROM stocks");
			$result = mysql_query ($query);
			?>
			<select name="stock"><option>Select Stock From List</option>";<?php
			while($row = mysql_fetch_array($result))
			{	
				echo "<option value=\"".$row[id]."\">".$row[stockname]."</option>";
			}
			?>
			</select>

                
                
            </td>
        	</tr>
        	<tr>
            	<td>
               	 Stock Qty:
            	</td>
            	<td>
                	<input name="stock_qty1" type="text" />
            	</td>
        	</tr>
        </table>
         <input name="submit" type="submit" value="Update stock items" />
        </form>
   	 </td>
</table>
</td>
</table>

<BR /><BR />

<?php
	$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
	mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

	$query=("SELECT stockname, stockqty FROM stocks");
	$result = mysql_query ($query);

	echo "<table border='1'>";
	echo "<tr><th>Stock Name</th> <th>Stock Quantity</th></tr>";

	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		echo "<tr><td>"; 
		echo $row['stockname'];
		echo "</td>";
		echo "<td>";
		echo $row['stockqty'];
		echo "</td>";
	} 
?>

</center>
<table>
<tr>
    	<td>
        </td>
        <td>
        </td>
    </tr>

</body>
</html>

The form handler section

<?php
session_start();




?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>stock</title>
</head>

<body>
<?php
$submit1 =&$_POST['submit1'];
$stockname =&$_POST['stockname'];
$stockqty =&$_POST['stockqty'];

$submit =&$_POST['submit'];
$stockqty1 =&$_POST['stock_qty1'];

$id =&$_POST['stock'];



if(isset($submit1))
{
if(isset($stockname) && ($stockqty))
{
	$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
	mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

	$query = mysql_query("SELECT * FROM stocks WHERE stockname='$stockname'");
	$numrows = mysql_num_rows($query);

	if ($numrows!=0)
	{
		echo("Item already exists!");?><BR /><BR /><?php echo("Please Add A non-existent Item!");?> <a href="stockmanager.php">Try again!</a><?php

	}
	else
	{
		$queryreg = mysql_query("INSERT INTO `stocks` (stockname, stockqty) VALUES ('$stockname','$stockqty')");
		echo("Stock has been added"); 

	}
}
else
{
	echo("Please fill in all fields to add stock!");	
}

}

if(isset($submit))
{
	$connect = mysql_connect("localhost","root", "") or die ("Couldn't Connect!");
	mysql_select_db("stock", $connect) or die("Couldn't find db"); // select database

	$query=("SELECT id, stockname FROM stocks");
	$result = mysql_query ($query);


	$sql = mysql_query("SELECT * FROM stocks WHERE id='$id'");
	$numrows = mysql_num_rows($sql);

	$update = mysql_query("UPDATE stocks SET stockqty=$stockqty1 WHERE id=$id'");
	echo("Item has been updated");?><BR /><BR /> <?php echo("Click here to return to stock manager!");?> <a href="stockmanager.php">Click Here!</a><?php
}

?>
</body>
</html>

Thanks

Lance

lanceox · May 18, 2011

it works

there was a problem with the form and the it wasnt sending correct values.

I echo'd out the values to see what wasnt working and corrected them

Thanks for your help

Lance

Sign In

Can a select box be used for updating a database value?

Recommended Posts

lanceox

Link to comment

Share on other sites

cmattoon

Link to comment

Share on other sites

lanceox

Link to comment

Share on other sites

lanceox

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information