MySQL Database basics – part 2

MySQL Database basics – part 2

In this article I will explain useful approach helping to split data between tables. Obviously, all data should be correctly inserted in tables’s fields. Common mistake beginning programmers sometimes make is using the same data in two or more tables. I mean, lets say in one table we have several fields with information concerning customer first name, last name, address (street), town, country, all this with unique user id (auto incremented unsigned int as primary field). In second table – again – auto incremented unsigned int as primary field which is unique order id and user id field related to this order id.

Let’s assume that there is no table with nicely placed order id and user first and second name and order status – all in one row. The needed data is there in database, but it is split between two tables. So, to make life a bit “easier” some programmers create third table, especially for orders ids related to customer first and last name and order status. Now, it is possible to use simple SELECT command in queries to database to get exactly what is needed. However this is wrong solution, because this way our database becomes bigger (and slower) only because of creation of new tables filled with data which already exists in other tables.

To find the right solution, we have to realize that in our example both tables have common field: userid. If two tables have common field it is possible to join them in a special, very useful way, to list relations between fields in both tables. Join ties both tables together as if there was one big table with all fields from both tables. Now it is possible to SELECT whatever fields we like.

The only problem is that SELECT command for joined tables is a bit more complicated than basic SELECT. Let’s see an example:

tables:

1. users table (t_users):
userid | firstname | lastname | address | town | country

2. orders table (t_orders)
orderid | userid | orderstatus

query example (PHP)

$query = "SELECT t_users.firstname, t_users.lastname, t_orders.orderid, t_orders.orderstatus ".
"FROM t_users, t_orders ".
"WHERE t_users.userid = t_orders.userid"; //common field userid is used as a connector

and code for listing fields: firstname, lastname, orderid, orderstatus

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['firstname']."-".$row['lastname']."-".$row['orderid']."-".$row['ordrstatus'];
echo "
";
}