resources
helpful hints - MySQL
MySQL is an open source relational database management
system that continues to rapidly grow in popularity and use. Known for its speed and ease of use, MySQL has proven itself to be particularly well-suited for developing database-backed web sites and applications.
MySQL has many attractive features:
- Speed.
- Connectivity and Security.
- Portability
- Cost - Open Source
- Support - huge community
The LAMP architecture has become on of the most popular in the web industry today as a way of deploying web applications. MySQL is the M in this bundle alongside Linux, Apache and PHP (or Perl).
Number of Rows Returned
$totalrows = mysql_num_rows($sql);
Dealing with HTML Special Characters
PHP 4, PHP5 [htmlspecialchars()]
PHP 5 >= 5.1.0 [htmlspecialchars_decode()]
htmlspecialchars($str)
converts '&' (ampersand), " (double quote), ' (single quote), < (less than), and > (greater than)
htmlspecialchars_decode($str, ENT_NOQUOTES)
reverses above
converts '&' (ampersand), " (double quote), ' (single quote), < (less than), and > (greater than)
htmlspecialchars_decode($str, ENT_NOQUOTES)
reverses above
Script Security
You should never use addslashes() for escaping characters for use in SQL queries. The only safe function for this purpose in MySQL is the use of mysql_real_escape_string(). mysql_real_escape_string() escapes characters, that have a special meaning in SQL and could break a query (it also helps to prevent SQL injection).
htmlentities() escape characters that have a special meaning in HTML and might break your markup (it also help to prevent XSS attacks).
When you are securing data going into a database, use mysql_real_escape_string(). If you are securing text going out to an HTML response, you use htmlentities(). To do otherwise risks undocumented behavior and vulnerabilities.
number_format issue with large numbers
number_format ($number, [int precision], [decimal separator], [thousands separator])
default for number_format ($number, 2) is a decimal and a coma
(e.g. 1,2300.00)
This can give problems when adding large numbers (the comma is the problem).
number_format($number,2, '.', '');
The above solves the problem by eliminating the comma (the last '')
Alternative:
$number = '12000';
$html_number = number_format($number,2);
result: 12,000.00
$mysql_number = str_replace(',', '', $number_with_comma);
result: 12000.00
Duplicate Data Within a Field Query
A simple query to find duplicate entries within a single field (i.e. this is not duplicate rows, just duplicate entries within one field):
SELECT *
FROM your_table
GROUP BY the_field HAVING COUNT(*) > 1
ORDER BY the_field
This will give you all the duplicate data within the specified field for the given table.
Left Joins
Left joins (sometimes called left outer joins) are especially useful for finding rows that are only in one table and not the other. For example, say we have a members table and a customer table and we want to find only members who have not placed orders (i.e. a customer):
SELECT members.member_id, members.first_name, members.last_name, members.email_address
FROM members LEFT JOIN customer ON customer.member_id = members.member_id
WHERE customer.member_id IS NULL
FROM members LEFT JOIN customer ON customer.member_id = members.member_id
WHERE customer.member_id IS NULL
This will give us all members from the members table (the left table) who are not present in the customers table (i.e. customer.member_id IS NULL).
Custom Sort Query
In MySQL you can use the CASE expression in the ORDER BY clause to define a custom sort order:
SELECT *
FROM finger_lakes
ORDER BY
FROM finger_lakes
ORDER BY
CASE name
WHEN 'Canandaigua' THEN 1
WHEN 'Keuka' THEN 2
WHEN 'Seneca' THEN 3
WHEN 'Cayuga' THEN 4
ELSE NULL
END;
This will give us a sort order of four of the Finger Lakes in a west to east order (Canandaigua Lake, Keuka Lake, Seneca Lake and Cayuga Lake).