How can I change this code to INSERT instead of UPDATE if content doesn't exist?

Question

I have a query that will update a row in the database, which works fine providing there is a row there to begin with.

How could I say; update if exists insert if doesn't?

    require_once('../scripts/includePDO.php');

    $who = $_SESSION['who'];
    $formText = $_POST['protext'];

$sql = "UPDATE tbl_profiles SET proText = :formText WHERE user_id = :who";

    $q   =  $conn->prepare($sql);
    $q->bindValue(':who',$who,PDO::PARAM_INT);
    $q->bindValue(':formText',$formText,PDO::PARAM_STR);
    $q->execute();

header("Location: ../settings/?status=Done");

score 0 · Answer 1 · answered Mar 18 '14 at 19:26

0

Assuming user_id is a unique key in the db:

$sql = "INSERT INTO tbl_profiles (user_id, proText) VALUES (:who, :formText) ON DUPLICATE KEY UPDATE proText = :formText";

answered Mar 18 '14 at 19:26

dave

62,300
5
72
93

score 0 · Answer 2 · answered Mar 18 '14 at 19:33

0

Your SQL query should be:

INSERT INTO tbl_profiles (user_id,proText) VALUES (:who,:formText)
ON DUPLICATE KEY UPDATE proText=:formText

This is assuming that user_ID is a unique id

answered Mar 18 '14 at 19:33

Just Lucky Really

1,341
1
15
38

score 0 · Answer 3 · answered Mar 18 '14 at 19:34

1- simple way is use ORM such as Dotrine
2- How ORM handle this :
usually tables has primary key(id) that should not be null .if you have update then you had select that load this data . in you select load id field in you data structure (array or object or something else) . in save method only check current row you want save that it has id (if this record has id then it exist and need to update else you should save).

How can I change this code to INSERT instead of UPDATE if content doesn't exist?

3 Answers3