Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
tam2000k2
Forum Newbie
Posts: 13 Joined: Mon Jul 31, 2006 2:08 pm
Post
by tam2000k2 » Tue Aug 01, 2006 11:59 pm
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
bmcewan
Forum Commoner
Posts: 55 Joined: Wed Jun 02, 2004 7:19 am
Location: West Yorkshire, UK.
Post
by bmcewan » Wed Aug 02, 2006 2:57 am
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
GM
Forum Contributor
Posts: 365 Joined: Wed Apr 26, 2006 4:19 am
Location: Italy
Post
by GM » Wed Aug 02, 2006 3:30 am
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.
}
bmcewan
Forum Commoner
Posts: 55 Joined: Wed Jun 02, 2004 7:19 am
Location: West Yorkshire, UK.
Post
by bmcewan » Wed Aug 02, 2006 3:32 am
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);
}
tam2000k2
Forum Newbie
Posts: 13 Joined: Mon Jul 31, 2006 2:08 pm
Post
by tam2000k2 » Wed Aug 02, 2006 9:08 am
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