PHP MIDI

Years ago I discovered someone’s libraries to convert .mid music files to text by means of PHP. Since PHP is also a strong text parsing language, I can imagine how to utilize this to possibly create some randomized decent music. But it is a bit complex. Here is a link to a page where it seems the author is doing the most to maintain the PHP MIDI library. Posted in PHP | Leave a reply

The XmlHttpRequest

How to do an AHAH / AJAX Request.

It’s really quite simple, but broken down in to several parts. First, we need a cross browser solution to the ability to making an xmlhttprequest variable on demand. This seems to be all that is really needed:

function createXMLHttpRequest() {
   try { return new XMLHttpRequest(); } catch(e) {}
   try { return new ActiveXObject("Msxml2.XMLHTTP"); } catch (e) {}
   alert("XMLHttpRequest not supported");
   return null;
}

Next we need to understand that httprequests take a moment to ping over a message and pong return back a message… It would be nice to say we are talking in speeds of milliseconds, and at some times we are, but I’ve seen the delay occasionally seem to take as long as about 7 seconds to complete. I’ve used this for a clock displaying seconds and this strategy seemed to work fine. This call/delay is the nature of httprequest, vs. direct connection by socket (which we won’t be getting in to). It is not a direct socket connection, however XMLHTTPRequest does work fast and reliably enough to create many applications.

Now that we have our function to make new XMLHTTPRequest objects on the fly, let’s use it, and meanwhile make a global variable we can utilize for status notification of “currently fetching info”.

var xhDaynote = createXMLHttpRequest();
var oncall=0;

Now, however you’d like to… if it’s onclick of a button or onmouseover of a div or other document element, or something persistent using setInterval, you want to create a function you can call to send the request. Again this app is about sending a note from a form textarea form element to a server side PHP script for saving.

function readNote()
{
	if(oncallNote==0)
	{
		oncallNote=1;  // set our status of fething to busy
		xhDaynote.open("GET", "/index.php?pg=timeclock&task=writeNote&daynote="+escape(document.getElementById('daynote').value), true);
		xhDaynote.onreadystatechange = xhDaynoteFolowup;
		xhDaynote.send(null);
	}
}

So, first thing to notice is the if condition, if passed immediately will flip the boolean value of to a “busy status”. We use the xh object we created to open an url by httprequest. The key thing to notice is that when there is response back, a function which is passed no arguments is called, in this scenario it is a function named xhDaynoteFollowup. This is where your “after-the-fact” procedural commands fall, and we will ultimately set the variable used for “busy-fetching” status back to a status of being free to call out for response again.

function xhDaynoteFolowup()
{
	if(xhDaynote.readyState != 4) { return ; }
 	var DaynoteServerResponse = xhDaynote.responseText;
	alert(DaynoteServerResponse);
	oncallNote=0;
	xhDaynote.send(null);
}

In the above example… the first line just say to immediately get out if anything problematic occurred, otherwise continue. The response of our server request is stored now in xhDaynote.responseText … so for the fun of it, lets assign that to another variable and alert it to the screen just to prove we could do something. You could perform any javascript you wish to here. Finally, I’m going to set the fetching-status boolean back to 0, and set the xh object we used named xhDaynote.send to null.

All this may not be the absolute most elegant, but I think it is elegant enough and significantly re-usable. Absolutely adaptable to make the Followup function perform a list of your own procedures needed.

Getting Files From a Folder and Storing References to an Array

Ok, so many times, you may have a desire for all (or some) files from a particular folder stored as an array. Here’s one way how.

<?php
if ($handle = opendir('.')) {
    while (false !== ($entry = readdir($handle))) {
        if ($entry != "." && $entry != "..") {
            echo $entry."<br/>";
        }
    }
    closedir($handle);
}
?>

So what the above code does is:
1) creates a handle to the folder named ‘.’
2) iterate through every file within the folder provided
3) As long as the filename is not ‘.’ or ‘..’ then echo the filename followed by a

4) at the end of the full iteration, properly close the handle

Hopefully this alone can inspire you to do much more. Today I need to get all .pdf files stored in one deep subfolder, store those filenames to an array, so I can sort the array alphabetically and use a loop to output links to download each .pdf document.

Here’s how to write to clean array, even alphabetize by filename;

<?php
$files = array();
if ($handle = opendir('.'))
{
    	while (false !== ($entry = readdir($handle)))
    	{
        	if ($entry != "." && $entry != "..")
        	{
            	$files[] = $entry;
        	}
    	}
    	closedir($handle);
}
sort($files);
?>

pixels to mm for when screen design goes to print design

I’ve been creating PDF files with help of the FPDF library for PHP, which is a very helpful tool. But I’ve discovered that PDF’s always seem to be at 300dpi, where the image you may want to position in it is not that same dpi. I produced these conversion functions to handle the simple math. Notice the second variable argument, dpi, is preset to 300 but can be over-ridden if you need.

