form post very slow - many db fields to update

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

form post very slow - many db fields to update

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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";
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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'";
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply