Page 1 of 1

Updating a MySQL database

Posted: Sat Nov 12, 2005 9:26 pm
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

Posted: Sun Nov 13, 2005 1:11 pm
by twigletmac
Do you just need to update the information in that field? What's the table structure?

Mac

Posted: Sun Nov 13, 2005 2:59 pm
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.

Posted: Sun Nov 13, 2005 3:12 pm
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

Posted: Sun Nov 13, 2005 4:55 pm
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

?>

Posted: Mon Nov 14, 2005 6:41 am
by twigletmac
Should work (although you do need some double quotes around that SQL string ;) ). Best way to find out is to try.

Mac

Posted: Mon Nov 14, 2005 6:26 pm
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?

Posted: Mon Nov 14, 2005 6:57 pm
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?

Posted: Mon Nov 14, 2005 7:08 pm
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.

Posted: Mon Nov 14, 2005 9:25 pm
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';
?>

Posted: Mon Nov 14, 2005 9:30 pm
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..

Posted: Tue Nov 15, 2005 6:34 pm
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

Posted: Tue Nov 15, 2005 6:47 pm
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

Posted: Wed Nov 16, 2005 2:02 pm
by InnerShadow
It works now. Thanks a lot

-InnerShadow

Posted: Thu Nov 17, 2005 6:03 pm
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.