Updating Databases using variables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Updating Databases using variables

Post 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>');


?>
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Re: Updating Databases using variables

Post 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.
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Post 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.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post 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?
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Post 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
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

Why do you have a field named Police Force? Rename the field with no spaces.
Post Reply