Import data from text file?

[adammc]

Hi folks,

I'm not sure how to do this, can anyone sterr 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.

[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?

[jatar_k]

can you describe the data for me

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

I am guessing here

before the hyphen is the make
after the hyphen is the model

does the model need to be split up?

[adammc]

Hi Jatar-k, thanks for the reply.

The Vehicle_Makes table looks like this:
ID ¦ make

The Vehicle_Models table looks like this:
ID ¦ makeID ¦ model

I was aiming to insert the data like this:

Vehicle_Makes table:
1 ¦ Austin Healey
2 ¦ Austin Passenger

Vehicle_Models table:
1 ¦ 1 ¦ 3000, Sprite
2 ¦ 2 ¦ 1800, Allegro, Ital, Maxi, Mini

[jatar_k]

I tested this and left all my testing code in, you would have to add in the mysql_insert_id [php.net] and the actual mysql_query call but it does work

<? 
$nextline = ''; 
$fp = fopen('Vehicle-Lists.txt','r'); 
while (!feof($fp)) { 
 $mystuff = array(); 
 $nextline = fgets($fp); 
 $mystuff = split('-',$nextline); 
 $make = mysql_real_escape_string(trim($mystuff[0])); 
 $model = mysql_real_escape_string(trim($mystuff[1])); 
 $query = "INSERT into Vehicle_Makes VALUES ('', '$make')"; 
 echo '<p><b>makes insert:</b><br>',$query; 
 // add function to get last insert id here and assign it to $makeID 
 $makeID = 363; // static for this example 
 $query = "INSERT into Vehicle_Models VALUES ('', '$makeID', '$model')"; 
 echo '<br><b>models insert:</b><br>',$query; 
} 
echo '<p>done!'; 
?>

my text file was the exact three lines you quoted above

[adammc]

Thank you sooo much Jatar_k it worked a treat :)
I cant thank you enough.

I changed the DB structure around a little now I got it to work..

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

[PHP]
<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>
[/PHP]

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?

[jatar_k]

it is a bit of a misunderstanding about how mysql_fetch_array works. You need to put it in a loop, the array it is grabbing is only a single row

let's condense what you are doing there and do our reading from mysql and our outputting at the same time

you also will need to step out how you use the GET var in your query, Get data can be edited by the user and therefore is not considered safe. Let's just make sure it is alphanumeric before we use it. I don't believe car makes have anything but alphanumeric chars in them, you may need to add more but this is something at least. It really shouldn't have a die in live code but you will have to figure out how to handle the case where the GET value is no good or there is nothing in the db for that make.

$mymake = $_GET[vehicle_type];
if (!ctype_alnum($mymake)) {
echo 'that value is not allowed.';
die();
}
$q = "SELECT vehicle_id,model FROM Vehicles WHERE make='$mymake'";
$query = mysql_query($q);
echo '<select name="mycardropdown">';
while ($row = mysql_fetch_array($query)) {
echo '<option value="',$row['vehicle_id'],'">',$model['model'],'</option>';
}
echo '</select>';

I also stepped out your mysql_query into 2 parts, query construction and the actual function call. This is good practice and helps when debugging.

I am not sure what you plan on doing with this data, I assume it is submitted to somewhere. I added the id to your query and set that as the value of each option in the dropdown. This will make it very easy to select a specific car from the db at the next step. I wasn't sure of the colname for the id so I used vehicle_id.