Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2007
    Posts
    10

    Question Unanswered: run a php script to run a file of mysql commands (was "Help!")

    Hi all,
    I have an assignment to do and am struggling a bit. I have a text file that contains MySql commands in it such as create table commands. I need to run a PHP script that takes these commands to delete, recreate and repopulate the tables in the database.
    So far I have opened and read the file (i think) and have;
    Code:
    //Open the file
    $handle=fopen("mydata.txt" , "r");
    
    //Read the file
    $lines=file("mydata.txt");
    but can't figure out where to go from here. I know I have done it before, but looking back through my previous work I was stupid and didn't put comments, or didn't explain clearly enough what was going on.
    Hope someone can help or point me in the right direction.
    Cheers.

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The file(...) function opens and reads the lines of the file into an array. There is no need to use the fopen(...) function in this case. fopen(..) is used with a function like fread(..) to open the file before you read it.

  3. #3
    Join Date
    Apr 2007
    Posts
    10
    So if I was to change those two lines of code to file('filename'); that would open and read that file? Would this execute the MySql as well or is there something else I need to put? I'm guessing there is

  4. #4
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The only way to effectively learn a programming language is to make use of the programming language reference manual. Here is the section that explains the file(...) function - http://php.net/file

    This function reads the lines of the file into an array. This function has nothing to do with mysql. To do anything with those lines, you must write code to do it.

  5. #5
    Join Date
    Apr 2007
    Posts
    10
    Cheers for the help so far,
    can you confirm that the following code is correct - the code matches what the comments say?
    Code:
    //open file
    $file = ("filename");
    
    //check file is readable
    if (is_readable($file) == false) {
            die('File doesn\'t exist or cannot be read');
    } 
    else {
            echo 'It exists';
    }
    
    // Read file
    $data = file_get_contents($file);

  6. #6
    Join Date
    Apr 2007
    Posts
    10
    I have now got the file open and reading. I know that is working as I made it print the contents of the text file (the commands) onto the webpage. But I have spent all day trying to figure out how to get those commands to run. Anyone know? I've looked in books, on the internet and my lecture notes and cannot find the answer anywhere

  7. #7
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    If you have some code that you have written that you would like some help with, you would need to post it.

  8. #8
    Join Date
    Apr 2007
    Posts
    10
    thats the problem, i have no code. I don't know what the code is to get the MySQL commands in the text file to execute. I don't even know what I am looking for to be honest

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    then Id suggest you have a look at the same php.net site and have a look for the MYSQL functions or MySQLi methods, or better yet get yourself a decent book on PHP programming.. usually even the most basic books cover accessing data from MySQL pretty thoroughly

    in essence you need to.....
    open a connection to the required MySQL server
    you then need to select the appropriate database
    you then need to apply the SQL statements using a query
    you need to examine the the appropriate return code from the appropriate MySQL Function / method call to make sure the SQL query has executed properly.

    so have a look for mysql_connect, mysql_select_db, mysql_query.. and just about any function headed with mysql........
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ideally you should want to execute all of that SQL code at one time because you're going to have difficulty parsing the individual statements inside the txt file you have been given without creating your own parser.

    However it should be noted that in order for you to execute this whole file as a script you need access to the MySQL command line tool. You could try the exec command in PHP.

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It might also be helpful to know what is in the txt file you are trying to execute...

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or it might actually be better to use something like MySQL Query Borwser assuming that what we really have is a MySQL create script (dropping tbaels, creating tables, bulk loading of data)

    have a look at http://dev.mysql.com/doc/#gui-tools or http://dev.mysql.com/downloads/gui-tools/5.0.html to download the GUI tools (this includes MySQL Adminsitrator and the import / migration wizard

    failing that it may be appropriate to use somehting like PHPMyAdmin

    From my perspective theres no reason not to run the DDL scripts from within PHP if thats the only way of dong a bulk process (say onto a remote server where you have few tools availble to you. The only concern is to make sure that you read through the file untill the end of a particular statement is reached.. and that would be the final closing bracket on a table create or a new line on most other DDL statements. It may not be the smartest, the slcikest route, but sometimes it may be the only or preferred route availble
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2007
    Posts
    10
    Thanks for the help so far. Basically I have to use the text file to delete, recreate and repopulate tables in a database. The text file contains these commands -
    drop table if exists table_1;
    create table table_1 (columns in here);
    and then finally the insert commands.
    I know how to connect to and select databases, just not sure how I get these commands in a .txt file to execute.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if your text file contains a series of DDL statements then parse that file to fidn the end of each statement and then submit it using mysql_query, examine the results to see if the num_rows_affected matches expectations. then process then next parsed statement. the option call is to dump each statement to an array and then sequentially porcess the sarray using mysql_query
    or do it as a script within MySQL query Browser.

    in query browser you can import a script, then execute it.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I think part of the project is supposed to involve only processing these commands through the use of PHP. Am I right? If that is the case you need to show us what you have in your txt file. Is each line a separate statement?
    e.g.
    Line 1 : USE database1;
    Line 2 : DROP TABLE IS EXISTS `table1`;
    Line 3 : CREATE TABLE newtable(ID INT, name VARCHAR, someothercolumn VARCHAR);
    Line 4 : INSERT INTO newtable(ID,name,someothercolumn) VALUES(1,'AERBAERV','AREGAERGA');
    ETC....

    If that is the case then it's easy enough to split up. If that ISN'T the case then it's either going to be parsing the text file to find begin and end of each statement (bloody hard!) or finding a nice easy way to execute the SQL script straight onto MySQL (the preferred way).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •