PHP MySQL CSV UPLOAD SEARCH

Submitted by techrev on Fri, 03/17/2017 - 02:54
Forums

PHP MySQL CSV UPLOAD SEARCH

SQL QUERY for database creation

 

CREATE DATABASE IF NOT EXISTS freesjyh_csvmysql;
USE freesjyh_csvmysql;
DROP TABLE IF EXISTS `user`;
CREATE TABLE user
(
    id1 int(5)  NOT NULL AUTO_INCREMENT,
    name VARCHAR(50)      NOT NULL DEFAULT '',
    email VARCHAR(50) NOT NULL DEFAULT '',
    phone int(11),
    PRIMARY KEY (`email`),
    UNIQUE KEY (`id1`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

UPLOAD CSV FILE - php code

<?php
//ini_set('display_errors', 1);
error_reporting(E_ALL);
$db_host = 'localhost';
$db_user = 'freesjyh_csvmysql';
$db_pwd = 'unda1234';

$database = 'freesjyh_csvmysql';
$table = 'freesjyh_csvmysql';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");


    if(isset($_POST['submit']))
    {
         $fname = $_FILES['sel_file']['name'];
         echo 'upload file name: '.$fname.' ';
         $chk_ext = explode(".",$fname);
       
         if(strtolower(end($chk_ext)) == "csv")
         {
       
             $filename = $_FILES['sel_file']['tmp_name'];
             $handle = fopen($filename, "r");
      
             while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
             {
                $sql = "INSERT into user(name,email,phone) values('$data[0]','$data[1]','$data[2]') ON DUPLICATE KEY UPDATE name = '$data[0]',email = '$data[1]',phone = '$data[2]'";


                mysql_query($sql) or die(mysql_error());
             }
      
             fclose($handle);
             echo "Successfully Imported";
           
         }
         else
         {
             echo "Invalid File";
         }  
    }
   
    ?>
    <h1>UPLOAD PROJECT CSV file</h1>
    <form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post' enctype="multipart/form-data">
        Import File : <input type='file' name='sel_file' size='20'>
        <input type='submit' name='submit' value='submit'>
    </form>

 

SEARCH FOR A STRING IN UPLOADED CSV (from SQL)

 

<HTML>

 


CODE SCANNER SEARCH

<body>

<form action="" method="post">

  <input name="search" type="search" autofocus><input type="submit" name="button">

</form>

<table>
  <tr><td><b>First Name</td><td></td><td><b>Last Name</td></tr>

<?php

$con=mysql_connect('localhost', 'freesjyh_csvmysql', 'unda1234');
$db=mysql_select_db('freesjyh_csvmysql');


if(isset($_POST['button'])){    //trigger button click

  $search=$_POST['search'];

  $query=mysql_query("select * from user where name like '%{$search}%' ");

if (mysql_num_rows($query) > 0) {
  while ($row = mysql_fetch_array($query)) {
    echo "<tr><td>".$row['email ']."</td><td></td><td>".$row['phone']."</td></tr>";
  }
}else{
    echo "No employee Found<br><br>";
  }

}else{                          //while not in use of search  returns all the values
  $query=mysql_query("select * from user");

  while ($row = mysql_fetch_array($query)) {
    echo "<tr><td>".$row['email ']."</td><td></td><td>".$row['phone']."</td></tr>";
  }
}

mysql_close();
?>
</HTML>