Pages

Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Wednesday, March 13, 2013

mysql to csv export using php

mysql to csv export using php

The following PHP code is intendend to be used to export a MySQL table in CSV format in
order to be used with MS Excel.

Wednesday, September 5, 2012

Country list with ISO codes

This will create and then populate a MySQL table with a list of the names and # ISO 3166 codes for countries in existence as of the date below.

Wednesday, August 8, 2012

csv file to mysql table

Here is a function that accepts the path to a CSV file, and inserts all records to the given MySQL table, paying attention to the column names:

<?php
function csv_file_to_mysql_table($source_file, $target_table, $max_line_length=10000) {
    if ((
$handle = fopen("$source_file", "r")) !== FALSE) {
       
$columns = fgetcsv($handle, $max_line_length, ",");
        foreach (
$columns as &$column) {
           
$column = str_replace(".","",$column);
        }
       
$insert_query_prefix = "INSERT INTO $target_table (".join(",",$columns).")\nVALUES";
        while ((
$data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
            while (
count($data)<count($columns))
               
array_push($data, NULL);
           
$query = "$insert_query_prefix (".join(",",quote_all_array($data)).");";
           
mysql_query($query);
        }
       
fclose($handle);
    }
}

function
quote_all_array($values) {
    foreach (
$values as $key=>$value)
        if (
is_array($value))
           
$values[$key] = quote_all_array($value);
        else
           
$values[$key] = quote_all($value);
    return
$values;
}

function
quote_all($value) {
    if (
is_null($value))
        return
"NULL";

   
$value = "'" . mysql_real_escape_string($value) . "'";
    return
$value;
}
?>

This assumes that the columns in the table have exactly the same name as the columns in the CSV file, except that the dots (".") are removed. This is because MySQL column names cannot contain dots.

Wednesday, February 8, 2012

Usage of Regular Expression in MySQL

Summary: In this tutorial, you will learn how to use regular expression with SQL SELECT statement to retrieve data based on complicated patterns.

Regular expression is a powerful tool which gives you a concise and flexible way to identify strings of text, for instance characters, words or patterns of characters. As an example, you can useregular expression to find email, IP address, phone number, social security number or anything which has its own pattern. In computing world, regular expression usually refers as regex in short. Regular expression uses its own language with special syntax which can be interpreted by a regular expression processor. Regular expression is now widely use in almost platform from programming language to database world including MySQL.

Find and Replace with MySQL Replace Function

MySQL provides you a useful string function called REPLACE to allow you to replace a string in a column of a table by a new string. The REPLACE function is very handy to search and replace text, which affects multiple records such as obsolete URL, spelling mistake…
The syntax of REPLACE function is as follows: