Trying to update column value for specific row only?

Question

I am having some problems with a script, I am basically inputting data into a MySQL table. This data will be inserted in the table as 1 row.

Upon a row of data being entered into the table I want the current/specific row currently being entered to have the column 'account_type' to be updated from its default value 'member' to 'client'.

It's a long story why I need to do it this way but I do not want to simply just enter the value 'client' it must be updated from 'member' to client.

The script I have (which is the bit at the bottom) is currently doing just this but it is affecting all rows in the table, is there a way I can add a where clause to the update to say only affect the current row being entered and do not update all other rows in the table?

<?php ob_start(); 
// CONNECT TO THE DATABASE
    require('../../includes/_config/connection.php');
// LOAD FUNCTIONS
    require('../../includes/functions.php');
$username = $_POST['username'];
$password = $_POST['password'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$number = $_POST['number'];
$dob = $_POST['dob'];
$accounttype = $_POST['accounttype'];


$query="INSERT INTO ptb_registrations (
username,
password,
firstname,
lastname,
email,
number,
dob,
accounttype,
date_created )
VALUES(
'".$username."',
'".$password."',
'".$firstname."',
'".$lastname."',
'".$email."',
'".$number."',
'".$dob."',
'".$accounttype."',
now()
)";
mysql_query($query) or die();


$query="INSERT INTO ptb_users (
first_name,
last_name,
email,
password )
VALUES(
'".$firstname."',
'".$lastname."',
'".$email."',
MD5('".$password."')
)";
mysql_query($query) or dieerr();
$result = mysql_query("UPDATE ptb_users SET ptb_users.user_id = ptb_users.id,
      ptb_users.account_type = 'Client'");

There are a lot of code -would be useful to highlight the important one-. Anyway, if you just want to update a record, what about `UPDATE ... WHERE ...`? — fedorqui, Apr 15 '13 at 11:32

score 3 · Answer 1 · edited May 25 '19 at 14:44

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

You can use the MySQL function LAST_INSERT_ID() to do this.

The old ext/MySQL extension exposes this functionality through mysql_insert_id(), but you can also access it directly, and more cleanly, and safely, in a query.

So you can do something like this:

$result = mysql_query("
    UPDATE ptb_users
    SET ptb_users.user_id = ptb_users.id,
        ptb_users.account_type = 'Client'
    WHERE id = LAST_INSERT_ID()
");

I know you say "it's a long story..." But what you are doing makes little-to-no sense. I can only imagine you are doing this because of a trigger - and that demonstrates quite nicely why triggers are generally a bad idea ;-)

Please try and re-think your design if at all possible.

score 1 · Answer 2 · answered Apr 15 '13 at 11:34

1

Get the inserted ID after your first query then use it in the update (assuming you have a primary key with auto-increment).

answered Apr 15 '13 at 11:34

slash197

9,028
6
41
70

how would i do that please? im only just new to mysql and wouldnt know where to start with that, but yes my auto icncrement id is primary – Bear John Apr 15 '13 at 11:36

score 0 · Answer 3 · answered Apr 15 '13 at 11:52

0

Try With WHERE Condition on unique coloumn

     mysql_query("UPDATE ptb_users SET ptb_users.user_id = ptb_users.id,
     ptb_users.account_type = 'Client'" WHERE ptb_user.email='$email');

answered Apr 15 '13 at 11:52

venkatesh

164
1
13

Trying to update column value for specific row only?

3 Answers3