943,753 Members | Top Members by Rank

Web Development > PHP > insert csv file into mysql through php
Ad:
  • PHP Discussion Thread
  • Unsolved
  • Views: 19584
  • PHP RSS
Permalink
Jul 17th, 2006
0

insert csv file into mysql through php

Expand Post »
HI

I want to submit csv file data into mysql
pls tell me how 2 do
if any one has any program made to insert csv file into mysql pls give me the code i really need it
pls help me

bye
Similar Threads
Reputation Points: 21
Solved Threads: 1
Newbie Poster
manish812 is offline Offline
12 posts
since Jun 2006
Permalink
Jul 17th, 2006
0

Re: insert csv file into mysql through php

1) Split the csv file line-by-line using explode()
2) Then insert into your mysql DB normally.
Moderator
Reputation Points: 161
Solved Threads: 38
He's No Good To Me Dead
stymiee is offline Offline
1,422 posts
since May 2006
Permalink
Jul 17th, 2006
0

Re: insert csv file into mysql through php

Before you can 'split' the cvs data, you need the following:[php]
// after uploading using a html form
$tmpName = $_FILES['userfile']['tmp_name'];
$fp = fopen($tmpName, 'r');
$fr=fread($fp, filesize($tmpName));
$line = explode("\n", $fr);
// etc etc ...
[/php]
Reputation Points: 10
Solved Threads: 7
Posting Whiz in Training
zippee is offline Offline
294 posts
since Jan 2005
Permalink
Dec 15th, 2006
0

Re: insert csv file into mysql through php

