PHP & MySQL

PHP & MySQL

By ghost | 4102 Reads |

0     0

PHP & MySql tutorial by BobbyB.

Getting PHP to interact with databases is an extremely useful thing to be able to do, and really isn't that complicated, but to many people seems too much like hard work, so they don't bother. Anyway, here's what I hope to be a fairly comprehensive yet definitive tutorial on the matter.

For this tutorial, you're expected to have a fairly good basic knowledge in PHP. This isn't a PHP tutorial, so you shouldn't be here if that's what you're expecting. By the same token, I'm also not going to teach you any SQL commands.

Anyway, on to the tutorial…

So set up your database using cPanel or whatever new fangled contraption you kids have nowadays, and fire up notepad so we can get our geek on and code some PHP.

First of all, you're going to need to connect to your database. That's done with a cheeky little function, mysql_connect().

mysql_connect takes three arguments.

1. The server the database is on (99% of the time this will be localhost)
2. Your username on the server
3. Your password for that server

So, the command to connect to your database should look a little something like this:

mysql_connect(localhost,"Username","Password");

Hard? I think not.

Great. But the fun doesn't stop there. Now, we select our database. We do this with the @mysql_select_db(). This takes just one argument, and that's the database to be selected. If the database is called "database", then the function would go like this:

@mysql_select_db(database);

Now, it's time for queries. Like I said, you should already know SQL commands, relieving me the bother of listing them. Well, let's say you wanted to create a table called "badboytable" and have to fields, "firstname" and "lastname".

We'd do this using the mysql_query() command. The one argument that commandd takes is, fairly obviously, the query you wish to execute. So, the code could look like this:

mysql_query("CREATE TABLE badboytable (firstname text, lastname text)");

Or, we could do it like this:

$query = ("CREATE TABLE badboytable (firstname text, lastname text)"; mysql_query($query);

Whichever floats your boat.

I know what you're thinking. You're thinking "BobbyB, that's all well and good, but what if I want to display data that I have in my table? WHAT DO I DO? WHY MUST YOU TORMENT ME SO?". Or possibly you're not. I don't care, I'm going to tell you how to do it, and you're gonna sit there and like it.

Let's get back on track, shall we?

I'm just going to assume that have some data in badboytable. If not, you can either hope the database fairies put some in for you, or you can put some in yourself. I'd suggest the latter.

Right. First of all, say we want to use the query "SELECT * FROM badboytable". First off, we put it through mysql_query(), only this time we assign the result to a variable, which I have called "result". This goes as follows:

$query = "SELECT * FROM badboytable"; $result = mysql_query($query);

Now, it's not quite as simple as "echo $result;". No, there's more to it than that. First of all, you've got to know how many different results you've returned, and that's quite simply done with the mysql_numrows(), which is a command which returns the number of rows in a result. So, let's do that:

$rows = mysql_numrows($result);

Not hard. Now, to get some actual data out of the result, we've got to use the mysql_result() command. This command takes three arguments:

1. The variable in which the result is contained (in our case, $result).
2. The row in the result which you are trying to retrieve. (0 is the first row, and the final row is one less than the total number of rows).
3. The field you want to return.

So, if we want to return all the data we've just collected with our query, we're going to have to use a cheeky little for() loop.

The comments beside will show you what's going in.

for($i = 0; $i < $rows; $i++) //this will loop through all the rows in our result { $first=mysql_result($result, $i, "firstname"); //Get the "firstname" field on the current row $last=mysql_result($result, $i, "lastname"); //Get the "lastname" field on the current row echo "$firstname : $lastname"; //Display the data }

That's not so bad.

Now, the one last thing to remember is the mysql_close() command. Stick this at the end of any scripts which interact with a MySql database:

mysql_close();

OK there, you have it. A crash course in PHP and MySql.