PHP/MYSQL/ XHTML FORM. How to update multi row table at once?

Question

I have following example of the PHP/SQL which worked perfectly when editing one row & saving changes.

It is working based on POST/GET method - where URL is setting up which row ID to edit / save.

form -> post id & live values

seperate php file -> get 'id' and change row with this 'id'.

<?php

session_name('users');
session_set_cookie_params(2*7*24*60*60);
session_start();

define('INCLUDE_CHECK',true);

require 'connect.php';
require 'functions.php';

if(!$_SESSION['id']) {
    header ("Location: index.php"); 
}


//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
    $str = @trim($str);
    if(get_magic_quotes_gpc()) {
        $str = stripslashes($str);
    }

    return mysql_real_escape_string($str);
}

//Sanitize the POST values
$id = clean($_POST['id']);
$usr2 = $_SESSION['usr'];
$live = (isset($_POST['live']))?1:0;
$updated = date("F j, Y, g:i a",time()+60*60);
$title = clean($_POST['title']);
$content = clean($_POST['content']);

//Create INSERT query
$qry = "UPDATE table SET live = '$live' WHERE id='".mysql_real_escape_string($_POST['id']). "'  ";
$result = mysql_query($qry);
echo mysql_error();

//Check whether the query was successful or not
if($result) {
    header("location: notes.php");
    exit();
}else {
    die("Query failed");

}

?>

My question is how to update multiple rows table/form like this one?

2 little things. 1) For your cookie, put in the result of that equation and comment what it is doing. No need doing that calculation every load. 2) Is there a reason why you aren't using a BEGIN - COMMIT/ROLLBACK? You might want to do that incase it does fail. — afuzzyllama
– afuzzyllama, Commented Aug 15, 2011 at 14:26
@afuzzyllama - reason is I'm just starting to work with PHP/ MYSQL. I'm not aware of all of the options. — Iladarsda
– Iladarsda, Commented Aug 15, 2011 at 14:28
@afuzzyllama - cookie is set as 'remeber me' field - part of the login system. Not the part of the SQL query. — Iladarsda
– Iladarsda, Commented Aug 15, 2011 at 14:32

Brad · Accepted Answer · 2011-08-15 14:33:26Z

1

The best way to do it is with a simple for loop.

foreach ($_POST as $key=>$value) {
    // You didn't provide the names of the fields, so you will need to validate them here yourself.
    // I usually name them with a prefix, like somefield_3, and then I can use substr() to determine if the field is the one I'm looking for.

    $result=mysql_query("UPDATE whatever SET somefield=1 WHERE id=" . mysql_real_escape_string($key) . ";");

    //etc., etc.
}

Since you are only setting one boolean field, you could also do something like this:

UPDATE table SET live=1 WHERE id IN (1,2,3,4,5);

But I'd recommend doing the loop, since you will inevitably need to update other fields at some point with this query.

edited Aug 15, 2011 at 14:33

answered Aug 15, 2011 at 14:22

Brad

164k57 gold badges380 silver badges559 bronze badges

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

3 Comments

Iladarsda Over a year ago

the number of rows is unspecific. Can you give an example of using for loop in this example?

Iladarsda Over a year ago

What in our case somefield=1 will stand for? Does it mean somefield=1 in the row of THIS ROW ID ?

afuzzyllama Over a year ago

somefield in your case would be live

Collectives™ on Stack Overflow

PHP/MYSQL/ XHTML FORM. How to update multi row table at once?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related