PHP + Mysql queries for a real Beginner

Question

After years of false starts, I'm finally diving head first into learning to code PHP. After about 10 failed previous attempts to learn, it's getting exciting and finally going fairly well.

The project I'm using to learn with is for work. I'm trying to import 100+ fixed width text files into a MySql database.

So far so good
I'm getting comfortable with sql, and I'm learning some php tricks, but I'm not sure how to tie all the pieces together. The basic structure for what I want to do goes something like the following:

1. Name the text file I want to import
2. Do a LOAD DATA INFILE to import the data into one field it to a temporary db
3. Use substring() to separate the fixed width file into real columns
4. Remove lines I don't want (file identifiers, subtotals, etc....)
5. Add the files in the temp db, to the main db
6. Drop the temp db and start again

As you can see in the attached code, thigns are working fine. It gets the new file, imports it to the temp table, removes unwanted lines and then moves the content to final main database. Perfect.

Questions three
My two questions are:

1. Am I doing this 'properly'? When I want to run a pile of queries one after anohter, do I keep assinging mysql_query to random variables?

2. How would I go about automating the script to loop through every file there and import them? Rather than have to change the file name and run the script every time.

3. And, last, what PHP function would I use to 'select' the file(s) I want to import? You know, like attaching a file to an email -> Browse for file, upload it, and then run the script on it?

Sorry for this being an ultra-beginner question, but I'm having trouble seeing how all the pieces fit together. Specifcally I'm wondering how multiple sql queries get strung together to form a script? The way I've done it below? Some other way?

Thanks x 100 for any insights!

Terry

<?php 
 // 1. Create db connection  
 $connection = mysql_connect("localhost","root","root") or die("DB connection failed:" . mysql_error());

 // 2. Select the database  
 $db_select = mysql_select_db("pd",$connection) or die("Couldn't select the database:" . mysql_error());
?> 



 <?php 
  // 3. Perform db query  
  // Drop table import if it already exists
  $q="DROP table IF EXISTS import";


  //4. Make new import table with just one field
  if ($newtable = mysql_query("CREATE TABLE import (main VARCHAR(700));", $connection)) {
   echo "Table import made successfully" . "<br>";
  } else{
   echo "Table import was not made" . "<br>";
   }

  //5. LOAD DATA INFILE 
  $load_data = mysql_query("LOAD DATA INFILE '/users/terrysutton/Desktop/importmeMay2010.txt' INTO table import;", $connection) or die("Load data failed" . mysql_error());


   //6.  Cleanup unwanted lines
  if ($cleanup = mysql_query("DELETE FROM import WHERE main LIKE '%GRAND%' OR main LIKE '%Subt%' OR main LIKE '%Subt%'  OR main LIKE '%USER%'  OR main LIKE '%DATE%'  OR main LIKE '%FOR:%' OR main LIKE '%LOCATION%' OR main LIKE '%---%' OR `main` = '' OR `main` = '';")){
   echo "Table import successfully cleaned up";
  } else{
   echo "Table import was not successfully cleaned up" . "<br>";
  }



  // 7. Next, make a table called "temp" to store the data before it gets imported to denominators
  $temptable = mysql_query("CREATE TABLE temp
  SELECT
  SUBSTR(main,1,10) AS 'Unit', 
  SUBSTR(main,12,18) AS 'Description', 
  SUBSTR(main,31,5) AS 'BD Days', 
  SUBSTR(main,39,4) AS 'ADM',
  SUBSTR(main,45,4) AS 'DIS', 
  SUBSTR(main,51,4) AS 'EXP', 
  SUBSTR(main,56,5) AS 'PD', 
  SUBSTR(main,100,5) AS 'YTDADM', 
  SUBSTR(main,106,5) AS 'YTDDIS', 
  SUBSTR(main,113,4) AS 'YTDEXP', 
  SUBSTR(main,118,5) AS 'YTDPD'
  FROM import;");


  // 8. Add a column for the date 
  $datecolumn = mysql_query("ALTER TABLE temp ADD Date VARCHAR(20) AFTER Unit;");
  $date = mysql_query("UPDATE temp SET Date='APR 2010';");

  // 8. Move data from the temp table to its final home in the main database
  // Append data in temp table to denominator table
  $append = mysql_query("INSERT INTO denominators SELECT * FROM temp;");

  // 9. Drop import and temp tables to start from scratch.  
  $droptables = mysql_query("DROP TABLE import, temp;");

  // 10. Next, rename the text file to be imported and do the whole thing over again.

 ?>


<?php 
// 5. Close connection
mysql_close($connection);
?>

Answer 1

Why don't you load the files and process them in PHP, and use it to insert values in the actual table?

Ie:

$data = file_get_contents('somefile');

// process data here, say you dump it into a 2d array like
// $insert[$rows][$cols]

// then you can insert these into the db, ie:
$query = '';
foreach ($insert as $row) { 
   $query .= "INSERT INTO table VALUES ({$row[1]}, {$row[2]}, {$row[3]});";
}
mysql_query($query);

Answer 2

The purpose behind setting mysql_query to a variable is so that you can get the data you were querying for. In the case of any other query than SELECT, it only returns true or false.

So in the case where you are using if ($var = mysql...) you do not need the variable assingment there at all as the function returns true or false.

Also, I feel like doing all your substring and data file processing would be MUCH better suited in PHP. you can look into the fopen function and the related functions on the left side of that page.

Answer 3

If you have access to the command like, you can do all your data loading right from the mysql command line. Further, you can automate the process by writing a shell script. Just because you can do something in PHP doesn't mean you should.

For instance, you can just install PHPMyAdmin, create your tables on the fly, then use mysqldump to dump your database definitions to a file. like so

mysqldump -u myusername -pmypassword mydatabase > mydatabase.backup.sql

later, you can then just reload the whole database

mysql -u myusername -pmypassword < mydatabase.backup.sql

It's cool that you are learning to do things in PHP, but focus on doing the stuff you will do in PHP regularly rather than doing RDBMS stuff in PHP which is not where you should do it most of the time anyway. Build forms, and process the data. Learn how to build objects, and why you might want to do that. Head over and check out Symphony and Doctrine. Learn about the Front Controller pattern.

Also, look into PDO. It is very "bad form" to use the direct mysql_query() functions anymore.

Finally, PHP is great for templating and including disparate parts to form a cohesive whole. Practice making a left and top navigation html file. Figure out how you can include that one file on all your pages so that your same navigation shows up everywhere.

Then figure out how to look at variables like the page name and highlight the navigation tab you are on. Those are the things PHP is well suited for.