<?php

function px2mm($pixels, $dpi=300)
{
	return ($pixels * 25.4) / $dpi;
}

function mm2px($mm, $dpi=300)
{
	return ($mm * $dpi) / 25.4;
}

?>

Converting Seconds to Time Format

I had a project where I wanted to initially stamp a date, and then be able to not only compute how long it has been since that moment – but also display it in a pretty english format.

<?php
// you can get the number of seconds since the epoch as simple as:
$s = date("U");
?>

Let’s say you perform this twice and record it, you can now subtract the smaller number from the larger number to know how many seconds span the difference between the two dates. Now I wanted to know that value, not in seconds, but in like number of days, number of hours, number of minutes. I used this function:

<?php
function Sec2Time($time){
  if(is_numeric($time)){
    $value = array("years"=>0, "days"=>0, "hours"=>0, "minutes"=>0, "seconds"=>0);
    if($time >= 31556926){
      $value["years"] = floor($time/31556926);
      $time = ($time%31556926);
    }
    if($time >= 86400){
      $value["days"] = floor($time/86400);
      $time = ($time%86400);
    }
    if($time >= 3600){
      $value["hours"] = floor($time/3600);
      $time = ($time%3600);
    }
    if($time >= 60){
      $value["minutes"] = floor($time/60);
      $time = ($time%60);
    }
    $value["seconds"] = floor($time);
    return (array) $value;
  }else{
    return (bool) FALSE;
  }
}
?>

Now I can use this:

<?php
$diff = abs(strtotime(date("Y-m-d H:i:s")) - strtotime($a['datetime_assigned']));
$timeArray = Sec2Time($diff);
if($timeArray['days']>0){ echo $timeArray['days']."d "; }
echo $timeArray['hours']."h ";
echo $timeArray['minutes']."m";
?>

And the above can output something like: 6d 14h 37m , which is what I wanted for my project.

Creating a new MySQL database by code

I was working on a project where I needed unlimited databases, created in an automated fashion. In order to do so, you must connect to MySQL as user root.

<?php
$clientcode = 'newdb';

mysql_close();

$link = mysql_connect('localhost', 'root', 'YOURROOTPASSWORD') or exit("Could not connect as root");
mysql_query("CREATE DATABASE ".$clientcode) or exit(mysql_error());
mysql_query("GRANT ALL ON ".$clientcode.".* TO 'regular_dbuser'@'localhost';") or exit(mysql_error());
mysql_close();

$link = mysql_connect('localhost', 'regular_dbuser', 'YOURREGULARDBUSERPASSWORD') or exit(mysql_error()." ::couldn't connect to mysql");
mysql_select_db($clientcode);
?>

Convert Address to Latitude Longitude using Google Maps API

You can do a lot of really cool things with Google Maps. It begins with understanding that every geographical position on the planet is defined as coordinates of latitude and longitude degrees, which is the angular distance north/south of equator & west/east of the prime meridian (Greenwich, England).

We’re going to use PHP cURL to query GoogleMaps for JSON data and immediately convert that to a multidimensional associative array and snag just what we want to learn (latitude/longitude for this address).

function curl_fetch($Url){
    // is cURL installed yet?
    if (!function_exists('curl_init')){
        die('Sorry cURL is not installed!');
    }
     // OK cool - then let's create a new cURL resource handle
    $ch = curl_init();
     // Now set some options (most are optional)
     // Set URL to download
    curl_setopt($ch, CURLOPT_URL, $Url);
     // Set a referer
//   curl_setopt($ch, CURLOPT_REFERER, "http://www.example.org/yay.htm");
     // User agent
//   curl_setopt($ch, CURLOPT_USERAGENT, "MozillaXYZ/1.0");
     // Include header in result? (0 = yes, 1 = no)
    curl_setopt($ch, CURLOPT_HEADER, 0);
     // Should cURL return or print out the data? (true = return, false = print)
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
     // Timeout in seconds
    curl_setopt($ch, CURLOPT_TIMEOUT, 10);
     // Download the given URL, and return output
    $output = curl_exec($ch);
     // Close the cURL resource, and free system resources
    curl_close($ch);
     return $output;
}

$addy = $_REQUEST['address'].", ".$_REQUEST['city'].", ";
if($_REQUEST['country']=="USA") { $addy .= $_REQUEST['state']; } else { $addy .= $_REQUEST['province']; }
$addy .= ", ".$_REQUEST['zip'];

$addy = stripslashes($addy);

$apicall = "http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($addy)."&sensor=false";

$ret = curl_fetch($apicall);

$decoded = json_decode($ret,true);

