Solved

update multiple columns and rows using mysql for PHP Page

Posted on 2011-02-26
11
539 Views
Last Modified: 2012-05-11
I have table "t_task" I need to update 221 row , with 221 variable (mark1 ..... mark221) but is very default to write 221 row ,

and its not working well ?

please write the command to me. to update all 221 row.

<?php
$sql="
UPDATE t_task SET remarksm='$mark1' ,checkm=1 WHERE sn =1;
UPDATE t_task SET remarksm='$mark2' ,checkm=1 WHERE sn =2;
UPDATE t_task SET remarksm='$mark3' ,checkm=1 WHERE sn =3;
UPDATE t_task SET remarksm='$mark4' ,checkm=1 WHERE sn =4;
UPDATE t_task SET remarksm='$mark2' ,checkm=1 WHERE sn =2;
			.
			.
			.
			.
UPDATE t_task SET remarksm='$mark221' ,checkm=1 WHERE sn =221;
";
?>

Open in new window

0
Comment
Question by:AymanDasa
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34986776
Hi,

Have you tried to execute the UPDATES one by one?...

Because if I'm not wrong.. you create $sql with 221 UPDATE statements, right?

I've noticed as well that you reference the vbles inside the string instead of concatenating them.

Maybe you can try


$sql=" UPDATE t_task SET remarksm='" . $mark1 . "' ,checkm=1 WHERE sn =1" ;
// Here you execute it

$sql=" UPDATE t_task SET remarksm='" . $mark2 . "' ,checkm=1 WHERE sn =2" ;
// Here you execute it

** Note that the string have sigle quote for the Mysql statement (remarksm VARCHAR???), double quote for the $sql string, concat the vble concat double quote, single quote... etc...


And having in mind that you have to do this 221 times, you should look for a loop not to repeat your code that many

Good luck!

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 75 total points
ID: 34986897
error message is? ...

anyhow, your posted code does not show the "execution part" of the sql ...

you could try to change the SQL to 1 single UPDATE:
 $sql = " UPDATE t_task SET remarksm = CASE WHEN sn = 1 THEN '" .$mark1. "' WHEN sn = 2 THEN '" . $mark2 ."' end WHERE sn in (1,2)"; 

Open in new window


and you can add/modify the code to put all 221 cases/sn values as needed.
0
 
LVL 11

Assisted Solution

by:Ovid Burke
Ovid Burke earned 425 total points
ID: 34986993
Try this:
<?php

$rows = 221;

for($i = 1; $i <= $rows; $i++) {
	// ${'mark' . $i} = "Remark number " . $i;
	$sql = "UPDATE t_task_EE SET remarksm = '" . ${'mark' . $i} ."' ,checkm = 1 WHERE sn = $i;";
	mysql_query($sql, $db_conn) or die("ERROR updating " . $i);
}
?>

Open in new window

0
 

Author Comment

by:AymanDasa
ID: 34987063
Dear raulggonzalez
I need to execute all of them in one time !!!
 
angelIII
yes its what I am talking ... but

checkm=1 in all should be update.
it is update 2 line only !! how if i need to update 221 line

0
 

Author Comment

by:AymanDasa
ID: 34987268
Haaay experts

I try this One

BUT its more than 221 line !!!!!!
There is no short way to do it?



$sql = " UPDATE t_task SET checkm=1, mark = 
 CASE 
 WHEN sn = 1 THEN '" .$mark1. "'
 WHEN sn = 2 THEN '" .$mark2. "'
 WHEN sn = 3 THEN '" .$mark3. "'
 WHEN sn = 4 THEN '" .$mark4. "'
 WHEN sn = 5 THEN '" .$mark5. "'
 WHEN sn = 6 THEN '" .$mark6. "'
 WHEN sn = 7 THEN '" .$mark7. "'
 WHEN sn = 8 THEN '" .$mark8. "'
 WHEN sn = 9 THEN '" .$mark9. "'
 WHEN sn = 10 THEN '" .$mark10. "'
 WHEN sn = 11 THEN '" .$mark11. "'
 WHEN sn = 12 THEN '" .$mark12. "'
 WHEN sn = 13 THEN '" .$mark13. "'
 WHEN sn = 14 THEN '" .$mark14. "'
 WHEN sn = 15 THEN '" .$mark15. "'
 ....
  WHEN sn = 221 THEN '" .$mark221. "'

 end 
 WHERE sn in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,.......,221) ";

Open in new window

0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34987293
Hi,

can you use a temporary table??

If so, create a temp table with columns 'sn' and 'remarksm ' and insert the values 1, $mark1, 2, $mark2 and so on... (maybe in a loop???)


and the update would look like

UPDATE t_task_EE a, YourTempTable b
SET a.remarksm = b.resmarksm
where a.sn = b.sn


If you want to you for Angellll's  solution include the 221 cases and the values in the IN CLAUSE

 WHERE sn in (1,2, ...  , 221)";



Good luck!


0
 
LVL 11

Accepted Solution

by:
Ovid Burke earned 425 total points
ID: 34987358
I am not sure whether you have tried this. It may have thrown an error because i had renamed the table for my tests, but here i is again,
<?php

$rows = 221;

for($i = 1; $i <= $rows; $i++) {
        // ${'mark' . $i} = "Remark number " . $i;
        $sql = "UPDATE t_task SET remarksm = '" . ${'mark' . $i} . "' ,checkm = 1 WHERE sn = $i;";
        mysql_query($sql, $db_conn) or die("ERROR updating " . $i);
}
?>

Open in new window

0
 

Author Comment

by:AymanDasa
ID: 34987363
(maybe in a loop???)


sorry its not clear Mr raulggonzalez
what is the code ?
0
 
LVL 11

Expert Comment

by:Ovid Burke
ID: 34987365
There is one question I have, and that is: Where do you define your 221 $mark(n) variables?
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34987393
well, taking the loop from madaboutasp you can do ...

more or less...

if you need the syntax to create a temp table

http://dev.mysql.com/doc/refman/5.1/en/create-table.html 


cheers
<?php

$rows = 221;

for($i = 1; $i <= $rows; $i++) {
   $sql = "INSERT INTO yourTempTable(sn, remarksm) " .
          " VALUES (" $i . ", '". ${'mark' . $i} . "')";
        mysql_query($sql, $db_conn) or die("ERROR inserting " . $i);
}
?>

Open in new window

0
 

Author Closing Comment

by:AymanDasa
ID: 34990331
Thanks All
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now