File handling

[DangerM0use]

Hi again,

I am in the middle of an assignment at uni which has lots of tasks which will eventually create 4 different dynamic pages. On one of the pages, I need to create a script to open a file containing MySql in it. The MySql has commands to delete, recreate and repopulate a table. I currently have;

 

$handle=fopen("filename" , "r");
$lines=file("filename");

 

but I'm not entirely sure where to go from here. Can someone point me in the right direction as to where I need to look?

Cheers.

[rpadilla]

$handle = fopen("script.sql", "rb");
while (!feof($handle)) 
{
  $text = fgets($handle, 4096);
  if (!empty($text)  )
   {
mysql_query($text);
   }
}
fclose($handle);

[obsidian]

We'll need to see the format of the SQL file to help you thoroughly through this. There are a number of ways to store SQL. If each of your SQL statements is on an individual line, you could probably just use the file() function and get by with it ok, but there are numerous formatting preferences in SQL that may not allow for that simple of an approach -- especially if it's a SQL export from an application such as phpMyAdmin.

 

If you can share a sample file with us, we can help you parse it out and run it.

[DangerM0use]

This is a bit of code from it,

 

drop table if exists wes_ica_6;
drop table if exists wes_ica_7;
drop table if exists wes_ica_8;
create table wes_ica_6 (id int(11), country varchar(255), prizes int(11), fff varchar(255), uuu float);
create table wes_ica_7 (id int(11), fruit varchar(255), prizes int(11), ggg varchar(255), ttt float);
create table wes_ica_8 (id int(11), dinosaur varchar(255), prizes int(11), hhh varchar(255), sss float);

There is more drop tables and create tables and there is also code for inserting data into each table

[rpadilla]

the code I posted should work with the 1 sql per line;

[obsidian]

It looks like you are storing one query per line. Keep in mind that when running your query through mysql_query(), it's not recommended to have your semicolon on the end of your query, so there's a little cleanup I would recommend to each query before it is run:

$file = "myQueries.txt";
$lines = file($file);
foreach ($lines as $q) {
  $q = trim($q);
  $q = rtim($q, ';');
  mysql_query($q);
}

[obsidian]

the code I posted should work with the 1 sql per line;

 

The code that rpadilla posted earlier is a safer way to loop through your file since it will handle large file sizes without running into your variable memory limit, too. If you find your SQL files getting up into the several MB size, you'll want to loop through line by line as he shows for sure.