PHP - Help Needed To Fix Script For Import Of Csv File To Mysql

I have mysql exported in the following sql format:

Code: [Select]
-

CREATE TABLE IF NOT EXISTS `pma_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `db` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `table` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `timevalue` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sqlquery` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`,`db`,`table`,`timevalue`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='SQL history for phpMyAdmin' AUTO_INCREMENT=3761 ;

--
-- Dumping data for table `pma_history`
--

INSERT INTO `pma_history` (`id`, `username`, `db`, `table`, `timevalue`, `sqlquery`) VALUES
(2, 'root', 'information_schema', 'FILES', '2010-03-04 19:26:33', 'SELECT * FROM `FILES`')

Just as an example anyway.

What I need to be able to do, is import this using PHP.

Does anyone know of a suitable class for doing this?

I could make my own, but I cant quite work out the best way to seperate the queries out?

Thanks

include "connect.php";

if(isset($_POST['submit']))

   {

     $filename=$_POST['filename'];

     $handle = fopen("$filename", "r");

     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

     {

    

       $import="UPDATE isc_products(prodavailability,prodinvtrack,prodcurrentinv) values('$data[1]','$data[2]','$data[3]') where vendor_id = '($data[0])' ";

       mysql_query($import) or die(mysql_error());

     }

     fclose($handle);

     print "Import done";

 

   }

   else

   {

 

      print "<form action='inv_update.php' method='post'>";

      print "Type file name to import:<br>";

      print "<input type='text' name='filename' size='20'><br>";

      print "<input type='submit' name='submit' value='submit'></form>";

   }




?>


I am building this script to import some data via a csv that has three fields. The where statement gives me this error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(prodavailability,prodinvtrack,prodcurrentinv) values('35','1','35') where vendo' at line 1.

I'm know my syntax is flawed but can't find a solution to this.... any one point me in the right direction? I know just enough php to get myself in trouble!! lol

I've written the script below to import a csv file into the database. I've tried to make this as secure as possible by sanitizing and validating the data, but is there anything else I could do to improve it? I'm happy with the way it works so far.   All I can think to add is a meme check via php but I can't find a way to check whether it's specifically a csv file (other than just checking the extension but that's not secure)

<?php

if ($_FILES[csv][size] > 0) {
 
    //get the csv file
    $file = $_FILES[csv][tmp_name];
    $handle = fopen($file,"r");
 
    //set the row counter
    $csv_row = 0;
 
    //loop through the csv file and insert into database
    do {
 
        // start the import
        if ($data[0]) {
 
          //add 1 the row counter
          $csv_row++;
         
          //clear errors
          $errors = '';
         
          //sanitize the inputs
          $firstName = addslashes($data[0]);
          $lastName = addslashes($data[1]);
          $email = addslashes($data[2]);

         
 
          //validate the inputs - set $errors as 'YES' if any fail
          if(!ctype_alpha($firstName)){
          $errors = 'YES';    
          }
          if(!ctype_alpha($lastName)){
          $errors = 'YES';
          }
          if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
          $errors = 'YES';
          }
 
          //check whether there were any errors in the validation
          if($errors!=='YES'){
              //insert into database
              $stmt = $db->prepare("INSERT INTO test (firstName,lastName,email) VALUES (?,?,?)");
              $stmt->bind_param('sss',$firstName,$lastName,$email);
              $stmt->execute();
          }
          //there was an error, store the affected row number in the error array
          else
          {
          $error_report[] = "There was an error on row $csv_row";
          } //end error check
 
 
 
       } //end single row import
 
    } while ($data = fgetcsv($handle));

    if($csv_row>0){
    	$success = 'YES';
    }
      
 } // end csv import
?>

Edited by paddyfields, 11 June 2014 - 01:06 PM.

Hello, I am new to PHP and am trying to make a script that will ftp into the server via a cron of every hour or so and save the data to a mysql table. The first step is to draw variables out of information in the file via ftp. Its a ban list for a game server I run.

How could I get php to single out the ban reason, admin, etc.

the looks like this

Code: [Select]
"STEAM_0:0:42741441"
{
"name" "good mom"
"unban" "0"
"time" "1329161245"
"admin" "(Console)"
"reason" "no more trolling for you"
}
"STEAM_0:1:40563669"
{
"unban" "0"
"admin" "Headcrap(STEAM_0:1:42287608)"
"time" "1332855999"
"reason" ""
}
"STEAM_0:1:17693550"
{
"name" "ʎɖɑɱ"
"time" "1323055427"
"modified_time" "1323221308"
"unban" "0"
"modified_admin" "Strategos(STEAM_0:0:26662109)"
"admin" "Strategos(STEAM_0:0:26662109)"
"reason" "And so died the trolling"
}
Example: Code: [Select]
<?php
$bannedid = "STEAM_0:0:42741441";
$bannedby "(console)";
// and so on
?>

I am trying to import csv file to wordpress using database.the files are uploaded in database but its not coming in wordpress .
The code is as below:
<?php


if(isset($_POST['submit']))

{
   

   

    $fname=$_FILES["file"]["name"];
    $datetime=$_FILES["file"][""];

     $chk_ext = explode(".",$fname);
if(strtolower(($chk_ext[1]) == "csv"))

        {

      $filename = $_FILES['file']['tmp_name'];

       $handle = fopen($filename, "r");

      

      while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

         {

           

         $fname=addslashes($data[0]);

         $datetime=addslashes($data[1]);

         $products=addslashes($data[2]);

         $preset=addslashes($data[3]);

         $format=addslashes($data[4]);



         

         $sql = "INSERT into ".$wpdb->prefix ."facebook(filename,datetime,products,preset,format) values('$fname','$datetime','$products','$preset','$format')";

         

            mysql_query($sql) or die(mysql_error());

         }

   

         fclose($handle);

         echo "Successfully Imported";

     }

     else

     {

         echo "Invalid File";

     } 

}
?>

<form enctype="multipart/form-data" action="" method="post" name="uploadDetails">
   <table cellpadding="4" cellspacing="0" border="1">
   <tr>
   <td><label for="upload_a_file">Upload File:</label></td>
   <td><input type="file" name="file" id="upload_a_file" /></td>
   </tr>
   <tr>
   
   </tr>
   </table>
   <input type="hidden" name="fd_upload" value="1" />
   <input type="submit" value="Submit" class="allbutton" name="submit" />
   </form>
Hope you will help me
   

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=358188.0

I have a problem with my project. i want to import Excel sheet directly into mysql database for PHP language without any conversion of Excel sheet into another format. i am very fed about this from last two weeks. So Please help me How I Do it. any expert please help me   

Hello

I am trying to do a simple CSV import of a file and then upload to mysql. I am basing my code on an example I have found and I am getting the error message 'invalid file'. The example used includes a text box for the file import field and I am referencing the file from my computer in that box 'Users/James/name.csv'

My code is as follows:

HTML

        <form action="results.php?data=upload" method='post'>

    Import File : <input type='text' name='sel_file' size='20'>
    <input type='submit' name='submit' value='submit'>

        </form>

PHP

        if ($b == 'upload') {
     $fname = $_FILES['sel_file']['name'];

     $chk_ext = explode(".",$fname);

     if(strtolower($chk_ext[1]) == "csv")
     {

         $filename = $_FILES['sel_file']['tmp_name'];
         $handle = fopen($filename, "r");

         while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
         {
            $sql = "INSERT into ".$db['name']."(field1,field2,field3,field4) values('$data[0]','$data[1]','$data[2]', '$data[3]')";
            mysql_query($sql) or die(mysql_error());
         }

         fclose($handle);
         echo "Successfully Imported";
     }
     else
     {
         echo "Invalid File";
     }
}

my file format is:

1,23,1,5
1,24,2,5
1,25,3,5
1,26,4,5
1,27,5,5
1,28,6,5
1,29,7,5
1,30,8,5
1,31,9,5
1,32,10,5

If anybody can help that would be good - or perhaps point out if I am going about this the wrong way - cheers

Hey guys, I'm having a nightmare at the moment.   I am trying to import a CSV file into a MySQL table, some of the data is interested properly into the rows however some of the CSV row do not get imported.   Here is my PHP script:

<?php

$databasehost = "localhost";
$databasename = "name";
$databasetable = "import";
$databaseusername="username";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "db-core/feed/csv/test.csv";

if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}

try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename",
$databaseusername, $databasepassword,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}

$pdo->exec("TRUNCATE TABLE `$databasetable`");

$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
FIELDS OPTIONALLY ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
TERMINATED BY ".$pdo->quote($fieldseparator)."
LINES TERMINATED BY ".$pdo->quote($lineseparator)."
IGNORE 1 LINES");

echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

As you can see the script is importing the CSV into the table however only 40 of the rows are added to the SQL table when there are actually 80 rows in the CSV. I am also telling the script to IGNORE 1 LINES if that has anything to do with the issue.   Here is one of the rows from the CSV file:

"AETA83919","AETV34458584","SD57VUP","BLACK","Diesel","2007","76778","Hatchback","3","VAUXHALL","CORSA","1.3 CDTi [90] SXi 3dr [AC]","1200","3989","","Manual","http://images.autoexposure.co.uk/AETA83919/AETV34458584_1b.jpg","0","3","","0","2007 57 reg VAUXHALL CORSA 1.3 CDTi [90] SXi 3dr [AC], Manual, Hatchback, Diesel, 76,778 miles. BLACK, Comprehensive Warranty Included In Price. HPI Checked! Pre Delivery Health Check!, 5 seats, LOVELY VAUXHALL CORSA SXI 1.3CDTI SXI WITH AIR CONDITIONING!! VERY WELL CARED FOR AND MAINTAINED AND JUST SERVICED BY DRIVEN CAR SALES LTD AT 76K!! GREAT COMBINATION OF PERFORMANCE AND ECONOMY!! VIEWING AND TEST DRIVING IS RECOMMENDED!! EACH CAR COMES WITH A MININIUM 9 MONTHS MOT. FULL MECHANICAL HEALTH CHECK GUARANTEES CAR IS PREPARED TO THE HIGHEST STANDARD. COMPREHENSIVE RAC WARRANTY INCLUDED. DRIVEN CAR SALES WELCOME ANY INSPECTION. BUY WITH CONFIDENCE AND REASSURANCE AS DRIVEN CAR SALES LTD ARE OFFICIAL MEMBERS OF RETAIL MOTOR INDUSTRY FEDERATION. VIEW OUR REVIEWS ON TRUSTPILOT. 3,989","","0","1","","UK","0","","0","0","","","",""

As far as I can see everything looks fine with the row, all of the rows in the CSV end like this.   One thing I have noticed is it looks like the rows that are missing are in fact being placed in the last column of every other row here is an example:         So as you can see it looks like one row is being inserted into the last column of some rows and I am so confused to why this might be happening?   Any ideas?      

i was working on a mini project that imports csv file to the database through ajax and it's working fine

<?php
if(!empty($_FILES["marks_file"]["name"]))
{
  $connect = mysqli_connect("localhost", "root", "", "dbname");
  $output = '';
  $allowed_ext = array("csv");
  $extension = end(explode(".", $_FILES["marks_file"]["name"]));
  if(in_array($extension, $allowed_ext))
  {
       $file_data = fopen($_FILES["marks_file"]["tmp_name"], 'r');
       fgetcsv($file_data);
       while($row = fgetcsv($file_data))
       {
            $name = mysqli_real_escape_string($connect, $row[0]);
            $Physics = mysqli_real_escape_string($connect, $row[1]);
            $Maths = mysqli_real_escape_string($connect, $row[2]);
            $Chemistry = mysqli_real_escape_string($connect, $row[3]);
            $Biology = mysqli_real_escape_string($connect, $row[4]);
            $SST = mysqli_real_escape_string($connect, $row[5]);
            $query = "
            INSERT INTO csv
                 (name, Physics, Maths, Chemistry, Biology, SST)
                 VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST')
            ";
            mysqli_query($connect, $query);
       }
       $select = "SELECT * FROM csv ORDER BY id DESC";
       $result = mysqli_query($connect, $select);
       $output .= '
       <table class="table table-bordered">
           <tr>
               <th width="25%" >name</th>
               <th width="15%" >Physics</th>
               <th width="15%" >Maths</th>
               <th width="15%" >Chemistry</th>
               <th width="15%" >Biology</th>
               <th width="15%" >SST</th>
           </tr>
       ';
       while($row = mysqli_fetch_array($result))
       {
            $output .= '
                 <tr>
                      <td>'.$row["name"].'</td>
                      <td>'.$row["Physics"].'</td>
                      <td>'.$row["Maths"].'</td>
                      <td>'.$row["Chemistry"].'</td>
                      <td>'.$row["Biology"].'</td>
                      <td>'.$row["SST"].'</td>
                 </tr>
            ';
       }
       $output .= '</table>';
       echo $output;
  }
  else
  {
       echo 'errorx';
  }
 }
  else
{
     echo "errory";
}
 ?>

however the imported csv files inserts null values in the tables because the format of all csv files assigned to me are in the exact same format:

,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100
,,,Name1,10,25,35,42,62
,,,Name2,80,45,45,45,25
,,,Name3,63,25,63,36,36
,,,Name4,82,36,75,48,42
,,,Name5,45,45,78,25,24
,,,Name6,36,36,15,75,36
,,,Name7,99,45,24,24,45
,,,Name8,45,85,85,85,96

i changed my code a bit modified the functions to espace blank spaces and not return null values in the data tables

while (($row = fgetcsv($file_data, 1000, ",")) !== FALSE)
    {
        if ((string) $row[0] != '0' and empty($row[0]))
        {
            continue;
        }

                $name = mysqli_real_escape_string($connect, $row[0]);
                $Physics = mysqli_real_escape_string($connect, $row[1]);
                $Maths = mysqli_real_escape_string($connect, $row[2]);
                $Chemistry = mysqli_real_escape_string($connect, $row[3]);
                $Biology = mysqli_real_escape_string($connect, $row[4]);
                $SST = mysqli_real_escape_string($connect, $row[5]);
                $query = "
                INSERT INTO csv
                     (name, Physics, Maths, Chemistry, Biology, SST)
                     VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST')
                ";
                mysqli_query($connect, $query);
           }

But it doesn't work with the specified csv files i think the ajax call breaks and that's why nothing happens.However it works fine with csv files without blank spaces

Edited March 19, 2019 by zetastreak

Hello, this script originally works by reading a physical copy of 'bans.txt' located in the same directory. How could I get it to read the remote bans.txt located here and parse info from it like the on-site copy?

the remote file: http://108.163.211.219/bans.txt

Code: [Select]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Data Tables and Cascading Style Sheets Gallery</title>
<style>/*
Tema: Soft Table - A Simple table style with the use of the soft brown color
Author: Newton de G?es Horta
Site: --
Country Origin: Brazil
*/

table {
 font-size:0.9em;
 font-family: Arial, Helvetica, verdana sans-serif;
 background-color:#fff;
 border-collapse: collapse;
 width: 100%;
}
caption {
 font-size: 25px;
 color: #1ba6b2;
 font-weight: bold;
 text-align: left;
 background: url(http://www.nghorta.com/csstg/header_bg.jpg) no-repeat top left;
 padding: 10px;
 margin-bottom: 2px;
}
thead th {
 border-right: 1px solid #fff;
 color:#fff;
 text-align:center;
 padding:2px;
 text-transform:uppercase;
 height:25px;
 background-color: #a3c159;
 font-weight: normal;
}
tfoot {
 color:#1ba6b2;
 padding:2px;
 text-transform:uppercase;
 font-size:1.2em;
 font-weigth: bold;
 margin-top:6px;
 border-top: 6px solid #e9f7f6;
}
tbody tr {
 background-color:#fff;
 border-bottom: 1px solid #f0f0f0;
}
tbody td {
 color:#414141;
 padding:5px;
 text-align:left;
}
tbody th {
 text-align:left;
 padding:2px;
}
tbody td a, tbody th a {
 color:#6C8C37;
 text-decoration:none;
 font-weight:normal;
 display:block;
 background: transparent url(http://www.nghorta.com/csstg/links_yellow.gif) no-repeat 0% 50%;
 padding-left:15px;
}
tbody td a:hover, tbody th a:hover {
 color:#009193;
 text-decoration:none;
}
/* tr:nth-child(even) {
    background-color: grey;
} */
</style></head>
<body>

<table summary="Submitted table designs">
<thead><tr>
<th style="font-size:12px" scope="col"><center>Nickname</center></th>
<th style="font-size:12px" scope="col"><center>Admin</center></th>
<th style="font-size:12px" scope="col"><center>Banned</center></th>
<th style="font-size:12px" scope="col"><center>Reason</center></th>
<th style="font-size:12px" scope="col"><center>SteamID</center></th>
<th style="font-size:12px" scope="col"><center>Length</center></th>
<th style="font-size:12px" scope="col"><center>Status</center></th>
</tr></thead>


<tbody>

<!-- <tr><th scope="row" id="r100"><a href="100.php">rows table template</a></th>
<td><a href="http://www.adobati.it">Omar '0m4r' Adobati</a></td><td>Italy</td>
<td>Simple, clean and a quite classic table template :)</td>
<td><a href="http://www.adobati.it/labs/CSSTable/0m4r.table.css" title="Download the rows table template CSS file">Download</a></td>
<td>test</td>
<td>test2</td>
</tr> -->




<?php

// Credits to justin as he was able to understand my messy PHP code and do this much better code for me.

function buildBanList($arr)
{

$filename = "bans.txt";

$bans = array();

$tmp_array = array();



// Build Ban Array
if (file_exists($filename)) 
{
foreach( $arr as $line )
{



if( substr(trim($line), 0, 7) == '"STEAM_' )
{

$tmp_array["user_steamid"] = str_replace('"', '', trim($line));

}



if( substr(trim($line), 0, 6) == '"time"' )
{

$tmp = explode(' ', trim($line));

$tmp_array["user_bantime"] = str_replace('"', '', $tmp[1]);

}



if( substr(trim($line), 0, 15) == '"modified_time"' )
{

$tmp = explode(' ', trim($line));

$tmp_array["user_modified"] = str_replace('"', '', $tmp[1]);

}



if( substr(trim($line), 0, 7) == '"unban"' )
{

$tmp = explode(' ', trim($line));

$tmp_array["user_unban"] = str_replace('"', '', $tmp[1]);

}



if( substr(trim($line), 0, 7) == '"admin"' )
{

$tmp = explode('" "', trim($line));

$tmp2 = explode('(', $tmp[1]);

$tmp_array["admin_name"] = str_replace('"', '', $tmp2[0]);

}



if( substr(trim($line), 0, 6) == '"name"' )
{

$tmp = explode('" "', trim($line));

$tmp_array["user_name"] = str_replace('"', '', $tmp[1]);

}



if( substr(trim($line), 0, 8) == '"reason"' )
{

$tmp = explode('" "', trim($line));

$tmp_array["user_reason"] = str_replace('"', '', $tmp[1]);

}



// Save ban record to main array once detected end.

if( substr(trim($line), 0, 1) == '}' )
{

// If console ban then set required fields.

if( $tmp_array["admin_name"] == "")
$tmp_array["admin_name"] = "Console";

array_push($bans, $tmp_array);
$tmp_array = array();

}

}
}



//Sort Array by Ban Date

$tmp = array(); 

foreach($bans as &$ma) 

    $tmp[] = &$ma["user_bantime"];

array_multisort($tmp, SORT_DESC, $bans); 



return $bans;



}



//Begin Presentation



$file = file("./bans.txt");

date_default_timezone_set("Europe/London");



$bans = buildBanList($file);



foreach ($bans as $ban)

{

echo '<tr>
';

// Output
if ($ban["user_name"] != '')
echo '<td style="text-align:center; font-size: 12px">'.$ban["user_name"].'</td><td style="text-align:center; font-size: 12px" class="admin">';
else
echo '<td style="text-align:center; font-size: 12px"><span style="color:#FF0000">N/A</span></td><td style="text-align:center; font-size: 12px" class="admin">';


echo $ban["admin_name"].'</td><td style="text-align:center; font-size: 12px">'.date('H:i - d/m/y',$ban["user_bantime"]).'</td>';

/* Unban date.
if ($ban["user_unban"] != "0")
{

echo date('H:i - d/m/y',$ban["user_unban"]);

} else {

echo "Never";

}*/


 echo '<td style="text-align:left; font-size: 11px">'.$ban["user_reason"].'</td>';


// Status
$today = strtotime("now");
echo '</td><td style="text-align:center; font-size: 12px">'.$ban["user_steamid"].'&nbsp;&nbsp;</td>';

// Ban Length
$date1 = date('y-m-d H:i:s',$ban["user_bantime"]);
$date2 = date('y-m-d H:i:s',$ban["user_unban"]);
$to_time=strtotime($date1);
$from_time=strtotime($date2);

$ban_length = round(abs($to_time - $from_time) / 60,0);
if ($ban["user_unban"] != "0")
{
// Days
if ($ban_length >= 1440)
{
$ban_length = round(abs($to_time - $from_time) / 24 / 60 / 60,0);
// Years couldn't be arsed to think of another way to do it.
if ( $ban_length >= 365 )
{
$ban_length = round(abs($to_time - $from_time) / 360 / 24 / 60 / 60,0);
if ($bans_length > 1)
echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Years </td>";
else
echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Year </td>";
}
else
echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Days </td>";
}
// Hours
else if ($bans_length >= 60)
{
$ban_length = round(abs($to_time - $from_time) / 60 / 60,0);
if ($bans_length > 1)
echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Hours </td>";
else 
echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Hour </td>";
}
else
echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Minutes </td>";
}

else {
echo "<td style=\"text-align:center; font-size: 12px\">Permanent</td>";
}

if ($ban["user_unban"] == "0")
echo '<td style="text-align:center; font-size: 12px"><span style="color:#FF0000">Banned</span></td>';

else if ( $today  > $ban["user_unban"] )
echo '<td style="text-align:center; font-size: 12px"><span style="color:#008000">Expired</span></td>';

else
echo '<td style="text-align:center; font-size: 12px"><span style="color:#FF0000">Banned</span></td>';

echo '</tr>';


}

?></table>

<center><p>Web-based ULX bans by Russy.</p></center>

</div>



</body>

</html>
I've tried experimenting w/ stuff like

Code: [Select]
$filename = "http://108.163.211.219/bans.txt";
$contents = file_get_contents($filename);
but it doesn't seem to want to work.

Hi,

I assigned a project where i have to import the mobile numbers from the file, but i have no idea how to proceed this.  What kind of file to accept as input and how to read.


Thanks

Hi..

I have code for importing .xml file to database.

the problem is I cannot upload my file but no error display. And also I got a problem in importing data from .xml file to database but not all row will be save.

here is my code:

Code: [Select]
<?php
$data = array();

$con = mysql_connect("localhost", "root","");
if (!$con) { 
  die(mysql_error());
}
$db = mysql_select_db("mes", $con);
if (!$db) { 
  die(mysql_error());
}

  $sql = "select * from sales_order";
$result =  mysql_query($sql);
if (!$result) {
    die(mysql_error());
}
  
function add_employee($ProductType,$WorkOrder,$POIssueDate,$SalesMonth)
  {
      global $data;
      
      
      $con = mysql_connect("localhost", "root","");
      if (!$con){ die(mysql_error());}
      $db = mysql_select_db("mes", $con);
      if (!$db) { 
          die(mysql_error());
      }

      $ProductType= $ProductType;
      $WorkOrder = $WorkOrder;
      $POIssueDate = $POIssueDate;
      $SalesMonth = $SalesMonth;
         
      $sql = "INSERT INTO sales_order (ProductType,WorkOrder,POIssueDate,SalesMonth) 
      VALUES 
      ('$ProductType','$WorkOrder','$POIssueDate','$SalesMonth')" or die(mysql_error());
      mysql_query($sql, $con);
      
      
      $data []= array('ProductType'=>$ProductType,'WorkOrder'=>$WorkOrder,'POIssueDate'=>$POIssueDate,'SalesMOnth'=>$SalesMonth);
  }
  if ( $_FILES['file']['tmp_name'] ['error'])
 //f (empty($_FILES['file']['tmp_name']['error']))  
  { //$dom = DOMDocument::load('SalesOrder.xml');   
    $dom = DOMDocument::load($_FILES['file']['tmp_name']);
     $dom = DOMDocument::__construct();           
      $rows = $dom->getElementsByTagName('Row');
      global $last_row;
      $last_row = false;
      $first_row = true;
      foreach ($rows as $row)
      {
          if ( !$first_row )
          {
              $ProductType = "";
              $WorkOrder = "";
              $POIssueDate = "";
              $SalesMonth = "";
              
              $index = 1;
              $cells = $row->getElementsByTagName( 'Cell' );
          
              foreach( $cells as $cell )
              { 
                  $ind = $cell->getAttribute( 'Index' );
                  if ( $ind != null ) $index = $ind;
              
                  if ( $index == 1 ) $ProductType = $cell->nodeValue;  
                  if ( $index == 2 ) $WorkOrder = $cell->nodeValue;
                  if ( $index == 3 ) $POIssueDate = $cell->nodeValue;
                  if ( $index == 4 ) $SalesMonth = $cell->nodeValue;
                  $index += 1;
              }

             if ($ProductType=='' AND $WorkOrder=='' AND $POIssueDate=='' AND $SalesMonth=='') {  
                    $last_row = true;
              }      
              else {
                    add_employee($ProductType,$WorkOrder,$POIssueDate,$SalesMonth);  
              }      
          }
          if ($last_row==true) {
              $first_row = true;
          }     
          else {
              $first_row = false;
          }
      }
  }
  ?>
 
  <html>
  <body>
  <table>
  <tr>
      <th>Sales Order</th>
  </tr>
  <?php foreach( $data as $row ) { ?>
  <tr>
  <td><?php echo( $row['ProductType'] ); ?></td>
  <td><?php echo( $row['WorkOrder'] ); ?></td>
  <td><?php echo( $row['POIssueDate']) ;?> </td>
  <td><?php echo( $row['SalesMonth'] ); ?></td>
  </tr>
  <?php } ?>
  </table>
  </body>
 </html>

and I will attach my sample data and the data with color yellow background is only row I want to save to my database.

Thank you so much..

Hi,

I am importing data from a csv file into mysql.

Everything works fine except that the 'order_date' column in CSV is in the following format mm/dd/yy.

Upon import I would like for that format to be changed to yyyy-mm-dd

I am not sure how to modify the following code to be able to do so.

Any help would be appreciated.




<?
include "connect.php";

if(isset($_POST['submit']))

   {

     $filename=$_POST['filename'];

     $handle = fopen("$filename", "r");

     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

     {


$date = date("Y-m-d");


$import="INSERT into table
(order_id,
order_status,
order_date,
todays_date)
values
('$data[0]',
'$data[1]',
'$data[2]',
'$date')";
       mysql_query($import) or die(mysql_error());

     }

     fclose($handle);

     print "Import done";

 

   }

   else

   {

 

      print "<form action='importnew.php' method='post'>";

      print "Type file name to import:<br>";

      print "<input type='text' name='filename' size='20'><br>";

      print "<input type='submit' name='submit' value='submit'></form>";

   }
?> 

I am working on a csv file import to mysql, which will include items in one table (irrelevant here), and a hierarchical table of categories (id | name | parent).  I would think the simplest method to get each category name is too explode the categories from the csv into an array, for example:
$array = "root/Business/Employment";
$categories = explode("/", $array);

My problem though, is figuring out what the ID's are for each of the categories without knowing each name will be unique and not knowing for sure the full depth of each category hierarchy either.  So (I think) I need to rely on the category structure to define the lowest level ID.

So something like this:
Code: [Select]
SELECT
t1.parent AS p1,
t1.name AS lev1,
t1.id AS cat1,
t2.name AS lev2,
t2.id AS cat2
FROM
(categories AS t1)
LEFT JOIN categories AS t2 ON t2.parent = t1.id
WHERE t1.parent = '1'
AND t2.name = "Employment";

This gives me the ID of Employment, but it's easily possible that the name will be duplicated somewhere else in another heirarchy (in fact I get 4 results from phpmyadmin).  And since I don't know all these will be only 3 levels deep, it won't work all the time.

Any ideas help would be greatly appeciated!