Member Avatar for fred999

Hello php gurus!

i need help with a little script, i have to import a tab delimited files into a mysql db

file.tab

"user1"TAB"pass1"TAB"miscdata1_1"TAB"miscdata1_2"TAB"miscdata1_3"TAB"miscdata1_4"TAB
"user2"TAB"pass2"TAB"miscdata2_1"TAB"miscdata2_2"TAB"miscdata2_3"TAB"miscdata2_4"TAB

Here is what i have programmed with the help of google

<?
include('config.php');
$conn = mysql_connect($dbserver, $dbuser, $dbpass);
if (!$conn) {
		 echo "Impossible de se connecter la base de donns : " . mysql_error();
		 exit;
}
if (!mysql_select_db($dbname)) {
		 echo "Impossible d'accer la base de donns : " . mysql_error();
		 exit;
}
 
$fcontents = file('./file.tab'); 
for($i=0; $i<sizeof($fcontents); $i++) { 
	 $line = trim($fcontents[$i]); 
	 $arr = explode("\t", $line); 
	 #if your data is comma separated
	 # instead of tab separated, 
	 # change the '\t' above to ';' 
	 $sql = "insert into accounts values ('". implode("','", $arr) ."')"; 
	 mysql_query($sql);
	 echo $sql ."<br>\n";
	 if(mysql_error()) {
		 echo mysql_error() ."<br>\n";
	 } 
}
?>

This code will import the data in my database, something is annoying, all the db fields are filled with values with ""

This isnt a big problem, i can deal with it.

The second part of my script needs to be able to export the mysql database into a tab delimited file (same kind of file than file.tab) So i can add new entry in the database, export the content of the db to this file.

This is where i am jammed, i dont know how to do that, i dont know how to export the content of a db into a file to get the same result as the original file.tab

Any of the php masters here can help me? thanks in advance

fred

Sorry for the terrible english.

  • 4 Contributors
  • 4 Replies
  • 1K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by Troy
Member Avatar for fred999

anyone?

Member Avatar for Gnome_101

Well, when you get the query, and the results, could you not then run through each row with a fetch row and then run it into the tab file. I not an expert, but try getting the data first, without the explode() function, then on each row run the explode, and dump it into a file.

Member Avatar for Troy

First, you said you had the import working, but you ended up with double quotes in your values. You can strip those using code like:

$val = str_replace('"', '', $val);

Here is example code that shows you how to connect to a mysql server, select a specific database, execute a SQL statement, then work with the data.

<?php

$server = "localhost"; // Name or IP of database server.
$user   = ""; // username
$pwd    = ""; // password
$db     = ""; // Name of database to connect to.

if (!$cnn = mysql_connect($server,$user,$pwd )) {
  die("mysql_connect() failed");
}

if (!mysql_select_db($db,$cnn)) {
  die("Could not select database named ".$db);
}

/* Build your SQL statement however you need. */
$sql = "select * from mytable";

/* Execute the query. */
if (!$res = @mysql_query($sql)) {
  die(mysql_error());
  return false;
}
/* Create an array of arrays out of the recordset. */
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
  $data[] = $row;
}

/* Now iterate through the recordset creating a simple table. */
echo "<style>table.dump { font-family:Arial; font-size:8pt; }</style>";
echo "<table class=\"dump\" border=\"1\" cellpadding=\"1\" cellspacing=\"0\">\n";
echo "<tr>";
echo "<th>#</th>";
foreach($data[0] as $key=>$val) {
  echo "<th><b>";
  echo $key;
  echo "</b></th>";
}
echo "</tr>\n";
$row_cnt = 0;
foreach($data as $row) {
  $row_cnt++;
  echo "<tr align='center'>";
  echo "<td>".$row_cnt."</td>";
  foreach($row as $val) {
    echo "<td>";
    echo $val;
    echo "</td>";
  }
  echo"</tr>\n";
}
echo "</table>\n";    

?>

Next, you need to know how to save the recordset back into a TAB delimited file. This example uses the $data value from the code example above.

<?php
$fp = fopen("path_to_file_here", "w");

foreach($data as $row) {

  $line = "";

  foreach($row as $val) {
    $line .= "\t\"".$val."\"";
  }

  /* Strip off the first TAB and add a carriage return. */
  $line = substr($line, 1)."\n";

  $fwrite($line);
}

fclose($fp);

?>

I hope that helps! Enjoy the journey!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.