PHPMySQL: How to determine if value already exists in database

Question

I have a form button that I need to do two different things, based on user input and whether that input already exists in my database. If the input DOES NOT exist, then the button will create a new record. If it DOES exist, then the existing record will be updated.

Here's my PDO query as it stands now:

/* First, we need to discover whether the Proposal No. entered already exists in the
database. If it doesn't, then a new record will be created. If                                                                            
it does, then an existing record will be updated. */
$pNoExists = $con->prepare("SELECT ProposalNo FROM ptfp1"); 
$pNoExists->execute();
$row = $pNoExists->fetch(PDO::FETCH_ASSOC);

When I run $row = $pNoExists->fetch(PDO::FETCH_ASSOC); through a while loop, all of the values for the field are present. Now I just need some guidance on how to use that in my button setup. This is what I want to do:

if($_POST['ButtonPush'] && input doesn't exist) {
  Create new record;
}
else {
  Update existing record;
}

Simple, right? But it's eluding me.

Having a WHERE clause in your SQL statement might help, rather than fetching every single proposal and then looping in PHP: let the database do what it's designed to do well — Mark Baker, Sep 26 '13 at 19:52
you can use `$count = mysqli_num_rows($con, "SELECT * FROM table WHERE ProposalNo = '$value'");` If `$count` is greater than 1, update existing. — Jacques ジャック, Sep 26 '13 at 19:55
Why don't you just use `INSERT` with the `ON DUPLICATE KEY UPDATE` clause to do it in one step? — Barmar, Sep 26 '13 at 20:02

score 0 · Answer 1 · answered Sep 26 '13 at 19:55

Given what you have, I would do:

if($_POST['ButtonPush'] && array_search($all_values, $input_value)) {
    Create new
}
else {
    Update
}

However, like the comment above, you may want to simply add a where clause to your "SELECT" statement so you are not grabbing the entire database table contents every time. And, one could even convert the SELECT in to a SELECT COUNT to bring down the amount of data being requested.

score 0 · Answer 2 · answered Sep 26 '13 at 19:55

0

You could use SELECT count(*) FROM ptfp1 WHERE ProposalNo = :input

Than check if the value you get is bigger than one. If it is, update it:

UPDATE ptfp1 set ... where ProposalNo = :input

else

INSERT INTO ptfp1(...) VALUES (...)

answered Sep 26 '13 at 19:55

deathek

31
3

score 0 · Answer 3 · answered Sep 26 '13 at 20:07

Assuming ProposalNo has a unique index in the table, you can do it all in one query:

INSERT INTO ptfp1 (ProposalNo, colA, colB, colC, ...)
VALUES (:ProposalNo, :colA, :colB, :colC, ...)
ON DUPLICATE KEY
    UPDATE colA = VALUES(colA), colB = VALUES(colB), colC = VALUES(colC), ...

Documentation

score 0 · Answer 4 · answered Sep 26 '13 at 20:29

Figured out an answer. Just use the user's input (stored in a session variable) in my SELECT statement:

$pNoExists = $con->prepare("SELECT ProposalNo FROM ptfp1 WHERE ProposalNo =                                                                                                                             
'".$_SESSION['ProposalNo']."'"); 
$pNoExists->execute();
$row = $pNoExists->fetch(PDO::FETCH_ASSOC);

And the button:

if($_POST['ButtonPush'] && !$row['ProposalNo']) {
  Write new record;
}
else {
  Update existing record;
}

Hiding in plain sight!

PHPMySQL: How to determine if value already exists in database

4 Answers4