One UPDATE line works, multiple lines won't.

[sergiozambrano]

I'm learning SQL and PHP, and my very first SQL tables work fine

Although, I've been changing around the whole code of my PHP script for the last two days and I still can't make it work.

I just narrowed it down to this:

 

I have a for loop repeating UPDATE queries and it breaks as soon as it loops. If I set the loop to 1, it works fine. Also, there are two other insert/delete statements that add up to the same $sql1 variable before to be executed, and those won't work either if combined. ONLY ONE statement works every time.

 

So the error must be in the line breaks… I said, but it's not

If I paste the echoed code in Workbench it works fine. (copied from browser output, and source code of it)

If I remove the backslash-n (new line) character the error appears in line 1. Otherwise, it's always in line2.

 

Here's the loop

	for($i=0;$i<$count;$i++){
$sql1.="UPDATE $tbl_name SET toolname = '$toolname[$i]', Providers_provid = $Providers_provid[$i], posturl = '$posturl[$i]', formdata = '$formdata_safe[$i]' WHERE toolid = $toolid[$i];\n";
}

 

An example of the generated sql is:

 

UPDATE tools SET toolname = 'domain whateveri', Providers_provid = 2, posturl = 'semrushi', formdata = 'abcdefghijklmno' WHERE toolid = 1;
UPDATE tools SET toolname = 'pageranker', Providers_provid = 1, posturl = 'hllp://somesite.con', formdata = 'form info' WHERE toolid = 2;
UPDATE tools SET toolname = 'nuevo tool', Providers_provid = 2, posturl = 'aaa', formdata = 'jiji' WHERE toolid = 3;

 

Server version: 5.1.45

Apache/2.2.14 (Unix) mod_ssl/2.2.14 OpenSSL/0.9.8l DAV/2 PHP/5.3.1

MySQL client version: mysqlnd 5.0.5-dev - 081106 - $Revision: 289630 $

Version information: 3.3.2-rc1

 

Here is the error (If I remove the backslash-n (new line) character the error appears in line 1. Otherwise, it's always in line2.)

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 'UPDATE tools SET toolname = 'pageranker', Providers_provid = 1, posturl = 'http:' at line 1

 

I already tried:

• Adding quotation marks and backticks to values and field names.
  
• Spacing and removing white space around equal signs and commas.
  
• Changing and removing backslash-n (new line) characters.
  
• Number variables without quotation marks.
  
• changing $sql1.="UPDATE…    to  sql1 = $sql1 . "UPDATE…
  
• Removing all variables except one, or any other.
  
• Changed the Character set of HTML to UTF8 (it was none before)
   
  

 

Can you think of any other reason this error can be caused?

 

Thanks.

[PFMaBiSmAd]

From the mysql_query documentation -

Description

resource mysql_query ( string $query [, resource $link_identifier ] )

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

 

 

[sergiozambrano]

Stands to reason…

 

I'll try something different and I'll close this humiliating post, jeje.

 

Thanks.