Web Design & Development Blog
MySQL
Enchaning an Invoicing System for Reporting
04 months ago
At the start of the year I wrote a custom invoicing system for a client, using PHP and Smarty (hope to convert to use Twig at some point). The client has since come back to me to built in extra functionality – namely a reporting section. I thought I’d share my solution to the issue I was faced with.
The client required some fairly involved reporting facilities, here is a sample of some of the requirements from the client:
- Aggregated report, split between two dates of our choosing, with totals split by either month, day or week (we want to choose)
- A list of invoices for a day of our choosing with aggregated totals of for the day selected
- Annual invoicing report – we want to show a summary of the total for the items sold, the total vat charged and summaries for average invoice values
- Invoices totals grouped by customer
There were several more, but they were all similar to the above.
Firstly, I’ll explain the database setup, for the invoicing, of the current system I built. It was basically two tables – one for the invoivce header and one table for the invoices items – nothing un typical there at all.
Storing Ecommerce Order Information Correctly
37 months ago
Yesterday I was doing a tiny bit of web realted freelancing for a friend – simply involving doing a bulk, conditional update of his entire stock for his ecommerce website. The site itself was simple in design and used PayPal with a sprinkle of IPN. So, after a bit of MySQL magic, I updated all of his prices, for all ~700 products in one fell swoop – all good so far, easy money.
However, after an hour or so of emailing the store owner I got a very worried phone call. In short, the bulk price update to had caused all archived order invoices to become incorrect. Why – due to bad database design by the original developer, let me explain.
Best Practices With PEAR Cache Lite
411 months ago
Caching a website using PEAR Cache Lite has many benefits – it will speed up your site and is very simple to implement. However, there are several common pitfalls they could cause potential issues on a website. Basically, you can’t approach caching from a single angle, as the type of caching used is always project/problem specific.
Full File Level Caching
Full file caching is the simplist method and essentially takes an image of the page at a particular time. This method is very fast to implement but does have it’s downsides. As you are storing a static file of the page you’ll instantly notice the following issues (I’ve included some typical examples you’ll run into):
- Any elements of your page that require session data will fail to work (you’ve essentially cached the session id too!)
- If the page needs to be served using a particular header you’ll be out of luck, as a statiuc file will always be served using a plain text header (E.g. a cached rss feed won’t validate as the header has changed)
- Clearing the cache becomes very clumsey as the whole cache requires cleaning when even a small change is made (E.g. say a new article is added by the admin)
- User logins that make use of session data
- E-Commerce sites and a shopping basket summary
- Search results pages
With full file caching dynamic content will always be a issue. However, full file caching CAN help high traffic sites, even when a short cache lifetime is set – use with caution!
(more…)
Associate Products with Multiple Categories Using MySQL
51 year ago
Having a database structure that allows a product to be associated with more than one category is a very common scenario in any eCommerce website. However, after working on a couple of truely awful bespoke solutions from other developers recently, whose methods to store and retreive such data were so convoluted, have inspired me to write this article.
Story such data need to not be overly complicated. The following, simple, table structure is required (in a real ecommerce system you would definately have additional fields – these have been omitted here for thae sake of the example):
Product
product_id (PK)
name
Category
category_id (PK)
name
Products_Category
product_id (PK)
category_id (PK)
The products_category table is a simple linking table that allows a many-to-many relationship between the product and category table. It contains to two primary keys to ensure every combination of product and category is unique. for example, this table will contain many unique number pairs and a row may be 1,4 or product_id 1 and category_id 4. The files to create and populate this table structure with sample data can be found here.
Now it is simplay a case of running a series of MySQL statements (I’d advise converting them to stored procedures for more security and better application seperation) to retreieve the appropriate data. For example:
Products Within a Certain Category (E.g. category_id 1):
SELECT p.product_id, p.name FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id WHERE pc.category_id = '1';
Count Products Within a Certain Category (E.g. category_id 1):
SELECT COUNT(p.product_id) As myCount FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id WHERE pc.category_id = '1';
…and that’s it. Extremely simple, can be expanded to any eCommerce system and not convoluted at all 
Displaying a Breadcrumb Navigation for Multiple Sub Categories via PHP
51 year ago
While making an ecommerce store I ran into the issue of displaying a category breadcrumb. Usually this is easy as I always recommend keeping the numbers of categories and sub categories to a maximum of 1 level deep. E.g. Tshirts > Red tshirts. For this store, due to the sheer number of products the owner wanted to add up to seven category levels. While there are many tutorials on this floating about, they all seem tocus on displaying the whole tree – very useful for a sitemap page, but not a breadcrumb navigation. While it would have been possible to hard code several if statements into the category listing page, this seemed a bit messey and would cause problems if an eighth level was added.
For the breadcrumb naviagtion I needed a function to displaying the path to a given category ID, sometimes refered to as a single branch or node. I was using a simple parent child database table structure:
While the latter may seem fairly trival I really couldn’t get my head around the problem. After a bit of Googling, I found a function that was a good starting point so adapted it to fit my problem (the function is part of a categoru class):
function getCategoryTreeIDs($catID) {
$row = DB::getInstance()->query("SELECT parent FROM categories WHERE ID = '$catID'")->fetch();
$path = array();
if (!$row['parent'] == '') {
$path[] = $row['parent'];
$path = array_merge($this->getCategoryTreeIDs($row['parent']), $path);
}
return $path;
}
The function simply returns an array of category IDs. E.g 20, 28. So from the array I’d know that the tree would go as Home > Cat ID 20 > Cat ID 28.
Displaying the Breadcrumb Navigation
To display the actual breadcrumb I simply added the following method, that loops through the array of ID we just generated. The getNameLink method simply generates an SEO feindly website URL for the category, inside the <a> tag.
function showCatBreadCrumb($catID) {
$array = $this->getCategoryTreeIDs($catID);
$numItems = count($array);
for ($i = 0; $i<=$numItems-1; $i++) {
echo $this->getNameLink($array[$i]) . ' » ';
}
}
The result is a nicely formatted breadcrumb (to use our tshiorts example again):
Home &rquao; Clothes &rquao; tshirts &rquao; Mens &rquao; Red tshirts &rquao; Offensive &rquao;
A recursive function inside a loop, are you insane?
Some of you may have noticed that the function used to generate the category IDs is called recursively. This generally considered bad practice, for large data sets due to performance issues. However, for the current use this isn’t an issue. I know for a fact that client won’t be adding categories more than several levels deep, so performance really isn’t an issue in my eyes here. Maybe if we had hundreds of categories, but for several it’s really a non issue in my opinion.
getCategoryTreeIDs
MySQL Cheatsheet – Useful MySQL Queries
0 |
||
| Page rank |
5
|
|
|
||
| Page rank |
5
|
|
|
||
| Page rank |
5
|
|
|
||
| Page rank |
5
|
|
What follows is a list of some very useful MySQL queries for use in projects, enjoy. I won’t go into detail how and why they work, I’ll leave that up to you
/*** select records from the previous day ***/
SELECT * FROM users WHERE TO_DAYS(last_login) = ( TO_DAYS(NOW()) - 1 )
/*** select records from last 90 minutes ***/
SELECT* FROM users DATE_SUB(NOW(),INTERVAL 90 MINUTE);
/*** select records from last 1hr 5 mins ***/
SELECT DATE_ADD('1970-01-01 12:00:00', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
/*** select records from last hour ***/
select DATE_SUB(NOW(), INTERVAL 1 HOUR);
/*** using the SIGN function to mark a number as positive, negative or null ***/
SELECT backlist, SIGN(backlist) AS user_to_backlist
FROM users
WHERE user_banned IS NOT NULL;
/*** select records from last week ***/
select DATE_SUB(NOW(), INTERVAL 1 WEEK);
/*** get the last day of next month ***/
SELECT LAST_DAY('2006-03-06' + INTERVAL 1 MONTH) AS last_day;
/*** select unique records only ***/
SELECT user_name FROM users GROUP BY users HAVING ( COUNT(user_name) = 1 );
/*** select records from one table that are in another table i.e. all the customers that have placed an order ***/
SELECT DISTINCT cust.customer_id, cust.customer_name
FROM cust INNER JOIN orders ON cust.customer_id = orders.customer_id;
/*** insert data from one table into another ***/
INSERT INTO customers(customer_id, customer_name)
SELECT cus_key, cus_name
FROM customers_2 WHERE customer_name LIKE 'W%';
/*** update information based upon a seperate table ***/
UPDATE cust SET status = '1'
FROM orders WHERE orderdate > '2009-01-01' and orders.customer_id = cus.customer_id;
/*** classic self join example - who is an emoployees manager ***/
SELECT emp.empID, emp.Name, emp.Salary, managers.Name AS manager_name
FROM emp
LEFT JOIN emp AS manager_name
ON emp.ManagerID = Manager.EmployeeID
WHERE (emp.empID= '123456');
/*** using UNION to combine results from multiple queries into a single table ***/
SELECT users.name
FROM users WHERE (users.name BETWEEN 'A%' AND 'M%')
UNION
SELECT banned_users.name FROM banned_users
WHERE (banned_users.name BETWEEN 'A%' AND 'M%');
/*** concatenate column data into a single column ***/
SELECT CONCAT(emp.firstname, '-', emp.lastname) AS emp_full_name FROM emp;
/*** select count of records for each hour ***/
SELECT HOUR(last_login) AS last_login_hour, COUNT(*) AS the_count FROM users GROUP BY HOUR(last_login);
/*** import a csv file ***/
LOAD DATA INFILE '/path/xxx.csv' INTO users_table csv_test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n" (user_name, access_level , user_email);
/*** display current mysql user ***/
SELECT USER();
Adding Unlimited Form Fields With JQuery and Saving to a Database
64In this article I’ll discuss how to add an unlimited number of additional form elements to a form and then save to a database. The latter part is the key here as a variety of tutorials exist on adding form elements, but I have yet to see anywhere that actually explains how to manipulate these added form fields. For example, how to get values to store them in a MySQL datbase. In the example we’ll have a simple user signup form where the user can add multiple fields to describe their favourite websites. The basic Form HTML is as follows (nothing amazing, just a simple html form):
<script src="js/jquery.js" type="text/javascript"></script>
<h1>New User Signup</h1>
<form action="index.php" method="post">
<label for="name">Username:</label>
<input id="name" name="name" type="text" />
<label for="name">Password:</label>
<input id="password" name="password" type="text" />
<div id="container">
<a href="#"><span>» Add your favourite links.....</span></a>
</div>
<input id="go" class="btn" name="btnSubmit" type="submit" value="Signup" />
</form>
The only part that isn’t standard is highlighted above. This is simply the link users click to add additional form fields on the fly. To make that happen we’ll need some JQuery:
var count = 0;
$(function(){
$('p#add_field').click(function(){
count += 1;
$('#container').append('<strong>Link #' + count + '</strong>'+ '<input id="field_' + count + '" name="fields[]' + '" type="text" />' );
});
});