Db has been used in a number of my projects, the main focus of this class is to simplify the overall process of prepared statements. As this was migrated into my work place need for docs and log functions was created.

Db is a connection manager, it will use the connection you first create throughout your application. You can continue to call Db:init() and as long as a connection has already been made, you can run queries. Best of all It has prepared statements thanks to Wrapping MySQLi. For documentation and download please visit Gorilla Labs

 
$db = Db::init('localhost', 'root', 'pass', 'clients');
 
//-- Prepared Statements
$rows = $db->query('SELECT * FROM blog_entries WHERE pub_date < ?', array('2008-04-01'));
 
echo 'Number of result: ' . $db->count();
echo '<br/>';
 
echo 'SQL executed: ' . $db->debug();
echo '<br/>';
 
$rows = $db->fetchAll();
/* Output:
 * Number of result: 22
 * SQL executed: SELECT * FROM blog_entries WHERE pub_date < '2008-04-01'
 */
 

0 comments

At work there is a growing need to move into pdf creation, since we generate all of our invoices via HTML. Some clients need precision on form creation. PDF offers that but Zend_Pdf lib is very limited. So I implemented text-wrapper and table class/functions to help guide me. Its far from done, but a good start to blog about it.

download Gorilla_Pdf file

 
$pdf = new Zend_Pdf();
$page = new Gorilla_Pdf_Page(Zend_Pdf_Page::SIZE_LETTER);
$font = Zend_Pdf_Font::fontWithName(Zend_Pdf_Font::FONT_COURIER); 
 
//-- Header
$page->setFont($font, 24);
$page->drawMultilineText(array('Purchase Order'), 390, 48);
 
$page->setFont($font, 10);
$page->setLineWidth(0.5);
$page->drawMultilineText(array('Szzzzice', '9zzzze', 'Szzzzzzzzzzzz26', 'Phone 8555-888-5  Fax 855-4-5554'), 33, 61);
 
$page->drawInfoBox('Date', array('2/29/2008'), 420, 61, 75, 45); // Header Text, Body Text, X, Y, Width, Height
$page->drawInfoBox('PO #', array('000032'), 495, 61, 75, 45);
 
$page->drawInfoBox('Vendor', array('EMC', '3464 Blaire Dr.', 'San Deigo, CA 92081'), 33, 130, 248, 109);
$page->drawInfoBox('Ship To', array('Billy Bob', '453 Wonker Rd.', 'San Deigo, CA 92081'), 320, 130, 248, 109);
 
//-- Draw inventory items
$table = new Gorilla_Pdf_Table($page, 33, 300); // $pdf_page, start x, start y
 
//-- Output the table header
$row = new Gorilla_Pdf_Table_Row();
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(26)->setText('Qty');
$row->addColumn($col);
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(93)->setText('Item');
$row->addColumn($col);
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(183)->setText('Description');
$row->addColumn($col);
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(93)->setText('Customer');
$row->addColumn($col);
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(49)->setText('Rate');
$row->addColumn($col);
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(65)->setText('Amount');
$row->addColumn($col);
$table->addRow($row);
 
//-- Output the inventory
foreach(range(0, 15) as $i) {
	$row = new Gorilla_Pdf_Table_Row();
		$col = new Gorilla_Pdf_Table_Column();
		$col->setWidth(26)->setText(strval($i)); // Qty
	$row->addColumn($col);
		$col = new Gorilla_Pdf_Table_Column();
		$col->setWidth(93)->setText('MAY-ULEX')->setAlignment('center'); // Inventory Code
	$row->addColumn($col);
		$col = new Gorilla_Pdf_Table_Column();
		$col->setWidth(183)->setText('Freedom Task Chair- with Standard Gel Armrests with Matching Textile Cover'); // Inventory Description
	$row->addColumn($col);
		$col = new Gorilla_Pdf_Table_Column();
		$col->setWidth(93)->setText('Billy Bob'); //-- Customer
	$row->addColumn($col);
		$col = new Gorilla_Pdf_Table_Column();
		$col->setWidth(49)->setText('64.00'); // -- Each Price
	$row->addColumn($col);
		$col = new Gorilla_Pdf_Table_Column();
		$col->setWidth(65)->setText(number_format(64 * $i, 2))->setAlignment('right');
	$row->addColumn($col);
	$table->addRow($row);
}
 
