Check to see if tuple data exists in database before insert

Question

I am trying to check if tuple data exists before entering the data into the database columns. I am trying mysql_query and getting errors that I should be using mysqli, but it does not seem to be working for me.

This is my code so far

foreach ($AllData as $sigleData) {
    $table = $sigleData['name'];
    $columns = implode(", ", $sigleData['columns']);
    $columnData = implode(" ',' ", $sigleData['data']);

    //insert into database tuple data       
    $sqlQuery = "INSERT INTO " . $table . " ( " . $columns . ") VALUES( '" . $columnData . "')";

    if ($dbConnectionT->query($sqlQuery) == TRUE) {
        echo "database updated";
        echo "</br>";
    }
}

I have this function :-

        function check_tuple_exists($table){
        global $dbConnectionT; // connection to database
        $sql = "SELECT ALL FROM '".$table."'";
        $res = $dbConnectionT->query($sql);
        if ($res->num_rows > 0) 
        {   // output data of each row
            while($row = $res->fetch_assoc()) {
            echo "data " . $columnData . "<br>";
            }
        } else 
                {
                    echo "0 results";
                }       

        }

but it gives me this error:- Trying to get property of non-object

Your code doesn't seem to be checking for the existence of anything in the database, so I don't understand the question. — Gordon Linoff, Apr 16 '15 at 10:44
Sorry, I have just added the function i have created @GordonLinoff — tony, Apr 16 '15 at 10:46
If you don't want duplicate entries in databases, then you **don't do** what you are doing. You create a unique key and you only insert. If the record exists, MySQL will use SIGNAL SQLSTATE which is interpreted as an exception in most languages. Reason for this way of doing things is that there exists certain lag between MySQL and connected clients (in your case that's PHP). When you get the info that something doesn't exist, another process might insert the data during that time. That's why we let databases worry about data integrity, not languages using them. — N.B., Apr 16 '15 at 10:52
I just want to try and check them like say a username and password check — tony, Apr 16 '15 at 11:47
In this case we need a more specific example. Looking at your description above, your example means that you would insert a new row (username, password2) if there is already a row (username, password1). I think that this is not what you want to do... — Pascal Ockert, Apr 16 '15 at 12:00
Ok, explain what line exactly fails: this could be the dbConnectionT, and you'll have troubles when connecting, or may it be $dbConnectioT->query that is not being performed. The object that you're retrieving is not correctly instantiated, so you can't fetch any property from it — Federico J., Apr 16 '15 at 12:52

score 1 · Answer 1 · edited May 23 '17 at 12:22

Well, if you're doing that because you wants to update/skip insert the data you're inserting, you should try with INSERT REPLACE or INSERT IGNORE queries instead of checking on advance. Is going to be easier for you and cleaner and faster to your application. Check this question to see the difference:

What is the performance difference between "insert ignore" and replace in MySQL?

Basically:

INSERT IGNORE skip insert if exists
INSERT REPLACE change the row if exists

Also, and that is FYI, be aware you have possible SQL injections in your code. Check for PDO and prepared statements to avoid them.

I just want to try and check them like say a username and password check — tony, Apr 16 '15 at 11:47

score 1 · Answer 2 · answered Apr 16 '15 at 10:57

An faster and easier way (which always ensures a consistent database, independent from PHP) is to create a composite UNIQUE index on your MySQL table (see MySQL docs, this is also possible in phpMyAdmin). This index should contain all columns that have to be unique in this combination; in your case this would be all columns.

This index ensures that there are no duplicates in your table. When you insert a new row with PHP, just use the statement INSERT IGNORE INTO ... (see docs). This will first check if there is already a row with the same contents. In this case, the statement will be ignored, otherwise it will be created.

I just want to try and check them like say a username and password check — tony, Apr 16 '15 at 11:47

Check to see if tuple data exists in database before insert

2 Answers2