Updating a MySQL database

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 a MySQL database

Post by InnerShadow »

I was wondering if anyone could help me write a php code that would update a MySQL table named 'united states' in a database named 'country.' Also, i was wondering if the sql code can use variables?

The name of the field that im trying to update is 'popularity'

thx
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Do you just need to update the information in that field? What's the table structure?

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

Post by InnerShadow »

The field that i want to edit is called 'popularity' and it's type is int(4).
My problem is that i want to add a variable amount, specified by a form, to the value already stored in the table.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

That shouldn't be a problem - you can do arithmetic stuff in MySQL:
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

For example:

Code: Select all

UPDATE mytable SET myfield = myfield + 4 WHERE unique_field=value
Mac
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Post by InnerShadow »

If you wrote it in php could you do something like

Code: Select all

<?php
$amount = $_POST['amount'];

$sql = UPDATE mytable SET myfield = myfield + $amount  WHERE unique_field=value

?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Should work (although you do need some double quotes around that SQL string ;) ). Best way to find out is to try.

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

Post by InnerShadow »

Im kinda new to the whole database thing, and i was wondering what goes in the "unique_field = value" part of the code?
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Post by InnerShadow »

also, say i wanted to take a value from the database and set it as an variable in PHP, how would i do that?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

after performing a selection you'll have a query result. Using a fetch function such as mysql_fetch_array(), you will recieve data about the next available record in array or object form. Using that returned data, you can set your variable.
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Post by InnerShadow »

Im having real problems with the update statement. Here's the code that i have to update the table.

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("world", $conn);
$sql = 'UPDATE `world` SET `popularity` = \'10\' WHERE CONVERT(`country` USING utf8) = \'United States\'  LIMIT 1';
?>
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post by trukfixer »

InnerShadow wrote:Im having real problems with the update statement. Here's the code that i have to update the table.

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("world", $conn);
$sql = 'UPDATE `world` SET `popularity` = \'10\' WHERE CONVERT(`country` USING utf8) = \'United States\'  LIMIT 1';
?>
try:

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("world", $conn);
$sql = "update world set popularity = 10 where convert(country using utf8) = 'United States' limit 1";
$res = mysql_query($sql);
?>
However, I am not sure why you need a limit 1 , unless you have many entires with "United States" in the country field..
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Post by InnerShadow »

i tried that code, and it still will not update the field. When the script runs, the error code comes up and says that it couldn't select the mysql database
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Try adding some error handling to the code:

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error());
mysql_select_db("world", $conn) or die('Could not select the database: '.mysql_error());
$sql = "update world set popularity = 10 where convert(country using utf8) = 'United States' limit 1";
$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');
?>
Perhaps it will give a more useful error message.

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

Post by InnerShadow »

It works now. Thanks a lot

-InnerShadow
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

trukfixer wrote:
InnerShadow wrote:Im having real problems with the update statement. Here's the code that i have to update the table.

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("world", $conn);
$sql = 'UPDATE `world` SET `popularity` = \'10\' WHERE CONVERT(`country` USING utf8) = \'United States\'  LIMIT 1';
?>
try:

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("world", $conn);
$sql = "update world set popularity = 10 where convert(country using utf8) = 'United States' limit 1";
$res = mysql_query($sql);
?>
However, I am not sure why you need a limit 1 , unless you have many entires with "United States" in the country field..
If you only want one record that is the most efficient way to retrieve the record. When you have a limit of 1 on a database query the database server will immediately stop scanning the database table for entries as soon as one entry is found. Without the LIMIT 1 the database server will continue scanning every single entry in that table until it reaches the end. If you only need one record out of any table you should always use the LIMIT attribute to reduce the load on the database server. If you know how many records you are going to process on any query it is best to use the LIMIT attribute to reduce the load on the database server. This really increases the execution speed and lowers the load of a query, especially if the table has thousands of entries.
Post Reply