My digital scrapbook

WishList: Own a mySQL “superduper” extension on my PHP production server

on under Software and Web DevelopmentBe the first to comment

I hope much from PHP 5 and MySQL 5 when developing a application which provide summary reports. The constraint is coming from the requirement. OK, I make the code with full PHP 5 and MySQL 5 features.

I have 3 master tables which have one to many relationship. I am looking for feature that could combine the three table, and I do INNER JOIN on those 3 tables. I should retype this as variable in PHP code, which is hard to read and I should send several bytes to MySQL Server.

Description of SQL code that I build (this is fake, i am trying to give a case):

SELECT A.User, B.Thread, B.ThreadName, C.Topic FROM myUsers AS a 
INNER JOIN myThread as B ON a.id_user = b.id_user
INNER JOIN myTopic AS C ON b.id_thread = c.id_thread;

Maybe you ask, why I don’t use asterisk near SELECT (example SELECT * FROM)? because I don’t need all fields and I want perfomance!. For queries like this the asterisk giving big impact perfomance output. Can you Imagine how long the query is? and when needed the WHERE clause also be used on the queries.

Oh yeah, you might said . Just use variables in PHP!. Heck, the matter is not that simple, I want 75% process is held on mySQL server because I need computing and processing to currency with SUM, MAX, MIN, and so on so forth. 

The purpose is PHP + HTML become the presentation layer. I would use stored procedure when knowing have to create a summary, but I was develop too far which give me effort and time to edit all mySQL command into mySQLi. I should consider to use mySQLi extension for next projects. The disadvantage to use mySQLi is run slow for some machine.

According to PHP manual, mySQLi could support for mySQL server version 4.1 and above. Which help me to use Store Procedure. I remember that mySQL have connector, because I am using it in .NET too. I visit mySQL developer website and I found no connector for mySQL 5.0.45. The stories goes on and I am looking the best way to make the process faster.

I really want to use VIEW feature in mySQL to shortening the long code like that, so I just call

SELECT * FROM myViewTable

and giving the same result as the previous query. Well, the code is finished and I use it for fun in mysql client console. I read that VIEW have limitation, so I don’t use it.

Now, the code is working good but often make the CPU work hard, it utilize around 84% – 100% only for mySQL. In some case, maximum timeout is arise. This is happen because the bootleneck between PHP engine and mySQL server, so user must wait a moment when generating report in real time. Increasing maximum time for processing and memory available for php engine is just for temporarily solution.

Is there any mySQL “superduper” extension?. One conclusion has been made:The production server will always need continuously patched with new version of connector to make the availability of the web application reached.

Another question is came up to the case, “Are LAMP ready for Enterprise and OLAP application?”.

PHP Trick to generate variables from FORM

on under Software and Web DevelopmentBe the first to comment

I hate to write code, really! hehehe … I often hand-code my code, even in Visual Studio! Heck, I don’t know why I love this style of coding. The one that I don’t code is when approching Database, I often use tools to help me generate code, just say PHPMyAdmin.

Sometime, I hate to write code especially when dealing with something called “Form Gathering” with any method like GET/POST. If I handle only one to six data, I will write it happlily ever after. I was create a webform consisting 27 inputs, yeah, 27 inputs. I assume you understand about HTML Form.

PHP is very handy programming engine and I love to make a shortcut to deal with those 27 inputs. I create a code which gathering all inputs and my PHP code create another code.

This is the code :

