Category: Snippet

Categories
Database MySQL Snippet

Exporting MySQL Data to a .csv File

Sometimes you'll need to move data out of your database into a spreadsheet or other application to work with or to send to someone else.

The syntax below, not entirely common, will create a comma separated file at the disk location indicated. The example below refers to a windows system, adapt as appropriate for other operating systems.

Double-click on the file and it will open in Excel or any other application associated with the .csv extension.

SELECT lname,fname,address,email
INTO OUTFILE 'd:/CurrentCustomers.csv'
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
LINES
  TERMINATED BY '\n'
FROM customers
WHERE STATUS = 'current'
ORDER BY lname,fname;

A dynamic outfile name is another idea, as this statement will not overwrite an existing outfile.  An article on the  MySQL discussion board covers this, and while it does not provide a perfect answer, is points in the direction of a solution involving the PREPARE statement.

Categories
MySQL PHP Snippet

Quick and Dirty: List records in a table using PHP and MySQL

This code lists reads a dataset from the database and lists all attributes in a table. 

This code sample is deliberately quite basic, though CSS is used (through the $bgcolour variable) to create alternating background colours for the rows.

The code assumes you have already connected to a database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

echo "<table border='0'>";
// capture the data
$dataset = mysql_query("SELECT * FROM tablename");
// now process the dataset, one row at a time
while ($row = mysql_fetch_assoc($dataset)) {
  // create a variable for each attribute in the record
  //  - variables are easier to use when building the table 
  foreach($row as $var => $value) {
    $$var = $value;
  }
  // set the alternating background colour for the row
  $bgcolour = (!isset($bgcolour) || $bgcolour == "#F0F7FF") ? "#FFFFFF" : "#F0F7FF";
  // build the rows, placing the variables in <td> elements as appropriate
  echo "\n<tr style="background-color:$bgcolour;">";
  echo "\n<td style="font-size:80%;">$attribute</td>";
  echo "\n</tr>";
} // end while
echo "</table>";