The Get Method – CRUD

So why not call it the ‘Read’ method instead of the ‘Get’ method, are you trying to be purposefully obtuse, Cat?

Truth is, it doesn’t matter what you call it. You could call it the FriedEggs method if you wanted to. If you feel better using Read, then go ahead. The only reason I am using Get is that is what I have gotten used to, and it works for me.

Ok, semantics aside, lets get to the code.

The Code

I’ll start by dumping out all the code, and then will go through it line by line. Simply copy this below and put it into your DB_handler class that we have built in the previous tutorials.

Let’s see it in action first before we start with explanations of the code. To do this, we will need to alter our index.php a little, as that is still set up to insert a record ( see last post )

Updating the Index.php file

Open up the index.php file in your favourite editor and we’ll make some changes.

To keep things simple, we’ll simply comment out the stuff we don’t need and add a few new lines.

So, comment out the code at the end of your index.php file, and note that I’ve pulled out the line that sets the $table variable, as we’ll be using this in all our calls. Your code in index.php should now look like this:

And that’s that. If you now run this code by navigating to index.php, you should see all the records you have added in the previous tutorial printed out to your screen!

The method we have written will also take a record id, and return only that record. So change the line that calls the new get method to the following:

$records = $snowboard_db->get( $table, 1 );

And now when you run it, you should see that only that one record, with an id of 1, will be returned.

So how’s that work? Let’s delve into our method and see.

Nuts and Bolts.

As always, we’ll go through line by line.

public function get($table, $id=false){
try{

You should be comfortable with most of this bit of code by now – we are declaring a public method within our class called get and starting a try-catch block to put our code in, so any errors will be caught.

One thing you may not be familiar with though – look at the parameters being passed to the method. You will note that there are two, the first one $table, is just a simple parameter, no surprises, expecting to receive a table name.

The second parameter , however, you will note has been set to ‘false’. This is simply a way of making this parameter optional. So when calling ‘get’, we can decide to pass this parameter or not. If $id is passed, great, it will retain the value passed. If it isn’t passed, then no worries, no errors will be thrown and $id will get the value of ‘false’. We’’ll see how this is being used in a minute.

Ok, next line,

$query = "SELECT * FROM " . $table;

This is the query that we will be using to talk to out MySQL table with. Pretty simple query, We are selecting all records ( indicated by the * ) from the table that we have passed into the method, identified by $table.

We store this query in $query.

Now we get to the funny business with $id. Have a look at the next chunk of code.

We test the value of $id – remember, we can decide to pass one in or not. If we don’t it will be set to false. So the first ‘if’ statement is checking to see if we have an id. If we do, it goes on to execute the next three lines, and if not, it will execute the second block with only one line.

We’ll look at these in turn.

Getting one record by id

First, a situation where we have passed in an $id. What we have done is tell the get method that we are interested only in the record with the id we have passed in, so the query needs to be modified to do this, and that’s what is happening here in this first block.

We use the concatenation operator to append a WHERE clause to $query. Note that we use :id as the value instead of $id. This is because we are using PDO, and binding our queries to stop any MySQL injection jokers out there. If you need a refresher on this, have a look here.

Now we have the query, we call the prepare method of our PDO database object, and on the result from this, $stmt, we call the bindParam method, and it is here that we finally pass in the $id.

$stmt now contains a PDO object with a fully bound query ready to do the lookup for us.

Getting all records

The second block in the if clause takes is to a one liner where we simply pass in the query that we built right at the start of the method. This will return everything from the table.

This block is executed when we don’t pass in any id.

What we have done is set up a more flexible get method that allows us to determine the type of get we want to do, dependant on the parameters we pass to the method.

Getting the records

Ok, the next line is straight forward:

$count = $stmt->execute();

Here we are finally executing the query that we have set up. This will return the number of records found into $count.

In the next block ,we can deal with the results based on what we got in $count.

So, if $count is more that 0, great, we have one record or more. We will set out $success flag to TRUE, and we will use the PDO method fetchAll to get an array of the results, which we store in the variable $msg.

Note that we are passing a parameter to the fetchAll method, PDO::FETCH_ASSOC. This is merely defining the way we want the results returned. You can look here for further details, and do some experimenting to see the different ways you can get your results.

If, on the other hand, we have no results, there is some very simple error handling with the $success flag being set to FALSE, and a useless message is put into $msg. Obviously in our applications we would so something much more meaningful with our errors :)

Tidy up

The next few code blocks simply wrap things up, all the hard work has already been done.