php mysql select box

Question

I have a selection box and I wish to loop over to add to the database. It looks right to me but it will not enter into the database.

<select name="countylist" style="height:300px;" class="multiple" multiple="multiple" id="box2View">
      <option value="11">Beer Gardens</option><option value="10">Historic Bars</option>           
      <option value="8">Live Music</option><option value="1">Night Clubs</option>
      <option value="4">Pubs Serving Food</option><option value="6">Sports Bars</option>      
 </select>

SQL:

foreach($_POST["countylist[]"] as $s) {  
  $insertSQL = sprintf("INSERT INTO cat_pubs (pub_id, cat_id) 
  VALUES(LAST_INSERT_ID(),". $s . ")");

  mysql_select_db($database_localhost, $localhost);
  $Result1 = mysql_query($insertSQL, $localhost) or die(mysql_error());
}

Thanks

no doesn't work either, thanks anyway, although I am sure it is an array — Keith Power
– Keith Power, Commented Apr 25, 2012 at 16:39
Change the name from 'countylist' to 'countylist[]' in the HTML — BeRecursive
– BeRecursive, Commented Apr 25, 2012 at 16:41

Armatus · Accepted Answer · 2012-04-25 16:49:12Z

3

You will need to adjust the name of the select to include the array marker [] like so:

<select name="countylist[]"...

Then in PHP remove the array marker like so:

foreach($_POST["countylist"] as...

What is very important is that in PHP you check the input is actually one of the allowed values and not something the user input themselves maliciously. For selects it may be easiest to hold an array of allowed values and then check against this:

if(!in_array($s, $allowed_counties)) { /*false input, do not save in db*/}

answered Apr 25, 2012 at 16:49

Armatus

2,20117 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BeRecursive · Accepted Answer · 2012-04-25 16:43:20Z

2

You need to change the name of the select tag to countylist[] so that PHP knows it represents an array:

<select name="countylist[]"/>

answered Apr 25, 2012 at 16:43

BeRecursive

6,3961 gold badge28 silver badges41 bronze badges

Comments

Konerak · Accepted Answer · 2012-04-25 16:40:05Z

1

What are you trying to do with that LAST_INSERT_ID()?

Have you done an insert before which you are now trying to refer to?

In that case, store the value in a variable because it will be overwritten after your first (new) insert.

Are you trying to have the insert take the next autoincrement?

Then just don't name the column in your insert, or put NULL in the value:

INSERT INTO cat_pubs (cat_id)   VALUES(". $s . ")");

PS: You will get hacked by MySQL Injection if you just insert data from POST straight into your DB by building SQL from strings like that. Escape the string, or use a prepared statement...

answered Apr 25, 2012 at 16:40

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

1 Comment

Keith Power Over a year ago

I am just testing for now, thanks though for the advice I will be changing after testing

Lawrence Cherone · Accepted Answer · 2012-04-25 17:05:05Z

Some pointers, you need to tell the POST that the value is an array with name="countylist[]" else your only get the last selected value in php.

Also if your doing multiple inserts it is always faster to build a single query for insert compared to iterating over the result and inserting on each iteration.

Your also selecting the database on each iteration which is wrong:

<?php 
//connect
mysql_connect('localhost','user','pass');
//select your db
mysql_select_db('database');

//is posted
if($_SERVER['REQUEST_METHOD']=='POST'){
    //build query for a single insert
    $query = 'INSERT INTO cat_pubs (pub_id, cat_id) VALUES ';
    foreach($_POST["countylist"] as $s) {
        $query .='("","'.mysql_real_escape_string($s).'"),';
    }
    //trim the last ,
    $query = rtrim($query,',');
}

//do query
$result = mysql_query($query) or die(mysql_error());

?>

<form method="POST" action="">
  <!-- tell POST that countylist is an array --> 
  <select name="countylist[]" style="height:300px;" class="multiple" multiple="multiple" id="box2View">
      <option value="11">Beer Gardens</option>
      <option value="10">Historic Bars</option>           
      <option value="8">Live Music</option>
      <option value="1">Night Clubs</option>
      <option value="4">Pubs Serving Food</option>
      <option value="6">Sports Bars</option>      
 </select>

  <p><input type="submit" value="Submit"></p>
</form>

Del Pedro · Accepted Answer · 2012-04-25 16:40:02Z

0

thats ok for an example, I have one remark:

use mysql_real_escape() like

$insertSQL = sprintf("INSERT INTO cat_pubs (pub_id, cat_id) VALUES(LAST_INSERT_ID(),". mysql_real_escape_string($s) . ")");

answered Apr 25, 2012 at 16:40

Del Pedro

1,21212 silver badges32 bronze badges

Collectives™ on Stack Overflow

php mysql select box

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related