Click to Expand / Collapse  Quote originally posted by zippee ...
Before you can 'split' the cvs data, you need the following:[php]
// after uploading using a html form
$tmpName = $_FILES['userfile']['tmp_name'];
$fp = fopen($tmpName, 'r');
$fr=fread($fp, filesize($tmpName));
$line = explode("\n", $fr);
// etc etc ...
[/php]
<?
if($_REQUEST['submit'])
{
$contents = file ('./email_entries.csv');
for($i=0; $i<sizeof($contents); $i++)
{
$line = trim($contents[$i],'",');
$arr = explode(',', $line);
// $sql = "insert into employees values ('".implode("','", $arr)."')";
$sql = mysql_query("insert into employees(first_name,last_name,email_id) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."')");
}
}
Reputation Points: 10
Solved Threads: 0
Light Poster
php_coder is offline Offline
34 posts
since Dec 2006
Permalink
Dec 16th, 2006
0

Re: insert csv file into mysql through php

Hi,

Just run this code to enter your csv file into mysql. Also enter the database details in a file named config.inc.php.
-----------------


php Syntax (Toggle Plain Text)
  1. <? include "../include/config.inc.php";
  2.  
  3.  
  4. function normalise($string) {
  5.     $string = str_replace("\r", "\n", $string);
  6.  
  7.     return $string;    
  8. }
  9.  
  10. if ($stage == "") {
  11.  
  12.     if ($msg == "file") {
  13.         $display_block = "Error opening CSV file.";
  14.     }
  15.  
  16.     $display_block .= "
  17.         <form action=\"csv_importer.php\" method=\"POST\">
  18.             <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"55%\" align=\"center\">
  19.                 <tr>
  20.                     <td colspan=\"2\" class=\"tdTitle\">PHP CSV Importer</td>
  21.                 </tr>
  22.                 <tr>
  23.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  24.                 </tr>
  25.                 <tr>
  26.                     <td>Select CSV file:</td>
  27.                     <td><input type=\"text\" name=\"csvFile\" size=\"35\" class=\"inputText\"> (relative to script)</td>
  28.                 </tr>
  29.                 <tr>
  30.                     <td>How are the columns separated?</td>
  31.                     <td><input type=\"text\" name=\"delimiter\" value=\",\" size=\"1\" class=\"inputText\"> If in doubt try a comma.  (,)</td>
  32.                 </tr>
  33.                 <tr>
  34.                     <td>How many lines do you want to preview?</td>
  35.                     <td><input type=\"text\" name=\"previewLimit\" value=\"5\" size=\"1\" class=\"inputText\"></td>
  36.                 </tr>
  37.                 <tr>
  38.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  39.                 </tr>
  40.                 <tr>
  41.                     <td colspan=\"2\" class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next &raquo;\" class=\"inputSubmit\"></td>
  42.                 </tr>
  43.             </table>
  44.             <input type=\"hidden\" name=\"stage\" value=\"preview\">
  45.         </form>";
  46.  
  47. } else if ($stage == "preview") {
  48.  
  49.     if (!$myFile = @fopen(stripslashes($csvFile), "r")) {
  50.         header("location: ?stage=&error=preview&msg=file");
  51.     } else {
  52.         $line = 0;
  53.         $maxCols = 0;
  54.  
  55.         while (($line < $previewLimit) && ($data = fgetcsv($myFile, 1024, $delimiter))) {
  56.             $numOfCols = count($data);
  57.             if ($numOfCols > $maxCols) $maxCols = $numOfCols;
  58.  
  59.             $csv_block .= "\n\t\t\t\t\t\t\t<tr>";
  60.  
  61.             for ($index = 0; $index < $numOfCols; $index++) {
  62.                 if (strlen(stripslashes(normalise($data[$index]))) > 10) {
  63.                     $dots = "...";
  64.                 } else {
  65.                     $dots = "";
  66.                 }
  67.                 if ($data[$index] == "") {
  68.                     $csv_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewContent\">"
  69.                                         . "\n\t\t\t\t\t\t\t\t\t"
  70.                                             . "&nbsp;"
  71.                                         . "\n\t\t\t\t\t\t\t\t</td>";
  72.                 } else {
  73.                     $csv_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewContent\">"
  74.                                         . "\n\t\t\t\t\t\t\t\t\t"
  75.                                             . substr(stripslashes(normalise($data[$index])), 0, 10) . $dots
  76.                                         . "\n\t\t\t\t\t\t\t\t</td>";
  77.                 }
  78.             }
  79.  
  80.             $csv_block .= "\n\t\t\t\t\t\t\t\t</tr>";
  81.  
  82.             $line++;
  83.         }
  84.  
  85.         $display_block .= "<form action=\"csv_importer.php\" method=\"POST\">
  86.             <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">
  87.                 <tr>
  88.                     <td colspan=\"2\" class=\"tdTitle\">PHP CSV Importer</td>
  89.                 </tr>
  90.                 <tr>
  91.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  92.                 </tr>
  93.                 <tr>
  94.                     <td class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next &raquo;\" class=\"inputSubmit\"></td>
  95.                 </tr>
  96.                 <tr>
  97.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  98.                 </tr>
  99.                 <tr>
  100.                     <td>
  101.                         <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">
  102.                             <tr>";
  103.  
  104.         for ($index = 0; $index < $maxCols; $index++) {
  105.             $display_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewColHeader\">Col " . ($index+1) . "</td>";
  106.         }
  107.  
  108.         $display_block .= "\n\t\t\t\t\t\t\t</tr>" . $csv_block . "</table>
  109.                     </td>
  110.                 </tr>
  111.                 <tr>
  112.                     <td class=\"tdSpacer\">&nbsp;</td>
  113.                 </tr>
  114.                 <tr>
  115.                     <td class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next &raquo;\" class=\"inputSubmit\"></td>
  116.                 </tr>
  117.             </table>
  118.                 <input type=\"hidden\" name=\"csvFile\" value=\"" . stripslashes($csvFile) . "\">
  119.                 <input type=\"hidden\" name=\"delimiter\" value=\"" . htmlspecialchars($delimiter) . "\">
  120.                 <input type=\"hidden\" name=\"maxCols\" value=\"$maxCols\">
  121.                 <input type=\"hidden\" name=\"stage\" value=\"setup_db_connection\">
  122.             </form>";
  123.     }
  124.  
  125.  
  126.     fclose($myFile);
  127.  
  128. } else if ($stage == "setup_db_connection") {
  129.  
  130.     function saveConnection($serverName, $username, $password) {
  131.         if (($serverName != "") || ($username != "") || ($password != "")) {
  132.             return "checked";
  133.         }
  134.     }
  135.  
  136.     if ($ck_csv[serverName] != "") {
  137.         $serverName = $ck_csv[serverName];
  138.     }
  139.  
  140.     if ($ck_csv[username] != "") {
  141.         $username = $ck_csv[username];
  142.     }
  143.  
  144.     if ($ck_csv[password] != "") {
  145.         $password = $ck_csv[password];
  146.     }
  147.  
  148.     $display_block = "
  149.         <form action=\"csv_importer.php\" method=\"POST\">
  150.             <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"55%\" align=\"center\">
  151.                 <tr>
  152.                     <td colspan=\"2\" class=\"tdTitle\">PHP CSV Importer</td>
  153.                 </tr>
  154.                 <tr>
  155.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  156.                 </tr>
  157.                 <tr>
  158.                     <td>Server name:</td>
  159.                     <td><input type=\"text\" name=\"serverName\" value=\"$serverName\" class=\"inputText\"></td>
  160.                 </tr>
  161.                 <tr>
  162.                     <td>Username:</td>
  163.                     <td><input type=\"username\" name=\"username\" value=\"$username\" class=\"inputText\"></td>
  164.                 </tr>
  165.                 <tr>
  166.                     <td>Password:</td>
  167.                     <td><input type=\"password\" name=\"password\" value=\"$password\" class=\"inputText\"></td>
  168.                 </tr>
  169.                 <tr>
  170.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  171.                 </tr>
  172.                 <tr>
  173.                     <td colspan=\"2\"><input type=\"checkbox\" name=\"sameConnection\"" . saveConnection($ck_csv[serverName], $ck_csv[username], $ck_csv[password]) . ">&nbsp;Always use this connection.</td>
  174.                 </tr>
  175.                 <tr>
  176.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  177.                 </tr>
  178.                 <tr>
  179.                     <td colspan=\"2\" class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next &raquo;\" class=\"inputSubmit\"></td>
  180.                 </tr>
  181.             </table>
  182.                 <input type=\"hidden\" name=\"csvFile\" value=\"" . stripslashes($csvFile) . "\">
  183.                 <input type=\"hidden\" name=\"delimiter\" value=\"" . htmlspecialchars($delimiter) . "\">
  184.                 <input type=\"hidden\" name=\"maxCols\" value=\"$maxCols\">
  185.                 <input type=\"hidden\" name=\"stage\" value=\"db_connect\">
  186.         </form>";
  187.  
  188. } else if ($stage == "db_connect") {
  189.  
  190.     if (isset($sameConnection)) {
  191.  
  192.         setcookie ("ck_csv[serverName]", $serverName, time()+31536000);
  193.         setcookie ("ck_csv[username]", $username, time()+31536000);
  194.         setcookie ("ck_csv[password]", $password, time()+31536000);
  195.  
  196.     } else if (($ck_csv[serverName] != "") || ($ck_csv[username] != "") || ($ck_csv[password] != "")) {
  197.  
  198.         setcookie ("ck_csv[serverName]", $serverName, time()-31536000);
  199.         setcookie ("ck_csv[username]", $username, time()-31536000);
  200.         setcookie ("ck_csv[password]", $password, time()-31536000);
  201.  
  202.     }
  203.  
  204.     if (!$connection = @mysql_connect($serverName, $username, $password)) {
  205.         $variables = "stage=setup_db_connection"
  206.                         . "&fail=yes"
  207.                         . "&serverName=$serverName"
  208.                         . "&username=$username"
  209.                         . "&csvFile=$csvFile"
  210.                         . "&delimiter=$delimiter";
  211.         header("location: ?$variables");
  212.     } else {
  213.  
  214.         $database_list = mysql_list_dbs($connection);
  215.         $database_index = 0;
  216.  
  217.         $js_block = "function getOptions(myID) {
  218.                         var e_table = document.form1.table;
  219.                         var optionCount = e_table.options.length;
  220.  
  221.                         for (var index = optionCount; index >= 0; index--) {
  222.                             e_table[index] = null;
  223.                         }
  224.  
  225.                         if (myID > 0) {
  226.                             var arrayLength = tableArray[(parseInt(myID-1))].length;
  227.                             for (index = 0; index < arrayLength; index++) {
  228.                                 eval('e_table.options[index] = ' + tableArray[(parseInt(myID-1))][index]);
  229.                             }
  230.                         } else {
  231.                             e_table.options[0] = new Option(\"Select database\", \"\");
  232.                         }
  233.  
  234.                     }
  235.  
  236.                     \nvar tableArray = new Array;";
  237.  
  238.         while ($database_index < mysql_num_rows($database_list)) {
  239.                 $js_block .= "\n\ntableArray[$database_index] = new Array;";
  240.                 $database_options .= "\n\n\t<option value=\"" . mysql_db_name($database_list, $database_index) . "\">" . mysql_db_name($database_list, $database_index) . "</option>";
  241.  
  242.                 $table_list = mysql_list_tables(mysql_db_name($database_list, $database_index));
  243.                 $table_index = 0;
  244.  
  245.                 while ($table_index < mysql_num_rows($table_list)) {
  246.                     $js_block .= "\n\ttableArray[$database_index][$table_index] = \"new Option(\\\"" . mysql_tablename($table_list, $table_index) . "\\\", \\\"" . mysql_tablename($table_list, $table_index) . "\\\");\";";
  247.                     $table_index++;
  248.  
  249.                 }
  250.  
  251.             $database_index++;
  252.         }
  253.  
  254.         mysql_close($connection);
  255.  
  256.     $display_block = "
  257.         <form name=\"form1\" action=\"csv_importer.php\" method=\"POST\">
  258.             <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"55%\" align=\"center\">
  259.                 <tr>
  260.                     <td colspan=\"2\" class=\"tdTitle\">PHP CSV Importer</td>
  261.                 </tr>
  262.                 <tr>
  263.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  264.                 </tr>
  265.                 <tr>
  266.                     <td>Select database:</td>
  267.                     <td>
  268.                         <select name=\"database\" onChange=\"getOptions(this.selectedIndex)\">
  269.                             <option value=\"\">Select database</option>
  270.                             $database_options
  271.                         </select>
  272.                     </td>
  273.                 </tr>
  274.                 <tr>
  275.                     <td>Select table:</td>
  276.                     <td>
  277.                         <select name=\"table\">
  278.                             <option value=\"\">Select a database</option>
  279.                         </select>
  280.                     </td>
  281.                 </tr>
  282.                 <tr>
  283.                     <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  284.                 </tr>
  285.                 <tr>
  286.                     <td colspan=\"2\" class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next &raquo;\" class=\"inputSubmit\"></td>
  287.                 </tr>
  288.             </table>
  289.                 <input type=\"hidden\" name=\"csvFile\" value=\"" . stripslashes($csvFile) . "\">
  290.                 <input type=\"hidden\" name=\"delimiter\" value=\"" . htmlspecialchars($delimiter) . "\">
  291.                 <input type=\"hidden\" name=\"maxCols\" value=\"$maxCols\">
  292.                 <input type=\"hidden\" name=\"serverName\" value=\"$serverName\">
  293.                 <input type=\"hidden\" name=\"username\" value=\"$username\">
  294.                 <input type=\"hidden\" name=\"password\" value=\"$password\">
  295.                 <input type=\"hidden\" name=\"stage\" value=\"field_list\">
  296.         </form>";
  297.  
  298.     }  // End if-else(can't connect)
  299.  
  300. } else if ($stage == "field_list") {
  301.  
  302.     if (!$myFile = @fopen(stripslashes($csvFile), "r")) {
  303.         die("Can't open CSV file.  Has it been moved/deleted?");
  304.     } else {
  305.         for ($index = 0; $index < $maxCols; $index++) {
  306.             $columnOptions .= "<option value=\"$index\">Column " . ($index+1) . "</option>";
  307.         }
  308.         $line = 0;
  309.  
  310.         while (($line < 5) && ($data = fgetcsv($myFile, 1000, $delimiter))) {
  311.             $numOfCols = count($data);
  312.  
  313.             $csv_block .= "\n\t\t\t\t\t\t\t<tr>";
  314.  
  315.             for ($index = 0; $index < $numOfCols; $index++) {
  316.                 if (strlen($data[$index]) > 10) {
  317.                     $dots = "...";
  318.                 } else {
  319.                     $dots = "";
  320.                 }
  321.                 if ($data[$index] == "") {
  322.                     $csv_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewContent\">"
  323.                                         . "\n\t\t\t\t\t\t\t\t\t"
  324.                                             . "&nbsp;"
  325.                                         . "\n\t\t\t\t\t\t\t\t</td>";
  326.                 } else {
  327.                     $csv_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewContent\">"
  328.                                         . "\n\t\t\t\t\t\t\t\t\t"
  329.                                             . substr($data[$index], 0, 10) . $dots
  330.                                         . "\n\t\t\t\t\t\t\t\t</td>";
  331.                 }
  332.             }
  333.  
  334.             $csv_block .= "\n\t\t\t\t\t\t\t\t</tr>";
  335.  
  336.             $line++;
  337.         }
  338.  
  339.         fclose($myFile);
  340.  
  341.         $display_block .= "<form name=\"form1\" action=\"csv_importer.php\" method=\"POST\">
  342.         <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">
  343.             <tr>
  344.                 <td class=\"tdTitle\">PHP CSV Importer</td>
  345.             </tr>
  346.             <tr>
  347.                 <td class=\"tdSpacer\">&nbsp;</td>
  348.             </tr>
  349.             <tr>
  350.                 <td>
  351.                     <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">
  352.                         <tr>";
  353.  
  354.         for ($index = 0; $index < $maxCols; $index++) {
  355.             $display_block .= "\n\t\t\t\t\t\t\t\t<td class=\"tdPreviewColHeader\">Col " . ($index+1) . "</td>";
  356.         }
  357.  
  358.         $display_block .= "\n\t\t\t\t\t\t\t</tr>$csv_block</table>
  359.                     </td>
  360.                 </tr>
  361.                     <tr>
  362.                         <td class=\"tdSpacer\">&nbsp;</td>
  363.                     </tr>
  364.                 <tr>
  365.                     <td>
  366.                         <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">";
  367.  
  368.  
  369.         if (!$connection = mysql_connect($serverName, $username, $password)) {
  370.             die("Can't connect to database.  Has MySQL stopped?");
  371.         } else {
  372.             $fields = mysql_list_fields($database, $table, $connection);
  373.             $columns = mysql_num_fields($fields);
  374.  
  375.             for ($index = 0; $index < $columns; $index++) {
  376.                  $display_block .= "\n\t\t\t\t\t\t<tr>
  377.                          <td colspan=\"5\" class=\"tdPreviewAssignHeader\">" . mysql_field_name($fields, $index) . ":&nbsp;</td>
  378.                     </tr>
  379.                     <tr>
  380.                          <td class=\"tdPreviewAssignContent\"><input type=\"radio\" name=\"useValue[$index]\" value=\"column\" checked>Use column:&nbsp;</td>
  381.                         <td class=\"tdPreviewAssignContent\">
  382.                             <select name=\"fieldColumn[$index]\" onClick=\"setAssignment('" . $index . "', 'column')\">
  383.                                 <option value=\"\">Select column</option>
  384.                                 <option value=\"none\">None</option>
  385.                                 $columnOptions
  386.                             </select>
  387.                         </td>
  388.                          <td align=\"center\" class=\"tdPreviewAssignContent\">&nbsp;OR&nbsp;</td>
  389.                          <td class=\"tdPreviewAssignContent\">
  390.                             <input type=\"radio\" name=\"useValue[$index]\" value=\"value\">
  391.                                 Use this value:&nbsp;
  392.                         </td>
  393.                         <td class=\"tdPreviewAssignContent\">
  394.                             <input type=\"text\" name=\"fieldValue[$index]\" size=\"8\" class=\"inputText\" onClick=\"setAssignment('" . $index . "', 'value')\">
  395.                         </td>
  396.                     </tr>";
  397.                 $js_field_array .= "\nfieldArray[$index] = \"" . mysql_field_name($fields, $index) . "\";";
  398.             }
  399.         }
  400.  
  401.         $display_block .= "
  402.                     <tr>
  403.                         <td colspan=\"5\" class=\"tdSpacer\">&nbsp;</td>
  404.                     </tr>
  405.                     <tr>
  406.                         <td colspan=\"2\" align=\"center\"><input type=\"button\" name=\"sequence\" value=\"Sequence\" class=\"inputSubmit\" onClick=\"sequenceOptions()\"></td>
  407.                         <td>&nbsp;</td>
  408.                         <td colspan=\"2\">&nbsp;</td>
  409.                     </tr></table></td></tr>
  410.                 <tr>
  411.                     <td class=\"tdSpacer\">&nbsp;</td>
  412.                 </tr>
  413.                 <tr>
  414.                     <td>Would you like to import the first line of the CSV file?</td>
  415.                 </tr>
  416.                 <tr>
  417.                     <td>
  418.                         <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\">
  419.                             <tr>
  420.                                 <td><input type=\"radio\" name=\"startLine\" value=\"0\" checked>Yes</td>
  421.                             </tr>
  422.                             <tr>
  423.                                 <td><input type=\"radio\" name=\"startLine\" value=\"1\">No</td>
  424.                             </tr>
  425.                         </table>
  426.                     </td>
  427.                 </tr>
  428.                 <tr>
  429.                     <td class=\"tdSpacer\">&nbsp;</td>
  430.                 </tr>
  431.                 <tr>
  432.                     <td>Are you updating existing records or adding new ones?</td>
  433.                 </tr>
  434.                 <tr>
  435.                     <td>
  436.                         <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\">
  437.                             <tr>
  438.                                 <td><input type=\"radio\" name=\"sqlType\" value=\"add\" checked onClick=\"setPrimaryKey(this.value)\">Adding</td>
  439.                             </tr>
  440.                             <tr>
  441.                                 <td><input type=\"radio\" name=\"sqlType\" value=\"update\" onClick=\"setPrimaryKey(this.value)\">Updating - Primary key: <select name=\"primaryKey\" class=\"inputCombo\"></select></td>
  442.                             </tr>
  443.                             <tr>
  444.                                 <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If there are new records not in table, how are they marked? <input type=\"text\" name=\"noRecordMarker\" size=\"3\" class=\"inputText\"></td>
  445.                             </tr>
  446.                         </table>
  447.                     </td>
  448.                 </tr>
  449.                 <tr>
  450.                     <td class=\"tdSpacer\">&nbsp;</td>
  451.                 </tr>
  452.                 <tr>
  453.                     <td class=\"tdSubmit\"><input type=\"submit\" name=\"submit\" value=\"Next &raquo;\" class=\"inputSubmit\" class=\"inputSubmit\"></td>
  454.                 </tr>
  455.             </table>
  456.                 <input type=\"hidden\" name=\"csvFile\" value=\"" . stripslashes($csvFile) . "\">
  457.                 <input type=\"hidden\" name=\"delimiter\" value=\"" . htmlspecialchars($delimiter) . "\">
  458.                 <input type=\"hidden\" name=\"maxCols\" value=\"$maxCols\">
  459.                 <input type=\"hidden\" name=\"serverName\" value=\"$serverName\">
  460.                 <input type=\"hidden\" name=\"username\" value=\"$username\">
  461.                 <input type=\"hidden\" name=\"password\" value=\"$password\">
  462.                 <input type=\"hidden\" name=\"database\" value=\"$database\">
  463.                 <input type=\"hidden\" name=\"table\" value=\"$table\">
  464.                 <input type=\"hidden\" name=\"stage\" value=\"insert\">
  465.             </form>";
  466.  
  467.         $js_block = "\n\nfunction sequenceOptions() {
  468.                         var d = document.form1;
  469.                         var total_elements = document.form1.length;
  470.                         var selectedIndex = 2;
  471.                         var regexp = /column/i;
  472.  
  473.                         for (var index = 0; index < total_elements; index++) {
  474.                             element = d[index];
  475.  
  476.                             if ((element.type == \"select-one\") && (regexp.test(element.name))) {
  477.                                 if (element.options.length > selectedIndex) {
  478.                                     element.selectedIndex = selectedIndex;
  479.                                     selectedIndex++;
  480.                                 } else {
  481.                                     element.selectedIndex = 1;
  482.                                 }
  483.                             }
  484.                         }
  485.                     }
  486.  
  487.                     function assignContent(field) {
  488.                         alert(field.value);
  489.                     }
  490.  
  491.                     function setAssignment(index, side) {
  492.                         var df = document.form1;
  493.                         var fieldList = \"\";
  494.                         var name_regexp = eval('/\[' + index + '\]/');
  495.                         var element;
  496.  
  497.                         for (var items = 0; items < df.length; items++) {
  498.                             element = df[items];
  499.                             if ((element.type == \"radio\")
  500.                                 && (name_regexp.test(element.name))
  501.                                 && (element.value == side)) {
  502.  
  503.                                     element.checked = true;
  504.                             }
  505.                         }
  506.                     }
  507.  
  508.                     var fieldArray = new Array;
  509.                     $js_field_array
  510.  
  511.                     function setPrimaryKey(value) {
  512.                         var element = document.form1.primaryKey;
  513.                         if (value == \"update\") {
  514.                             for (var index = 0; index < fieldArray.length; index++) {
  515.                                 element.options[index] = new Option(fieldArray[index], fieldArray[index]);
  516.                             }
  517.                         } else {
  518.                             for (var index = fieldArray.length; index >= 0; index--) {
  519.                                 element.options[index] = null;
  520.                             }
  521.                         }
  522.  
  523.                     }\n\n\n";
  524.     }
  525.  
  526. } else if ($stage == "insert") {
  527.  
  528.     if (!$connection = mysql_connect($serverName, $username, $password)) {
  529.         die("Can't connect to database.  Has MySQL stopped?");
  530.     } else {
  531.         $fields = mysql_list_fields($database, $table, $connection);
  532.         $columns = mysql_num_fields($fields);
  533.         $selectedFieldIndex = 0;
  534.  
  535.         for ($index = 0; $index < $columns; $index++) {
  536.             $fieldArray[$selectedFieldIndex][0] = mysql_field_name($fields, $index);
  537.             $fieldArray[$selectedFieldIndex][1] = $fieldColumn[$index];
  538.  
  539.             $selectedFieldIndex++;
  540.         }
  541.  
  542.         if ($sqlType == "add") {
  543.  
  544.             if (!$myFile = @fopen(stripslashes($csvFile), "r")) {
  545.                 die("Can't open CSV file.  Has it been moved/deleted?");
  546.             } else {
  547.  
  548.                 $line = 0;
  549.  
  550.                 while ($data = fgetcsv($myFile, 2048, $delimiter)) {
  551.                     if ($line >= $startLine){
  552.                         $numOfCols = count($data);
  553.  
  554.                         $sql = "INSERT INTO
  555.                                     $table
  556.                                 (";
  557.  
  558.                         for ($index = 0; $index < count($fieldArray); $index++) {
  559.                             if ((($fieldColumn[$index] != "") && ($fieldColumn[$index] != "none")) || ($useValue[$index] == "value")) {
  560.                                 $sql .= "`".$fieldArray[$index][0] . "`, ";
  561.                             }
  562.                         }
  563.  
  564.                         $sql = substr($sql, 0, -2) . ")\nVALUES\n(";
  565.  
  566.                         for ($index = 0; $index < count($fieldArray); $index++) {
  567.                             if ($useValue[$index] == "value") {
  568.                                 $sql .= "\"" . $fieldValue[$index] . "\", ";
  569.                             } else if (($fieldColumn[$index] != "none") && ($fieldColumn[$index] != "")) {
  570.                                 $sql .= "\"" . $data[$fieldArray[$index][1]] . "\", ";
  571.                             }
  572.                         }
  573.  
  574.                         $sql = substr($sql, 0, -2) . ")";
  575.  
  576.                         if ($update_db = mysql_query($sql, $connection)) {
  577.                             $mysql_log .= "Ok: $sql\n<br>\n<br>";
  578.                         } else {
  579.                             $mysql_log .= "<strong>Failed:</strong> $sql\n<br>Reason: " . mysql_error() . "\n<br>\n<br>";
  580.                         }
  581.  
  582.                     }
  583.                     $line++;
  584.                 }
  585.             } 
  586.  
  587.             fclose($myFile);
  588.         } else {
  589.  
  590.             if (!$myFile = @fopen(stripslashes($csvFile), "r")) {
  591.                 die("Can't open CSV file.  Has it been moved/deleted?");
  592.             } else {
  593.  
  594.                 $line = 0;
  595.  
  596.                 while ($data = fgetcsv($myFile, 2048, $delimiter)) {
  597.                     if ($line >= $startLine){
  598.                         $numOfCols = count($data);
  599.  
  600.                         for ($index = 0; $index < count($fieldArray); $index++) {
  601.                             if ($fieldArray[$index][0] == $primaryKey) {
  602.                                 if ($data[$fieldArray[$index][1]] == $noRecordMarker) {
  603.                                     $action = "insert";
  604.                                 } else {
  605.                                     $action = "update";
  606.                                 }
  607.                                 break;
  608.                             }
  609.  
  610.                         }
  611.  
  612.  
  613.                         if ($action == "insert") {
  614.                             $sql = "INSERT INTO
  615.                                             $table (";
  616.  
  617.                             for ($index = 0; $index < count($fieldArray); $index++) {
  618.                                 if ($fieldArray[$index][0] != $primaryKey) $sql .= $fieldArray[$index][0] . ", ";
  619.                             }
  620.  
  621.                             $sql = substr($sql, 0, -2) . ")\nVALUES\n(";
  622.  
  623.                             for ($index = 0; $index < count($fieldArray); $index++) {
  624.                                 if ($fieldArray[$index][0] != $primaryKey) {
  625.                                     $sql .= "\"" . $data[$fieldArray[$index][1]] . "\", ";
  626.                                 }
  627.                             }
  628.  
  629.                             $sql = substr($sql, 0, -2) . ")";
  630.  
  631.                         } else {
  632.  
  633.                             $sql = "UPDATE
  634.                                         $table
  635.                                     SET ";
  636.  
  637.                             for ($index = 0; $index < count($fieldArray); $index++) {
  638.  
  639.                                 if ($fieldArray[$index][0] == $primaryKey) {
  640.                                     if ($useValue[$index] == "column") {
  641.                                         $primaryKeyValue = $data[$fieldArray[$index][1]];
  642.                                     } else {
  643.                                         $primaryKeyValue = $fieldValue[$index];
  644.                                     }
  645.                                 }
  646.  
  647.                                 if ($fieldArray[$index][0] != $primaryKey) {
  648.                                     if (($useValue[$index] == "column")
  649.                                             && ($fieldColumn[$index] != "")
  650.                                             && ($fieldColumn[$index] != "none")) {
  651.                                         $sql .= $fieldArray[$index][0] . " = \"" . $data[$fieldArray[$index][1]] . "\", ";
  652.                                     } else if ($useValue[$index] == "value") {
  653.                                         $sql .= $fieldArray[$index][0] . " = \"" . $fieldValue[$index] . "\", ";
  654.                                     }
  655.                                 }
  656.                             }
  657.  
  658.                             $sql = substr($sql, 0, -2) . " WHERE $primaryKey = \"" . $primaryKeyValue . "\"";
  659.  
  660.                         }
  661.                         if ($sql != "") {
  662.                             if ($update_db = mysql_query($sql, $connection)) {
  663.                                 $mysql_log .= "Ok: $sql\n<br>\n<br>";
  664.                             } else {
  665.                                 $mysql_log .= "<strong>Failed:</strong> $sql\n<br>Reason: " . mysql_error() . "\n<br>\n<br>";
  666.                             }
  667.                             $sql = "";
  668.                         }
  669.                             $sql = "";
  670.  
  671.                     }
  672.                     $line++;
  673.                 }
  674.             } 
  675.  
  676.             fclose($myFile);
  677.         }
  678.  
  679.  
  680.         $display_block = "
  681.             <form action=\"csv_importer.php\" method=\"POST\">
  682.                 <table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"55%\" align=\"center\">
  683.                     <tr>
  684.                         <td colspan=\"2\" class=\"tdTitle\">PHP CSV Importer</td>
  685.                     </tr>
  686.                     <tr>
  687.                         <td colspan=\"2\" class=\"tdSpacer\">&nbsp;</td>
  688.                     </tr>
  689.                     <tr>
  690.                         <td>" . $mysql_log . "</td>
  691.                     </tr>
  692.                 </table>
  693.                 <input type=\"hidden\" name=\"stage\" value=\"preview\">
  694.             </form>";
  695.     }
  696.  
  697. } else {
  698.  
  699.     echo "Bad stage";
  700.  
  701. }
  702.  
  703. ?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  704.  
  705. <html>
  706. <head>
  707.     <title>Import a File</title>
  708.     <link rel="stylesheet" href="csv_importer.css" type="text/css">
  709. <script>
  710. <? echo $js_block ?>
  711. </script>
  712. </head>
  713.  
  714. <body>
  715.  
  716.     <? echo $display_block ?>
  717.  
  718. </body>
  719. </html>
Last edited by stymiee; Dec 16th, 2006 at 4:22 pm.
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
mahe4us is offline Offline
54 posts
since Sep 2006
Permalink
Mar 3rd, 2009
0

Re: insert csv file into mysql through php

Ok I have this code running on my site. Only thing is I get an error when I put csv files location in the text box. Also I had to add slashes to everywhere the script said "highlight" I was getting some kind of parse error unless it looked like /"highlight/".

My question is why am I getting the error message when I select my csv file.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
mattjfox@gmail. is offline Offline
1 posts
since Mar 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in PHP Forum Timeline: displaying random images
Next Thread in PHP Forum Timeline: simple mysql question..plz answer :)





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC