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.

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.

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!

This article has been dead for over six months. Start a new discussion instead.