[PHP/MySQL] Update rows with a values pick from a listbox

[eagle0473]

Hi all ,
I've a listbox multiple and when pick some values from list below

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<select name="test[]" multiple="multiple">
    <option value="one">one</option>
    <option value="two">two</option>
    <option value="three">three</option>
    <option value="four">four</option>
    <option value="five">five</option>
</select>
<input type="submit" value="Send" />
</form>

I would like to UPDATE some rows to a table like example below

+------+------+--------------
| id | column1 | column2 |

| 22 | LS19/EDC | one |
| 23 | LS19/EDC | two |
| 24 | LS19/EDC | three |
| 25 | LS19/EDC | one |
| 26 | LS19/EDC | two |
| 27 | LS19/EDC | three |

I wrote this code, but does not work correctly

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<select name="test[]" multiple="multiple">
    <option value="one">one</option>
    <option value="two">two</option>
    <option value="three">three</option>
    <option value="four">four</option>
    <option value="five">five</option>
</select>
<input type="submit" value="Send" />
</form>
<?php
$con = mysql_connect('localhost','root','');
if (!$con)
  {
  die('Not connect to database: ' . mysql_error());
  }

mysql_select_db('my_db', $con);


$mycount = count($test);
    while($mycount){
        $mycount--;
        mysql_query("UPDATE table SET column2='$test[$mycount]' WHERE column1 LIKE 'LS19/EDC'");
    }
?>

Help me please and thank you for the answers 😕

[paulnaj]

Hi,

You need to change the $test variable to $_POST['test'] these days.

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<select name="test[]" multiple="multiple">
    <option value="one">one</option>
    <option value="two">two</option>
    <option value="three">three</option>
    <option value="four">four</option>
    <option value="five">five</option>
</select>
<input type="submit" value="Send" />
</form>
<?php
//$con = mysql_connect('localhost','root','');
//if(!$con){
//	die('Not connect to database: ' . mysql_error());
//}
//mysql_select_db('my_db', $con);
echo '<pre>CHECK: $test = '.$test.'</pre>';
echo '<pre>CHECK: $_POST = '; var_dump($_POST); echo '</pre>';
$mycount = count($_POST['test']);
while($mycount){
	$mycount--;
	$sql = 'UPDATE `table` SET `column2` = "'.$_POST['test'][$mycount].'" WHERE `column1` = "LS19/EDC"';
	echo '<pre>CHECK: '.$sql.'</pre>';
	//mysql_query($sql);
}
?>

... seems to work - will need to un-comment out the db stuff to make sure.

A couple of things to note:

1. Should check whether variables are being filled (echo them out to check)
2. Same for SQL statement
3. I replaced 'LIKE' with '=' in the SQL - didn't seem necessary here

Hope that helps.

Paul

[eagle0473]

Thanks a lot for the answer Paul,
the script works correctly in page, but not works in a table of database.

For example ,if I select from the listbox "one" and "two , in table I have this situation :

+------+------+-------------- 
| id | column1 | column2 | 

| 22 | LS19/EDC | two | 
| 23 | LS19/EDC | two | 
| 24 | LS19/EDC | two | 
| 25 | LS19/EDC | two | 
| 26 | LS19/EDC | two | 
| 27 | LS19/EDC | two |

Why ?

Regards ,Luigi

paulnaj;10919093 wrote:

Hi,

You need to change the $test variable to $_POST['test'] these days.

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<select name="test[]" multiple="multiple">
    <option value="one">one</option>
    <option value="two">two</option>
    <option value="three">three</option>
    <option value="four">four</option>
    <option value="five">five</option>
</select>
<input type="submit" value="Send" />
</form>
<?php
//$con = mysql_connect('localhost','root','');
//if(!$con){
//	die('Not connect to database: ' . mysql_error());
//}
//mysql_select_db('my_db', $con);
echo '<pre>CHECK: $test = '.$test.'</pre>';
echo '<pre>CHECK: $_POST = '; var_dump($_POST); echo '</pre>';
$mycount = count($_POST['test']);
while($mycount){
	$mycount--;
	$sql = 'UPDATE `table` SET `column2` = "'.$_POST['test'][$mycount].'" WHERE `column1` = "LS19/EDC"';
	echo '<pre>CHECK: '.$sql.'</pre>';
	//mysql_query($sql);
}
?>

... seems to work - will need to un-comment out the db stuff to make sure.

A couple of things to note:

1. Should check whether variables are being filled (echo them out to check)
2. Same for SQL statement
3. I replaced 'LIKE' with '=' in the SQL - didn't seem necessary here

Hope that helps.

Paul

[djjjozsi]

the main problem is here, that you don't use the table table's primary key to identify which row you want to update. mysql makes what you're telling. If you use the item name, that will update the table where the item name is 'LS19/EDC'

why are you harcode options? insert them into a table , your life would be easier.

if one property could have more then one item <=> one item could have more then one property , this is called many - to - many relationship.

one table to store options,
one table to store items
one table to store items and options ID pairs to realize many-to-many relations.

[djjjozsi]

You can make it with the table structure, but first delete where the column2 is LS19/EDC. Into the form insert the item's value as a hidden field.
then foreach on the selected options, and insert them to the table:

however this is not a tested code, the basics are in.

<?php

if ( isset( $_POST["list"] ) AND isset( $_POST["hidden_value"] ) ) {
    $hiddenvalue = mysql_real_escape_string( $_POST["hidden_value"] );
    $delete = sprintf( "delete from `table` WHERE column1='%s'" , $hiddenvalue );
    mysql_query( $delete ) OR die( mysql_error() );

foreach( $_POST["list"] AS $option ) {
    $sql[] = sprintf( "( '%s' , '%s' )" ,
        $hiddenvalue , mysql_real_escape_string( $option ) );
} 

if ( !empty( $sql ) ) {
    $mainsql = "insert into `table` (`column1`,`column2`) values " . implode( " , " , $sql );
    mysql_query( $mainsql ) or die( mysql_error() . " - in sql query: " . $mainsql );
    print "values inserted/updated";
} 
} 

?>