php
Selecting Data from the Database
Now I have some data in my database (I added a few more lines using the form), I want to display them on the page. Below the form code in index.php, I added the below…
<ul>
<?php
$sql = mysql_query("SELECT * FROM form_test");
while ($row = mysql_fetch_row($sql)) {
echo "<li>$row[0] $row[1]</li>";
}
?>
</ul>
This has now produced a list of all the entrys that are currently held within the database. I would now like to build that list into a menu that will take the user to an individual page to display the content on it’s own. In retrospect, had I planned to do this from the start, I would have added another column in the table called ‘title’ and used that for the menu. However, I didn’t so the row ID will have to do and just imagine it’s the title!!
<ul>
<?php
$sql = mysql_query("SELECT * FROM form_test");
while ($row = mysql_fetch_row($sql)) {
echo "<li><a href=\"results.php?id=$row[0]\">$row[0]</li>";
}
?>
</ul>
I now have a list on the screen with just the ID of the row going from 1 to 6, each as a hyperlink pointing at ‘results.php?id=’ and the id number. Fantastic! We’re getting somewhere! To build results.php, I’m going to use the $_GET[id] string, but before I do that I’m going to put it into it’s own variable of $id should I need to use it again on the page.
<?php
$id = $_GET[id];
$sql = mysql_query("SELECT * FROM form_test WHERE id=$id");
while ($row = mysql_fetch_row($sql)) {
echo "<h1>$id</h1><p>$row[1]</p>";
}
?>
Excellent! Now to work out what we’re going to do with this new knowledge…
Adding Data to the Database with PHP Form
Following on from my last post http://www.shaungill.co.uk/2009/03/first-things-first-connecting-to-the-database/ I’ve got round to writing the form to enter data into the MySql database using a really simple form. I’ll post the code first and then make a few comments about it (the code still uses the db_config.php file I wrote last time)…
index.php
<body> <h1>Hello World!!</h1> <form action="post.php" method="post"> <input type="text" name="post_string"> <input type="submit" value="Post"> </form> </body> </html>
post.php
<body>
<?php
$sql="INSERT INTO form_test (text)
VALUES
('$_POST[post_string]')";
if (!mysql_query($sql,$conn))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($conn)
?>
Your comment of "<?php echo $_POST["post_string"]; ?>" has been entered into the database!
</body>
</html>
The index.php file is basically the worlds simplest form with one text box with a name of ‘post_string’, and the form then posts to ‘post.php’.
Post.php is where all the action takes place.
- $sql=”INSERT INTO form_test (text) - this is building the query string which will put the data we enter from the form into the database
- (’$_POST[post_string]‘)”; - takes the data from the form field ‘post_string’ and builds it into the query
- if (!mysql_query($sql,$conn)) - now, I’ve been doing my homework and the exclamation mark ! is a php negation operator and basically flips a false value to a true value and vise versa. The mysql_query($sql,$conn) is trying to make a connection to the database, and if it fails will return a value of false, so with the exclamation mark it reads “If running the query fails, then…”
- die(’Error: ‘ . mysql_error()); - “… stop the script and give the error message, otherwise…”
- echo “1 record added”; - “… tell the nice people that 1 record has been added”
- mysql_close($conn) - closes the database connection
- Your comment of “<?php echo $_POST["post_string"]; ?>” has been entered into the database! - I added this line for my own peace of mind, I wanted visual confirmation that the text that I had written in the form was actually in the $_POST['post_string'] array.
Thats me done for the night now, next time I’ll have a go at pulling the data out of the database and display it on the page.