//-- Output the table footer
$row = new Gorilla_Pdf_Table_Row();
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(468)->setText('Total')->setAlignment('right');
$row->addColumn($col);
	$col = new Gorilla_Pdf_Table_Column();
	$col->setWidth(65)->setText(number_format(64 * 16, 2))->setAlignment('right');
$row->addColumn($col);
$table->addRow($row);
 
//-- Render the table to the pages/s
$pages = $table->render();
 
//-- If the table overflows onto a new page, they are created
$pdf->pages[] = $page;
foreach($pages as $page) {
	$pdf->pages[] = $page;
}
 
$filename = rand().'.pdf';
header('Content-type: application/pdf');
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); 
echo $pdf->render();
 

3 comments

A 14 minute - 2 part intro to submiting forms via php to email.




Source code:
contactus.zip

HD Videos:
Part 1 - download
Part 2 - download

0 comments

An intro to basic profiling for your php scripts, using php's microtime(). This is the FIRST video encoded in both 1024x768 and 320x240, watch the 320x240 stream on youtube or download the thoera/mp4 edition.


Watch this screen casting at 1024x768:
-- Php Execution Time - Theora ~ 5 megabytes
-- Php Execution Time - Mp4 avi ~ 26 megabytes

Free Php Debugger:
http://www.xdebug.org/



0 comments

In this entry will be dealing the following dataset:
geoip.sql ~ 94k rows | 7.8 MB

Figure 1. Shows the typical random MySQL random row Selection. On my server I ran 1000 iterations coming to about 0.6 seconds average in response time. The script took over 10 mins to execute 1000 random rows. Thats is awful performance and mean that if your site has more then 1 queries per second your database would crawl to a halt.

Figure 1. ~ 0.6 seconds

<?php
//-- Mysql Database Config
$db_host = 'localhost';
$db_db   = 'mysite';
$db_user = 'root';
$db_pass = '';
 
//-- Mysql Connection
$db_link = mysql_connect($db_host, $db_user, $db_pass)
	or die('MySQL Connection Error:'.mysql_error());
mysql_select_db($db_db)
	or die('MySQL Error: Cannot select table');
//-- Fetch a random row
$sql = "SELECT * FROM `csv` ORDER BY RAND() LIMIT 1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
?>

Figure 2. Shows an alternative method yields 1000 iterations coming to about 0.001 seconds average in response time. Meaning your database can now withstand 1000 queries per second.

Figure 2. ~ 0.001 seconds

<?php
//-- Mysql Database Config
$db_host = 'localhost';
$db_db   = 'mysite';
$db_user = 'root';
$db_pass = '';
 
//-- Mysql Connection
$db_link = mysql_connect($db_host, $db_user, $db_pass)
	or die('MySQL Connection Error:'.mysql_error());
mysql_select_db($db_db)
	or die('MySQL Error: Cannot select table');
 
//-- Find the largest ID
$sql = "SELECT * FROM `csv` ORDER BY `id` DESC LIMIT 1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
//-- Fetch a random row
$max = intval($row['id']);
$sql = "SELECT * FROM `csv` WHERE `id` = ". rand(1, $max) ." LIMIT 1";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
?>

Now what if your database has large section of missing ids (They skip in range, i.e 1, 3, 7)? Well look at Figure 3. This is an example of just looping the random selection based on the same code as Figure 2. The performance on this will very as it could be the 1st result that yields a row or the 100th, none of the less it still a far better alternative result then using MySQL's built in RAND() function to fetch random rows.

Figure 3. ~ N/A

<?php
// Mysql Database Config
$db_host = 'localhost';
$db_db   = 'mysite';
$db_user = 'root';
$db_pass = '';
 
// Mysql Connection
$db_link = mysql_connect($db_host, $db_user, $db_pass)
	or die('MySQL Connection Error:'.mysql_error());
mysql_select_db($db_db)
	or die('MySQL Error: Cannot select table');
 
//-- Find the largest ID
$sql = "SELECT * FROM `csv` ORDER BY `id` DESC LIMIT 1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$max = intval($row['id']);
$result = false;
//-- Query a random row until we get a result
while(!$result) {
  $sql = "SELECT * FROM `csv` WHERE `id` = ". rand(1, $max) ." LIMIT 1";
  $result = mysql_query($sql) or die(mysql_error());
}
$row = mysql_fetch_assoc($result);
?>

Now if your looking for a pure Mysql method use a subquery, this will allow you to use Mysql to still generate random ids. If you look at Figure 4. the subquery is which just totals the number of rows within our database. Then the table's id is matched against php's random function lcg_value() which only generates 0 - 1 numbers, to fix that we just multiply that against our subquery's 'total'. So in the end what did 1000 iterations prove? Well I got 0.0005 seconds average per query, as you can see even faster then in Figure 2.

Firgure 4. ~ 0.0005 seconds ^{by Joe}

<?php
// Mysql Database Config
$db_host = 'localhost';
$db_db   = 'mysite';
$db_user = 'root';
$db_pass = '';
 
// Mysql Connection
$db_link = mysql_connect($db_host, $db_user, $db_pass)
	or die('MySQL Connection Error:'.mysql_error());
mysql_select_db($db_db)
	or die('MySQL Error: Cannot select table');
$sql = "SELECT * FROM csv WHERE id = ROUND(".lcg_value()." * (SELECT COUNT(*) FROM csv)) LIMIT 1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
?>

So when do you use RAND()?

For a single selection RAND() is just slow but when you need to use WHERE to filter your random selections as well as selection MANY random rows, RAND is your best best. Figure 5. & Figure 6. prove that point RAND() is just what to use when you need to filter your results. Now if you have a table that does not change very often and you are using the same filter over and over, Figure 6. would be your best best, this allows you to reuse the temp table to query rand rows much faster.

Figure 5. - Rand() ~ 0.65 seconds

//-- Mysql Database Config
$db_host = 'localhost';
$db_db   = 'mysite';
$db_user = 'root';
$db_pass = '';
//-- Mysql Connection
$db_link = mysql_connect($db_host, $db_user, $db_pass)
	or die('MySQL Connection Error:'.mysql_error());
mysql_select_db($db_db)
	or die('MySQL Error: Cannot select table');
//-- FINALLY query for our random rows w00t
$sql = "SELECT * FROM csv WHERE cn = 'Germany' ORDER BY RAND() LIMIT 50";
$result = mysql_query($sql) or die(mysql_error().'<br>'.$sql);
while($row = mysql_fetch_assoc($result)) {
  $row;
}

Figure 6. - Temp Table ~ 1.1 seconds / ~ 0.01 seconds

//-- Mysql Database Config
$db_host = 'localhost';
$db_db   = 'mysite';
$db_user = 'root';
$db_pass = '';
//-- Mysql Connection
$db_link = mysql_connect($db_host, $db_user, $db_pass)
	or die('MySQL Connection Error:'.mysql_error());
mysql_select_db($db_db)
	or die('MySQL Error: Cannot select table');
 
//-- Create a temp table, with our WHERE filters
$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS csvTemp (
    temp_id INT(10) unsigned NOT NULL auto_increment,
    PRIMARY KEY (temp_id)
    )
  SELECT * FROM csv 
  WHERE cn = 'Germany'";
mysql_query($sql) or die(mysql_error().'<br>'.$sql);
 
//-- Fetch the total row count (we cant join/subquery the same temp table)
$sql = "SELECT COUNT(*) AS total FROM csvTemp";
$result = mysql_query($sql) or die(mysql_error().'<br>'.$sql);
$row = mysql_fetch_assoc($result);
$total = intval($row['total']);
 
//-- Generate our random temp_ids
$rands = array();
foreach(range(1, 50) as $n) { // Select 50 random ids
  $rands[] = intval(lcg_value() * $total);
}
 
//-- FINALLY query for our random rows w00t
$sql = "SELECT * FROM csvTemp WHERE temp_id IN (".implode(', ', $rands).")";
$result = mysql_query($sql) or die(mysql_error().'<br>'.$sql);
while($row = mysql_fetch_assoc($result)) {
  $row;
}

If you know better methods, please by all means leave a comment and share your code. You can use syntax highlighting as above by encasing your code in BBcode style tags. i.e

10 comments