Is it possible to import a text file with phpmyadmin?
In the file is this:
country - county / city
USA - Florida / Orlando
USA - Florida / Miami
USA - Washington / Washington DC
Switzerland - Solothurn / Grenchen
The file has over 3000 lines
Now i want to import to my catergory table the table is like this:
id | parent_id | name
The country and county is in the file several times. In my database i need it only once. City is in the file unique
Can i make this with phpmyadmin or is it only possible with php? Can anybody tell me how to do this. Thanks
Here is my own solution
<?php
require 'funcs/connect.php';
$file = 'cities.txt';
$file_handle = fopen($file, 'r');
while (!feof($file_handle)) {
$line = fgets($file_handle);
$text_line = explode("-",$line);
echo $text_line[0];
echo $text_line[1];
echo $text_line[2];
if ($text_line[1] != "")
{
$name = $text_line[1];
$stmt = $handler->prepare("SELECT name FROM news_cat WHERE name = '$name'");
$stmt->execute();
$count = $stmt->rowCount();
if ($count >= 1)
{
echo"Kanton schon vorhanden";
}
else
{
$stmt = $handler->prepare("INSERT INTO news_cat(name,parent_id)VALUES('$name','0')");
$stmt->execute();
}
}
if ($text_line[2] != "")
{
$name = $text_line[2];
$canton = $text_line[1];
$stmt = $handler->prepare("SELECT name FROM news_cat WHERE name = '$name'");
$stmt->execute();
$count = $stmt->rowCount();
if ($count >= 1)
{
echo"Bezirk schon vorhanden";
}
else
{
$stmt = $handler->prepare("SELECT id FROM news_cat WHERE name = '$canton'");
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_OBJ)){
$parent_id = $row->id;
}
$stmt = $handler->prepare("INSERT INTO news_cat(name,parent_id)VALUES('$name','$parent_id')");
$stmt->execute();
}
}
if ($text_line[0] != "")
{
$name = $text_line[0];
$bezirk = $text_line[2];
$stmt = $handler->prepare("SELECT id FROM news_cat WHERE name = '$bezirk'");
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_OBJ)){
$parent_id = $row->id;
}
$stmt = $handler->prepare("INSERT INTO news_cat(name,parent_id)VALUES('$name','$parent_id')");
$stmt->execute();
}
echo "<br>";
}
fclose($file_handle);
?>
phpMyAdmin can help with this, but you'll have to massage the data with another program so that you use the same delimiter (either - or / should be fine). You can do this with "search and replace" inany text editor (or your favorite command-line tool; sed, awk, perl, etc). Just make sure your data doesn't contain the character you're replacing (for instance, in "Saxony-Anhalt").
Then you can do a standard import from the phpMyAdmin "Import" tab. It looks like you'll want to use the following parameters:
/ or - (based on what you select above)auto