CODEWITHSUNDEEP
phpmysqldatabaseloopsquerying
Skyfactor_interns
Skyfactor_interns

Reputation: 55

PHP MySQL large quantity of queries being passed,

I have a PHP script that is passing large quantities of queries to a DB very quickly. Does a MySQL DB queue the queries as they come in if it can't process them at the same speed they are being passed, or do they get lost?

My program has written and passed syntactically correct queries to the DB, but the DB is very far behind in terms of information contained in tables and number of tables.

Some example code (I am slightly new to PHP, so my code/coding style may be horrifying): 

//If table has one primary key
        $val = $tblColPkArray[0]; 

        $pkInsert = ", PRIMARY KEY (". $val['COLUMN_NAME'] .")"; 
        $pkColName = $val['COLUMN_NAME'];

        $string = ltrim($string, ","); 

        $oneCreateTableQuery = $beginning . $string . $pkInsert . $end; 

        echo $oneCreateTableQuery . "\n"; 

        $newLink->query($oneCreateTableQuery); 

        $pkValuesInOld = "SELECT " . $pkColName . " FROM " . $tables . ";";

        $pkValsResult = $link->query($pkValuesInOld);

        while($pkValues = $pkValsResult->fetch(PDO::FETCH_ASSOC))
        {
            $pkRowValuesQuery = "SELECT * FROM " . $tables . " WHERE " . $pkColName . " = '" . $pkValues[$pkColName] . "';"; 

            echo $pkRowValuesQuery . "\n";

            $valsOfPkInOldTable = $link->query($pkRowValuesQuery); 

            while($pkVals = $valsOfPkInOldTable->fetch(PDO::FETCH_ASSOC))
            {
                //var_dump($ckVals); 

                $insertBeginning = "INSERT INTO " . $tables . "("; 
                $insertValuesSection = ") VALUES (";
                $insertEnd = ");";
                $keys = ""; 
                $rowValues = ""; 

                foreach($pkVals as $key => $value)
                {
                    $keys = $keys . ", " . $key; 
                    $rowValues = $rowValues . ", '" . $value . "'"; 
                }

                $insertStatement = $insertBeginning . ltrim($keys, ",") . $insertValuesSection . ltrim($rowValues, ",") . $insertEnd; 

                echo $insertStatement . "\n"; 

                $newLink->query($insertStatement);

            }//While loop: Inserting values of old table into new table via INSERT INTO statement 

            //unset();

        } //While loop: Selecting all values from old table based on PK values per table, pass result of that query to next while loop

Upvotes: 1

Views: 44

Answers (2)

Rick James
Rick James

Reputation: 142296

Looks like you could do even combine the INSERT and SELECT:

INSERT INTO table (...)
    SELECT ... FROM ...;

Furthermore, your nested loops look like this might work:

INSERT INTO table (...)
    SELECT ... FROM ...
               JOIN ...;

That would get it down to one call to ->query() and eliminate most of your code.

Upvotes: 1

John Bupit
John Bupit

Reputation: 10618

You can do this in a single query, instead of calling multiple insert statements. For instance, instead of running these 3 queries,

INSERT INTO table VALUES(1, 2);
INSERT INTO table VALUES(3, 4);
INSERT INTO table VALUES(5, 6);
...

You could run this query:

INSERT INTO table VALUES(1, 2), (3, 4), (5, 6), ...;

Upvotes: 1

Related Questions