We now have Package Deals to create and maintain your website for you.
(includes special prices for community groups and hobby clubs)
Here is a new series of tutorials comparing the mySQLi and PDO interfaces for accessing mySQL databases.
- Connecting to a mySQL Database
We begin by comparing the alternative ways we can connect to a mySQL database.
- Database Deletes
The simplest of the common database calls and therefore the one that is the most similar between PDO and mySQLi.
- Database Updates
The main difference between a delete and an update is that you might only have one variable to bind in a delete but you always need at least two for an update.
- Database Inserts
These are the database calls that will have the largest number of variables to bind.
- Handling Errors as Exceptions
We can either test for errors in each database call or we can wrap them in a try block and catch exceptions.
- Affected Rows
Another way to check if your call worked the way you expected.
- Select (with record count)
To be able to count the number of rows returned we need to return all the results at once.
- Select (without record count)
If we don't need a row count we can return the rows one at a time.
- Select and Bind
An extra and unnecessary way of retrieving the data with mySQLi.
- bindParam and bindValue
PDO provides two ways to do the bind - by variable name or by the current content of the variable.
- Transaction Processing
A look at the difference in how mySQLi and PDO handle transaction processing.
For those who are yet to upgrade from the mysql interface that was declared to be obsolete in 2013 and which is now no longer a part of PHP the following compare the main commands you need to convert with the quickest to convert to equivalents.
- mysql and mysqli - Connect
A look at these two alternate interfaces and how to use them to connect to the database.
- mysql and mysqli - Query
How these two interfaces differ in how you can use them to run database queries.
The following are more general articles about mySQL and relational databases in general.
- Update mySQL on Windows
How to change which version of mySQL you are running on your own computer.
- Databases and Null
Null has a different meaning for databases to what it does in programs.
- mySQL enum Fields
What are the advantages/disadvantages of enum and what are the alternatives.
- Why Normalise a Database
A look at some common problems people have with their database that would not have happened if they normalised their data first.
- Primary Keys
There is usually an existing unique field that is the best choice for the primary key.
- Receipt Numbering using Autoincrement
A look at one of the rare situations where autoincrementing makes things easier.
- Float or Numeric
While the float data type may be the only option available to you in your programming language, the numeric data type may be more appropriate in the database.
- Defining Tables
Reasons for writing a script to create the tables that you need in your database.
- Images and Databases
Should you put your images inside or outside your database?
- One Field With Multiple Values
One thing newbies often get wrong in their database design is how they handle multiple values in a single field.
- Sorting Databases
The data in your database is not sorted in any particular order.
- GROUP BY and Fields Returned
You need to be careful how you define the fields you want to retrieve when grouping data.
- To Index or not to Index
Some indexes will speed your database up while others will slow it down.
- Storing Passwords in Databases
A look at the various options available to secure stored passwords so that you can select the alternative most appropriate to your site's requirements.
- Referential Integrity
Make your code simpler by letting the database do what it is designed to do.
- Gaps in Pseudokeys
Once you start deleting records your autoincrement pseudokeys will no longer be sequential. Don't make the mistake of trying to fill the gaps.
- Select and Union
The UNION statement allows you to combine the results of queries together and treat them as a table for further processing.
- Keeping Data Separate
Keeping the SQL and data completely separate is more secure than escaping the data.
- Don't Use Wildcards
By specifying exactly which fields to retrieve your script will not break if other fields are added or removed from the table.
- SQL Efficiency
Make use of the efficiencies built into the SQL optimiser by combining SQL together where practical.
- "Learning MySQL"
A review of the only book on MySQL that most people will ever need.
- "mySQL Cookbook"
A huge problem solving resource for those who are already familiar with mySQL.
- "mySQL pocket reference"
The almost perfect reference to mySQL.
- "High Performance mySQL"
This book covers all the aspects of mySQL databases except for the basic SQL commands covered by the beginners books.
- "Refactoring SQL Applications"
Getting your SQL to work better can have a dramatic affect on your script.
- "A Sane Approach to Database Design"
The book for anyone who needs to design a database.
- "SQL in a Nutshell"
A complete desktop reference to SQL including four popular implementations.
- "SQL Antipatterns"
Lots of common mistakes in database design and use along with ways to fix them.
- Storing Images in Blobs
More complex systems need to store images in the database as well as text.
- mySQL Table Joins
There is more than one way to join tables together.
- Transaction Processing
How to combine multiple database calls into a single transaction.
- mySQL Data Types
What types are available and what is the difference between them.
- Retrieving Data
How to use prepare statements and retrieve data.
- Group By
A quick look at aggregate functions and how to use them.
- SQL Created and Last Updated Times
Define fields that track the created and last updated times without having to reference them after you crate them.
- Row Numbers
mySQL supports using LIMIT to identify which range of rows to return from the query. Other SQL versions don't have this. Here we look at how we can generate a temporary table that has row numbers we can use in the query without needing to use LIMIT.
If you have a different PHP or mySQL question please ask Felgall.
This article written by Stephen Chapman, Felgall Pty Ltd.