Outputting updates stored on a database.
So this is my first tutorial, so it may not be the best. I am writing this for a friend, but I may as well upload here the help others.
What I am going to try to TEACH (rather the do for you) is how to load data from database using SQL then output it in a nice format. To save questions being asked, I am going to assume some basic knowledge of web technologies, so will explain what I am doing and trust you can work out the simple bits.
Firstly, we need to decide how our data is stored in the database. For this tutorial, we are going to be storing the updates for a site, so the table will be called ‘updates’. We will then have four fields in this table, The ID for each update, the DATE, the SUBJECT and the actual UPDATE. I wont go into how to make this table as most hosts have tools to help you do this. One thing to bare in mind though, you need to make sure the ID auto increments on each update and is set to be unique.
Now for the code, we first need the HTML stuff that starts all pages, which I will not give you. The first bit PHP code we will use is that which will connect us the our database.
<?php
$server = mysql_connect($host,$username,$password)
$databse = mysql_select_db($database)
?>
The first line connects to your database server, the second selects the actual database you want to use. If you want this code explain in more detail read this tutorial, http://www.newgrounds.com/bbs/topic.php?id=52 7971.
We now need to load that data from the database. This is done in one simple line,
<?php
$result = mysql_query("SELECT * FROM updates ORDER BY id DESC");
?>
This command will select all of the data from the updates table, order it by the ID going from highest to lowest.
We will now output the data to the browser.
while ($updates = mysql_fetch_array($result)){ /* for each row that the query returned */
?>
<div> /* make a new DIV for each update */
<p>
<?php
echo stripslashes($updates[‘subject’]); /* output the subject for this update */
?>
</p>
<p>
<?php
echo $updates[‘date’]; /* output the data of this update */
?>
</p>
<p>
<?php
echo nl2br(stripslashes($updates[‘update’])); /* output the actual contect of this update */
?>
</p>
</div> /* close the DIV for this update
<?php
}
This code will loop through for each update that is stored in the table. Each time it loops, it goes to the next highest ID (as that is what we said to order the data by). It will make a new DIV (this means that we can format the look of each update so that it appears in its own box) and output the subject, data and content of the update.
Right, that is it! You can now store your data in a nice simple database and have it shown back real simply. If you made a form that let you update the database and added in users, you will have yourself a simple forum!
* * * NOTES * * *
Obviously this code is very simple, the major flaw with it being that it will output EVERY update, so if you have 100 updates, all 100 will be shown to the user at once. This can be very simple to fix, and if people want me to, I will write a follow up tutorial explain how to do this.
while ($updates = mysql_fetch_array($result)){
Let me explain this line a bit more. ‘mysql_fetch_array()’ is an inbuilt PHP function. What it dose is turn the big long list of data the was retrieved via the SQL statement and turn it into a nice simple to manage array, with the name $updates. However, it works in a very nice different way to normal arrays. You do not use this to move from different elements in the array. As you called it as the condition for the while loop, it is automatically moved through each time the loop runs.
echo nl2br(stripslashes($updates[‘update’]));
I’m sure you know how the echo function works. But here we are echo-ing what Is returned after two functions have been run on the data. These two functions are because of how I have stored the data in the database. Firstly, the ‘stripslashes()’ function. When the data is stored into the data base, people can enter data the will harm it, to get around this, you can use a PHP function that makes it safe to give to SQL (SQL is very littoral, you tell it jump of a cliff and it will!) ‘stripslashes()’ just reverse this process. ‘nl2br()’ is another function the is need because of the way data is stored in a database. When data from a text area is stored, where ever you have pressed ‘enter’/’return’ it is stored as white space, but HTML dose not understand white space. ‘nl2br()’ is their for needed so that it adds in this white space in the form of the <br /> tag
j