The "Freeola Customer Forum" forum, which includes Retro Game Reviews, has been archived and is now read-only. You cannot post here or create a new thread or review on this forum.
Following on from PART ONE where we ran through the basics of database design we will now move on to:
CREATING A CONNECTION TO YOUR DATABASE
PHP provides a fairly simple method of creating a connection to your MySQL database. The connection takes the form of a few lines of PHP code. Once created, the lines of code can be added to each page where you want to access your data or, more efficiently, you can create a connections file and then include it in each page.
At this point it would be good if you have read and understood A quick introduction to PHP a serverside scripting language.
PHP uses mysql_pconnect(‘hostname’, ‘user name’, ‘password) to create the connection. This command can be assigned to a variable and used to create a simpler connection through your code.
NOTE: You can also use mysql_connect() with the same parameters to get the same result but I prefer mysql_pconnect because is persistent and will keep the connection open for use with other connection requests instead of closing it down after use.
Let’s get on with it: Create a file called connection.php and copy the following code into it.
Change the above connection details to the ones provided by Freeola when you set up your MySQL database. You don’t need to understand the error catching mechanisms other than to know it will throw an error message if the page fails to connect to your database.
Now that this connection page has been created all you need to do is add the line…
…whenever you want to create a connection prior to retrieving data. And that is the connection done. Don’t forget to add a path before the connection.php if you have created the file in a sub directory.
RETRIEVING DATA
The simplest way of displaying data is in a list or in a table but first we have to write a few lines of code to specify exactly what data you want to show.
You can copy and paste the follwoing section into a new web page (don’t forget the .php extension).
Some quick notes on the above code
The text in the ‘or die’ statement will be shown on your page if there is a connection problem. I often use this to debug which statement has a problem by giving each one a unique reference (or maybe that’s a little bit geeky).
In STEP THREE we are selecting all records from the Employee table created in PART ONE of this post. The writing of SQL statements is a whole other subject. If anyone wants a beginner’s guide to SQL statements then leave a request.
GETTING TO YOUR DATA
Up to STEP SIX we still haven’t seen any data. However, we are now very close to being able to display it on our site.
The variable $employeeResult is actually an array - albeit an array with only one record in it.
We can now do something like…
But of course this is only the first record of the list. We can either execute STEP 5 (which will extract the next record in the set) and STEP 7 repeatedly and echo the result each time OR we can do something much more efficient altogether.
By using a repeat loop we can cycle through the records returned in STEP THREE.
The most common repeat loop is a WHILE LOOP and looks like this:
MYSQL_ASSOC – denotes the method used to extract the associated arrays and it is not necessary to understand in any more detail.
STEP 7b will repeat until $result runs out of records.
This is getting to be more like what we want on a web site. If we now take this one step further and stick the results into a table then life would look even better.
If you know your HTML then this will not look that strange:
The results of the above steps can be seen here
So now you know how to:
- create a MySQL connection and include it in your web page.
- create and execute a SQL query and assign the results to a variable.
- extract the data and display it on a web page.
- loop through a data set and display in a list or table.
I know promised session variables etc.. but I think that’s enough to be going on with for now.
If anyone is interested in the next stage(s) of: getting smarter with queries, formatting results with nested loops, using forms to add, update or delete data and use posted values in queries then let me know.
Cheers
JTD
CREATING A CONNECTION TO YOUR DATABASE
PHP provides a fairly simple method of creating a connection to your MySQL database. The connection takes the form of a few lines of PHP code. Once created, the lines of code can be added to each page where you want to access your data or, more efficiently, you can create a connections file and then include it in each page.
At this point it would be good if you have read and understood A quick introduction to PHP a serverside scripting language.
PHP uses mysql_pconnect(‘hostname’, ‘user name’, ‘password) to create the connection. This command can be assigned to a variable and used to create a simpler connection through your code.
NOTE: You can also use mysql_connect() with the same parameters to get the same result but I prefer mysql_pconnect because is persistent and will keep the connection open for use with other connection requests instead of closing it down after use.
Let’s get on with it: Create a file called connection.php and copy the following code into it.
<?php
$hostname = "mysql.freeola.net";
$database = "sr0123456";
$username = "sr0123456";
$password = "password1";
// now create the connection
$connection = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
?> Change the above connection details to the ones provided by Freeola when you set up your MySQL database. You don’t need to understand the error catching mechanisms other than to know it will throw an error message if the page fails to connect to your database.
Now that this connection page has been created all you need to do is add the line…
<?php include_once(�connection.php�); ?>
…whenever you want to create a connection prior to retrieving data. And that is the connection done. Don’t forget to add a path before the connection.php if you have created the file in a sub directory.
RETRIEVING DATA
The simplest way of displaying data is in a list or in a table but first we have to write a few lines of code to specify exactly what data you want to show.
You can copy and paste the follwoing section into a new web page (don’t forget the .php extension).
<?php
// Step 1 � include the connection details
include_once(�connection.php�);
//STEP 2 � select the database and use the connection
$db = mysql_select_db($database, $connection)
or die ("Couldn't select database.");
//STEP 3 � now write and assign an SQL statement to a variable which we use in the next step to choose the records
$sql = " SELECT * FROM `Employee`";
//STEP 4 � execute the query and assign the results array to a variable $result
$result = mysql_query($sql)
or die("Couldn't execute query statement");
//STEP 5 - extract the first row of the array of results assigned to $result in STEP FOUR and assign the row to $employeeResult
$employeeResult = mysql_fetch_array($result, MYSQL_ASSOC);
//STEP 6 � for potential use later on � assign the number of records returned in STEP FOUR to $numrows
$numrows = (mysql_num_rows($result));
?>
Some quick notes on the above code
The text in the ‘or die’ statement will be shown on your page if there is a connection problem. I often use this to debug which statement has a problem by giving each one a unique reference (or maybe that’s a little bit geeky).
In STEP THREE we are selecting all records from the Employee table created in PART ONE of this post. The writing of SQL statements is a whole other subject. If anyone wants a beginner’s guide to SQL statements then leave a request.
GETTING TO YOUR DATA
Up to STEP SIX we still haven’t seen any data. However, we are now very close to being able to display it on our site.
The variable $employeeResult is actually an array - albeit an array with only one record in it.
We can now do something like…
<?php
// STEP 7a � extract the data and assign it to variables � this isn�t strictly necessary but it make echoing the results much cleaner.
mysql_data_seek($result, 0);
$employeeResult = mysql_fetch_array($result, MYSQL_ASSOC);
$name=$employeeResult['Name'];
$employeeID=$employeeResult['EmployeeID'];
$DoB=$employeeResult['DateOfBirth'];
$address=$employeeResult['Address'];
//and now we can use
echo "ID: $employeeID, Name: $name, DoB: $DoB <br>";
//wherever we want to display the Employee�s details.
?> But of course this is only the first record of the list. We can either execute STEP 5 (which will extract the next record in the set) and STEP 7 repeatedly and echo the result each time OR we can do something much more efficient altogether.
By using a repeat loop we can cycle through the records returned in STEP THREE.
The most common repeat loop is a WHILE LOOP and looks like this:
<?php
// STEP 7b � extract the data and assign it to variables;
while($employeeResult = mysql_fetch_array($result,MYSQL_ASSOC))
{
$name=$employeeResult['Name'];
$employeeID=$employeeResult['EmployeeID'];
$DoB=$employeeResult['DateOfBirth'];
$address=$employeeResult['Address'];
echo "ID: $employeeID, Name: $name, DoB: $DoB <br>";
}?>
MYSQL_ASSOC – denotes the method used to extract the associated arrays and it is not necessary to understand in any more detail.
STEP 7b will repeat until $result runs out of records.
This is getting to be more like what we want on a web site. If we now take this one step further and stick the results into a table then life would look even better.
If you know your HTML then this will not look that strange:
<?php
// STEP 7c � create a table, extract and display the data.?>
<table border="1">
<tr>
<td>ID</td>
<td>Name</td>
<td>DoB</td>
<td>Address</td>
</tr>
<?php
while($employeeResult = mysql_fetch_array($result,MYSQL_ASSOC))
{?>
<tr>
<td><?php echo $employeeResult['EmployeeID'];?></td>
<td><?php echo $employeeResult['Name'];?></td>
<td><?php echo $employeeResult['DateOfBirth'];?></td>
<td><?php echo $employeeResult['Address'];?></td>
</tr>
<?php } ?>
</table>
The results of the above steps can be seen here
So now you know how to:
- create a MySQL connection and include it in your web page.
- create and execute a SQL query and assign the results to a variable.
- extract the data and display it on a web page.
- loop through a data set and display in a list or table.
I know promised session variables etc.. but I think that’s enough to be going on with for now.
If anyone is interested in the next stage(s) of: getting smarter with queries, formatting results with nested loops, using forms to add, update or delete data and use posted values in queries then let me know.
Cheers
JTD
There have been no replies to this thread yet.
Following on from PART ONE where we ran through the basics of database design we will now move on to:
CREATING A CONNECTION TO YOUR DATABASE
PHP provides a fairly simple method of creating a connection to your MySQL database. The connection takes the form of a few lines of PHP code. Once created, the lines of code can be added to each page where you want to access your data or, more efficiently, you can create a connections file and then include it in each page.
At this point it would be good if you have read and understood A quick introduction to PHP a serverside scripting language.
PHP uses mysql_pconnect(‘hostname’, ‘user name’, ‘password) to create the connection. This command can be assigned to a variable and used to create a simpler connection through your code.
NOTE: You can also use mysql_connect() with the same parameters to get the same result but I prefer mysql_pconnect because is persistent and will keep the connection open for use with other connection requests instead of closing it down after use.
Let’s get on with it: Create a file called connection.php and copy the following code into it.
Change the above connection details to the ones provided by Freeola when you set up your MySQL database. You don’t need to understand the error catching mechanisms other than to know it will throw an error message if the page fails to connect to your database.
Now that this connection page has been created all you need to do is add the line…
…whenever you want to create a connection prior to retrieving data. And that is the connection done. Don’t forget to add a path before the connection.php if you have created the file in a sub directory.
RETRIEVING DATA
The simplest way of displaying data is in a list or in a table but first we have to write a few lines of code to specify exactly what data you want to show.
You can copy and paste the follwoing section into a new web page (don’t forget the .php extension).
Some quick notes on the above code
The text in the ‘or die’ statement will be shown on your page if there is a connection problem. I often use this to debug which statement has a problem by giving each one a unique reference (or maybe that’s a little bit geeky).
In STEP THREE we are selecting all records from the Employee table created in PART ONE of this post. The writing of SQL statements is a whole other subject. If anyone wants a beginner’s guide to SQL statements then leave a request.
GETTING TO YOUR DATA
Up to STEP SIX we still haven’t seen any data. However, we are now very close to being able to display it on our site.
The variable $employeeResult is actually an array - albeit an array with only one record in it.
We can now do something like…
But of course this is only the first record of the list. We can either execute STEP 5 (which will extract the next record in the set) and STEP 7 repeatedly and echo the result each time OR we can do something much more efficient altogether.
By using a repeat loop we can cycle through the records returned in STEP THREE.
The most common repeat loop is a WHILE LOOP and looks like this:
MYSQL_ASSOC – denotes the method used to extract the associated arrays and it is not necessary to understand in any more detail.
STEP 7b will repeat until $result runs out of records.
This is getting to be more like what we want on a web site. If we now take this one step further and stick the results into a table then life would look even better.
If you know your HTML then this will not look that strange:
The results of the above steps can be seen here
So now you know how to:
- create a MySQL connection and include it in your web page.
- create and execute a SQL query and assign the results to a variable.
- extract the data and display it on a web page.
- loop through a data set and display in a list or table.
I know promised session variables etc.. but I think that’s enough to be going on with for now.
If anyone is interested in the next stage(s) of: getting smarter with queries, formatting results with nested loops, using forms to add, update or delete data and use posted values in queries then let me know.
Cheers
JTD
CREATING A CONNECTION TO YOUR DATABASE
PHP provides a fairly simple method of creating a connection to your MySQL database. The connection takes the form of a few lines of PHP code. Once created, the lines of code can be added to each page where you want to access your data or, more efficiently, you can create a connections file and then include it in each page.
At this point it would be good if you have read and understood A quick introduction to PHP a serverside scripting language.
PHP uses mysql_pconnect(‘hostname’, ‘user name’, ‘password) to create the connection. This command can be assigned to a variable and used to create a simpler connection through your code.
NOTE: You can also use mysql_connect() with the same parameters to get the same result but I prefer mysql_pconnect because is persistent and will keep the connection open for use with other connection requests instead of closing it down after use.
Let’s get on with it: Create a file called connection.php and copy the following code into it.
<?php
$hostname = "mysql.freeola.net";
$database = "sr0123456";
$username = "sr0123456";
$password = "password1";
// now create the connection
$connection = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
?> Change the above connection details to the ones provided by Freeola when you set up your MySQL database. You don’t need to understand the error catching mechanisms other than to know it will throw an error message if the page fails to connect to your database.
Now that this connection page has been created all you need to do is add the line…
<?php include_once(�connection.php�); ?>
…whenever you want to create a connection prior to retrieving data. And that is the connection done. Don’t forget to add a path before the connection.php if you have created the file in a sub directory.
RETRIEVING DATA
The simplest way of displaying data is in a list or in a table but first we have to write a few lines of code to specify exactly what data you want to show.
You can copy and paste the follwoing section into a new web page (don’t forget the .php extension).
<?php
// Step 1 � include the connection details
include_once(�connection.php�);
//STEP 2 � select the database and use the connection
$db = mysql_select_db($database, $connection)
or die ("Couldn't select database.");
//STEP 3 � now write and assign an SQL statement to a variable which we use in the next step to choose the records
$sql = " SELECT * FROM `Employee`";
//STEP 4 � execute the query and assign the results array to a variable $result
$result = mysql_query($sql)
or die("Couldn't execute query statement");
//STEP 5 - extract the first row of the array of results assigned to $result in STEP FOUR and assign the row to $employeeResult
$employeeResult = mysql_fetch_array($result, MYSQL_ASSOC);
//STEP 6 � for potential use later on � assign the number of records returned in STEP FOUR to $numrows
$numrows = (mysql_num_rows($result));
?>
Some quick notes on the above code
The text in the ‘or die’ statement will be shown on your page if there is a connection problem. I often use this to debug which statement has a problem by giving each one a unique reference (or maybe that’s a little bit geeky).
In STEP THREE we are selecting all records from the Employee table created in PART ONE of this post. The writing of SQL statements is a whole other subject. If anyone wants a beginner’s guide to SQL statements then leave a request.
GETTING TO YOUR DATA
Up to STEP SIX we still haven’t seen any data. However, we are now very close to being able to display it on our site.
The variable $employeeResult is actually an array - albeit an array with only one record in it.
We can now do something like…
<?php
// STEP 7a � extract the data and assign it to variables � this isn�t strictly necessary but it make echoing the results much cleaner.
mysql_data_seek($result, 0);
$employeeResult = mysql_fetch_array($result, MYSQL_ASSOC);
$name=$employeeResult['Name'];
$employeeID=$employeeResult['EmployeeID'];
$DoB=$employeeResult['DateOfBirth'];
$address=$employeeResult['Address'];
//and now we can use
echo "ID: $employeeID, Name: $name, DoB: $DoB <br>";
//wherever we want to display the Employee�s details.
?> But of course this is only the first record of the list. We can either execute STEP 5 (which will extract the next record in the set) and STEP 7 repeatedly and echo the result each time OR we can do something much more efficient altogether.
By using a repeat loop we can cycle through the records returned in STEP THREE.
The most common repeat loop is a WHILE LOOP and looks like this:
<?php
// STEP 7b � extract the data and assign it to variables;
while($employeeResult = mysql_fetch_array($result,MYSQL_ASSOC))
{
$name=$employeeResult['Name'];
$employeeID=$employeeResult['EmployeeID'];
$DoB=$employeeResult['DateOfBirth'];
$address=$employeeResult['Address'];
echo "ID: $employeeID, Name: $name, DoB: $DoB <br>";
}?>
MYSQL_ASSOC – denotes the method used to extract the associated arrays and it is not necessary to understand in any more detail.
STEP 7b will repeat until $result runs out of records.
This is getting to be more like what we want on a web site. If we now take this one step further and stick the results into a table then life would look even better.
If you know your HTML then this will not look that strange:
<?php
// STEP 7c � create a table, extract and display the data.?>
<table border="1">
<tr>
<td>ID</td>
<td>Name</td>
<td>DoB</td>
<td>Address</td>
</tr>
<?php
while($employeeResult = mysql_fetch_array($result,MYSQL_ASSOC))
{?>
<tr>
<td><?php echo $employeeResult['EmployeeID'];?></td>
<td><?php echo $employeeResult['Name'];?></td>
<td><?php echo $employeeResult['DateOfBirth'];?></td>
<td><?php echo $employeeResult['Address'];?></td>
</tr>
<?php } ?>
</table>
The results of the above steps can be seen here
So now you know how to:
- create a MySQL connection and include it in your web page.
- create and execute a SQL query and assign the results to a variable.
- extract the data and display it on a web page.
- loop through a data set and display in a list or table.
I know promised session variables etc.. but I think that’s enough to be going on with for now.
If anyone is interested in the next stage(s) of: getting smarter with queries, formatting results with nested loops, using forms to add, update or delete data and use posted values in queries then let me know.
Cheers
JTD