Conditional Registration with PDO

How do we skip existing rows and open unregistered as a new record when doing bulk recording with PDO? For this query we need a unique column in MySql. In addition, we will use “ON DUPLICATE KEY UPDATE” condition in PDO query.

Database requirements for conditional batch registration with PDO

Before writing our query, we must define a unique column in our database. In doing so, the data to be entered in the column we select must be unique. (Id cannot be used for this operation because id is auto increment, which is determined at the time of registration. We need a value to process before registering.)

There are many methods that you will use for a unique column that you can use any of them available on the Internet. (E-mail and record time are the most commonly used values for the unique value.)

The method I use is like this, when I examined the records I added in my last job, I noticed that the url part of the content I added was unique. But there was a problem that the length of the url can be too long to create a unique index. To overcome this situation, we opened a column named control, first convert the incoming url to record in MD5 format and then add it to this field. Of course, thanks to this process in my hand md5 converted was a unique value. That’s exactly what we need.

'control' varchar(32) NOT NULL

This is our job in Mysql so let’s move on to the PDO query we will use in PHP.

Query for conditional batch recording with PDO

The key part of this query is ”ON DUPLICATE KEY UPDATE” MySql function. This function checks if the content to be added is present in the table before recording.

Let’s see how they explained this in W3Schools, which sets the standards of this work, in response to the question of how to make a mass registration with PDO before moving on to our code. (Prepared Statements in PDO)

Conditional Registration with PDO

// Database Info
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    // PDO connect
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // PDO error display
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // clean and secure queries thanks to prepare
    $stmt = $conn->prepare("INSERT INTO posts (title, url, control)
                                VALUES (:title, :url, :control)
                                ON DUPLICATE KEY UPDATE control= :control");
    // Define values to variables
    $stmt->bindParam(':title', $title, PDO::PARAM_STR);
    $stmt->bindParam(':url', $url, PDO::PARAM_STR);
    $stmt->bindParam(':control', $control, PDO::PARAM_STR);


    // Loop incoming data with foreach
    foreach($results as $data){

        // we assign the url part in the incoming data to a variable named controlId as md5
        $controlId = md5($data["url"]);

        // let's add the query to
        $title = $data["title"];
        $url = $data["url"];
        $control = $controlId;
        // run query 
        $stmt->execute();

    }

    echo "Registrations were made successfully";

}
catch(PDOException $e)
{
    echo "Error: " . $e->getMessage();
}
$conn = null;

If you omit the Foreach loop from the above code, the query makes one record at a time.

Hope to be useful…

Add a Comment

Your email address will not be published. Required fields are marked *