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

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

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 :

<?php

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!.