Reading form array

Question

I have this previous post where I need to change my HTML form to accept an array. This post has the full details of the code.

So I changed all forms accordingly, such as:

<select name="camera_type[]">

But now I'm stuck on how to run the update query. Now that I store everything in an array I obviously can't use my query as is. Here is a post that seems to be exactly what I need, but I'm not sure I understand: Update MySQL table with an Array using Foreach

Any further help on this is appreciated.

What error are you getting when you run the UPDATE query? It looks like you have all of the code on the other post. — Homer6, Nov 14 '11 at 03:53

score 1 · Accepted Answer · answered Nov 14 '11 at 02:06

1

Use a foreach loop on the submitted value. Then you can iterate over the entire array and construct queries as you please.

For example: You have a $_POST['camera_type'] variable which is an submitted from the browser. In your php script do the following.

foreach($_POST['camera_type'] as $value) {
  //do your processing.
}

answered Nov 14 '11 at 02:06

footy

5,803
13
48
96

Sorry, wasn't clear. I've added the foreach loop on the submitted value. Just not sure how to construct the update query. Do I update in each loop? Seems...inefficient? – Tom Nov 14 '11 at 02:21
Agreed, it seems inefficient, but it'd the only way. Unless you want to go down the track of creating a single UPDATE query and using CASE arguments to set the values for the various feeds based on the tuple's ID field. – Luke Stevenson Nov 14 '11 at 04:58
@TomPepernic Tho may be inefficient its a general way. The `POST['camera_type']` holds *ONLY* those which are submitted, so it wont have redundant values. Hence its better. – footy Nov 14 '11 at 06:41

Yes Barry · Answer 2 · 2011-11-14T09:07:34.040

1

To help understand looping through an array and running UPDATE queries check this out:

// first setup your MySQLi connection
$db = new mysqli('localhost', 'user', 'pass', 'dbname');

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit;
}

// disable autocommit for transactions
$db->autocommit(false);

// **EDIT** forgot to escape input data. fixed now. note the mysqli_real_escape_string() and int type casting
foreach ($_POST['camera_type'] as $type) {
    // assuming 'id' is set in the POST array
    $query = sprintf(
        'UPDATE table SET column = "%s" WHERE id = %d', 
        $db->real_escape_string($type), 
        (int) $_POST['id']
    );
    $db->query($query);
}

// commit transactions (or commit the updates that were run) and then close
$db->commit();
$db->close();

edited Nov 14 '11 at 09:07

answered Nov 14 '11 at 07:56

Yes Barry

9,514
5
50
69

hmm oops missed that one. fixed. Thanks! ...but why was this downvoted? – Yes Barry Nov 14 '11 at 08:53
because of sql injection which still persists. you have to either cast the id to int instead of escaping OR quote it. – Your Common Sense Nov 14 '11 at 08:57
Ok touche. Thanks for commenting! I fixed it now. :) Did I miss anything else? (by the way, I _swear_ I always validate all my data before running queries lol) – Yes Barry Nov 14 '11 at 09:00
data validation and query composing are different matters. – Your Common Sense Nov 14 '11 at 09:09
true. to me that's just semantics, but I guess I use the term loosely and perhaps shouldn't. thanks for the help, though! – Yes Barry Nov 14 '11 at 09:21
1

I am taking validation as data-specific routine, say, email validation. while query composing should be quite contrary - data-irrelevant, allowing any kind of data into query while making it safe way. – Your Common Sense Nov 14 '11 at 10:56

Reading form array

2 Answers2