Forum Moderators: coopster
I am writing some PHP (v4.1.2) to parse webpages, collect link details and place them in a MySQL table.
This will help me to manage reciprocal links.
As development continued I sudddenly hit a brick wall. MySQL reported a syntax error in the SQL yet there isn't one.
I know there isn't one because I echo it to the browser window.
If I then copy it and paste it into some test code - the SQL works fine.
Furthermore, even though a syntax error is reported, the insert works OK.
I build the INSERT statement using variables:
$link = LinkURL();
$domain = GetDomain($link);
.
etc. - then run the insert:
$InsertSQL = "INSERT INTO links ".
" (Status, SiteName, Domain, URL, Description, Password, Updated)".
" VALUES".
" ('NEW', '$SiteName', '$domain', '$link', '$Description', '$password', '".date("d/m/y H:i:s")."')";
echo $InsertSQL."<BR>";
$result = mysql_query($InsertSQL, $mydb) or mydie(mysql_error());
Seem reasonable?
When I run it, the $result is "1" - the number of rows inserted, but I get a syntax error report:
You have an error in your SQL syntax near '?p=coachsearch_profilepage&id=78#body, '')' at line 1
All of this is run-of-the-mill debugging of course, but here's the truly bizarre part.
Whilst debugging this, I removed the $ signs from all of the variables inside the insert query - thereby turning that query into a simple insertion of fixed string literals.
Yet MySQL still reports the same syntax error! Even though the string it says the error is near to - is no longer present in the query at all.
The string it mentions is a fragment of the value of $link on that run.
I can only remove that syntax error by commenting out the assignment to $link, which of course, is entirely ridiculous.
But when I do that - I get a different error: Unknown table 'www' in field list
So now I'm at the point where I'm questioning whether one plus one equals two or not.
Does PHP of MySQL have it's knickers in a twist?
If I've made a huge blunder - please be gentle. I simply cannot see it.Help!
mysql_real_escape_string($link)
dc
There are no special characters in the inserted values - so escaping should not be necessary.
And it also doesn't seem to explain the odd behaviour whereby the syntax error NEAR string is not any part of the query.
Yes, as I mentioned in my post.
where?
=====QUOTE=====
I know there isn't one because I echo it to the browser window.
If I then copy it and paste it into some test code - the SQL works fine.
===============
when I look at that error something doesn't jive.
...rofilepage&id=78#body, '')' at line 1
it looks like the link is the last value, given the ) in there, which shouldn't be the case. You tried commenting out the $link assignment and it went away, which I would expect.
you could also try concatenating the query differently and see if that matters, it probably won't.
$InsertSQL = "INSERT INTO links ".
" (Status, SiteName, Domain, URL, Description, Password, Updated)".
" VALUES".
" ('NEW', '$SiteName', '$domain', '" . $link . "', '$Description', '$password', '".date("d/m/y H:i:s")."')";
I know you have echo'ed the query but did you also look at the source for that page to make sure there isn't a char in there that is being interpretted by the browser? meaning there could be something in the actual query sent to the db that isn't in the browser output.
You could even try writing the string to a text file to get a good look at the raw query.
Depressingly, as almost always - there was nothing spooky going on - just run-of-the-mill idiocy.
In this case, there was another query being run in another function call just after the one i mentioned.
It was THAT query which was causing the problem - not this one - and the cause of that was a simple missing single quote.
Ugh.
nice work on finding it
