i can't insert a new values

Question

i can't insert a new value to my DB after checking if it is existed in the DB or not the update work fine but the insert doesn't work just i wanna know where i'm wrong here to learn any help ?

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("alimcenter");
$P1=$_POST['p1'];
$P2=$_POST['p2'];
$P3=$_POST['p3'];
$P4=$_POST['p4'];
$P5=$_POST['p5'];
$P6=$_POST['p6'];
$P7=$_POST['p7'];
$P8=$_POST['p8']; 
$P9=$_POST['p9']; 

$req1=mysql_query("select REF from mat where REF='$P1'");

if(mysql_num_rows($req1) != 0){
    $req2=mysql_query("UPDATE mat set qte=qte+$P3 where REF='$P1'");

    if($req2){
        echo "succes update";
    }
}
if(mysql_num_rows($req1) == 0){
    $req7=mysql_query("insert into mat 
                                (REF, designation, qte, un, PU, rem, 
                                MONTANT, tva, puf) 
                        values ('$P1', '$P2', '$P3', '$P4', '$P5', '$P6',
                                '$P7', '$P8', '$P9')");
    mysql_error();
    if($req7){
        echo "succes";
        echo "<br /><a href='ajout.html'>return</a>";
    }
}
?>

it just display to me the button but when i check my DB i can't find the new values insert

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7.0+. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Jan 08 '19 at 14:05
_Small Note:_ The CSS is irrelevant to this question and could have been left out for brevity — RiggsFolly, Jan 08 '19 at 14:10
You call `mysql_error`, but never store or show the error message that it returns. That's probably one of the reasons you are in the dark. — GolezTrol, Jan 08 '19 at 14:10
Also note, `mysql` has a [`REPLACE` statement](https://dev.mysql.com/doc/refman/8.0/en/replace.html), that can insert a new row and remove any existing row that matches. That way, you can _almost_ have one statement to replace the separate update and insert statements. I say almost, because there are certain rules that apply. There has to be a PK or unique key for this to work, and behind the screens the old row is removed while the new one is inserted, which may affect auto-increment fields, and other ways default values are assigned, nevertheless, it may make your life somewhat easier. — GolezTrol, Jan 08 '19 at 14:13
Check out [the red boxes](http://php.net/manual/en/function.mysql-connect.php) on all the `mysql_` api documentation — RiggsFolly, Jan 08 '19 at 14:13
Oh and Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Jan 08 '19 at 14:15

waterloomatt · Answer 1 · 2019-01-08T15:03:54.170

I'm taking a bit of a gamble here because you haven't told us if you have a unique key defined but if you do, you can use what is called an upsert: try an insert and then fall back to an update if it already exists. The key here is that you need to have a unique key defined on your columns. You can read more about it here - https://stackoverflow.com/a/42305283/296555

As the comments have pointed out, you're using a very old, deprecated API. You should switch over to mysqli or PDO.

So without further ado, welcome to the wonderful world of PDO. I should mention that this code has not been tested and comes with no warranty. Please read this page if you have any questions - https://phpdelusions.net

<?php
$host = '127.0.0.1';
$db   = 'alimcenter';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {

    // Create a new connection
    $pdo = new PDO($dsn, $user, $pass, $options);

    // Define your SQL statement with placeholders. Do not pass in variables here.
    $sql = "INSERT INTO mat (REF, designation, qte, un, PU, rem, MONTANT, tva, puf) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE qte = qte + ?";

    $stmt = $pdo->prepare($sql);

    // Finally, bind your variables to the statement and execute it. There are a few ways to bind variables. 
    $stmt->execute([
        $_POST['p1'],
        $_POST['p2'],
        $_POST['p3'],
        $_POST['p4'],
        $_POST['p5'],
        $_POST['p6'],
        $_POST['p7'],
        $_POST['p8'],
        $_POST['p9'],
        $_POST['p1'], // this is the final (10th) placeholder in the "DUPLICATE KEY UPDATE" portion of the statement
    ]);

    $count = $stmt->rowCount();

    echo "Rows affected: {$count}";

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

i can't insert a new values

1 Answers1