Solved

update multiple columns and rows using mysql for PHP Page

Posted on 2011-02-26
11
548 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
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

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

Industry Leaders: 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!

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

717 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