Solved

update multiple columns and rows using mysql for PHP Page

Posted on 2011-02-26
11
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Suggested Solutions

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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 look for a specific file type in a local or remote server directory using PHP.

726 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