import csv data to mysql using php

We can import data of CSV into MYSQL via PHP using fgetcsv() function along with some file handling functions. These codes help you to import data from csv file using PHP.

Create Table in bellow formate

CREATE TABLE `users` (

  `id` int(11) NOT NULL auto_increment,

  `first` text,

  `middle` text,

  `last` text,

  `email` text,

  PRIMARY KEY  (`id`)

);

Save Bellow Code in import.php file

<\?php

$con = mysql_connect("HostName", "DataBaseUserName", "DataBasePassword");

mysql_select_db("DataBaseName");

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

{

      $fileExt = $_FILES['importData']['name'];

      $chkExt = explode(".",$fileExt);

      if(strtolower($chkExt[1]) == "csv")

      {

            $fileName = $_FILES['importData']['tmp_name'];

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

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

            {

                   $sql = "INSERT into users(first, middle, last, email) values('".addslashes($data[0])."', '".addslashes($data[1])."', '".addslashes($data[2])."', '".addslashes($data[3])."')";

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

            }

            fclose($handle);

            echo "Successfully Imported";

      }else{

            echo "Invalid File";

      }

}

?>

<\form action='' method='post' enctype="multipart/form-data">

                Import File : <\input type="file" name='importData' size='20'>

                <\input type='submit' name='submit' value='Submit'>

<\/form>


Note:- Remove "\" from html tags and php opening tags

birth date to year calculation in php

Note:- If you want to change formate of date for example YYYY-MM-DD to MM-DD-YYYY then change in list() function parametter and pass same formate parameter end of birthday function.

<\?php
function birthday($birthday)
{
    list($year,$month,$day) = explode("-",$birthday);

    //list($day,$month,$year) = explode("-",$birthday);

    $year_diff = date("Y") - $year;
    $month_diff = date("m") - $month;
    $day_diff = date("d") - $day;
   
    if ($month_diff < 0)
    {
        $year_diff--;
    }
    elseif (($month_diff==0) && ($day_diff < 0))
    {
        $year_diff--;
    }
    return $year_diff;
}

//echo birthday("YYYY-MM-DD");

echo birthday("1982-11-10");

//echo birthday("11-01-1985");
?>

multidimensional array sorting in php

Note:- In this post I am sorting multidimensional array by first name if you want to sort by other field then remove first_name and write other field in
array_sort_by_column($people, 'first_name');
Note :- Remove "\" from all places I have added "\" because blogger posting not supported html tags and php opening tag

<\?php

$people = array(
    1 => array(
        'id' => 1,
        'first_name' => 'Kamran',
        'surname' => 'Ahmad',
        'age' => 25,
        'sex' => 'm'
    ),
    2 => array(
        'id' => 2,
        'first_name' => 'Adam',
        'surname' => 'Smith',
        'age' => 18,
        'sex' => 'm'
    ),
    3 => array(
        'id' => 3,
        'first_name' => 'Joe',
        'surname' => 'Bloggs',
        'age' => 23,
        'sex' => 'm'
    ),
    4 => array(
        'id' => 4,
        'first_name' => 'Amy',
        'surname' => 'Jones',
        'age' => 21,
        'sex' => 'f'
    )
);

function array_sort_by_column(&$arr, $col, $dir = SORT_ASC) {
    $sort_col = array();
    foreach ($arr as $key=> $row) {
        $sort_col[$key] = $row[$col];
    }

    array_multisort($sort_col, $dir, $arr);
}

array_sort_by_column($people, 'first_name');
?>
<\table width="500px" cellpadding="0" cellspacing="0" border="1">
    <\tr>
        <\td height="30px" align="center"><\b>ID<\/td>
        <\td height="30px" align="center"><\b>First name<\/b><\/td>
        <\td height="30px" align="center"><\b>Surname<\/b><\/td>
        <\td height="30px" align="center"><\b>Age<\/b><\/td>
        <\td height="30px" align="center"><\b>Sex<\/b><\/td>
  
