944,137 Members | Top Members by Rank

Web Development > PHP > How to Update a record in a database
Ad:
  • PHP Discussion Thread
  • Unsolved
  • Views: 36064
  • PHP RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
Aug 1st, 2004
0

How to Update a record in a database

Expand Post »
php Syntax (Toggle Plain Text)
  1. if(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=".$row['name']." and hometel=".$row['hometel'])){
  2. echo "<font face='arial narrow' size='3' color='#ffffff'>The Record has updated succesfully.</font><br>";
  3. echo "<a href='index.php'><img src='images/buttons/continue_butt.gif' border='0'></a>";
  4. }else{
  5. echo "<font face='arial narrow' size='3' color='#ffffff'>An error has occured. Updating record failed.</font>";
  6. }

All Variables' names are right but it dosn't update the record. Please, do you have an idea how to make it work.
Last edited by digital-ether; Nov 3rd, 2009 at 9:58 pm. Reason: Fixed code tags...
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
alaa_steel is offline Offline
2 posts
since Jul 2004
Permalink
Aug 1st, 2004
0

Re: How to Update a record in a database

I assume all columns are varchar. You can try one of them. If it's not working, then post here again.

php Syntax (Toggle Plain Text)
  1. if(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='".$row['name']."' and hometel='".$row['hometel']."'")){
  2. echo "<font face='arial narrow' size='3' color='#ffffff'>The Record has updated succesfully.</font><br>";
  3. echo "<a href='index.php'><img src='images/buttons/continue_butt.gif' border='0'></a>";
  4. }else{
  5. echo "<font face='arial narrow' size='3' color='#ffffff'>An error has occured. Updating record failed.</font>";
  6. }

or

php Syntax (Toggle Plain Text)
  1.  
  2. $name = $row['name'];
  3. $htel = $row['hometel'];
  4.  
  5. if(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'")){
  6. echo "<font face='arial narrow' size='3' color='#ffffff'>The Record has updated succesfully.</font><br>";
  7. echo "<a href='index.php'><img src='images/buttons/continue_butt.gif' border='0'></a>";
  8. }else{
  9. echo "<font face='arial narrow' size='3' color='#ffffff'>An error has occured. Updating record failed.</font>";
  10. }
Last edited by digital-ether; Nov 3rd, 2009 at 9:58 pm. Reason: fixed code tags
PoA
Reputation Points: 19
Solved Threads: 9
Posting Whiz in Training
PoA is offline Offline
234 posts
since Jul 2004
Permalink
Nov 3rd, 2009
-2
Re: How to Update a record in a database
Can i use one of this to update my password in the mysql db?
Reputation Points: 10
Solved Threads: 2
Newbie Poster
foxwizzy is offline Offline
17 posts
since Oct 2009
Permalink
Nov 3rd, 2009
0
Re: How to Update a record in a database
Click to Expand / Collapse  Quote originally posted by foxwizzy ...
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 Syntax (Toggle Plain Text)
  1. <?php
  2.  
  3. // see http://www.php.net/manual/en/function.mysql-connect.php
  4.  
  5. // connect to mysql server at example.com
  6. $link = mysql_connect('example.com', 'mysql_user', 'mysql_password');
  7. if (!$link) {
  8.     die('Could not connect: ' . mysql_error());
  9. }
  10.  
  11. // see http://www.php.net/manual/en/function.mysql-select-db.php
  12.  
  13. // make database_name the current db
  14. $db_selected = mysql_select_db('database_name', $link);
  15. if (!$db_selected) {
  16.     die ('Can\'t use database_name: ' . mysql_error());
  17. }
  18.  
  19. // see http://www.php.net/manual/en/function.mysql-query.php
  20.  
  21. // send update query to db
  22. $result = mysql_query('UPDATE tablename set column = \'value\' where id = 1', $link);
  23. if (!$result) {
  24.     die('Invalid query: ' . mysql_error());
  25. }
  26.  
  27. mysql_close($link);
  28.  
  29. ?>

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
Last edited by digital-ether; Nov 3rd, 2009 at 10:26 pm.
Moderator
Reputation Points: 457
Solved Threads: 101
Nearly a Posting Virtuoso
digital-ether is offline Offline
1,250 posts
since Sep 2005
Permalink
Apr 27th, 2010
0
Re: How to Update a record in a database
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
php Syntax (Toggle Plain Text)
  1. $updateQuery = "UPDATE users SET username = '".$username."',password='".$password."',skills= '".$skills."',email= '".$email."',gender='".gender."' WHERE ID = ".$id;
  2.  
  3. 	$result = mysql_query($updateQuery);

