multiple query at once

@HelleshternMay 19.2010

I have a form with multiple input fields. Each field have the same name and id with different number at the end, like:

[code=php] <tr> <td><input type=”text” name=”name1″ id=”name1″ value=”something1″></td> <td><input type=”text” name=”surname1″ id=”surname1″ value=”something1″></td> </tr> <tr> <td><input type=”text” name=”name2″ id=”name2″ value=”something2″></td> <td><input type=”text” name=”surname2″ id=”surname2″ value=”something2″></td> </tr> <tr> <td><input type=”text” name=”name3″ id=”name2″ value=”something3″></td> <td><input type=”text” name=”surname3″ id=”surname3″ value=”something3″></td> </tr> … [/code]

When I submit it have to add each row of those data to database. Do I have to make query for each like:

[code=php] $sql = “UPDATE $tbl_name SET name='”.mysql_real_escape_string($_POST[‘name1’]).”‘, surname='”.mysql_real_escape_string($_POST[‘surname1’]).”‘ WHERE nick='”.mysql_real_escape_string($_POST[‘nick1’]).”‘”; $result=mysql_query($sql);

$sql2 = “UPDATE $tbl_name SET name='”.mysql_real_escape_string($_POST[‘name2’]).”‘, surname='”.mysql_real_escape_string($_POST[‘surname2’]).”‘ WHERE nick='”.mysql_real_escape_string($_POST[‘nick2’]).”‘”; $result2=mysql_query($sql2);

$sql3 = “UPDATE $tbl_name SET name='”.mysql_real_escape_string($_POST[‘name3’]).”‘, surname='”.mysql_real_escape_string($_POST[‘surname3’]).”‘ WHERE nick='”.mysql_real_escape_string($_POST[‘nick3’]).”‘”; $result3=mysql_query($sql3);

… [/code]

or there’s shorter way?

to post a comment

PHP

10 Comments(s) _↴

@NogDogMay 19.2010 — #If each update is going to be a different row and have different values, then yes, they will have to be separate queries.

@HelleshternauthorMay 20.2010 — #Oh I see ? I thought it's possible to do something like:

[code=php]
 for($i=1; $i<$count_array; $i++)
 {
 $sql = "UPDATE $tbl_name SET name='".mysql_real_escape_string($_POST['name[$i]'])."', surname='".mysql_real_escape_string($_POST['surname[$i]'])."' WHERE nick='".mysql_real_escape_string($_POST['nick[$i]'])."'";
 $result=mysql_query($sql); 
 }
 [/code]

@Jarrod1937May 20.2010 — #Oh I see ? I thought it's possible to do something like:

[code=php]
 for($i=1; $i<$count_array; $i++)
 {
 $sql = "UPDATE $tbl_name SET name='".mysql_real_escape_string($_POST['name[$i]'])."', surname='".mysql_real_escape_string($_POST['surname[$i]'])."' WHERE nick='".mysql_real_escape_string($_POST['nick[$i]'])."'";
 $result=mysql_query($sql); 
 }
 [/code]

[/QUOTE]
Unless i'm not understanding what you're trying to do, that code will still submit multiple update queries to the database, it is just in a neater package code wise.

You can try messing around with some techniques discussed at stack overflow to try and get a single query for your multiple updates. But i'd recommend working on a test dataset/database because you can easily wipe all your tables data if you're not careful:

http://stackoverflow.com/questions/3432/multiple-updates-in-mysql

@HelleshternauthorMay 20.2010 — #Let me explain what I'm trying to do:

I have database with some people nicknames. There are empty cols left in each row to fill them later with names, surnames and e-mails (look attachement). Than I have text file with that other data formatted like this:

LangLang

John

Smith

[email][email protected][/email]

Matrix

Chris

Brown

[email][email protected][/email]

Kitty

Maria

Lopez

[email][email protected][/email]

...
[/QUOTE]

I explode it and makes array:

Array[0] ( [0] => LangLang [1] => John [2] => Smith [3] => [email][email protected][/email] )

Array[1] ( [0] => Matrix [1] => Chris [2] => Brown [3] => [email][email protected][/email] )

Array[2] ( [0] => Kitty [1] => Maria [2] => Lopez [3] => [email][email protected][/email] )

...
[/QUOTE]

Than automatically generate form with fields from those data:

[code=php]
 <?php
 $count_array = count($array);
 for($i=0; $i<$count_array; $i++) {
 $multi_array = explode(" ", $array[$i]);
 ?>
 
 <tr>
 <td><input type="text" name="nick<?php echo $i; ?>" id="nick<?php echo $i; ?>" value="<?php echo $multi_array[0] ?>"></td>
 <td><input type="text" name="name<?php echo $i; ?>" id="name<?php echo $i; ?>" value="<?php echo $multi_array[1] ?>"></td>
 <td><input type="text" name="surname<?php echo $i; ?>" id="surname<?php echo $i; ?>" value="<?php echo $multi_array[2] ?>"></td>
 <td><input type="text" name="email<?php echo $i; ?>" id="email<?php echo $i; ?>" value="<?php $multi_array[3] ?>"></td>
 </tr>
 
 <?
 }
 ?>
 [/code]

Than I click submit button and want to post all those data to empty fields from database, where proper name, surname and e-mail will be updated into row of proper nickname.

Actually my code is very very long because I have 100 x query like:

