The concept of NULL in SQL, including MySQL, is a little hard to get at first, so the NULL keyword is tough to use as well.
This blog post helps to clarify it.
Category: Database
An auto-incremented column can become a little cluttered if records are deleted from the table, and you may wish to delete the slack of the sequence in your id column for instance doesn't bear any relationship to the number of records in the table.
Before doing anything about that to a primary key, bear in mind that there are those who say that a primary key should never, ever be changed. Do you really need to re-number your primary key?
If you really want to do it, read the caveat below before proceeding.
CAVEAT: I haven't actually tested this!
First, be safe and backup your database.
Next copy your table:
CREATE TABLE mytable2 SELECT * FROM mytable1 |
Now run the following commands, modified as necessary for your situation, on the copy:
ALTER TABLE mytable2 DROP mycolumn;
ALTER TABLE mytable2 ADD mycolumn int(6) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Have a look at the new table. Browse the records. Do you like what you see? Run as many tests as are appropriate to your situation. More is better than less.
If you're satisfied, it's time to disable mytable1 and make mytable two current.
Rename mytable1 to mytable1_old, then rename mytable2 to mytable1.
Keep a close an eye on the situation. If you're not happy, you can revert to mytable1_old or even restore the backup.
MySQL and PHP handle time and data data in different ways, and it's important to be aware of the difference.
Both these environments have a TIMESTAMP construct, but they're not entirely compatible.
While PHP uses a UNIX timestamp format (an integer representing the number of seconds since January 1st, 1970) MySQL's TIMESTAMP data type uses a YYYY-MM-DD HH:MM:SS format.
You can overcome this by using the mysql funtion UNIX_TIMESTAMP() to pull dates in the UNIX format native to PHP.
Making this conversion in your SQL query is generally more efficient and simple than converting in PHP. Remember, you always want to work your data as much as you can in the SQL query and deliver a clean set of data to PHP.
In fact, if you like you can pull the same attribute in different ways in the same query, creating what I'll call pseudo-attributes, as in;
[SOURCECODE language='sql']SELECT UNIX_TIMESTAMP(date_attribute) AS unix_date,date_attribute AS nice_date FROM mytable;[/SOURCECODE]
This gives you two dates to work with in PHP, represented as unix_date and nice_date for a more human readable format, and balances the processing between your web and database servers.
Sometimes you just want to pull a single value from the database without a whole lot of work. Here's how to do it in a single line;
MySQLi
// Assumes that a mysqli connection ($db) has been made to the database $value = $db->query("SELECT value FROM table WHERE condition = 'met'")->fetch_row()[0]; |
MySQL (Deprecated)
// Assumes that a connection exists to the database (mysql_connect()) and a database is selected (mysql_select_db()) $value = mysql_result(mysql_query("SELECT value FROM table WHERE condition = 'met'"), 0); |
Categories
Database Primer
A database is a structured collection of information.
This is not a database;
Adam Thomson, 555-6335 Anita McColl, 108 Verdun Ave., 555-5249
But this is;
Adam Thomson, , 555-6335 Anita McColl, 108 Verdun Ave., 555-5249
The difference? We don't have Adam's address right now, so in the second example we've left room for it. The information (data) is structured in the second example, but not in the first.
Purists will have far more complicated explanations, and so do I, but at the heart of it, that's what it comes down to.
Today, when anyone talks about databases, they're almost certain to be talking about a relational database, often referred to as a Relational Database Management System (DBMS or RDBMS.) We'll cover what makes it relational shortly.
Information in a database is stored, conceptually, in tables of rows and columns, just like a spreadsheet. One row, often called a record, is maintained for each thing or entity we're keeping information about, for instance an employee or a book in a library.
Each record is divided into fields, often called attributes, of information which relates to that entity, such as first name, last name and phone number. It is this rigid structure that makes a database work.
We'll build a concept of a database as we go here, so let's start with what we've covered and imagine that we're going to build a database to run a small hotel.
With what we've seen we can imagine that we have a database containing a single table of records, one for each guest. This includes those who have merely made reservations, those who are in the hotel now, and those who have checked out. This single table is sometimes referred to as a flat-file database.
At this point we'll imagine that each record in our database is keeping all the information about the guest (name and address etc.) as well as information about the visit (arrival and departure dates, room number, etc.)
What happens when a guest comes back for another visit? We'll have to add another record to the database for the second visit.
So far so good. Now let's say that our guest has something from the minibar. Now we have to add that to the database so we can charge him for it. If we add another record to our table, we'll be keeping information about the guest, about the visit and about the item purchased (what it was, how much is cost, etc.)
You can see that our database has quickly become unmanageable. It would be completely impractical to try to maintain the information like this. Nor only would the database size spiral out of control, if the guest changed his room number or home address, every record in the database relating to that guest would have to be changed! There's just no way that could be done without introducing errors and inconsistency over time.
This is where the Relational in Relational Database comes in.
If we want to bring our database under control we need to break it into multiple tables (database people call this decomposing the data) and relate the tables to each other.
To start with, we will create a Guest table for the basic information about the guest. Name, address, room preferences, etc. Now we'll create a Visit table with information about indivudual visit; arrival date, departure date, room number, etc. And of course we'll create a Purchases table tracking the sales outlet (such as the minibar, restaurant or gift shop), the item, the price, etc.
Now we can add only the information we need to each table without duplicating data among them, which is one of the cardinal sins of database management.
The next thing we need to do it find a way to relate records in the three tables to each other (there's that relational thing again.) Before we can do that we need to ensure that as we build these relationships we always know exactly what record we're dealing with. That brings us to a critical piece of database theory (and jargon) called the Primary Key.
Let's say we have Adam Thomson staying with us. Since it's his birthday his son Adam has come to share the occasion with him. Now we have a problem. Once the second Adam Thompson checks in, how are we going to tell them apart? They could live at the same address and have the same phone number.
We resolve this by introducing a Primary Key, a new attribute which is guaranteed to be unique in the table. There are generally two ways to go about this. First, we can find a unique number that is natural to the situation, like a Social Security number in an employee table. The other approach is simpler. Use a consecutive number for each record as it is added. Relational database systems are set up to handle this automatically. The point is that every record absolutely must be unique. By using a Primary Key we can guarantee that. Every table in a relational database has a Primary Key. That much is carved in stone.
Now that we've decomposed out data and implemented our primary keys, it's time to relate our tables.
You'll recall that we saw that it was impractical to keep the information about the guest and the information about the visit in the same table. Our solution was to break the data into separate tables.
Now we'll establish a relationship between the guest and visit tables by adding a new attribute to the visit table. This attribute is called a Foreign Key because it matches the primary key in the Guest table. The DBMS is responsible for ensuring that each Foreign Key entry in the Visit table matches the Primary Key of an entry in the Guest table. With that we've established a tight relationship – there's that word again – between the Guest and Visit tables. In another lesson we'd explore the means by which the DBMS maintains the integrity of that relationship in the event that a record in the guest table is deleted.
By splitting the data out like this, we've reduced the space required to store our database. But there's another significant benefit. If our guest changes his address, we only have to make the change in one place – the corresponding record in the guest table. And that means that there's no chance of contradictory data in the database. This is why duplication of data is considered one of the cardinal sins of database design.
In the Structured Query Language (SQL) tutorial we'll look at how we can query the two tables using the primary and foreign keys to obtain a list of all guests with their corresponding visits.