Import data from text file?

[adammc]

Hi folks,

I'm not sure how to do this, can anyone steer me in the right direction?

I have a DB table called 'vehicles'

It has three columns:
ID | vehicle_type | vehicle_make

I have a text file containing a list of the vehicle types and makes. Example:

Austin Healey - 3000, Sprite
Austin Passenger - 1800, Allegro, Ital, Maxi, Mini
Bentley - Corniche, Mulsanne, T Series

How would I import this into my database?
Any help would be greatly appreciated.

[Installer]

See LOAD DATA INFILE Syntax.

[adammc]

After creating 2 tables (1 for vehicle types & 1 for vehicle makes) I have got this far:

// connect to and select db here
      require_once ('mysql_connect.php');

  // define text file
  $fileArray = file('Vehicle-Lists.txt');

  // loop through lines, parse and insert info to db
  foreach ($fileArray as $line) {

      preg_match("#^(.*?)\s-\s(.*)#", $line, $matches);
      $make = mysql_real_escape_string($matches[1]);
      $models = explode(", ", $matches[1]);

      $query = "INSERT into Vehicle_Makes VALUES ('', '$make')";
      mysql_query($query) or die (mysql_error());

      $makeID = mysql_insert_id();

      foreach ($models as $model) {

          $model = mysql_real_escape_string($model);
          $query = "INSERT into Vehicle_Models VALUES ('', '$makeID', '$model')";
          mysql_query($query) or die (mysql_error());
      }
  } 

However, It inserted the vehicle make into:
Vehicle_Makes - make
Vehicle_Models - model

Text file is currently in exactly this format:
Austin Healey - 3000, Sprite
Austin Passenger - 1800, Allegro, Ital, Maxi, Mini
Bentley - Corniche, Mulsanne, T Series

In other words, the models havent been inserted.
Can anyone help?

[Roger_Ramjet]

With the data in that format then you cannot just read it in with load data infile. You are going to have to restructure the data so that each model has a line on its own
eg:

Bentley, Corniche
Bentley, Mulsanne
Bentley, T Series

If you have something to process it with then do so, else you are going to have to read the file and process it in php. You could either insert each row one at a time, or you could output it to a new csv file in the correct format and then use load data infile on that. Up to you.

[Roger_Ramjet]

Sorry, I did not read your last post fully.

Common error, you have an autoinc column for the make and model ids but are trying to insert '' into it. That does not work - and never will. You DO NOT need a placeholder in your query for an autoinc column, just omit it from the insert and let the db do its thing.

Also, you do not need quotes around numeric values, it works because mysql does type conversion according to context so the string you are trying to insert will be converted to an integer, but why make the db engine go through all that when it is not neccessary.

[adammc]

Hi Roger, thank you so much for your reply 🙂

The Vehicle_Models tabgle looks like this:

ID | makeID | model

The result of the 2nd insert query:

$model = mysql_real_escape_string($model);
              $query = "INSERT into Vehicle_Models VALUES ('', '$makeID', '$model')";
              mysql_query($query) or die (mysql_error()); 

Is inputing the correct ID, makeID however its inserting the makes instead of the models?

[Installer]

If "ID" is auto-increment, one for each model, and you want the data in one table:

$arr = file('Vehicle-Lists.txt');
foreach ($arr as $line) {
    list($make, $models) = explode('-', $line);
    $models_arr = explode(',', $models);
    foreach ($models_arr as $model) {
        $query = "INSERT INTO Vehicle_Models (makeID, model) " .
                 "VALUES ('" . trim($make) . "', '" . trim($model) . "')";
        $result = my_mysql_query($query);
    }
}

[adammc]

Hi Installer,
Thank you sooo much!! It worked a treat 😃 😃

One last question, if Im not asking too much?

I am trying to autofill a dropdown list using that data based on the vehicle make which is found in the url using GET$

I have got it to kinda work, only problem is it is only listing the first 'model' it finds in the DB?

DB Structure:
1 | BMW | modelname1
2 | BMW | modelname2
3 | BMW | modelname3

<SELECT name="vehicle_make" class="textbox"> 
<option value="">select a type</option>

<?php 

require_once ('../mysql_connect.php');

$query = mysql_query("SELECT model FROM Vehicles WHERE make='$_GET[vehicle_type]'");   // autofill dropdown list 'select' with options from DB
$r = mysql_fetch_array($query); 

$model=explode(',', $r['model']); 
for($i=0;$model[$i];$i++) 
{ 
echo "<option value=$model[$i]>$model[$i]</option>";  
}   

?> 
 </select>

If $GET[$vehicle type] = BMW (found in the url)
the output in the dropdown list is only 'modelname1' instead of:

modelname1
modelname2
modelname3

Can anyone possibly help?

[Installer]

Although your models are each in a separate row, you're treating them as if they were still comma-delimited. Try something more like this:

$query = mysql_query("SELECT model FROM Vehicles WHERE make='$_GET[vehicle_type]'");
while ($r = mysql_fetch_assoc($query) { 
    echo '<option value="' . $r['model'] . '">' . $r['model'] . '</option>';   

}