Page 1 of 2
for loop update
Posted: Mon Jul 31, 2006 2:29 pm
by tam2000k2
feyd | Please use 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
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]
Posted: Mon Jul 31, 2006 3:26 pm
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.
Posted: Mon Jul 31, 2006 4:29 pm
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?
Posted: Mon Jul 31, 2006 5:07 pm
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.
Posted: Mon Jul 31, 2006 5:18 pm
by tam2000k2
Everah | Please use 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
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]
Posted: Mon Jul 31, 2006 5:32 pm
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
Posted: Mon Jul 31, 2006 5:39 pm
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.
Posted: Mon Jul 31, 2006 6:01 pm
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.' ]}',
Posted: Mon Jul 31, 2006 6:09 pm
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
I don't think there's a valid sql syntax that allows you to update records with more than one WHERE clause.
Posted: Mon Jul 31, 2006 11:28 pm
by tam2000k2
feyd | Please use 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
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]
Posted: Tue Aug 01, 2006 5:36 am
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.
Posted: Tue Aug 01, 2006 11:35 am
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.
Posted: Tue Aug 01, 2006 2:32 pm
by tam2000k2
I figured out why it appeared that the
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
Posted: Tue Aug 01, 2006 2:59 pm
by RobertGonzalez
Yes, '@' signs need to be escaped with a backslash.
Posted: Tue Aug 01, 2006 3:02 pm
by feyd
The error is referring to the paren.