Page 1 of 1

form post very slow - many db fields to update

Posted: Thu Jul 07, 2011 2:10 pm
by inosent1
Hi

i am using this code to update a mysql db from a php web form

Code: Select all

<?php
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$b_fname = mysql_real_escape_string($_POST['b_fname']);
$cb_fname = mysql_real_escape_string($_POST['cb_fname']);
$b_lname = mysql_real_escape_string($_POST['b_lname']);
$cb_lname = mysql_real_escape_string($_POST['cb_lname']);

$query3="UPDATE cdata SET b_fname='$b_fname' WHERE files_id=$files_id";
$query4="UPDATE cdata SET cb_fname='$cb_fname' WHERE files_id=$files_id";
$query5="UPDATE cdata SET b_lname='$b_lname' WHERE files_id=$files_id";
$query6="UPDATE cdata SET cb_lname='$cb_lname' WHERE files_id=$files_id";

if (mysql_query($query3)) {echo "";} else {echo "An error has occured.";}
if (mysql_query($query4)) {echo "";} else {echo "An error has occured.";}
if (mysql_query($query5)) {echo "";} else {echo "An error has occured.";}
if (mysql_query($query6)) {echo "";} else {echo "An error has occured.";}

mysql_close();
require_once 'insert2_exp.php';
?> 



if all i did was post the 4 fields above everything would be fine. but i have 267 fields to update and when *all* of them are in the update code above it takes a good 2 minutes to update the DB.

so what slows it down (with that code type above) is the number of fields.

is there a faster way to update even though i have 267 fields instead of, for example, 4?

thanks

Re: form post very slow - many db fields to update

Posted: Thu Jul 07, 2011 3:05 pm
by AbraCadaver
Holy crap! Use one query :)

Code: Select all

$query = "UPDATE cdata SET b_fname='$b_fname', cb_fname='$cb_fname', b_lname='$b_lname', cb_lname='$cb_lname'
WHERE files_id=$files_id";

Re: form post very slow - many db fields to update

Posted: Thu Jul 07, 2011 3:17 pm
by AbraCadaver
Since you'll have a lot of form fields, maybe something like this:

Your form fields can be an array to omit the submit button and to help if you add other things:

[text]<input type="text" name="data[b_fname]">[/text]

Code: Select all

$data = array_map('mysql_real_escape_string', $_POST['data']);

foreach($data as $column => $value) {
        $set[] = "`$column` = '$value'"
}   
$set = implode(',', $set);

$query = "UPDATE `cdata` SET $set WHERE `files_id` = '$files_id'";