When it comes to MySQL as a database for data driven application I can recommend it. I have done some complicated queries for some of the applications and it performs very well.

The Tools I use when making MySQL Database's and Tables are phpMyAdmin which is a Free PHP application. You will need to have access to a web server and the permissions set to allow you to run it. Best of all it's FREE.

The other Tool I use a lot is Navicat There is a Free Version of Navicat which has a few features missing like HTTP tunnel which is fantastic to connect to a remote MySQL Database when the server has all the standard connection ports blocked.

Navicat makes building databases and writing complex queries child's play. The time it would have saves me in writing complex queries over the years would be thousands of dollars in saved time.

Lets take a look at what Navicat can do .

Lets take a look at a complex query and see how easy it is to create them in Navicat.

Below is an image of the visual query builder.

To build this query i took me about 10 minutes to have it built and running on the site. I I had to do it manually it would have taken me several hours to set the right indexes and test the query.

Once you have produced this you can then test it and just grab the code it has made and drop it into your script.

SQL Query example below:

SELECT
my_job_tab.job_id,my_job_tab.job_date,my_customer_tab.cust_partner,
cliMgr.recruited_by AS recruiter, mentor.mentor AS mentor,technician.repCode AS technician,my_jobPaymentTab.accounts,my_jobPaymentTab.amtPaid,
my_jobPaymentTab.invoiceNumber,my_item_job_tab.job_item_code,
my_jomy_paid_tab.tech_id,my_customer_tab.cust_companyName,my_customer_tab.cust_last,
technician.tech_percentage,technician.sales_percentage,technician.recruit_percentage,
technician.new_percentage,technician.mentor_percentage,cliMgr.firstYearRealEstate,
cliMgr.firstYearDomestic,cliMgr.RepeatDomestic,cliMgr.RepeatRealEstate,
cliMgr.secondYearRepeatDomestic,cliMgr.secondYearRepeatRealEstate,
my_item_job_tab.upsell,my_item_job_tab.job_item_fee,my_customer_tab.cust_id_num,
my_customer_tab.cust_company, my_job_tab.repeatNew,my_job_tab.bookingFee,
my_jobPaymentTab.PaidBy, my_item_job_tab.job_item_id,my_job_tab.job_salesperson_id,
my_job_tab.invoiceNumber,my_job_tab.invoicePrefix
FROM
my_job_tab
Inner Join my_customer_tab ON my_customer_tab.cust_id_num = my_job_tab.job_id_num
Inner Join Prospect_rep_tab AS recruiter ON recruiter.repCode = my_job_tab.job_tech_id
Inner Join Prospect_rep_tab AS mentor ON mentor.repCode = my_job_tab.job_tech_id
Left Join my_jobPaymentTab ON my_jobPaymentTab.job_id = my_job_tab.job_id
Inner Join my_item_job_tab ON my_item_job_tab.job_item_id_num = my_job_tab.job_id
Left Join my_jomy_paid_tab ON my_jomy_paid_tab.job_item_id = my_item_job_tab.job_item_id
Inner Join Prospect_rep_tab AS technician ON technician.repCode = my_job_tab.job_tech_id
Left Join Prospect_rep_tab AS cliMgr ON cliMgr.repCode = my_customer_tab.cust_partner
WHERE
my_job_tab.job_id = '$job_id'
GROUP BY
my_job_tab.job_date
ORDER BY
my_job_tab.job_date ASC