Page 1 of 1

for loop of UPDATE, how?

Posted: Tue Aug 01, 2006 11:59 pm
by tam2000k2
What is the syntax for breaking apart UPDATE queries within a PHP for loop?

Here's my for loop that concatenates the string

Code: Select all

for ($j=1; $j<=$i-1; $j++)
					{
							$my_sql_string2 .= "UPDATE guest_login SET";
        					$my_sql_string2 .= " FIRST_NAME= '{$_REQUEST['first_name'.$j]}', LAST_NAME= '{$_REQUEST['last_name'.$j]}', EMAIL_ADDRESS= '{$_REQUEST['email_address'.$j]}', WHAT_U_LIKED=  '{$_REQUEST['what_u_liked'.$j]}'";
        					$my_sql_string2 .= " WHERE ";					
							$my_sql_string2 .= "VISITOR_ID='{$_REQUEST['hidden_visitor_id'.$j]}'";
							$my_sql_string2 .= " ";
					}
Here's the resulting query:

Code: Select all

UPDATE guest_login SET FIRST_NAME= 'bob2', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob@newhart.com', WHAT_U_LIKED= 'yes' WHERE VISITOR_ID='31' UPDATE guest_login SET FIRST_NAME= 'bob3', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob2@newhart.com', WHAT_U_LIKED= 'yes2' WHERE VISITOR_ID='32' UPDATE guest_login SET FIRST_NAME= 'bob4', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob3@newhart.com', WHAT_U_LIKED= 'yes' WHERE VISITOR_ID='33'
How do I use the for loop to break the strings up and send them seperately? Do I call the mysql_query($my_sql_string2, $con) from within the for loop?

Thanks in advance

Posted: Wed Aug 02, 2006 2:57 am
by bmcewan
Hi, do you mean updating each field seperately?

Or just executing the whole query in each iteration of the loop?

If the latter, then yes you would call mysql_query from within the loop.

cheers

Re: for loop of UPDATE, how?

Posted: Wed Aug 02, 2006 3:30 am
by GM
I'd change it to look something like this:

Code: Select all

for ($j=1; $j<=$i-1; $j++)
{
    $my_sql_string2 = "UPDATE guest_login SET"; // changed .= to = here in order to reset $my_sql_string2 at the start of the loop
    $my_sql_string2 .= " FIRST_NAME= '{$_REQUEST['first_name'.$j]}', LAST_NAME= '{$_REQUEST['last_name'.$j]}', EMAIL_ADDRESS= '{$_REQUEST['email_address'.$j]}', WHAT_U_LIKED=  '{$_REQUEST['what_u_liked'.$j]}'";
    $my_sql_string2 .= " WHERE ";					
    $my_sql_string2 .= "VISITOR_ID='{$_REQUEST['hidden_visitor_id'.$j]}'";
//    $my_sql_string2 .= " "; No need for this line now
    $result = mysql_query($my_sql_string2); // Pseudocode - do the query here.
}

Posted: Wed Aug 02, 2006 3:32 am
by bmcewan
Sorry, misread the code... you need to change your code so that at each iteration of your loop, when you first assign the variable;

Code: Select all

$my_sql_string2 .= "UPDATE guest_login SET";


don't concantenate it as it is carring over the query from the previous loop iteration, like so.

Code: Select all

$my_sql_string2 = "UPDATE guest_login SET";
Although, I would rewrite the code like this just from a readability standpoint. (personal preference)

Code: Select all

for ($j=1; $j<=$i-1; $j++)
                                        {
                                                $my_sql_string2 = "UPDATE guest_login SET
                                                `FIRST_NAME` = '" . $_REQUEST['first_name'.$j] . "', 
                                                `LAST_NAME` = '" . $_REQUEST['last_name'.$j] . "', 
                                                `EMAIL_ADDRESS` = '" . $_REQUEST['email_address'.$j] . "', 
                                                `WHAT_U_LIKED` =  '" . $_REQUEST['what_u_liked'.$j] . "'
                                                WHERE            
                                                `VISITOR_ID` = '" . $_REQUEST['hidden_visitor_id'.$j] . "'";

                                                mysql_query($my_sql_string2, $con);
                                        }

Posted: Wed Aug 02, 2006 9:08 am
by tam2000k2
That worked! Calling the query once each iteration through the for loop without concatenating the string made it a very simple, yet elegant solution.

Code: Select all

for ($j=1; $j<=$i-1; $j++)
					{
							$my_sql_string2 = "UPDATE guest_login SET FIRST_NAME= '{$_REQUEST['first_name'.$j]}', LAST_NAME= '{$_REQUEST['last_name'.$j]}', EMAIL_ADDRESS= '{$_REQUEST['email_address'.$j]}', WHAT_U_LIKED=  '{$_REQUEST['what_u_liked'.$j]}' WHERE VISITOR_ID='{$_REQUEST['hidden_visitor_id'.$j]}'";
							mysql_query($my_sql_string2, $con);
					}
Thanks for all the help