$lat = $decoded['results'][0]['geometry']['location']['lat'];
$lng = $decoded['results'][0]['geometry']['location']['lng'];

The code above was used after a form that received address input (capable of USA or CAN). Builds those variables in to an address string and encodes that for use by URL. cURL is used to contact maps.googleapis.com, then the returned JSON is decoded to a multidimensional associative array. After examining the array using print_r(), I was able to find the location I need in the array and write to variables $lat and $lng.

MySQL at Command Line

PHPMyAdmin is a great tool, but PHPMyAdmin is not the only way to visually create and manipulate your database information. This blog is aimed at explaining the original interface, connecting to MySQL from the console.

First you are probably going to want to login to your webhost/webserver by ssh. If you’re on windows, you are going to need a program like puTTY. Otherwise, open a terminal window and use the following:
ssh username@domain.com

If you can’t connect by SSH / TTY. Then contact your host to make sure you even have shell access privileges.

Once connected, you can connect to mysql with the following pattern.
mysql --user=YOURUSERNAME --pass=YOURPASSWORD

To see all databases, type:
show databases;

notice the semi-colon, its very important. But you’ll realize that quickly if you find yourself hung up. You can commit many queries or commands at once by separating and ending with

To quit or exit, type either:
quit
or
exit
the semi-colon is not needed to exit the program.

If you want to select a database to explore, type:
use YOURDATABASENAME;

To view the tables in this database, type:
show tables;

To view the fields and structure / architecture of any of your tables, type:
describe YOURTABLENAME;

To see the command / statement that created that table, type:
show create table YOURTABLENAME

You can run any common query you know, like:
select * from YOURTABLENAME;

If you want to export all the information of a database to a text file
mysqldump YOURDATABASENAME > YOURTEXTFILE.TXT

If you want to eliminate a field / column from a table, type:
alter table YOURTABLENAME drop column YOURCOLUMNNAME;

If you really want to clear the contents (delete every record) of a table. Faster than deleting records is to drop the table fast and re-create it, this also resets the auto_increment field placeholder to 1.
truncate table YOURTABLENAME;

If you really want to eliminate a table, the command is:
drop table YOURTABLENAME;

If you really want to drop a database, the command is:
drop database YOURDATABASENAME;

Open CSV, CSV to MySQL INSERT

The manual has a great page about opening CSV’s.
http://php.net/manual/en/function.fgetcsv.php
It’s a very good read. Here is some code I discovered in the comments section that although I haven’t yet tested, it looks very helpful.

“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.”

I had a .CSV that was more tab delimited than comma seperated, All I needed to do was change the “,” to “\t” in the code above at line 4 and line 9.

Or just incorporate this new function:

<?php
function tbd_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, "\t");
        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, "\t")) !== FALSE) {
            while (count($data)<count($columns))
                array_push($data, NULL);
            $query = "$insert_query_prefix (".join(",",quote_all_array($data)).");";
            mysql_query($query);
        }
        fclose($handle);
    }
}
?>

PHP – SQLITE3

<?php
// step 1: lets create or connect to a sqlite3 database by filename
$db = new SQLite3('game.sqlite3.db');

// this is helpful if the db ever says that it's busy, retry for 10s
$db->busyTimeout(10000);

// build a table to play with, if it doesn't exist
$q = 'CREATE TABLE IF NOT EXISTS `dt` (
  `id` INTEGER PRIMARY KEY,
  `currently` varchar(7) NOT NULL,
  `amt` int(11) NOT NULL,
  `buyPrice` float NOT NULL,
  `sellPrice` float NOT NULL,
  `dte` datetime NOT NULL,
  `wins` int(11) NOT NULL
);';

// commit the above query in a style that handles failure
if ($db->exec($q)) {
    echo "Database Connection Successful.";
} else {
    echo "DB Connection Failed.";
}

// delete record example
// $db->exec("DELETE FROM dt WHERE id=".$key);

// insert record example
// $db->exec("INSERT INTO dt (currently, amt, buyPrice, sellPrice, dte, wins) VALUES ('buying', ".$qty.", ".$buyPrice.", ".$sellPrice.", '".@date("Y-m-d H:i:s")."', 0)");

// select records example
/*
$r = $db->query("select * from dt order by id ASC");
while ($a = $r->fetchArray()) {
echo $a["id"]."\t".$a["currently"]."\t".$a['amt']."\t".$a['buyPrice']."\t".$a['sellPrice']."\n";
}
*/

// update record example
// $db->exec("UPDATE dt SET currently='buying' WHERE id=1");
?>

The code above is very basic / simplistic examples for reference purposes. I’ve learned that my CREATE TABLE statement is likely overkill, there’s no need for backticks, but more importantly sqlite3 treats every field type kind of like a BLOB type. The fastest way to make a table is probably to declare as INT then store whatever string or float you need.