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.
Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts
Wednesday, March 13, 2013
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.
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:
The syntax of REPLACE function is as follows:
Subscribe to:
Posts (Atom)