RSS Forums RSS

insert csv file into mysql through php

Reply
•
•
Posts: 10
Reputation: manish812 is an unknown quantity at this point 
Solved Threads: 1
manish812 manish812 is offline Offline
Newbie Poster

Question insert csv file into mysql through php

  #1  
Jul 17th, 2006
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
AddThis Social Bookmark Button
Reply With Quote  
•
•
Posts: 1,422
Reputation: stymiee is on a distinguished road 
Solved Threads: 34
Moderator
stymiee's Avatar
stymiee stymiee is offline Offline
He's No Good To Me Dead

Re: insert csv file into mysql through php

  #2  
Jul 17th, 2006
1) Split the csv file line-by-line using explode()
2) Then insert into your mysql DB normally.
John Conde
Brainyminds | Merchant Account Services | I Love Code
IT'S HERE: Merchant Accounts 101 Everything you need to know about merchant accounts!
Reply With Quote  
•
•
Posts: 294
Reputation: zippee is an unknown quantity at this point 
Solved Threads: 6
zippee's Avatar
zippee zippee is offline Offline
Posting Whiz in Training

Re: insert csv file into mysql through php

  #3  
Jul 17th, 2006
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]
Ecommerce-Web-Store.com Building Your e-Business.
Reply With Quote  
•
•
Posts: 34
Reputation: php_coder is an unknown quantity at this point 
Solved Threads: 0
php_coder php_coder is offline Offline
Light Poster

Solution Re: insert csv file into mysql through php

  #4  
Dec 15th, 2006
•
•
•
•
Originally Posted by zippee View Post
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]."')");
}
}
Reply With Quote  
•
•
Posts: 53
Reputation: mahe4us is an unknown quantity at this point 
Solved Threads: 0
mahe4us's Avatar
mahe4us mahe4us is offline Offline
Junior Poster in Training

Re: insert csv file into mysql through php

  #5  
Dec 16th, 2006
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 3:22 pm.
Reply With Quote  
•
•
Posts: 1
Reputation: mattjfox@gmail. is an unknown quantity at this point 
Solved Threads: 0
mattjfox@gmail. mattjfox@gmail. is offline Offline
Newbie Poster

Re: insert csv file into mysql through php

  #6  
Mar 3rd, 2009
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.
Reply With Quote  
Reply

Quick Reply to Thread
•
•
•
•

Only community members can participate in forum threads. You must register or log in to contribute.

Register


Similar Threads
Other Threads in the PHP Forum
•
•
•
•
Views: 8232 | Replies: 5 | Currently Viewing: 1 (0 members and 1 guests)

 

•
•
•
•
Thread Tools Display Modes
Linear Mode Linear Mode
Forums | Blogs | Tutorials | Code Snippets | Whitepapers | RSS Feeds | Advertising
All times are GMT -4. The time now is 9:59 pm.
Newsletter Archive - Sitemap - Privacy Statement - Acceptable Use Policy - Contact Us
Forum system based on vBulletin Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC