Category: MySQL

Automate Db Model Creation with Zend_CodeGenerator_Php_Class

I’m working on a new tool at work that will automate several processes for a few employees so they don’t have to spend too much time doing very repetitive tasks. This tool has to do a good bit of database manipulation so I’ve decided I’ll build it in PHP using Zend Framework.

I’ll be using Zend_Db_Table_Abstract to communicate with the db tables from my project and I’ll be creating a model for each table as well to store and manipulate data. I’ll be working with lots of tables in the database and many have lots of fields.

I start by opening up Zend Studio on one monitor and SQL Query Analyzer on the other and get to work. The first table I want to work with is the ‘Student’ table. I create a new file in my project called Student.php. Place it on my models/DbTable folder and inside I simply have to declare ‘_name’ as a protected property with the value ‘Student’ and extend ‘Zend_Db_Table_Abstract’. Easy enough but now I want to create the model I will be using to convert the database data into workable objects through my mapper class.

Problem

I create a new file called ‘Student.php’ and save it to my models folder. I open the file up and now I have to create a property (it’s actually an array _data with all properties defined as keys inside) for each field in the Student table… all 50 of them! I have to be careful to name each correctly as well as to not accidentally miss some field. It ends up being a time consuming process and inefficient so I start looking for a better way to accomplish this. Continue reading “Automate Db Model Creation with Zend_CodeGenerator_Php_Class”

Caching using PHP/Zend_Cache and MySQL

I like the definition used in Wikipedia: “a cache is a temporary storage area where often accessed data can be stored for quick access”. The idea is to get ‘often accessed data’ from a database and store it in memory (RAM or as a file in your local file system). This is because:

  • it’s quicker for a machine to read from memory than to connect to a database and query data.
  • it’s more efficient for the database to not waste time and resources returning the same dataset multiple times when it could be focusing on other tasks.

As long as the data, in this scenario from the database, doesn’t change, there is no need to query it again.

Resources are limited on systems and to take advantage of your resources, you need to make sure time isn’t spent on tasks that could be handled better elsewhere. Here is a silly real world example. Imagine on a daily basis, I have to track how many magazines I have and send this information to Person X. I get new magazines at the beginning of each month only. To track the number of magazines I have every day I could

  1. Count them, one by one every day and send Person X the total. If I have 50 magazines this could take some time and assume I get 10 more every month, after a year or two I could spend all day just counting how many magazines I have instead of working. Sound productive?
  2. Count them once and write the number down on a piece of paper (caching!). Everyday when Person X asks how many magazines I have, I read the number from the piece of paper. Only when I get new magazines (once a month) do I count them again (or just add the current number + the new amount) to get my new total. Then I update my piece of paper with the new total (updating the value in cache).

The latter is definitely the more productive choice.

The same idea applies to computer systems. In the web, you have static and dynamic files. Static files are quicker to serve on a server because the server only has to read the contents of the file and send it to the browser requesting it. Dynamic pages take more time and resources because the server needs to execute the code in the page and only once it’s done can it send the request back. PHP can be used to create dynamic pages. The server executes the php code and spits out a file that then is read by the browser. If a database is involved, then the database has to run it’s task as well before the final file is returned.

When ever possible, it’s more efficient to serve a static file or static content. We use cache to accomplish this. In this post I’m going to talk about caching files and database queries to local files on the server. Continue reading “Caching using PHP/Zend_Cache and MySQL”

SQLSTATE[HY000]: General error – using php/pdo mysql stored procedures (sp)

I spent many hours last night trying to figure out why I was getting a fatal error: ‘SQLSTATE[HY000]: General error 0’ in my code and finally figured it out but first my environment. I’m using Zend Server CE running php 5.3.0, zend framework 1.9.0 and mysql 5.1.32. I should have tested this bug without the zend framework to make sure it’s not specific to zf (I don’t think it is) but I’m feeling lazy so I’ll let someone else try it out.

My code works as follows. I have a php class that calls a stored procedure which will take in an id, return a record set (if found) and will also return 2 out variables. While it was returning a record set everything was working perfectly fine. When I tried passing an invalid id, nothing was being returned and my code would keep giving me the ‘SQLSTATE[HY000]: General error 0’ (very helpful error indeed…).

The issue ended up being the way I had my stored procedure coded. I would first check to see if the id passed was valid, if so I would select the data else I would set my out vars to some value and do nothing else. For some reason, because I wasn’t returning a select, my code would blow up. In the mysql query browser, my stored procedure worked fine and my second select to get the out vars was working correctly. But php didn’t like it one bit. I tried forcing a select in my stored procedure in the invalid id section and then everything worked fine again. This sounds a bit confusing so here is the way I can replicate this. Continue reading “SQLSTATE[HY000]: General error – using php/pdo mysql stored procedures (sp)”

Creating RSVP in PHP/MySQL w/ Zend Framework

This post is to share the php/zend framework code I used to create an rsvp for my wedding site. I’m not going into all the details since that would take too long ;p but all the code is available if you want to use it. This was created using the zend framework version 1.7.

So I’m getting married in two month and for our wedding my fiancee and I decided to create a website for our guests. The site includes information such as location, time, links to registries, maps, and a section to rsvp. The site was made by my fiancee in html and css. When she was done, I ported it over to zend framework and started creating the rsvp section which I’ll describe next. You can view the finished wedding site here:

RSVP Page

http://www.joeyrivera.com/wedding

Continue reading “Creating RSVP in PHP/MySQL w/ Zend Framework”

Using MySQL Stored Procedure IN/OUT and Recordset w/ PHP

Note: The code below will not work on all environments. I’m using php 5.2.6 with mysql driver 5.0.18.

In a previous post:
http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/

I explained how to use MySQL stored procedures with different db adapters such as mysql, mysqli, and pdo in PHP. In those examples, I demonstrated calling a sp with in/out variables and calling a stored procedure that returns a recordset. One of the comments I received was a person asking how to call a stored procedure that uses in/out parameters as well as returns a recordset. It’s not much different and here’s how.

The trick is to combine both methods in one. Here’s an example of what the stored procedure looks like:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_users` $$
CREATE PROCEDURE `get_users`(
IN firstName VARCHAR(100),
OUT totalUsers INT
)
BEGIN
SELECT COUNT(*)
INTO totalUsers
FROM users
WHERE first_name = firstName;
SELECT *
FROM users
WHERE first_name = firstName;
END $$
DELIMITER ;

Continue reading “Using MySQL Stored Procedure IN/OUT and Recordset w/ PHP”

Using MySQL Stored Procedures with PHP mysql/mysqli/pdo

Wondering how to use stored procedures with PHP and MySQL? So was I and here’s what I’ve learned. In this tutorial I’ll explain how to use PHP (I’m using 5.2.6) to call MySQL (I’m using 5.0.2) stored procedures using the following database extensions:

First we need to setup our enviroment which consists of a new database with one table and two stored procedures. In your db tool of choice (I’ll be using the MySQL Query Browser) create a new database named test. After you create the new database, make sure to add a user called example with password example to the database and give it read access.

CREATE DATABASE `test`;

Now create the table users:

DROP TABLE IF EXISTS `test`.`users`;
CREATE TABLE  `test`.`users` (
`users_id` int(10) unsigned NOT NULL auto_increment,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY  (`users_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Continue reading “Using MySQL Stored Procedures with PHP mysql/mysqli/pdo”