MySQL Union : Get two separate results back from a single query.

It is usually more efficient to query as much information from MySQL is one go as possible. Make the database do the work. Here we use a Union to effectively do two queries, two counts, one to count all records and the other to count all records that have a flag (subscribed) set (subset).

I'd recommend that you take a note of this generic query for future use. SELECT SUM(a.TOTRECORDS) AS total_shop_leads, SUM(a.SUBRECORDS) AS subscribed_shop_leads FROM (SELECT COUNT(*) AS TOTRECORDS, 0 AS SUBRECORDS FROM TBL_SHOP_LEADS UNION ALL SELECT 0 AS TOTRECORDS, COUNT(*) AS SUBRECORDS FROM TBL_SHOP_LEADS WHERE SUBSCRIBED) a

LEFT OUTER JOIN : Joining two tables where record in second table may not exist

In one table tbl_catalogue_sku we had all the details of every possible stock item whether or not in stock

In the second table tbl_stock we store the quantity in stock, but only for items in stock

The JOIN should not fail if there is no record in the stock table

Table ORDER in the Query is thus vital, the full Set Table must be first (tbl_catalogue_sku), the sub-set table is second.

This query returns details from tbl_catalogue_sku with the stock count should there be a record in tbl_catalogue_sku, otherwise the stock count is null.

SELECT d.id AS sku_id,s.stock_cnt,d.title,d.current_price
FROM tbl_catalogue_sku AS d
LEFT OUTER JOIN tbl_stock AS s ON d.id=s.id
WHERE d.id = '$sku'

Update Customer Records with Counts from Orders Table

We wanted to know who were our best customers and curiously we didn't store a count of how many orders a customer had made.

We also wanted to know this information in order to highlight which orders were a fraud risk, i.e. Recently registered, nil or just one or two previous orders, too high a value order etc, anyway back to the plot, we needed to count all previous shipped orders on a per customer basis.

This is the model query we used as a basis

-- update counts from other records
-- Count how many poets each country has UPDATE nations SET COUNT = (SELECT COUNT(id) FROM poets WHERE poets.nation = nations.id GROUP BY nation);

We however needed a few joins to establish our counts

UPDATE tbl_customer AS c SET c.previous_orders=
(SELECT COUNT(d.id)
FROM tbl_order_head AS o
INNER JOIN tbl_order_detail AS d ON d.HEAD_ID=o.ID
WHERE d.ORDER_STATUS=5 AND (o.customer_id = c.id) );

How to Log Slow MySQL Queries

A website I am maintaining was being knocked over everytime the company did a mailshot. We had switched on the Slow Query Log but couldn't find where any such queries were being logged, it turned that they are logged in the often overlooked MySQL database called "mysql". This is used by MySQL to store meta-information about the databases that it is running. That's logical really that MySQL would store information about itself in a database.

Once we'd located the slow squery table we were able to identify four critical queries out of many thousand which were running very slowly (4 seconds). We managed to reduce this by a factor of a 40 to a 100 times simply by turning relevant fields in the query where clause into indexes (we didn't develop the original database).

What's also clever about storing slow queries in database is that you can write queries to list only those slow queries that interest you eg only those that access a certain table

Also we were monitoring slow queries with "show processorlist" which once again you absolutely must know about.

Prettying and Tidying up Your Data with MySQL Formatting

SELECT DATE_FORMAT(o.amended,'%d-%m-%Y') AS DATE,o.VOUCHER_CODE AS voucher,REPLACE (c.email1,'@','@') AS email,FORMAT(ORDER_ITEM_VALUE,2) AS order_value
FROM tbl_order AS o
INNER JOIN tbl_customer AS c
ON c.ID=o.CUSTOMER_ID
WHERE o.VOUCHER_CODE LIKE 'OFF20%'
ORDER BY o.amended DESC;

The above query was made to produce a CSV report on orders with Voucher Codes. Date_format,format,replace and AS were used to make the data more understandable for the report reader

SELECT LOWER(REPLACE (email1,'@','@')) AS email FROM tbl_customer WHERE POSTCODE REGEXP '^b[0-9]' AND email NOT LIKE '%.com';

Why Fields Other than the PK Need to be Indexed

The Table Primary Key (PK) is automatically an index.

Indexes should be used whenever a relationship needs to be established between two tables using a field other than the PK E.g. both fields included in the ON c.field1=t.field3 clause. Making both fields indexes allows MySQL to JOIN the two tables much more efficiently and much faster.

The working of an index is hidden from the user but involves MySQL setting up lookup pointers. There is an overhead or cost to indexes for example whenever there is an insert or delete.

Recently I was investigating a join-based query involving 2 60,000 record tables which would literally wouldn't run. As it was a live server we couldn't wait to see if it would eventually complete. I then noticed that one field involved in the the ON was of type text, I made this field an index and the query ran virtually instantly

MySQL Query Select All Orders Today with Customer Details

SELECT o.ORDER_VALUE,o.VISUAL_ID,c.FIRST_NAME,c.SURNAME,c.ADDRESS1,c.CITY,.items,
o.CREATED,C.VISUAL_ID AS CUSTOMER_VISUAL_ID
FROM tbl_order AS o
INNER JOIN tbl_customer AS c ON o.CUSTOMER_ID=c.id
WHERE (o.order_value>1 ) AND
(TO_DAYS(o.created)=TO_DAYS(NOW())) AND LENGTH(o.PAYMENT_CARD_AUTH_CODE)>3
ORDER BY ORDER_VALUE;

Standard MySQL Join Query: Get Customer Details

In this query we want to retrieve the customer details of all those orders greater than 300. So are linking (joining) data from records in two tables.

SELECT * FROM tbl_order AS o INNER JOIN tbl_customer AS c ON c.id=o.CUSTOMER_ID
WHERE TO_DAYS(o.created)=TO_DAYS('2009-12-15') AND o.ORDER_VALUE+0 >'300.00';

Note the +0 kludge which is required because the order_value field is a "text" field and not a binary, the +0 tells MySQL to treat the contents as a number

MySQL: Querying Fields/Columns with the Same Name

SELECT d.parameter_value AS last_dispatch_number ,tr.PARAMETER_VALUE as last_transaction_id
FROM `maindb`.`tbl_parameter_despatch` AS d,tbl_parameter_transactionid AS tr ;

Here the "as" method is used to give d.parameter_value and tr.parameter_value
different names. This method is also frequently used to give fields/columns more intuitive names when the database creator had chosen uninformative or even confusing names eg parameter_value, field2 etc


The "as" names are transmitted to say PHP's mysql_fetch_assoc or Perl's fetchrow_hashref

Coding conditional elements of a MySQL where clause in PHP for a form

It can be tricky to code a complex where clause for a web form driven driven Search box where different options may be present or absent. The following example is a for property search form where it is possible to search by keyword, by number of beds, maximum and minimum price and any combination of these. A simple algorithm is used to ensure that the where clause syntax is respected even when there are absent elements.

if (strlen($price_min)>0)
{
$price_min_sql=<<<__SQL__ (property_sale_price >= '$price_min')
__SQL__;
}
else {$price_min_sql=1; }

if (strlen($price_max)>0)
{
$price_max_sql=<<<__SQL__ (property_sale_price <= '$price_max') __SQL__; } else {$price_max_sql=1; } if (strlen($beds_min)>0)
{
$beds_min_sql=<<<__SQL__ (property_bedrooms >= '$beds_min')
__SQL__;
}
else {$beds_min_sql=1; }

if (strlen($beds_max)>0)
{
$beds_max_sql=<<<__SQL__
(property_bedrooms <= '$beds_max')
__SQL__;
}
else {$beds_max_sql=1;
}

if (strlen($keyword)>3)
{
$keywordsql=<<<__SQL__
((property_borough like '%$keyword%') or (property_address like '%$keyword%'))
__SQL__; }
else {$keywordsql=1; }

$query=<<<__SQL__
select * from tbl_properties where $keywordsql and $beds_min_sql and $beds_max_sql and $price_min_sql and $price_max_sql
__SQL__;

mysql insert select example with manipulation of fields

This mysql "insert from another table with select" example shows how you can concatenate two of the selected fields into one inserted field. Obviously you can use any of the other MySQL select string, arithmetic and mathematical operators and functions.

insert into tbl_properties (
agent_property_id,
agent_id,
property_address,
property_borough,
.. etc
)
select
Prop_ID,
concat(prop_firmid,prop_branchid),
Prop_Name,
Prop_Street,
.. etc
from tbl_raw_data;