Author: Editor

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
JavaScript Web Development

jQuery Troubleshooting

I recently spent a freat deal of time trying to get jQuery to run, with no success.

It all ended with the simple step of moving the loading of the jQuery below other javascript libraries in my <head> section.  Clearly there was a conflict that prevented my jQuery library from being accessed.

Benefit from my aggravating experience and save yourself some time!

Categories
JavaScript PHP Web Development

Data Validation

Some quick references here to data validation resources.  Many or most of these resources will refer to the Validate plugin for jQuery.

Server Side

http://phpmaster.com/form-validation-with-php/

Client Side

Much of client side data validation will center around jQuery.

Tutorials:

Custom Rules

Conditional Validation

Notes

To test if jQuery library is loaded, place the following in the HEAD of your document:

<script type="text/javascript">// <![CDATA[
  $().ready(function() {
    alert("HELLO");
  });
// ]]></script>

 

Categories
MySQL PHP

Access Column names in MySQL

I recently needed to create a page that would serve as a light admin page for my configuration table.  Just read in the table and let me edit the values. 

However, the field names serve as lables, and sometimes they don't have all the information needed on now the attribute is used, so I maintain more info in the comment for the field.  (The optional COMMENT is part of the meta data for the field in MySQL.)

So I wanted to capture the field comments into an array so that I could place them beside the edit control for each attribute.

Here's my solution:

1
2
3
4
5
6
7
8
9
10
11
12
13

// -------------------- capture column comments into array ---------------
$tablename = "configuration";
$meta_to_capture = array('Field','Comment');
$result = mysql_query("SHOW FULL COLUMNS FROM $tablename");
$row = mysql_fetch_assoc($result);
while ($row = mysql_fetch_assoc($result)) {
  foreach($row as $var => $value){
    if (in_array($var,$meta_to_capture)) {
      $field_comment[$row['Field']] = $row['Comment'];
    }
  }
}
// -------------------- /capture column comments into array --------------

To access it, simply use the following syntax:

$comment = $field_comment['attribute_name'];
Categories
PHP

Random String or Password Generator

The following function returns a random string of the length passed to the function, with a default length of 8 if none is passed.

If you look at the $chars string that supplied the character list for generating the string, you'll see that I've eliminated easily confused characters such as the zero and upper case "O", and the number "1" and lower case "l". This reduces support calls when users have to type the string in manually, as in when it is used as a temporary password.

I use this for generating temporary passwords when people are resetting lost passwords on a website and in other uses where I need a random string.

Where security is a significant concern, you may want to research random string generation more thoroughly.

function GeneratePassword($length = 8) {
  $chars = '2346789abcdefghjkmnprtuvwxyz';
  for ($p = 0; $p < $length; $p++) {
    $result .= $chars[mt_rand(0, 27)];
  }
  return $result;
}