在以PHP运行的MySQL文件中使用PHP会话变量? [英] Using a PHP session variable within a MySQL file run in PHP?

问题描述

这听起来可能有点令人困惑，也许我正在做的事情根本不对，但是可以.

It might sound a bit confusing, and perhaps what I'm doing isn't right at all, but here goes.

我们的用户可以上传包含学校数据的.csv文件，这些文件是从称为SIMS的外部管理系统导出的.一旦他们上传了该文件，我想运行一个.sql脚本，用他们在此文件中提供的信息更新我们的数据库.我在上传文件页面上完成的工作(就像超级按钮一样)是创建一个会话变量，其中包含文件路径和上传文件的名称，如下所示(文件路径为空白):

Our users can upload .csv files containing their school's data, exported from an external management system called SIMS. Once they upload that file I want to run a .sql script that updates our database with the information they provide in this file. What I've done, on the upload file page (works like a charm), is create a session variable containing the file path and name of the uploaded file, as shown below (file path is blanked out):

$target_path = "xxx"; 

$target_path = $target_path . basename( $_FILES['uploadedfile']['name']); 

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path))
   {
     echo "<p>The file ".  basename( $_FILES['uploadedfile']['name']). " has been uploaded.</p>" ;
     echo "<p><a href='sqltest.php'>Click here</a> to upload your files into the database.</p>" ;

     $_SESSION['file'] = $target_path ;
   }

下面是我的.sql文件的一部分，该文件在PHP中运行，包含会话变量.由于大多数填充脚本在整个过程中都使用相同类型的命令，因此可能无法向您显示所有973行代码(当然，页面顶部有一个session_start()处于活动状态).

Below is a portion of my .sql file operated within PHP containing the session variable. Because most of this populate script uses the same kind of commands throughout it will probably not make sense to show you all 973 lines of code (of course there is a session_start() active at the top of the page).

$filename = $_SESSION['file'] ;

mysqli_query($dbc, 'SET foreign_key_checks = 0') ;

$populate =

"CREATE TEMPORARY TABLE IF NOT EXISTS `mldb`.`TempSchool`
    (
        `CentreNo` INT UNSIGNED NOT NULL,
        `School` VARCHAR(255) NULL,
        `Street` VARCHAR(255) NULL,
        `Town` VARCHAR(255) NULL,
        `County` VARCHAR(255) NULL,
        `Postcode` VARCHAR(10) NULL,
        `Tel` VARCHAR(45) NULL,
        `URL` VARCHAR(512) NULL,
        `Email` VARCHAR(255) NULL,
        `Headteacher` VARCHAR(255) NULL,
        `LEA` VARCHAR(45) NULL,
        `LEANo` INT UNSIGNED NULL,
        `EstablishmentNo` INT UNSIGNED NULL,
        `URN` INT UNSIGNED NULL,
        `Governance` VARCHAR(45) NULL,
        `Phase` VARCHAR(45) NULL,
        PRIMARY KEY (`CentreNo`)
    )
    ENGINE = InnoDB ;

LOAD DATA INFILE '$filename'        
IGNORE INTO TABLE `TempSchool`                  
FIELDS TERMINATED BY ' , ' 
OPTIONALLY ENCLOSED BY ' \" '
LINES TERMINATED BY ' \r\n '                                
IGNORE 1 LINES  (etc...)                                            

在显示以下内容的代码(LOAD DATA INFILE'$ filename')的第25行出现错误:

I'm getting an error on line 25 of the code (LOAD DATA INFILE '$filename') displaying the following:

Invalid query:You have an error in your SQL syntax; check the manual that corresponds to your MySQL   
server version for the right syntax to use near 'LOAD DATA INFILE   
'C:/Users/Public/Dropbox/mlwebfiles/Trial/uploads/MarksLive Set' at line 25

大概与以下事实有关:即使它可以识别存储在$ _SESSION ['file']变量中的文件路径和文件名，但实际上并没有执行任何操作.有没有一种方法可以通过PHP为文件名设置变量来操作此.sql文件?非常感谢！

Presumably it's got something to do with the fact that, even though it's recognizing the file path and file name stored in the $_SESSION['file'] variable, but it's not actually doing anything with it. Is there a way where this .sql file can operate with through PHP set up variables for the file names? Many thanks!

推荐答案

在漫长而艰苦的搜索中，我们找到了一个(我们认为是唯一的)解决方案:创建一个循环遍历每个函数的函数这些迷你查询"中的每一个，现在看来似乎要快乐得多！我们实际上曾经遇到过这个建议，但是我们最初拒绝了它，因为它(并且确实)要花费很多时间来分离973行代码来拆分所有单个数据库添加项...

After searching long and hard, and far and wide we've found a (what we think is the only) solution: create a function to loop through every one of these 'mini-queries' individually and it seems to be a lot more happy now! We actually came across this suggestion before but we rejected it initially because it would (and did) take a lot of time to separate 973 lines of code to split all the individual database additions...

$populate = 
"
    CREATE TEMPORARY TABLE IF NOT EXISTS `mldb`.`TempSchool`
    (
        `CentreNo` INT UNSIGNED NOT NULL,
        `School` VARCHAR(255) NULL,
        `Street` VARCHAR(255) NULL,
        `Town` VARCHAR(255) NULL,
        `County` VARCHAR(255) NULL,
        `Postcode` VARCHAR(10) NULL,
        `Tel` VARCHAR(45) NULL,
        `URL` VARCHAR(512) NULL,
        `Email` VARCHAR(255) NULL,
        `Headteacher` VARCHAR(255) NULL,
        `LEA` VARCHAR(45) NULL,
        `LEANo` INT UNSIGNED NULL,
        `EstablishmentNo` INT UNSIGNED NULL,
        `URN` INT UNSIGNED NULL,
        `Governance` VARCHAR(45) NULL,
        `Phase` VARCHAR(45) NULL,
        PRIMARY KEY (`CentreNo`)
    )
    ENGINE = InnoDB ;

" ;
populate ($dbc, $populate);


$populate = 
"   
    LOAD DATA INFILE '$path'
    IGNORE INTO TABLE `mldb`.`TempSchool`
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\\r\\n'
    IGNORE 1 LINES
        (@AdNo, @UPN, @ULN, @UCI, @CandidateNo, @LegalSurname, @LegalForename, 
        @PreferredSurname, @PreferredForename, @Gender, @DOB, @Email,
        @InCare, @EverInCare, @FSM, @FSMEver6, @EAL, @SENStatus, @AMA,
        @Attendance, @RegGroup, @YearGroup, @EnteredYearDate, 
        @Class, @Subject, @Staff, @Initials, 
        CentreNo, School, Street, Town, County, Postcode, Tel, URL,
        Email, Headteacher, LEA, LEANo, EstablishmentNo, Governance, Phase)
" ;
populate ($dbc, $populate);

这篇关于在以PHP运行的MySQL文件中使用PHP会话变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！