My PHP , Wordpress and Linux Lab

A couple of users thankfully pointed out the issues with the paths, as i am stuck with windows as a development platform,  case sensitive directory names mess things up on Linux. So very minor updates to a few files.

Also PLEASE read the README file provided in the zip for the instructions on how to set up the application.  PHP Viz needs a database for storing the configurations and settings, so that must be created by using the SCHEMA.sql provided.

Download PHP Viz 0.2.1

Full text searches have been the one part which would always worry me in any project which will hold a reasonable amount of data with a decent traffic.  Not anymore!

I have been using Sphinx for a while and i have been really impressed. Easy deployment, easy integration and its pretty flexible as well. I cant say that i have explored everything it has to offer but whatever I needed it to do, it surely did for me.

Among other features you can use it to rank , filter ,  group , sort and query to targeted columns. It even supports “Sounds like” searches ….. wonderful! .  With several different matching and ranking modes , ones got to be right for you.

The PHP Client is a pure PHP implementation, so no deployment headaches. The PHP client API is included in the download package, hook it up with your DB wrapper and you are ready to battle with those Full Text Searches. What it would do is .. it would return to you the ranked record IDs, along with weights and then you can bring the full record of that ID directly from MySQL. Response times from Sphinx are amazing, So far I haven’t seen it take more than 0.7 seconds to return the resultset. Best of all, you can scale as much as you need, without any problems.

Here are the key features taken from their site directly:

• high indexing speed (upto 10 MB/sec on modern CPUs)
• high search speed (avg query is under 0.1 sec on 2-4 GB text collections)
• high scalability (upto 100 GB of text, upto 100 M documents on a single CPU)
• supports distributed searching (since v.0.9.6)
• supports MySQL natively (MyISAM and InnoDB tables are both supported)
• supports phrase searching
• supports phrase proximity ranking, providing good relevance
• supports English and Russian stemming
• supports any number of document fields (weights can be changed on the fly)
• supports document groups
• supports stopwords
• supports different search modes (“match all”, “match phrase” and “match any” as of v.0.9.5)
• generic XML interface which greatly simplifies custom integration
• pure-PHP (ie. NO module compiling etc) search client API

Amazing stuff! So if you are in the business of developing high volume traffic and/or data sites … you have to check it out for sure! Its available for both Linux and Windows platforms!

2 Thumbs Up for these guys!

I have been so busy over the past 2 years that I almost forgot about this code generator that I started to write. But recently i resumed work on this and created a new version from scratch.  and I am very happy with the results so far

PHP Viz is written in PHP and lets you create MySQL driven PHP sites. A prime feature it provides is the support to link up multiple tables (based on foreign and primary keys) and create views from them.

The application lets you create “sites” rather than simple pages. You can even have Login protection on your site if you have a users table in there somewhere in your database. All the basic functions are there , you can create tabular views, single record view , add and edit view. The generated code is very clean , simple and based on a very very simplified MVC architecture. This allows for expansion and code re-usability with ease.

The application code itself  is very rough at this point and i just want to see some feedback from the community to make sure that i am heading in the right direction. I am hoping to add some more advanced level features down the line and hoping to make this an every day tool for the PHP developer.

PHP Viz 0.2 should be publicly available within the next week. I am hoping that the next Site Admin you make is built by PHP Viz

When I started to work in a virtual team comprising of 6 developers , I felt that the most time consuming task was syncing the database changes amongst team members. As we were using SVN , we didn’t have to worry about the files but the DB schema was always an issue. So I started to look into something solid which we could couple of with SVN client in order to make this process as smooth as possible. Blame it on my search capabilities or eye sight but honestly i didn’t come across something I liked. So i devised a mechanism which might be far from ideal but which is working well for us. Keep in mind that we all are working in Windows environment (as the client is using Windows server) , so some steps might be slightly different for people in the Linux camp.

Here is what I did :

Step 1:
I located a ready made script which would let me sync 2 databases. I found a well written class by “Diogo Resende” here

Step 2:
I modified the class , so that instead of actually syncing the databases , it would just return the queries which it is going to run in order to sync the databases.

Step 3:
Now I created an exact copy of my current database , lets call this database “test”. The called the copy i created “svn_test”.  Keep in mind that my working database is still “test”. After this point I never touched “svn_test”.