[code=php]
 $sql = "UPDATE $tbl_name SET name='".mysql_real_escape_string($_POST['name1'])."', surname='".mysql_real_escape_string($_POST['surname1'])."' WHERE nick='".mysql_real_escape_string($_POST['nick1'])."'";
 $result=mysql_query($sql);
 
 $sql2 = "UPDATE $tbl_name SET name='".mysql_real_escape_string($_POST['name2'])."', surname='".mysql_real_escape_string($_POST['surname2'])."' WHERE nick='".mysql_real_escape_string($_POST['nick2'])."'";
 $result2=mysql_query($sql2);
 
 $sql3 = "UPDATE $tbl_name SET name='".mysql_real_escape_string($_POST['name3'])."', surname='".mysql_real_escape_string($_POST['surname3'])."' WHERE nick='".mysql_real_escape_string($_POST['nick3'])."'";
 $result3=mysql_query($sql3);
 
 ...
 [/code]

Is there really no way to make it shorter? ?

[upl-file uuid=5de79719-9802-4e5f-8600-a6d31b4b7bee size=22kB]dbtable.jpg[/upl-file]

@sohguanhMay 20.2010 — #Let me explain what I'm trying to do:

I have database with some people nicknames. There are empty cols left in each row to fill them later with names, surnames and e-mails (look attachement). Than I have text file with that other data formatted like this:

...

Than I click submit button and want to post all those data to empty fields from database, where proper name, surname and e-mail will be updated into row of proper nickname.
[/QUOTE]

Let me ask a question. This task that you are doing is to be executed by you only or open for other users ? That is, that text file you mention will be prepared by each user using the form or only you are going to use the form ?

If you are going to use the form only, then I think having a Web-based interface to trigger your task is redundant indeed. You can just use your PHP script to process the text file and then update your table directly. You cut off an additional layer for that Web form.

Latter versions of PHP can be run in non-Web enabled mode by default. That is, you can use PHP just like you use Perl to process text file. You do not need to have a Web server to serve out a HTML form and then use PHP to process the form input at all.

@HelleshternauthorMay 20.2010 — #Well, I gave this simple example to show how my script works but my original script is database of my auction clients. This part allow me to add multiple payment info based on data copied from PayPal site. I have textarea where I paste block of text copied from PyPal. After submit it explode array on parts and show choosen values in table with input fields. This way I can modify data and check is there everything ok. After another submit it updates (add) payment info in database to correct clients (based on their nickname).

My only question is: there's a way to make one query to fill everything or it's possible only the way I made it (each query for each updating row)?

As I said I though it may be done like this but I doubt:

[code=php]for($i=0; $i<$count_array; $i++)
 {
 $sql[] = "UPDATE $tbl_name SET name='".mysql_real_escape_string($_POST['name'][$i])."', surname='".mysql_real_escape_string($_POST['surname'][$i])."' WHERE nick='".mysql_real_escape_string($_POST['nick'][$i])."'";
 $result[]=mysql_query($sql);
 } [/code]

@HelleshternauthorMay 20.2010 — #I'm sorry to post twice but I solved my problem and made it in 1 query.

@criterion9May 20.2010 — #If you don't mind, would you post the solution. Curious minds are interested. ?

@svidgenMay 20.2010 — #Two possible solutions come to mind ... I too am interested to see which you've chosen and how well it performs.

@HelleshternauthorMay 26.2010 — #Sure ?

Here's a form part (array to lines, lines to single words, single words to input fields):

[code=php]
 <form name="form1" method="post" action="$_SERVER['PHP_SELF']">
 <table>
 
 <?php
 $array = explode("r", $input_data);
 $count_array = count($array);
 for($i=0; $i<$count_array; $i++)
 {
 $multi_array = explode(" ", $array[$i]);
 ?>
 
 <tr>
 <td><input type="hidden" name="id[]" value="<?php echo $i; ?>" /></td>
 <td><input type="text" name="nick<?php echo $i; ?>" id="nick<?php echo $i; ?>" value="<?php echo $multi_array[0] ?>"></td>
 <td><input type="text" name="name<?php echo $i; ?>" id="name<?php echo $i; ?>" value="<?php echo $multi_array[1] ?>"></td>
 <td><input type="text" name="surname<?php echo $i; ?>" id="surname<?php echo $i; ?>" value="<?php echo $multi_array[2] ?>"></td>
 <td><input type="text" name="email<?php echo $i; ?>" id="email<?php echo $i; ?>" value="<?php echo $multi_array[3] ?>"></td>
 </tr>
 
 <?php
 }
 ?>
 </table>
 ...
 [/code]

And this is update database part:

[code=php]
 <?php
 
 if($_POST['Submit'])
 {
 foreach($_POST['id'] as $id)
 {
 $sql1="UPDATE ".$tbl_name." SET name='".$_POST["name".$id]."', surname='".$_POST["surname".$id]."', email='".$_POST["email".$id]."' WHERE nick='".$_POST["nick".$id]."'";
 $result1=mysql_query($sql1);
 }
 }
 
 ?>
 [/code]

Also in #PHP _↴

Pictures not displaying session/cookies Code for a Shop using cash that is on the site.

Success!

Help @Helleshtern spread the word by sharing this article on Twitter...

Tweet This

multiple query at once

10 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

multiple query at once

10 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

10 Comments(s) _↴

Also in #PHP _↴