I am trying to use an UPDATE query in MySQL database table. I have primary key that auto increments in the table but I don’t want to use this key when doing this query. This is because this column doesn’t restart its count when records are deleted so there are gaps in the table. For example, if I delete row 25 and then INSERT a new row, the ID of that row/will be 26. So back to my question, I want to know how I can edit the values of one specific record/row. I have tried the following:

UPDATE `table_name` SET `first_Name` = "Robert" WHERE (SELECT * FROM `employees` LIMIT 1,1)

This was my try of updating the firs_Name in the second row only. But it doesn’t work. There is some syntax error. Is there any other way to do this? Or can I solve the problem of the auto-incrementing column (I would prefer an answer that ignores this).

Thanks in advance!

Answer

Your User Table Looks like this:

id    |   first_name
1     |   Robert
2     |   Sam

Now you want to edit SAM so he is called Samuel.

#1

Print the Users

Select * From Users

Then build a Loop to give user back in a table. Then you can do your Actions with it.

#2

<?php

if(isset($_GET['userid']))
{
    if(isset($_POST['update']))
    {
        mysql_query("UPDATE Users SET first_name = '".$_POST['first_name']."' where id=".$_GET['userid']." LIMIT 1");
    }
    else
    {
        $get_user_sql = "SELECT * FROM Users WHERE id = ".$_GET['userid']." LIMIT 1";
        $query_user = mysql_query($get_user_sql);
        $user = mysql_fetch_assoc($query_user);
        print '<form action="'.$_SERVER["PHP_SELF"].'" method="post">';
        print '<input type="text" name="first_name" id="first_name" value="'.$user['first_name'].'" />';
        print '<input type="submit" />';
    }
}
else
{
    $sql = "SELECT * FROM Users";
    $query = mysql_query($sql);
    while($row = mysql_fetch_assoc($query))
    {
        print '<a href="?userid='.$row['id'].'">edit user <b>'.$row['first_name'].'</b></a><br />';
    }
}

?>

How to update a specific row in a table without a primary key in MySQL?

Advertisement

Answer

#1

#2