PHP - How to update some values in postgresql table

Question

How do you update some values in postgresql table?

I have a large database with a lot of columns and within this column a lot of values. I would like to update only when the values aren't up to date any more. But I can't tell which one need to be updated because of the size. My code just updates all values but because of the large number of data it will take to long. Is there a way to specify, to determine which values need to be updated.

I though I could work on this principal ==> For example if a value in column sort is changed, the rest of the row will be updated automatically. In this way, it doesn't have to search for any changes in each column.

$host = "localhost";
$username = "postgres";
$password = "password";
$dbname = "dbtest";   
$dbh = new PDO("pgsql:dbname=$dbname; host=$host", $username, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if ($dbh->connect_error) {
    die("Connection failed: " . $dbh->connect_error);
}

# Query used to insert data
# $sql = "INSERT INTO test (id,sort,full, lm, gs, sp, gan, pac, gsyn, type, searchterm, inter, pat, go) VALUES ('".$id."','".$name ."','".$full_name."','".$lm ."','".$genesymbol ."','".$specie ."','".$geneacc ."','".$placc ."','".$genesyno ."','".$type ."','".$searchterm ."','".$intera ."','".$path ."','".$geneon ."')";

# Query used to update
UPDATE species SET sort ='".$name ."', full_name='".$full_name."' , lm='".$lm ."', gs='".$genesymbol ."', sp='".$specie ."', gan='".$geneacc ."', pac='".$placc ."', gsyn='".$genesyno ."', type='".$type ."', searchterm='".$searchterm ."', inter='".$intera ."', pat='".$path ."', go='".$geneon ."' WHERE id = '".$id."' ";

    if ($dbh->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $dbh->error;
}
$conn->close();

It seems this question is about how to synchronize existing rows against new incoming data, which is a common problem without a generic solution. It depends on the structure and meaning of the old and new data. — Daniel Vérité
– Daniel Vérité, Commented May 22, 2018 at 12:30
If for example the old data contains the wrong specie name and so the other information on that row is also incorrect. Is there then no generic solution to update these? — Nemo
– Nemo, Commented May 22, 2018 at 12:35
A key point is to match your old and new data with a field that is unique and present in both, so that you can join the new row with the old one. It does not appear in your question currently. — Daniel Vérité
– Daniel Vérité, Commented May 22, 2018 at 12:49

RDFozz · Accepted Answer · 2018-05-22 16:51:39Z

You know your data, and how safe it is to assume that the sort column will be different if any column has changed.

If you are correct, then your UPDATE statement should look like this (line breaks added for readability):

$sql = "UPDATE species SET sort ='".$name ."', full_name='".$full_name
      ."' , lm='".$lm ."', gs='".$genesymbol ."', sp='".$specie
      ."', gan='".$geneacc ."', pac='".$placc ."', gsyn='".$genesyno
      ."', type='".$type ."', searchterm='".$searchterm
      ."', inter='".$intera ."', pat='".$path ."', go='".$geneon
      ."' WHERE id = '".$id."' AND sort <> '".$name ."' ";

As others have noted, as written, the statement is vulnerable to a SQL injection attack, if the data is user-entered; it would be safer to parameterize the query, to avoid the possibility that users would form their input to do damage to your system. (Example - what if $name is ' + sort;delete species; ?)

deadpool · Accepted Answer · 2018-05-22 09:45:43Z

0

best solution would be to write one trigger which tells you last updated value of that coulmn & new value, so that you can update by the way your insert and update are prone to SQL INJECTION

answered May 22, 2018 at 9:45

deadpool

4562 gold badges10 silver badges24 bronze badges

Collectives™ on Stack Overflow

PHP - How to update some values in postgresql table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related