How to use replace existing data in row?

Question

I am trying to figure out how to update the data in a specific database row instead of deleting and then inserting another query again. Does anyone know how I could go about doing this?

main.php contents

<body>

<h1>Introduction Box</h1>
<form action="edit.php" method="post">
Title: <input type="text" name="introtitle">
Description: <textarea name="introdescription"></textarea>
<input type="submit">
</form>

</body>

edit.php contents (Right now this only adds the entry from the form)

<?php
// Connnection to MySQL
$connection = mysqli_connect("");

// Check Connection and Display If Error

if (mysqli_connect_errno($connection)) {
  echo "Failed to connect to MySQL: " . mysqli_conncet_error();
}

$introduction="INSERT INTO Introduction (Title, Description)
VALUES ('$_POST[introtitle]','$_POST[introdescription]')";

if (!mysqli_query($connection, $introduction) or die(mysql_error()))
  {
  die('Error: ' . mysqli_error($connection));
  }
echo "Record has been updated.";

mysqli_close($connection);

?>

Any help will be greatly appreciated!

You need to have a unique value you are passing, you can then add to your SQL on duplicate key ... — cmorrissey, Jul 26 '13 at 02:34
What exactly do you mean by a unique value and duplicate key? — user1804933, Jul 26 '13 at 02:36
First, SELECT * FROM tbl_name WHERE value = ""; If rowcount > 1, your value is exist, then you query should UPDATE else INSERT. Hope this logic can help. — 502_Geek, Jul 26 '13 at 02:37

score 2 · Answer 1 · answered Jul 26 '13 at 02:39

2

html form code will look like

<h1>Introduction Box</h1>
<form action="edit.php" method="post">
Title: <input type="text" name="introtitle">
Description: <textarea name="introdescription"></textarea>
<input type="hidden" name="id" value="<?php echo $current_edit_id;?>">
<input type="submit">
</form>

</body>

php script code will look like

<?php
// Connnection to MySQL
$connection = mysqli_connect("");

$current_edit_id = $_POST['current_edit_id'];

// Check Connection and Display If Error

if (mysqli_connect_errno($connection)) {
  echo "Failed to connect to MySQL: " . mysqli_conncet_error();
}

$introduction="UPDATE Introduction SET Title='value', Description='value' WHERE ID='$current_edit_id'";

if (!mysqli_query($connection, $introduction) or die(mysql_error()))
  {
  die('Error: ' . mysqli_error($connection));
  }
echo "Record has been updated.";

mysqli_close($connection);

?>

hope this will sure help you.

answered Jul 26 '13 at 02:39

liyakat

11,825
2
40
46

Does this look right to you? `$introduction="UPDATE Introduction SET Title='$_POST[introtitle]', Description='$_POST[introdescription]' WHERE ID='$current_edit_id'";` – user1804933 Jul 26 '13 at 02:48
thats just statement you need to write correct mysql update query, hope you could do it sure. – liyakat Jul 26 '13 at 02:49
it is post data when you submit form your hidden variable will post and save it in to this variable – liyakat Jul 26 '13 at 03:05
Thank you and do you know what the cause of this error is? `Error: Unknown column 'ID' in 'where clause'` – user1804933 Jul 26 '13 at 03:09
this is your primary key of your table where your data would be update on such row. – liyakat Jul 26 '13 at 03:12
@user1804933, is my answer is useful to you. will please accept it. so some one can have same problem in future so they will utilize our thread. – liyakat Jul 26 '13 at 03:20
1

Yes most reply is most definitely helpful! I don't have a key in my table and so I'll add that now. – user1804933 Jul 26 '13 at 04:04
1

Is this the type of table structure I should be looking for? [link](http://i.imgur.com/jgiggvK.png) – user1804933 Jul 26 '13 at 04:10

score 1 · Answer 2 · edited May 23 '17 at 11:49

In the Edit form, you need a hidden-field with the ID. Is "Title" the ID, or is there a separate ID column?

In the SQL, depending on whether ID or TITLE is your key, you need:

update INTRODUCTION set TITLE=?, DESCRIPTION=? where ID=?
update INTRODUCTION set TITLE=?, DESCRIPTION=? where TITLE=?  -- set new title, WHERE previous title

If you insist on using non-prepared statements (vulnerable to SQL injection), make sure you filter or sanitize the values going into your SQL statement. See: What are the best PHP input sanitizing functions?

UPDATE Introduction SET Title='value', Description='value' WHERE ID=id

Unfiltered values from the web into SQL allow attackers to hack or destroy your database with trivial ease.

score 1 · Answer 3 · answered Jul 26 '13 at 02:36

1

There are two options, you can use the clause "DUPLICATE KEY UPDATE" and just get what you want updated when the record is already there

INSERT INTO `table`
    (`col1`,`col2`)
SELECT * FROM table
ON DUPLICATE KEY UPDATE col1 = 'value';

The second option is use REPLACE. For this one just swap INSERT for REPLACE, and it should work fine:

REPLACE INTO Introduction (Title, Description)
VALUES ('$_POST[introtitle]','$_POST[introdescription]')

http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

http://dev.mysql.com/doc/refman/5.0/en/replace.html

answered Jul 26 '13 at 02:36

medina

8,051
4
25
24

Proprietary syntax, MySQL only, may also have multiuser/ concurrency issues. I recommend using correct ANSI SQL and remaining portable between databases. – Thomas W Jul 26 '13 at 02:38
He has not asked for a PORTABLE SQL. I don't even think he is really giving a shit for this, otherwise he would use PDO instead mysqli. No constructive comment. – medina Jul 26 '13 at 02:43
@medina agreed, this isn't a discussion about PDO – cmorrissey Jul 26 '13 at 02:47
Yes I am not considered about PDO right now. I just want to learn the basics of PHP and MySQL. Thank you for all your help and feedback though. – user1804933 Jul 26 '13 at 02:49

score 0 · Answer 4 · answered Jul 26 '13 at 02:36

0

The query syntax is mysql_query("UPDATE tablename SET column='value' WHERE CONDITION")

answered Jul 26 '13 at 02:36

Sarvap Praharanayuthan

4,212
7
47
72

How to use replace existing data in row?

4 Answers4