Step 4:
I created a small database on my online server with a table called “changes” with  change_id , change_set , submit_time fields. “change_id” was set to be primary and auto increment. I created a one page management script for this and placed the script on the remote server to be easily accessible by all. Lets say the location of this script is http://something.com/changes_list.php. This script lists all the records in descending order. I can edit , delete and add records from this script as well.

Step 5:
I created a PHP script called “pre_hook.php”, which would compare “test” and “svn_test” and return to me all the queries. These queries were stored in the remote database table called “changes” , it would store the last returned “change_id” in a file called “change-id.txt”. It would also generate a file called “changed.txt”, if there was no changes ,  nothing would be submited to the remote DB  and  the  file “changed.txt” will be deleted if it were present. This script also created a file called “run.bat” , if there were any new queries , the script would insert explorer “http://something.com/changes_list.php“ in the “run.bat” file , otherwise it would just insert “exit” in it.

Step 6 :
I created another script called “post_hook.php” , it would check if “changed.txt” was present , if so it would replace the database “svn_test” with “test”.

Step 7:
I created yet another script called “post_update.php” , which would connect to the remote database and tried to find query the records which were entered after its last run. The script stores the highest “change_id” it got in a text file. So next time it would only try to query for records which are higher than the stored “change_id”. If the script got any new records from the remote database. It would parse the queries in each record and run them on database “test”.

Step 8:
As we all use Tortoise SVN , I created 4 hooks. A hook on “start commit” , this hook would run the “pre_hook.php” script. Hook 2 was created on “pre commit” which would run the “run.bat” file created by pre_hook.php. Hook 3 was created on “post commit”, this would run the “post_hook.php” script. Hook 4 was created to on “post update” , this would run the “post_update.php” script.

How it works (or is supposed to work):
After all the above steps. I goto the database “test” and make a change to xyz table. Now remember that “svn_test” DOESNT have this change. So now i continue to work on php scripts and I decide to commit to SVN. So what will happen is when the commit starts the “pre_hook.php” will gather all the schema changes (if any) , send them to the remote MySQL database , place the link to the remote script I deployed at http://something.com/changes_list.php inside the “run.bat” file. BEFORE actually sending the files to the SVN , the” run.bat” would be executed and it would show me the list of changes that were just submitted , so that i can view them manually as well and adjust if needed , which is rare . After the commit is complete , the “test” database is copied over “svn_test” by “post_hook.php”. Next time someone updates the repository , the “post_update.php” script will kick in after the update is complete and return all the new changes submitted to the remote database. Now that persons database has the changes I made to mine as well. The remote DB management script also lets us input other queries as we may please.  So that on next update everyone can get the changes those queries reflect.

This system is not perfect, but it saves us plenty of time.  I am still thinking/searching for a better solution , which takes care of the changes in the data as well but for now this is what I have.

Hoping that this would help someone else as well.

FTP Sync 0.9

PURPOSE:
To upload files which have changed to your production server , automating the task of comparing which files have changed. You simply create a FTP job
in the script , run the job and it will upload the changed files for you.

REQUIREMENTS:
FTP functions are required for this script to work.
PHP 4 Or 5
MySQL 4+

INSTALL:
Open dbinc.php and put in the database connection variables in there. Once done run installer.php from your browser and you are all set to go.
Its advised to remove installer.php once you have finished installation.

SECURITY:
It if advised that the admin creates users and allocates jobs to them. Also the admin should block access to the database and script files
from general staff.

INFO:
We made this script for our internal use , thought it might be useful for some of you out there. It was a tiring job to find which files needed to be uploaded
to the main server after development on local server. Thus i created this script , where you can create jobs and upload folders and files which were changed.

The decision to upload happens on 3 rules:

1 : you can provide time and date in the job page , files which were created after that time will be uploaded.

2 : you can ask the script to match the time against the server file , the script would calculate the time offset and calculate if the file has
changed since last upload.

3 : The script also keeps a log , if the file changed after last upload , it will upload the file.

You can exclude the files you dont want to upload, for instance you wont want to upload and overwrite config.php or something like that. So you
can create the skip list. The skip list is the path of the file from the source directory you have provided.

