Updating a MySQL database
Moderator: General Moderators
-
InnerShadow
- Forum Commoner
- Posts: 37
- Joined: Thu Nov 10, 2005 10:44 pm
- Location: US
Updating a MySQL database
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
The name of the field that im trying to update is 'popularity'
thx
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
-
InnerShadow
- Forum Commoner
- Posts: 37
- Joined: Thu Nov 10, 2005 10:44 pm
- Location: US
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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:
Mac
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-
InnerShadow
- Forum Commoner
- Posts: 37
- Joined: Thu Nov 10, 2005 10:44 pm
- Location: US
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
?>- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
-
InnerShadow
- Forum Commoner
- Posts: 37
- Joined: Thu Nov 10, 2005 10:44 pm
- Location: US
-
InnerShadow
- Forum Commoner
- Posts: 37
- Joined: Thu Nov 10, 2005 10:44 pm
- Location: US
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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
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';
?>- trukfixer
- Forum Contributor
- Posts: 174
- Joined: Fri May 21, 2004 3:14 pm
- Location: Miami, Florida, USA
try: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'; ?>
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);
?>-
InnerShadow
- Forum Commoner
- Posts: 37
- Joined: Thu Nov 10, 2005 10:44 pm
- Location: US
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Try adding some error handling to the code:
Perhaps it will give a more useful error message.
Mac
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>');
?>Mac
-
InnerShadow
- Forum Commoner
- Posts: 37
- Joined: Thu Nov 10, 2005 10:44 pm
- Location: US
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
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.trukfixer wrote:try: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'; ?>However, I am not sure why you need a limit 1 , unless you have many entires with "United States" in the country field..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); ?>