Zend Insert Update ON Duplicate MySQL

Photo by Ben Griffiths on Unsplash

Zend Insert Update ON Duplicate MySQL

Theo van der Sluijs
·Oct 28, 2010·

3 min read

Subscribe to my newsletter and never miss my upcoming articles

Zend can sometimes be not so fun. Like inserting or updating when there is a duplicate in MySQL. Well I've got the solution.

Zend Framework is an open-source web application framework for PHP programming language. It is mainly used to develop enterprise-level web applications. It was developed by Andi Gutmans and Zeev Suraski in 2001, with active community involvement since then.

Zend is one of the most popular PHP frameworks today. It is an open source, object-oriented framework that helps PHP developers create and maintain high-quality PHP programs and Web sites. Zend is used by many developers and it can be integrated with other frameworks and libraries to provide various easy-to-use features.

Zend Framework promises to make web programming easier for developers by making it possible to build dynamic, powerful Web applications. Zend has three main components: a component library, a scripting language (PHP) and a Web server (Apache).

MySQL is the most popular open-source relational database system. It can be used to store data for web applications, desktop applications, and mobile apps.

MySQL is one of the most important tools for developers. It helps us store data like login information or user preferences. We can use it to create tables where we will save different types of data that can have different columns, each with its own type of data. This way we will have a more robust database solution that will allow us to interact with our application in a more efficient way.

Insert / Update on Duplicate

Many people struggle with the Insert / Update on duplicate feature of MySQL within Zend.

I also had a problem with this as the Zend does not support this in the framework .

To bad, so I have written my own, Insert and Update on Duplicate function.

Here it is:

<?php
    /**
    *   Create an Insert or Update on Duplicate statement
    *   @author iTheo
    *   @version 1
    *   @since 28 okt. 2010
    *   @param string $tableName The name of the table
    *   @param array $fields The Field names and data in it put in the array.
    *   @param string $IndexField The name of the index field  
    */  

    public function CreateInsertUpdate($tableName, array $fields = array(),n$IndexField) {  
        $fieldString = '';  
        print _pre($fields);  

    if (count($fields)) {  
        foreach ($fields as $fieldKey = > $field) {  
            $fieldNames[] = UniFrame_PDO::getInstance('main')->quoteIdentifier($fieldKey);  
            $fieldData[] = $field;
            $fieldUpdate[] = UniFrame _PDO::getInstance('main')->quoteIdentifier($fieldKey)."=".$field;
        }
        $fieldNameString = ‘ (‘.implode(‘,’, $fieldNames).’)’;  
        $fieldDataString = ‘ (‘.implode(‘,’, $fieldData).’)’;  
        $fieldUpdateString = implode(‘,’, $fieldUpdate);  
    }

    $query = "INSERT INTO ".$tableName.$fieldNameString." VALUES ".$fieldDataString.  
    " ON DUPLICATE KEY UPDATE ".$IndexField."=LAST_INSERT _ID(".$IndexField."), ".$fieldUpdateString;
     return $query;
    }

You can even fetch the last insert id with it (even when it updates, then it returns the index id).

Usage:

    <?php

    $tableName = "contacts";  
    $data = array('name' => 'Obama', 'firstname' => 'teddy',  'age' => '35');  
    $IndexField = "contact_id";

    $query = CreateInsertUpdate($tableName, $data, $IndexField );  
    $db->query($query);

    $id = $db->lastInsertId();

Did you find this article valuable?

Support Theo van der Sluijs by becoming a sponsor. Any amount is appreciated!

See recent sponsors Learn more about Hashnode Sponsors
 
Share this