douzheren3349 2014-05-22 18:02
浏览 89
已采纳

使用PHP为foreach循环的每次迭代创建和存储(mysql)唯一的id

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?

  • 写回答

1条回答 默认 最新

  • duancenxiao0482 2014-05-22 18:24
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 用matlab求微分方程初值问题
  • ¥15 vscode下编写第三方库opencv与pcl代码时没有代码提示
  • ¥15 能够跑通不报错,如何解决?(标签-matlab)
  • ¥15 MOS在RDS较大,频率高时开关波形异常
  • ¥15 SCENIC分析报错求解答
  • ¥15 ceph初始化mon不成功 下图不报错 这个是什么元
  • ¥15 数学建模数学建模少ai
  • ¥35 这个等效电路图是什么意思?(答疑)(有没有不是chat的,我就是在chat搜不到才来问的,chat还开了VIP)(可以追加酬金)
  • ¥200 基于同花顺supermind的量化策略脚本编辑
  • ¥15 黄永刚的晶体塑性子程序中输入的材料参数里的晶体取向参数是什么形式的?