Share on FacebookI'm working as a technician and as a programmer and 90% of my programming is done in PHP which is a great language but some see it as an amateur language. This language is for me a great language to learn and it's also a very capable language. Facebook is a great example of an application running PHP. This guide however is not for beginners since there is already a lot of beginner guides out there. This is for programmers who is ready to take the next step and how shuffle more than 5 values to and from a database.
The first big site i developed the SQL statements where a headache and after a while I found out that classes would be something that might come in handy. So read on and please comment if, makes the guide better and helps people to make PHP a better language.
The issue with big SQL-strings is that you sometimes get lost in them, especially the INSERT strings. An example below.
INSERT INTO tbl (var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, var11, var12, var13,var14) VALUES ($var1,$var2,$var3,$var4,$var5,$var6,$var7,$var8,$var9,$var10,$var11,$var12,$var13,$var14)
This is a messy code and we need a way of loading our variables and then just hit save.
The solution
Classes is a great way of manage you data and to have a decent structure in your code.
In a nutshell we are going to create a class, give it some properties, function and then load and save our data to a database.
Let's go!
I like using the car as an example so we are going to use that. A car have some characteristics like make, model, colour, engine and so on.
If we would build us a car inventory we need a class called car. So create a new file called classes.php and in that file we need to create a function that initialize a connection to our database. In this example I will use a ODBC connection. but you can use what ever connection you like, as it returns a connection handle.
function db_conenct()
{
$conn_id = odbc_connect("ODBC-DSN", "", "") or die ("Could not connect");
return $conn_id;
}
Then we will create the class car and the properties we wish to use is, make, model, colour. We also need a way of keeping track of our items in the database so we need to add Id and then I always have a Deleted column since it's easier to set to false again then to restore database from backup. Insert the following code in our classes.php
class Car
{
var $Id;
var $Make;
var $Model;
var $Colour;
var $Deleted;
var $connection_id;
function Car($conn)
{
$this->connection_id = $conn;
}
}
Notice the function with the same name as the class, this is called a constructor and is run every time you load a create a class in your code. But we need to insert and read some data from our class. So we need 4 functions and then we can do whatever we want with our car data. First of we need to get data for a specific car and we use the Id column to identify our cars. Insert the following function.
function GetCarById($id)
{
$sql = "SELECT * FROM tblCars WHERE Id = $id";
$result = odbc_exec($this->connection_id, $sql);
$data = odbc_fetch_array($result);
$this->Id = $data['Id'];
$this->Model = $data['Model'];
$this->Colour = $data['Colour'];
$this->Deleted = $data['Deleted'];
$this->Make = $data['Make'];
}
This function send a query to the database and get a result back, loads the current class's variables from the database, notice that we write $this->Id instead of $Id or $car->Id. The command $this-> tells php that THIS is for THIS instance of the class car. It is very important that we use the $this-> command. Next we need a function to get all the cars since we probably would like to make a list of cars.
Here comes a nice way of doing business, we only get the Id from the database and return all the data in an Array. We then use the Id number to get the actual database row. Go ahead and insert the following function in our class.
function GetAllCars()
{
$sql = "SELECT Id FROM tblCars Where Deleted = false Order by Make DESC";
$result = odbc_exec($this->connection_id , $sql);
$i = 0;
while($data = odbc_fetch_array($result))
{
$DataArray[$i] = $data['Id'];
$i++;
}
return $DataArray;
}
Notice the SQL statement we select just Id and we filter out the deleted rows and get an array in return. Now we can move on to our main page where we list our cars.
Create a file and a table with the columns (make,model,colour).
On top of our file we need to include our classes.php and this is done with require_once():. Go ahead and add our classes.php on row 1.
Below you have an example showing how this works.
<html>
<head>
<title>Nisses class example</title>
</head>
<body>
<table border="0">
<tr>
<td>Make</td>
<td>Model</td>
<td>Colour</td>
</tr>
<?
$clsCar = new Car(db_connect());
$DataArray = $clsCar->GetAllCars();
for($i = 0; $i < count($DataArray);$i++)
{
if($i % 2 == 0)
{
$bgcolor = "#FFFFFF";
}
else
{
$bgcolor = "#D8D8D8";
}
$clsCar->GetById($DataArray[$i]);
echo "<tr bgcolor=" . $bgcolor . ">";
echo "<td>" . $clsCar-Make . "</td>";
echo "<td>" . $clsCar-Model . "</td>";
echo "<td>" . $clsCar-Colour . "</td>";
echo "</tr>";
}
?>
</table>
</body>
</html>
The row $clsCar = new Car(db_connect()); creates a connection to our database. Remember the function db_connect that returns a connection handle. It's then taken care of by our constructor and stored in the class variable $this->connection_id . We have now a class called $clsCar and what we need to do is to get a list of all non deleted cars. We need to call our function GetAllCars and store the returned value in an array. $DataArray = $clsCar->GetAllCars(); does that for us. We now have ourselves an array of Id's on which we can query the database for actual items. So we loop the array until it ends and print out the data. Notice that we don't use the $this-> anymore. We are only interested in the instance we created $clsCar and only for this user.
I've been using this method for getting data from tables for a few years now and it proves to be very scalable since all we need to do if you like to add a property there is not that much work in doing that. Next step is to insert and update our data. The next step is in the works, stay tuned!