thank you
Last edited by digital-ether; Apr 27th, 2010 at 5:01 pm. Reason: Please wrap your code in [code] tags
Reputation Points: 10
Solved Threads: 0
Newbie Poster
mdshamsuddin is offline Offline
1 posts
since Apr 2010
Permalink
Apr 27th, 2010
0
Re: How to Update a record in a database
Try turning on error reporting.

PHP Syntax (Toggle Plain Text)
  1. error_reporting(E_ALL);
  2. ini_set('display_errors', '1');

And also doing some error handling:

PHP Syntax (Toggle Plain Text)
  1. $result = mysql_query($updateQuery)
  2. if (!$result) {
  3.   die('Mysql Error" '.mysql_error());
  4. }

It will let you know what is wrong.
Moderator
Reputation Points: 457
Solved Threads: 101
Nearly a Posting Virtuoso
digital-ether is offline Offline
1,250 posts
since Sep 2005
Permalink
Apr 27th, 2010
0
Re: How to Update a record in a database
Use Update query...

PHP Syntax (Toggle Plain Text)
  1. 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'");
Reputation Points: 16
Solved Threads: 48
Posting Whiz
BzzBee is offline Offline
327 posts
since Apr 2009
Permalink
May 22nd, 2010
0
Re: How to Update a record in a database
Hi, Can someone tell me why this isnt working?? Its not updating the records that i have changed.

PHP Syntax (Toggle Plain Text)
  1. <?php
  2.  
  3. $con = mysql_connect("localhost", "root", "");
  4. $SRID  = $_POST['SRID'];
  5. $title =  $_POST['title'];
  6. $fname =  $_POST['fname'];
  7. $sname =  $_POST['sname'];
  8. $add = $_POST['add'];
  9. $phone = $_POST['phone'];
  10. $call_status = $_POST['call_status'];
  11. $eng_id = $_POST['eng_id'];
  12. $eng_cmt = $_POST['eng_cmt'];
  13.  
  14. if (!$con)
  15.   {
  16.   die('Could not connect: ' . mysql_error());
  17.   }
  18.  
  19. mysql_select_db("localdatabase", $con);
  20.  
  21. 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]',
  22. WHERE SRID = '$_POST[SRID]'");
  23.  
  24. ?>
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ryry09 is offline Offline
4 posts
since May 2010
Permalink
May 22nd, 2010
0
Re: How to Update a record in a database
Try doing some error checking:

PHP Syntax (Toggle Plain Text)
  1. $result = mysql_query("...");
  2. if (!$result) {
  3.   die("MySQL Query failed with error: " . mysql_error());
  4. }

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:

PHP Syntax (Toggle Plain Text)
  1. $srid = mysql_real_escape_string($_POST['SRID']);
  2. ...
  3. $result = mysql_query("UPDATE testtable SET SRID = '$srid' .... ");

Imagine a user used a value:

PHP Syntax (Toggle Plain Text)
  1. $_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:

PHP Syntax (Toggle Plain Text)
  1. "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.
Last edited by digital-ether; May 22nd, 2010 at 2:45 pm.
Moderator
Reputation Points: 457
Solved Threads: 101
Nearly a Posting Virtuoso
digital-ether is offline Offline
1,250 posts
since Sep 2005
Permalink
May 22nd, 2010
0
Re: How to Update a record in a database
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 :-(
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ryry09 is offline Offline
4 posts
since May 2010

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in PHP Forum Timeline: My drop-down menu is at the back of the content, HELP!
Next Thread in PHP Forum Timeline: Problem with Link





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC