for loop update

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

for loop update

Post by tam2000k2 »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I am trying to run a for loop in PHP to construct multiple updates to mySql db:

Code: Select all

for ($j=0; $j<=$i; $j++)
{
$someOtherVar .= "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. "]' ".chr(10);
$someOtherVar .= "WHERE FIRST_NAME= '$_REQUEST[hidden_first_name" .$j. "]' AND LAST_NAME= '$_REQUEST[hidden_last_name" .$j. "]' AND EMAIL_ADDRESS= '$_REQUEST[hidden_email_address" .$j. "]' AND WHAT_U_LIKED= '$_REQUEST[hidden_what_u_liked" .$j. "]' ".chr(10);
}
This is the error message corresponding to the line of code where the $someOtherVar appears:

Code: Select all

Parse error: parse error, unexpected '\"', expecting ']' in /home/tam2000k2/www/php_tests/welcome3.php on line 23
Please help


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the error jumps out when highlighted. :)

If you still have no idea how to fix it, here's a hint:

Code: Select all

$someOtherVar .= 'SET FIRST_NAME = \'' . $_REQUEST['first_name' . $j] . '\''
If this is being used for SQL, be sure to sanitize and prepare the variables for insertion to your database.
tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

Post by tam2000k2 »

First, thanx for your replies.

I tried to use escape sequences but I would receive different errors. I don't know why it works now.

Now, there's a new error with the commas -- how do escape sequences work in PHP in relation to writing an update sql string?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

String are strings. When you use a string literal (wrapped in single quotes) you need to escape the single quote:

Code: Select all

$litstring = 'My name is Sunny O\'Dae and I say "Good day to ya"!';
If you are using a parsable string (wrapped in double quotes) you escape the double quotes:

Code: Select all

$parsestring = "My name is Sunny O'Dae and I say \"Good day to ya\"!";
More information on strings is available from the PHP Manual Section on Strings.
tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

Post by tam2000k2 »

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Thanks for the explaination and I thought that I tried that originally.

Here's what I've done after the last suggestion:

Code: Select all

$someOtherVar .= "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 . \"]' .chr(10);

$someOtherVar .= 'WHERE FIRST_NAME= '$_REQUEST[hidden_first_name\" . $j . \"]' AND LAST_NAME= '$_REQUEST[hidden_last_name\" . $j . \"]'  AND EMAIL_ADDRESS= '$_REQUEST[hidden_email_address\" . $j . \"]'  AND WHAT_U_LIKED= '$_REQUEST[hidden_what_u_liked\" . $j . \"]'  .chr(10);
Here are the errors that I've received:
Warning: Unexpected character in input: '\' (ASCII=92) state=1 in /home/tam2000k2/www/php_tests/welcome3.php on line 23

Parse error: parse error, unexpected ',' in /home/tam2000k2/www/php_tests/welcome3.php on line 23
Hope that I have used the properly, if not, then I'm sure that'll be another question :-)

Thanks in advance


Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

Post by tam2000k2 »

I could see a couple things that were mistakes, so I tried again hoping that it would work:

Code: Select all

$someOtherVar += \"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 . \"]'\" .chr(10);
					$someOtherVar += \"WHERE FIRST_NAME= '$_REQUEST[hidden_first_name\" . $j . \"]' AND LAST_NAME= '$_REQUEST[hidden_last_name\" . $j . \"]'  AND EMAIL_ADDRESS= '$_REQUEST[hidden_email_address\" . $j . \"]'  AND WHAT_U_LIKED= '$_REQUEST[hidden_what_u_liked\" . $j . \"]'\"  .chr(10);
Here are the erros with the last try:
Warning: Unexpected character in input: '\' (ASCII=92) state=1 in /home/tam2000k2/www/php_tests/welcome3.php on line 23

Parse error: parse error, unexpected T_CHARACTER, expecting ']' in /home/tam2000k2/www/php_tests/welcome3.php on line 23
When I try without having escape sequences before the beginning and ending quotes:

Code: Select all

$someOtherVar += "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 . \"]'" .chr(10);
					$someOtherVar += "WHERE FIRST_NAME= '$_REQUEST[hidden_first_name\" . $j . \"]' AND LAST_NAME= '$_REQUEST[hidden_last_name\" . $j . \"]'  AND EMAIL_ADDRESS= '$_REQUEST[hidden_email_address\" . $j . \"]'  AND WHAT_U_LIKED= '$_REQUEST[hidden_what_u_liked\" . $j . \"]'"  .chr(10);
Then these are the errors:
Parse error: parse error, unexpected T_CHARACTER, expecting ']' in /home/tam2000k2/www/php_tests/welcome3.php on line 23
Thanks in advance -- sorry, newbie here, I just started working on multiple updates this weekend
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Let's set aside the syntax error for a moment. What would the result be?
A query like

Code: Select all

UPDATE yaddayadda
SET FIRST_NAME='xyz', LAST_NAME='abc' ...
WHERE ...some conditions.
SET FIRST_NAME='xyz2', LAST_NAME='abc2' ...
WHERE ...some other conditions.
SET FIRST_NAME='xyz3', LAST_NAME='abc3' ...
WHERE ...some other conditions.
if I'm not mistaken.
And I cannot find that syntax at http://dev.mysql.com/doc/refman/5.0/en/update.html



