|
daytripper05
Psychonaut




Registered: 10/30/06
Posts: 6,962
Loc: In my garden
|
PHP/MySQL help
#8306201 - 04/19/08 09:48 PM (16 years, 1 month ago) |
|
|
Ok, so I am stuck with a syntax error of some sort. I have been working on this all day, it's about the time everything is blending to together, so I can't find this mistake for the life of me.
Anyway, all is good except this Update MySQL statement. I have a series of embedded files that completely manage a series of data tables. This is for a client of mine. I will try to make this as clear and as easy as possible to help. This is the HTML markup. The part that is specifically hanging everything up is the update script.
-------- There are variables in there that will automatically fill in data if the user is updating an existing entry. If the entry doesn't exist, and they are creating a new one. The values are blank.
And for the sake of helping me.
$action = "scripts/updateLink.php?id=".$linkID."&loc=".urlencode('Location:../index.php?nav=partners&action=linkManager&id='.$getParentID)"
In this case, the user is editing the data so the action takes us to the updateLink.php file. The id must be defined for the script to work. And the $loc variable redirects the users back to where they were after the script executes(updates the datatable of 4 columns)
<form name="addLink" method="POST" action="'.$action.'"> <div style="margin-left:40px">Location(URL): <input type="text" name="url" value="'.$valPath.'" style="width:250px;margin-left:25px;margin-top:15px;" /></div><br /> <div style="margin-left:40px">Text to Display: <input type="text" name="desc" value="'.$valDesc.'" style="width:250px;margin-left:17px;margin-top:10px;" /></div><br /> <input type="submit" name="submit" value="'.$buttonVal.'" style="position:relative;left:330px;"/> </form>
------
So the action...
$con = mysql_connect("dbServer","dbName","dbPassword"); $dbName = "dbName";
All the stuff above has been renamed for security purposes.
$tblName = "partner_links";
//selects the desired MySQL database mysql_select_db($dbName, $con);
//if connection fails, alerts user if (!$con) { die('Could not connect: ' . mysql_error()); }
$id = $_GET['id']; $path = $_POST['url']; $desc = $_POST['desc'];
$sql = "UPDATE $tblName SET path = '$path', desc = '$desc' WHERE id = '$id' LIMIT 1";
$result = mysql_query($sql, $con);
if(isset($_GET['loc'])) $loc = $_GET['loc']; else $loc = "Location:../index.php?nav=partners&action=".$action; header($loc);
If have broke this script down piece by piece. The only that doesn't work is the update sql statement itself. But the syntax seems to be correct. For testing, I did an echo of $sql to see what is being returned. Everything looked good, but when I take the syntax and MySQL query browser, and that is what gave me an Error Code 106, which is a syntax error.
Any help would be more than appreciated. Ask any question you need to help get this figured out.
|
Seuss
Error: divide byzero



Registered: 04/27/01
Posts: 23,480
Loc: Caribbean
Last seen: 3 months, 8 days
|
|
Start by commenting out line 106 and see if the error persists or not. Sometimes, a missing quote earlier in the code can cause an error to show up later. If the error persists, but keeps moving as you comment out the error next line, then this is probably the problem. If it is a missing quote, they can be difficult to find.
If it isn't a missing quote problem, then take the line you commented and copy it to an uncommented version. Then delete everything except the first part of the line ($sql = "UPDATE $tblName";) and put a print and exit statement after it. Test. If it works, add a bit more to the single line and repeat. Eventually, you will find your syntax error.
-------------------- Just another spore in the wind.
|
daytripper05
Psychonaut




Registered: 10/30/06
Posts: 6,962
Loc: In my garden
|
Re: PHP/MySQL help [Re: Seuss]
#8307362 - 04/20/08 08:57 AM (16 years, 1 month ago) |
|
|
I did what you said, it's really strange, almost not following any logic at all.
The SQL statement works is you remove the ", desc = '$desc'". The statement will update the path now. So I thought the error was in the comma or updating multiple fields, but this is not the case.
When I edit the SQL statement to say "UPDATE $tblName SET desc = '$desc' WHERE id = '$id'", I get a syntax error. But like I said, when I replace "desc = '$desc'" with "path = $path" then it works just fine.
Now, you would think I have problem with field name 'desc'. But that IS the field name and I see it in MySQL query browser. I even tried to update the desc field with a static value and still the same error. Even in MySQL query browser it returns the a syntax error with I enter "UPDATE $tblName SET desc = '$desc' WHERE id = '$id'" but not for the path....(I changed the php variables to static values for the query browser test so that didn't cause the syntax error)
I commented the header($loc) and made and echoed the sql statement after it was sent, and all dynamic values add up...It's simply somewhere with "desc = '$desc'".
|
daytripper05
Psychonaut




Registered: 10/30/06
Posts: 6,962
Loc: In my garden
|
|
I found the problem, very strange too. when I altered the table field, desc to description, and then changed all the SQL statements in my scripts, all works fine. The syntax error was the field name desc. It must be reserved or something, but I tell you what, that was a pain in the ass. At least it works. Thanks for your help.
|
Seuss
Error: divide byzero



Registered: 04/27/01
Posts: 23,480
Loc: Caribbean
Last seen: 3 months, 8 days
|
|
> "desc = '$desc'" with "path = $path"
There is a difference between your two examples (other than var names).
|
daytripper05
Psychonaut




Registered: 10/30/06
Posts: 6,962
Loc: In my garden
|
Re: PHP/MySQL help [Re: Seuss]
#8307668 - 04/20/08 11:52 AM (16 years, 1 month ago) |
|
|
yes there is, I think that was a typo though.
This is SQL statement that ended up working. I tried every combo of quotes around the vars, and nothing worked. The script didn't work until I changed the the field name from desc to description.
Who knows though, maybe a character was left out, as you pointed out, then the act of me rewriting all vars fixed the problem.
This is the final sql statement:
$sql = "UPDATE $tblName SET path = '$path', description = '$desc' WHERE id = '$id'";
|
Seuss
Error: divide byzero



Registered: 04/27/01
Posts: 23,480
Loc: Caribbean
Last seen: 3 months, 8 days
|
|
Ah, this is your problem:
http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
This is a runtime error, not a syntax error. The identifier 'desc' is reserved in MySQL.
-------------------- Just another spore in the wind.
|
ectolysergic
Stranger
Registered: 02/23/08
Posts: 42
Last seen: 15 years, 8 months
|
Re: PHP/MySQL help [Re: Seuss]
#8309158 - 04/20/08 10:48 PM (16 years, 1 month ago) |
|
|
use backticks to escape the reserved words. `desc`
also be sure to filter your input with mysql_real_escape_string() because you can't trust what the user will be giving you as $desc is coming from a postvar.
|
|