foreach($people as $peo)
{
    echo "<\tr>";
    echo "<\td height='30px' align='center'>".$peo['id']."<\/td>";
    echo "<\td height='30px' align='center'>".$peo['first_name']."<\/td>";
    echo "<\td height='30px' align='center'>".$peo['surname']."<\/td>";
    echo "<\td height='30px' align='center'>".$peo['age']."<\/td>";
    echo "<\td height='30px' align='center'>".$peo['sex']."<\/td>";
    echo "<\/tr>";
}

?>

<\/table>

export data from mysql database table in csv file using php

In this post we export data from mysql database table in csv file formate using php.

Note : Save bellow file as name  csvfile.php

<\?php
$host = 'localhost'; // MySql DataBase Host Name
$db = 'databasename'; // MySql DataBase Name
$user = 'root'; // MySql DataBase UserName
$pass = ''; // MySql DataBase Password

// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);

require 'exportcsv.php';
$table="TableName"; // Table Name that you want to export in csv file from mysql database.

exportMysqlToCsv($table);
?>



Note: Save bellow file as name  exportcsv.php



<\?php
function exportMysqlToCsv($table, $filename = 'csvfilename.csv')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";

    $sql_query = "select * from $table";
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);
  
    $schema_insert = '';
    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for
    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                }
                else
                {
                    $schema_insert .= $csv_enclosed .
                    str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            }
            else
            {
                $schema_insert .= '';
            }
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;
}
?>

url rewriting using .htaccess

URL Rewriting using .htaccess

Here is some example of URL Rewriting. If you are searching for URL Rewriting the bellow code help you for rewriting. In this post we are rewrite URL though .htaccess file. Bellow is some example for URL Rewriting using .htaccess file.

1) Redirecting non www URL to www URL

If we type on browser’s address bar domainname.com it will be redirected to www.domainname.com.

RewriteEngine On
RewriteCond %{HTTP_HOST} ^domainname\.com$
RewriteRule (.*) http://www.domainname.com/$1 [R=301,L]

2) Rewriting filename.php?id=111 to filename-111.html
Example : profile.php?id=111 to profile-111.html

It’s simple redirection rule in which php extension is hidden from browser address bar and generate dynamic url converted into a static URL.

RewriteEngine on
RewriteRule ^profile-([0-9]+)\.html$ profile.php?id=$1
Note: containing “?” character

3) Rewriting filename.php?id=111 to filename/name/111.html
Example: profile.php?id=111 to profile/kamran/111.html

In the following URL rewriting technique you can display the name of the profile in URL.

RewriteEngine on
RewriteRule ^profile/([a-zA-Z0-9_-]+)/([0-9]+)\.html$ profile.php?id=$2

4) Rewriting http://www.domainname.com/profile.php?username=abc to http://www.domainname.com/abc
Have you seen twitter.com or facebook.com. If you type http://twitter.com/ekamraan or http://facebook.com/ekamran in browser you can see my profile over there. If you want to do same kind of redirection i.e http://domainname.com/profile.php?username=abc to http://domainname.com/xyz then you need to add the following code to the .htaccess file.
RewriteEngine On
RewriteRule ^([a-zA-Z0-9_-]+)$ profile.php?username=$1
RewriteRule ^([a-zA-Z0-9_-]+)/$ profile.php?username=$1

5) Redirecting the domainname to a new subfolder of inside www or public_html.

If you have redeveloped your site and all the new development of site in "newfolder” of inside root folder. Then the new development of the site can be access like “http://www.domainname.com/newfolder”. Moving these files to the root newfolder can be a engaged process so you can use bellow code inside the .htaccess file and upload it under the root folder of the site. www.domainname.com point out to the files inside “newfolder”.

RewriteEngine On
RewriteCond %{HTTP_HOST} ^domainname\.com$ [OR]
RewriteCond %{HTTP_HOST} ^www\.domainname\.com$
RewriteCond %{REQUEST_URI} !^/newfolder/
RewriteRule (.*) /newfolder/$1