Hello, Rune-Server.
This tutorial will be taking us another step down the line of PHP into connecting to a database and retrieving information from the tables and columns inside that database, so this will cover all aspects of PHP/SQL connections. This is not a tutorial on SQL, however I will include some useful statements that you can use. Without further ado, my MySQL > PHP Connections tutorial.
N.B - Please note that I expect you to have a database already set up and ready to use, this is only for connections to your tables.
[hr]
Part 1 - Populating your MySQL table.
First of all, you want to add a table and add fields to your table, this is done through the use of an SQL script. I realise this can seem a little daunting, but it's not hard. As usual, I'll give you the code and then I'll talk through it after:
Code:
CREATE TABLE IF NOT EXISTS `tut` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`text` text NOT NULL,
`date` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
)
So, your first line of your script is more or less telling MySQL to create the table "tut" if it doesn't already exist. Your next line is then defining the row "id" this is an integer, which can be up to 11 characters long. It is a blank value, so it isn't null, because there's a difference between empty and a blank value and nothing. A blank value is something, where if there is no value, it is nothing. This is also auto incrementing which means when every new record is added, it gives it a new id, 1 higher than the previous id. You then create the row of "title" this is declared as a varchar holding up to 50 characters, a varchar is a variable-length string. Again, a blank value. You then create the row "text" this is declared as text, which is self explanatory and is again, a blank value therefore not null. Your final row is the date, this is a varchar (variable-length string) holding up to 25 characters, again a not null value since it is blank and not nothing. We're then declaring our primary key as the row of "id". This is more used for your most vital tables, as somewhat of a search process.
Now that you've done that, you want to click "insert" and then insert the values you wish each of these to hold. For example;
Title - Tutorial!
Text - Welcome to Ruby's tutorial!
Date - 3/9/12
The ID isn't needed since it's auto incrementing.
If you didn't understand that, have a run back through it a few times until you do.
Moving swiftly on to our next port of call; connecting, opening and closing through PHP.
So, after creating and understanding our table, here is the PHP part. We'll use three very simple functions here; mysql_connect(), mysql_select_db(), mysql_close(). These aren't hard to remember, but if you aren't good at remembering functions such as this, write them down somewhere or bookmark this tutorial for easy access. As usual I'll give you the coding then I'll talk you through it.
Code:
$username = "yourmysqlusername";
$password = "yourmysqlpassword";
$database = "yourprefix_tut";
$server = "localhost"; //99% of the time this is localhost, if it isn't, you'll know what it is.
$connect = mysql_connect($server, $username, $password);
$found = mysql_select_db($database, $connect);
if ($found) {
echo 'database located!';
mysql_close($connect);
} else {
echo 'database could not be located, rewrite your code';
}
This above code is really simple to understand so I won't go into too much detail. All you're doing is declaring 4 variables holding information on your database. $username, $password are your username and password. $database is the database you wish to select and $server is the server on which your database is hosted; usually localhost. We now declare the variable $connect this holds the function mysql_connect();, which now has $server, $username and $password in it, all the information you need to connect. We then declare $found for the database being found, this is where we make use of the mysql_select_db(); function, inside this function we have $database which is the database we want to connect to and we include $connect for good measures. We then start an if statement saying that if $found works and we find the database and manage to connect, it echoes some code saying we managed it. If we didn't manage to connect it tells us to re-write our code. However, it should work 100% since I just tested it two minutes ago.
Part 3 - Selecting data from the database.
Now that you've connected, we can now start to retrieve information from the database that you populated with the record from the first part.
Now, for this we will be using our script we created two minutes ago with some new code added in. I'll post the new script and then I'll talk you through it again, as usual.
Code:
$username = "yourmysqlusername";
$password = "yourmysqlpassword";
$database = "yourprefix_tut";
$server = "localhost"; //99% of the time this is localhost, if it isn't, you'll know what it is.
$connect = mysql_connect($server, $username, $password);
$found = mysql_select_db($database, $connect);
if ($found) {
$SQL = "SELECT * FROM yourprefix_tut";
$result = mysql_query($SQL);
while ($dbrow = mysql_fetch_assoc($result)) {
echo $dbrow['ID'] . "<br />";
echo $dbrow['title'] . "<br />";
echo $dbrow['text'] . "<br />";
echo $dbrow['date'] . "<br />";
}
mysql_close($connect);
} else {
echo 'database could not be located, rewrite your code';
}
Okay, so here's the break down. Everything has been explained above apart from this code:
Code:
$SQL = "SELECT * FROM yourprefix_tut";
$result = mysql_query($SQL);
while ($dbrow = mysql_fetch_assoc($result)) {
echo $dbrow['ID'] . "<br />";
echo $dbrow['title'] . "<br />";
echo $dbrow['text'] . "<br />";
echo $dbrow['date'] . "<br />";
}
So this is all I'll explain. First off, we're declaring $SQL which contains an sql query using the SELECT function in SQL. This also has a "*" in it, this just means all, the FROM is then asking from which database you wanted it loaded. We're then doing $result as our sql query, using a new function "mysql_query();" which now contains the $SQL variable which has all the information the query needs to run. We then start a while loop, if you haven't read my previous tutorials there's one about loops in there. We're declaring the new variable $dbrow which equals the data returned by $result from the SQL query. We're then echoing $dbrow and it's actual rows, e.g `text` and `title` as ['text'] and ['title']. If you have read my arrays tutorial, you'll realise this information has been returned in an array! After these is just simple line breaks, remember your " . " or it won't work, see previous tutorials as to why.
So you have now successfully wrote a database, created a table, added rows to a table, added records to that table, wrote a PHP script to access that database and then echoed the results you added in to that table earlier, happy? Good.
We're now finished with this tutorial.
Until then,
Ruby.