Code: Select all

for ($j=0; $j<=$i; $j++)
{
	$someOtherVar .= "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
			FIRST_NAME='{$_REQUEST['hidden_first_name'.$j]}'
			AND LAST_NAME= '{$_REQUEST['hidden_last_name'.$j]}'
			AND EMAIL_ADDRESS='{$_REQUEST['hidden_email_address'.$j]}'
			AND WHAT_U_LIKED='{$_REQUEST['hidden_what_u_liked'.$j]}'
		";
}
at least this version is accepted by the parser.
tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

Post by tam2000k2 »

Yes, that's exactly what it's supposed to do:

first_name1, last_name1, etc...
first_name2, last_name2, etc...

I was looking for the correct syntax but I couldn't find anything that suggested using parans, so thank you for that, and I'm past the parser with an SQL syntax error to figure out :-)

I tried the code as you suggested

Code: Select all

$someOtherVar += "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]}'";
        					$someOtherVar += "WHERE FIRST_NAME='{$_REQUEST['hidden_first_name'.$j]}' AND LAST_NAME= '{$_REQUEST['hidden_last_name'.$j]}' AND EMAIL_ADDRESS='{$_REQUEST['hidden_email_address'.$j]}' AND WHAT_U_LIKED='{$_REQUEST['hidden_what_u_liked'.$j]}'";
And here's the new SQL error -- so thanks guys -- I'm half way there.
Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1
Once again, thanks in advance, by the way shouldn't it be:

Code: Select all

SET FIRST_NAME= '{$_REQUEST['first_name'.$j.' ]}',
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

No, it shouldn't.
Parse error: parse error, unexpected '"', expecting ']' in /home/tam2000k2/www/php_tests/welcome3.php on line 23
That's a php error. php does not understand what you trying to tell it.
Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1
That's an sql error. php already accepted the php script, but the mysql server refuses the string the script sent.
Two different kinds of error.

And for the sql error
volka wrote:And I cannot find that syntax at http://dev.mysql.com/doc/refman/5.0/en/update.html
I don't think there's a valid sql syntax that allows you to update records with more than one WHERE clause.
tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

Post by tam2000k2 »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I figured out why the SQL string was not working -- it's because I was starting the for loop with 0 instead of 1, which would get first_name0, last_name0, etc... instead of the values of first_name1, last_name1, etc...

Which caused the SQL error of:

Code: Select all

Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1
This was after commenting out all the connection code and just echoing the sql string being generated.

Now, I have a new issue and I can't see any reason why it's doing this but the $_REQUEST for my hidden fields is returning with NULL

Code: Select all

