How to Update a record in a database

Show Comments

-2

Can i use one of this to update my password in the mysql db?

foxwizzy

Newbie Poster

17 posts since Oct 2009

Reputation Points: 10

Solved Threads: 2

Can i use one of this to update my password in the mysql db?

Please post your question in a new thread instead of resurrection a thread that is years old.

Here is the documentation to the syntax of a mysql update query: http://dev.mysql.com/doc/refman/5.0/en/update.html

Here is the documentation for mysql_query:
http://www.php.net/manual/en/function.mysql-query.php

Here is an example mysql update from connection to the db, to sending an update query:

<?php

// see http://www.php.net/manual/en/function.mysql-connect.php

// connect to mysql server at example.com
$link = mysql_connect('example.com', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

// see http://www.php.net/manual/en/function.mysql-select-db.php

// make database_name the current db
$db_selected = mysql_select_db('database_name', $link);
if (!$db_selected) {
    die ('Can\'t use database_name: ' . mysql_error());
}

// see http://www.php.net/manual/en/function.mysql-query.php

// send update query to db
$result = mysql_query('UPDATE tablename set column = \'value\' where id = 1', $link);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

mysql_close($link);

?>

Basically I just put this together from the mysql functions in the PHP.net documentation: http://www.php.net/manual/en/ref.mysql.php

PHP also has other database abstraction layers that you can use:
http://www.php.net/manual/en/refs.database.abstract.php

Nearly a Posting Virtuoso

Moderator

1,293 posts since Sep 2005

Reputation Points: 461

Solved Threads: 101

hi i am using the following query in program but i unable to update my data please tell the solution for this proble

the code is here

$updateQuery = "UPDATE users SET username = '".$username."',password='".$password."',skills= '".$skills."',email= '".$email."',gender='".gender."' WHERE ID = ".$id;
	
	$result = mysql_query($updateQuery);

thank you

mdshamsuddin

Newbie Poster

1 post since Apr 2010

Reputation Points: 10

Solved Threads: 0

Try turning on error reporting.

error_reporting(E_ALL);
ini_set('display_errors', '1');

And also doing some error handling:

$result = mysql_query($updateQuery)
if (!$result) {
  die('Mysql Error" '.mysql_error());
}

It will let you know what is wrong.

Nearly a Posting Virtuoso

Moderator

1,293 posts since Sep 2005

Reputation Points: 461

Solved Threads: 101

Use Update query...

mysql_query("update book set hometel='$edit_hometel',worktel='$edit_worktel',fax1='$edit_fax1',fax2='$edit_fax2',pobox='$edit_pobox',email='$edit_email' where name='$name' and hometel='$htel'");

BzzBee

Posting Whiz

327 posts since Apr 2009

Reputation Points: 16

Solved Threads: 48

Hi, Can someone tell me why this isnt working?? Its not updating the records that i have changed.

<?php

$con = mysql_connect("localhost", "root", "");
$SRID  = $_POST['SRID'];
$title =  $_POST['title'];
$fname =  $_POST['fname'];
$sname =  $_POST['sname'];
$add = $_POST['add'];
$phone = $_POST['phone'];
$call_status = $_POST['call_status'];
$eng_id = $_POST['eng_id'];
$eng_cmt = $_POST['eng_cmt'];

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("localdatabase", $con);

mysql_query("UPDATE testtable SET SRID = '$_POST[SRID]', title = '$_POST[title]', fname = '$_POST[fname]', sname = '$_POST[sname]', add = '$_POST[add]', phone = '$_POST[phone]',Call_Status = '$_POST[call_status]', eng_id= '$_POST[eng_id]', eng_cmt = '$_POST[eng_cmt]',
WHERE SRID = '$_POST[SRID]'");

?>

Newbie Poster

4 posts since May 2010

Reputation Points: 10

Solved Threads: 0

Try doing some error checking:

$result = mysql_query("...");
if (!$result) {
  die("MySQL Query failed with error: " . mysql_error());
}

That will tell you what went wrong with the query.

Note: You should never use values taken from POST and GET directly in your sql query. Such as $_POST['SRID']. You need to escape them first so they do not "break" the mysql query.

eg:

$srid = mysql_real_escape_string($_POST['SRID']);
...
$result = mysql_query("UPDATE testtable SET SRID = '$srid' .... ");

Imagine a user used a value:

$_POST['SRID'] = "this value has a single quote in it ' so it will break the query";

If you put that directly into the mysql query, then the query would look like:

"UPDATE testtable SET SRID = 'this value has a single quote in it ' so it will break the query' .... "

Notice how you now have three quotes, when there should only be two surrounding a value. This can be exploited intentionally or unintentionally by users to break to the query, or run other queries you did not expect.

When you use mysql_real_escape_string() on the value, it makes sure any quotes are escaped, so that they do not break the string.

Nearly a Posting Virtuoso

Moderator

1,293 posts since Sep 2005

Reputation Points: 461

Solved Threads: 101

Hi,

I got the error:

MySQL Query failed with error: 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 '...' at line

Can you help any further? This is the last thing I need for my project :-(

Newbie Poster

4 posts since May 2010

Reputation Points: 10

Solved Threads: 0

That was only an example. You have to put your actual mysql query into mysql_query(), and not the "..." that I put there.

Nearly a Posting Virtuoso

Moderator

1,293 posts since Sep 2005

Reputation Points: 461

Solved Threads: 101

Hi,

I REALLY appreciate your help!

I've done what you have said.. but it's still not updating the fields from the form which I have changed. So for example, I changed the Cal Status to REOPENED.

Is this coding right

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("budgetapps", $con);

$SRID  = $_POST['SRID'];
$title =  $_POST['title'];
$fname =  $_POST['fname'];
$sname =  $_POST['sname'];
$add = $_POST['add'];
$phone = $_POST['phone'];
$Call_Status = $_POST['Call_Status'];
$eng_id = $_POST['eng_id'];
$eng_cmt = $_POST['eng_cmt'];


$result = mysql_query("UPDATE testtable SET fname = '$fname', sname = '$sname', `add` = '$add', phone ='$phone' Call_Status = '$Call_Status', eng_id = '$eng_id', eng_cmt = '$eng_cmt' WHERE SRID = '$SRID'");if (!$result) {  die("MySQL Query failed with error: " . mysql_error());}



?>

Newbie Poster

4 posts since May 2010

Reputation Points: 10

Solved Threads: 0

Oh, and teh error is

MySQL Query failed with error: 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 'Call_Status = 'OPENED', eng_id = '', eng_cmt = '' WHERE SRID = '3'' at line 1

Newbie Poster

4 posts since May 2010

Reputation Points: 10

Solved Threads: 0