foreach($_POST as $key => $value) {
  echo $key.' = $_POST["'.$key.'"]; <br />';

I don’t like escaping too much so I use echo and concation operator (the dot). So what the code doing?

  • $_POST is an array and contain collections of element in form. So, we could create loop to access the individual item. If you use GET method, please change the $_POST into $_GET.
  • You may be wondering why I create $value variable which I never be use. The reason is very simple, I just want to know the “name” of form elements (input) not the value.
  • Echoing the the line to create a new code that I could copy paste into another PHP File.

Creating little tool like this often help developer increase his/her productivity. Why? because you can reuse it for next projects.

Happy Programming with PHP!. 

ASP.NET Typed Dataset Common Problem

on under Software and Web DevelopmentBe the first to comment

If you’re new to ASP.NET you will introduced to Typed Dataset, what the heck is that? It’s feature which available in Visual Studio. I assume you already understand how to create Typed Dataset.

Most of programmer tell me that Typed Dataset is very fragile to any modification. Yes, indeed it is. Why I have such conclusion? It started when I starting to use Typed Dataset. The famous “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.” error will make you desperate and force you to write a class to accesing your table. If you think it’s simpler to write your own code than automatically generated by, please do so. It will save your time a lot.

Well, some people will find the answer why it happen to their Typed Dataset. Typed Dataset saves our time to write a Data Access Layer (DAL) class, really!. I just run a wizard the voila you could use it in any database component in your Visual Studio toolbox. Okay, I admit it’s not easy when you found an error in dataset. Okay, let’s start!

First, I found that we must consistent in any SELECT command to query (TableAdapter). Let’s take simple case, you start with this SELECT command :

SELECT username, realname FROM myUsers

When you want to filter using WHERE clause for the user by userID then your query must like this :

SELECT username, realname FROM myUsers WHERE userID = @userID

Did you see what the same? yes! the field must exact like the first query when we create new TableAdapter. Why? it will save you from unnecessary error. I don’t know the technical reason, because this tool is really complex.

Secondly, stay away from Table Changes. When developing process often we add or change the column in a table, because many reasons. Often people meet the famous error that I mention above after changing the table and the corresponding DataTable/Table Adapter in Dataset. By trial and error I found several solution to this :

  1. You could always change the MaxLength on VarChar / NVarchar to restrict user when inputing data to database.
  2. If you want varchar data type to ntext / text, please user varchar(MAX) / NVarChar(MAX).  Then set the MaxLength with -1 value to allow release the length restriction. You will void the famouse error by stay using NVARCHAR(MAX) rather than NTEXT.
  3. Plan your database and the queries to heart before starting to build Typed DataSet … hehehe. What else better?

Thridly, DataType Conversion Error. The error often show like “System.InvalidCastException: Unable to cast object of type ‘System.DBNull’  to type ‘System.String’.“. How you get rid of this? don’t worry, it’s ASP.NET fault not the DataSet. You could this :

  1. Create a variable to store the data if exists, fill the variable with null.
  2. Check your field value

Example in C# :

string _realName = null;
If(!myUsers.IsRealNameNull()) _realName = myUsers.RealName;
labelRealName.Text = _realName;

myUsers is the TableAdapter, and IsRealNameNull() was automatically generated by ASP.NET.

Hopes this journal will help you out, I found many problem like this. Microsoft must fixing this Typed Dataset complexity into simpler form that human and developer understood.

Happy Programming, Folks!.

Multiple IE on same Windows Machine

on under Uncategorized • Be the first to comment

I often use Internet Explorer 7 for testing my application and always have difficulty to test in IE 6 because Microsoft only allow one browser version on the OS. I don’t want to use Virtual PC 2007 and install another virtualized Windows XP, because I need it fast and hate to wait the installation process.

I browse around and I found great site to webdeveloper who aware about IE 5.5 / 6 appreance, you could go to to download several former IE installer before IE 7 in one Installer!. You work on same machine, no need to installing VMWare or Virtual PC 2007. Take your time and try it!.

My Favorite Web Editor

on under Software and Web DevelopmentBe the first to comment

I love hand code but I need powerfull and flexible IDE (Integrated Development Environment). I search on google and I found good IDE for my self. The software is PSPad editor. PSPad editor is free and you could it for your commercial project.

PSPad have powerfull scripting engine which allow us to create our own menu and functions for our dialy job. The scripting engine use VBScript and JScript. You could the most comfortable scripting language for your own to expand the PSPad functionality. Integrated FTP client help me to edit my files on the web.

This site template created on PSPad. Why I choose PSPad? the reason is I don’t want extra huge mega size application and memory eater. It’s light and fast. PSPad still have limitation in threading and you could fell it when trying accessing to FTP server using the internal FTP client. I wish PSPad team will provide the source.

Want to try it? go to and install in your computer. It’s awesome editor, many PHP developer use this tool.

Happy Programming