$my_sql_string2 = "UPDATE guest_login SET";
					for ($j=1; $j<=$i-1; $j++)
					{
        					$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_string3 = " WHERE ";
					for ($k=1; $k<=$i-1; $k++)
					{					
							$my_sql_string4 .= "FIRST_NAME='{$_REQUEST['hidden_first_name'.$j]}' AND LAST_NAME= '{$_REQUEST['hidden_last_name'.$j]}' AND EMAIL_ADDRESS='{$_REQUEST['hidden_email_address'.$j]}' AND WHAT_U_LIKED='{$_REQUEST['hidden_what_u_liked'.$j]}'";
					}
					$my_sql_string2 .= $my_sql_string3;
					$my_sql_string2 .= $my_sql_string4;
					echo $my_sql_string2 . "This is your sql query";
The result returns NULL values for the hidden fields requested:

Code: Select all


UPDATE guest_login SET FIRST_NAME= 'bob2', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob@newhart.com', WHAT_U_LIKED= 'yes' FIRST_NAME= 'bob3', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob2@newhart.com', WHAT_U_LIKED= 'yes2' FIRST_NAME= 'bob4', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob3@newhart.com', WHAT_U_LIKED= 'yes' WHERE FIRST_NAME='' AND LAST_NAME= '' AND EMAIL_ADDRESS='' AND WHAT_U_LIKED=''FIRST_NAME='' AND LAST_NAME= '' AND EMAIL_ADDRESS='' AND WHAT_U_LIKED=''FIRST_NAME='' AND LAST_NAME= '' AND EMAIL_ADDRESS='' AND WHAT_U_LIKED=''

When clearly the View Source on the previous page is:

Code: Select all


<form action='welcome3.php' method='post'><input type='textfield' name='pre_intials1' value='Mr.'><input type='textfield' name='first_name1' value='bob'><input type='textfield' name='last_name1' value='newhart'><input type='textfield' name='email_address1' value='bob@newhart.com'><input type='textfield' name='what_u_liked1' value='yes'><input type='hidden' name='hidden_pre_intials1' value='Mr.'><input type='hidden' name='hidden_first_name1' value='bob'><input type='hidden' name='hidden_last_name1' value='newhart'><input type='hidden' name='hidden_email_address1' value='bob@newhart.com'><input type='hidden' name='hidden_what_u_liked1' value='yes'><input type='textfield' name='pre_intials2' value='Mr.'><input type='textfield' name='first_name2' value='bob2'><input type='textfield' name='last_name2' value='newhart'><input type='textfield' name='email_address2' value='bob2@newhart.com'><input type='textfield' name='what_u_liked2' value='yes2'><input type='hidden' name='hidden_pre_intials2' value='Mr.'><input type='hidden' name='hidden_first_name2' value='bob2'><input type='hidden' name='hidden_last_name2' value='newhart'><input type='hidden' name='hidden_email_address2' value='bob2@newhart.com'><input type='hidden' name='hidden_what_u_liked2' value='yes2'><input type='textfield' name='pre_intials3' value='Mr.'><input type='textfield' name='first_name3' value='bob3'><input type='textfield' name='last_name3' value='newhart'><input type='textfield' name='email_address3' value='bob3@newhart.com'><input type='textfield' name='what_u_liked3' value='yes'><input type='hidden' name='hidden_pre_intials3' value='Mr.'><input type='hidden' name='hidden_first_name3' value='bob3'><input type='hidden' name='hidden_last_name3' value='newhart'><input type='hidden' name='hidden_email_address3' value='bob3@newhart.com'><input type='hidden' name='hidden_what_u_liked3' value='yes'><input type='hidden' name='my_i' value='4'><input type='submit' name='update_button' value='Update Record'></form>

Clearly there are values in the hidden fields but the $_REQUEST is not capturing them, why?

I just noticed another problem with the SQL query while checking out the preview, there is no commas between the first SET of fields and the generated sets afterwards. Plus there are no ANDs between the first set of wheres and the generated where values afterwards.

I guess I'm going to have to create for loops within each other, but first I have to figure out why the hidden fields are returning NULL values.

Thanks in advance.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I don't understand why the form has these hidden_ fields anyway.
Is there no single id in your database table - something like an auto_increment field that clearly identifies a record?
It would make things considerably easier.
tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

Post by tam2000k2 »

The reason for the hidden fields is to contain the original values that were pulled by a Select SQL string from the previous page.

Yes, there is an index key "auto-incrementor" and that would actually be an easier update statement "WHERE ID = BLA BLA BLA" but if that was stored in a hidden field, then I would still have the same issue, which is that it is not populating the hidden fields.

Thanks in advance and I'm almost there -- I figured how to add the AND at the end of the for loop by using an

Code: Select all

if($j<=$i-2)
{
  $my_sql_string4 .= ' AND ';
}
This is pretty cool considering I've only been into this since Friday evening and I thank you for being so open to a newbie.
tam2000k2
Forum Newbie
Posts: 13
Joined: Mon Jul 31, 2006 2:08 pm

Post by tam2000k2 »

I figured out why it appeared that the

Code: Select all

$_REQUEST
did not capture the hidden fields.

It is because my for loop was using a different value than what I was using in the sql string:

Code: Select all

for ($j=1; $j<=$i-1; $j++)
					{
        					$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]}')";
							if(($j<=$i-2))
							{
							$my_sql_string2 .= " AND ";
							}
					}
        					$my_sql_string3 = " WHERE ";
					for ($k=1; $k<=$i-1; $k++)
					{					
							$my_sql_string4 .= " (FIRST_NAME='{$_REQUEST['hidden_first_name'.$k]}' AND LAST_NAME= '{$_REQUEST['hidden_last_name'.$k]}' AND EMAIL_ADDRESS='{$_REQUEST['hidden_email_address'.$k]}' AND WHAT_U_LIKED='{$_REQUEST['hidden_what_u_liked'.$k]}')";
							if(($k<=$i-2))
							{
							$my_sql_string4 .= " AND ";
							}
					}
Now I'm having an SQL syntax error:


This is the query:

Code: Select all

UPDATE guest_login SET (FIRST_NAME= 'bob2', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob@newhart.com', WHAT_U_LIKED= 'yes') AND (FIRST_NAME= 'bob3', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob2@newhart.com', WHAT_U_LIKED= 'yes2') AND (FIRST_NAME= 'bob4', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob3@newhart.com', WHAT_U_LIKED= 'yes') WHERE (FIRST_NAME='bob' AND LAST_NAME= 'newhart' AND EMAIL_ADDRESS='bob@newhart.com' AND WHAT_U_LIKED='yes') AND (FIRST_NAME='bob2' AND LAST_NAME= 'newhart' AND EMAIL_ADDRESS='bob2@newhart.com' AND WHAT_U_LIKED='yes2') AND (FIRST_NAME='bob3' AND LAST_NAME= 'newhart' AND EMAIL_ADDRESS='bob3@newhart.com' AND WHAT_U_LIKED='yes')

Code: Select all

this is the error:

Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(FIRST_NAME= 'bob2', LAST_NAME= 'newhart', EMAIL_ADDRESS= 'bob@
Do I have to account for the "@" within the sql value's string?

Thanks again
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Yes, '@' signs need to be escaped with a backslash.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The error is referring to the paren.
Post Reply