CODEWITHSUNDEEP
phpmysqldatabase
Michael Doye
Michael Doye

Reputation: 8171

Create and store(mysql) a unique id for each iteration of a foreach loop with PHP

Overview:

I am iterating through a file directory to find all the SQL files and then use PHP to execute the MYSQL queries inside the files and store all the data in the same table.

I am doing this because each SQL file contains client data which gets inserted into the same database table. I have created a client_id column in the same table.

For each SQL file that is found I need to generate and insert a unique client ID into the same table to which the SQL files are executed so that I may use that ID to associate data from other tables in the database.

My problem:

I cannot edit the SQL files in the file directory in order to add unique id's to the insert statements therein.

I cannot run an UPDATE statement within the foreach because it updates the entire table and overwrites the client_id for the previous files.

I cannot add a WHERE clause if I were to run an UPDATE because I have nothing unique for each file to match it against, except for the file name.

My code:

Below is the iteration through the file directory which executes the statements within each file:

$dir = new DirectoryIterator('path_to_sql_files');

foreach($dir as $file){
if(!$file->isDot() && $file->isFile() && strpos($file->getFilename(), '.sql') !== false){

  $content = file_get_contents($file->getPathname()); //Reads entire file into a string
     if($content)
     {    
  /* because the .sql export file contains multiple queries per file, we 
    have to iterate through each statement within the file and store them as variables then remove the ";" delimiter 
    to roll all the statements into one and eventually execute it.
    this is because the PHP mysql_query() function does not support multiple queries. 
  */

    $queries = file($file->getPathname()); //Reads entire file into an array

    while(list($i, $query) = each($queries)){ //store each mysql statement as a variable 

       $query = trim($query, ';'); //Strip the delimiter from the end of the string

          if(!empty($query)){ // if there is actually something to execute...

              mysql_query($query); // ...execute it

             }
          } 
      }
  }
}

In conclusion:

If anyone has a way for me to generate a unique id per file and somehow get it into the same database table at the same time, that would be really great. Or perhaps a way for the database to handle it?

Upvotes: 0

Views: 1242

Answers (1)

nl-x
nl-x

Reputation: 11832

Alter the table and add a primary key that is auto-increment (let's name this one id), next to the other column you already added (the one you named client_id).

now, if you assume the filename is your unique id, you can simply do:

$filename = mysql_real_escape_string($file->getPathname());
while(list($i, $query) = each($queries)) {
    $query = trim($query, ';');
    if(!empty($query)){
        if (mysql_query($query)) {
            mysql_query("update MyTable set client_id = '$filename' where id = last_insert_id()");
        }
    }
}

What happens here is that last_insert_id() always returns the id of the last insert done, if the table that has been inserted into contains a primary key that is auto_increment. So you can use that ID after each query to mark that row.

Upvotes: 1

Related Questions