For instance if you have a source directory called “/www/something/upload/” , and you dont want to upload the file “/www/something/upload/config.php”
or the folder “/www/something/upload/users/images” then you would put in something like this:

config.php
users/images

Remember no trailing slash for folders.

You need to have writable permissions for the logs folder , also if you want to utlize the copy permissions feature you will need the BcMath library
compiled with PHP.

The admin can run any job , but if you want to have users which can run specific jobs only please utilize the users section , in which a user can
run all jobs or only specific jobs.

Same goes for the jobs , you can set an attribute to allow all users to run the job.

This code has been tested on PHP 5 and PHP 4.4.

You are free to modify the code as you please. The code is released in the public domain under the GPL license.

Enjoy!

Decode IT has announced PHP 5 and MySQL 5 hosting.  After signup customers will be given a choice between version 4 and 5. The servers are dual core servers with high uptime and reliability. Small packages are ideal for WordPress installations which can be done with clicks via the Fantastico control panel. All accounts contain the all time great CPanel account manager.

Here is the overview….
50 MB Hosting – $2.00 pm
100 MB Hosting – $4.00 pm
200 MB Hosting – $8.00 pm
500 MB Hosting – $20.00 pm
1000 MB Hosting – $35.00 pm

You can find the details of the packages here.

Enjoy!!

Alot of PHP/MySQL programmers out there , specially the ones just starting out make some mistakes while using queries. The ideas i am going to outline aren’t just limited to MySQL , they can be equally applied elsewhere as well. We need to keep in mind the fact that MySQL will use more RAM and CPU than it should if inefficient queries are written.

• For instance one common mistake is to use “SELECT * from table1” , unless you don’t need to use all of the fields i strongly suggest against it. Use something like “SELECT field1,field2 from table1“
• Make sure you know what you are doing when using JOINs. Use “explain” to see how many rows are actually being scanned for the query to execute and bring the result. A lot of times its better to break the query up into several queries. JOINS ARE HEAVY!
• I have seen people use order by clause in queries where they need to get just the count of the records. That’s totally unintelligent. Order by clause uses up RAM as well to sort the data. Avoid it where it can be avoided.
• Saving images in DB is probably not a really good idea after all. Atleast I think so. If you can , doesn’t mean you should.
• Integer primary keys are faster than making username the primary key or something similar.
• Fixed length fields are processed faster than variable length fields.
• Use indexing wisely. Indexing helps in query the database faster BUT insertion and updates are slower.
• Sometimes it better not to create text book database structure aka normalization , sometimes a little redundancy can help minimize number of queries AND/OR load on database, specially when dealing with large databases.
• Keep in mind that in the loop while($array=mysql_fetch_array($queryresult)) , the transaction will keep the query open until the end of the loop. So if there is some heavy processing happening inside the loop on the result data, it is probably a good idea to first run the query loop and store the data you want to process in arrays and then later on process on the array. I find this technique helpful on many occasions.
• Using mysql_pconnect doesnt always help .
• Using mysql_free_result is probably a good idea.
• mysql_insert_id is a useful function to get newest primary key of the record , rather than using “….order by id desc limit 1″
• If you know you will be using some mysql field data alot , like the username of the logged in user , its always a good idea to store it in session , rather than banging on mysqls door again and again for it.
• ENUM field type is very useful , just like fixed length fields. Data processing on ENUM fields is quite fast.
• Using count(*) in queries instead of mysql_num_rows is faster i believe.
• I believe if you are storing signup dates etc , its probably a good idea to have the field as integer instead of datetime and put in mktime() value. This only applies to current and future dates. In cases where past dates are possible datetime should be used. As you never know if that date can be earlier than 1970. Storing mktime gives you faster processing and manipulation of data is easier as well in PHP with the date() function.
• Dont use fulltext unless you really have to.
• And finally DO USE PRIMARY KEYS , dont be affraid to use them , they can help you down the road. For instance “DELETE from table1 WHERE id = 1″ and “DELETE from table1 where firstname=’php’ and lastname=’rox’” , the first query i reckon will run faster.

Those are all i can think of right now. Please remember i am not an expert on MySQL , the above are based on professional experience with PHP and MySQL. Another thing to remember here is , all of these points might seem insignificantly small but when piled up together. They can cause major havoc. Specially when large databases are question.

Hope the tips can really help someone.