Page 1 of 1

Updating Databases using variables

Posted: Thu Nov 24, 2005 12:33 pm
by InnerShadow
I was writing some code to update one of my databases and figured it would be a lot easier to use variables then some long If/then statement, but i can't seem to figure out what's wrong with this code. Any help would be great.

Code: Select all

<?php

$bought = $_POST['bought'];

$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error()); 
mysql_select_db("countries", $conn) or die('Could not select the database: '.mysql_error()); 
$sql = "UPDATE improvements SET $bought = yes WHERE CONVERT(Country using utf8) = '$ID' LIMIT 1"; 
$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');


?>

Re: Updating Databases using variables

Posted: Thu Nov 24, 2005 3:58 pm
by neophyte

Code: Select all

$sql = "UPDATE improvements SET bought = 'yes' WHERE CONVERT(Country using utf8) = '$ID' LIMIT 1";


I don't understand why you have a dollar sign n front of bought. Is bought a field name? "Yes" should be a string and have single quotes around it.

Posted: Thu Nov 24, 2005 4:27 pm
by InnerShadow
$bought is a variable defined by a form on a previous page hence the:

Code: Select all

$bought = $_POST['bought'];
The way i set it up, the choices in the previous form are the same as the field names of the table, so when the program recieves information from the form, i can make updates without a long if/then or switch statement

I've done this before in previous code, and that worked fine, so i know the sql query can handle variables.

Posted: Thu Nov 24, 2005 4:40 pm
by neophyte
If 'yes' is a string it needs to be escaped or it will look for a field called yes. What is the specfic error returned by mysql?

Posted: Thu Nov 24, 2005 7:22 pm
by InnerShadow
Here's the exact error that it prints on the screen:


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 'Force = 'yes' WHERE CONVERT(Country using utf8) = 'United States' LIMIT 1' at line 1

SQL:
UPDATE improvements SET Police Force = 'yes' WHERE CONVERT(Country using utf8) = 'United States' LIMIT 1

Posted: Thu Nov 24, 2005 7:29 pm
by neophyte
Why do you have a field named Police Force? Rename the field with no spaces.