July 31st, 2007 at 09:00pm
Under MySQL+ PHP+ Tutorials
What is a community without friends? Not much really… So thats why this tutorial will cover how to add friends to the users presentation as bookmarks. We will also go over how to search the database for new friends to add to your list.
The code we write in this tutorial all goes in the same file (members.php) that we started in the previous tutorial. First lets look at how to add a new friend.
if(isset($_GET["add"]))
{ // add to your list of friends
require_once("classes/DbConnector.php");
$username = $_SESSION["username"];
$friend = $_GET["add"];
$db = new DbConnector();
$db->connect();
$query = "SELECT * FROM friends WHERE member='$username' AND friendwith='$friend'";
$result = $db->query($query);
$exist = mysql_num_rows($result); // Does the row exists?
if($exist=='0')
{
$query = "INSERT INTO friends(member,friendwith) VALUES('$username','$friend')";
$db->query($query);
echo "You are now friends with $friend";
}
else
{
echo "$friend is already in your list of friends!";
}
}We call this code with members.php?add=friend where “friend” is the friend we want to add to our list. This function should speak for itself by now but what we do is that we first check if the friend we are trying to add already exists in the list if not we add it to the database.
In order to get new friends there has to be some kind of search function. This is extremely easy to accomplish with our setup.
if(isset($_GET["find"]))
{ // Search for a user
require_once("classes/DbConnector.php");
$username = $_POST["username"];
$db = new DbConnector();
$db->connect();
$query = "SELECT * FROM members WHERE username LIKE '%$username%'";
$result = $db->query($query);
$exist = mysql_num_rows($result); // Does the row exists?
if($exist=='0')
{
echo "No match found";
}
else
{
echo "Your matches for string: $username<br><br>";
while($currow = mysql_fetch_array($result))
{
echo "<a href=\"member.php?id=".$currow["username"]."\">".$currow["username"]."</a><br/>";
}
}
}>
We use this code by calling members.php?find=username where username is our search string. The cool thing here is this row:
“SELECT * FROM members WHERE username LIKE ‘%$username%’”;
The LIKE function compares our search string with the usernames stored in the database and if the username contains the letters in the string they are returned.
So we don’t need to search for the full username, if we type in the letter a all entries in the database containing an a will be returned.
Ok we are now almost done, just one feature left. The guestbook.
if(isset($_GET["sign"]))
{ // Sign the guestbook
require_once("classes/DbConnector.php");
$username = $_GET["sign"];
$from = $_GET["from"];
$message = $_POST["message"];
$time = date("F j, Y, g:i a");
$db = new DbConnector();
$db->connect();
$query = "INSERT INTO guestbook(owner,postedby,post,time) VALUES('$username','$from','$message','$time')";
$db->query($query);
echo "Your message has been posted";
}We will also need to add the following to our view function that we made in part 4 to make the guestbook visible on the presentation.
// Display guestbook
$owner = $rows["username"];
$query = "SELECT * FROM guestbook WHERE owner='$member' ORDER BY ID DESC";
$result = $db->query($query);
while($gbrows = mysql_fetch_array($result))
{
echo "Posted by: <a href=\"member.php?id=".$gbrows["postedby"]."\">".$gbrows["postedby"]."</a> - ".$gbrows["time"]."<br/>".$gbrows["post"]."<br><br>";
}
echo "<center><br/><b>Sign $owner:s guestbook</b><br/>
<form action=\"member.php?sign=$owner&from=".$_SESSION["username"]."\" method='POST'>
<textarea name='message' rows='5' cols='30' align='left'>Your message</textarea><br/>
<input type='submit' value='Update' name='submit'>
</form>
</center>
";Next part: Finishing touches
By Stefan
July 31st, 2007 at 08:59pm
Under MySQL+ PHP+ Tutorials
In the previous tutorial we learned how to register a new user and how to create a login system with php sessions. With this done we can now move on to the users personal presentation page. So in this tutorial we will first go over how to display a presentation and then how to let the user edit his own page.
Pretty much everything of this will go in a file I have called members.php, let’s go over the file from top to bottom.
<?php
// members.php
if(isset($_GET["id"]))
{
// Check if user exists in the database
$member = $_GET["id"];
require_once("classes/DbConnector.php");
$db = new DbConnector();
$db->connect();
$query = "SELECT * FROM members WHERE username='$member'";
$result = $db->query($query);
$exists = mysql_num_rows($result); // Does the row exists?
if($exists !="0"){ // Presentation exists so display it
$rows = $db->fetchArray($result); // Get the profile from database
echo $rows["presentation"]."<br/><br/>";
//TODO: Display guestbook here
}
else
{
echo "That member does not exist";
}
}
?>This code is fairly simple. Basically what it does is that it gets the variable id that is passed to the file as an argument (remember how the login function forwarded to this page in the previous tutorial?) and then checks to see if there is a user by that name in the database. If there is a match that users presentation is displayed on the page.
Next on the list of features to add is the possibility to edit the presentation.
if(isset($_GET["edit"])) // Edit profile
{
// First lets make sure the user is logged in
if(session_is_registered("username") && session_is_registered ("password") && $_SESSION["username"] == $_GET["edit"])
{
if(isset($_GET["update"]))
{
require_once("classes/DbConnector.php");
$member = $_GET["edit"];
$db = new DbConnector();
$db->connect();
$presentation = $_POST["presentation"];
$query = "UPDATE members SET presentation='$presentation' WHERE username='$member'";
$result = $db->query($query);
echo "Profile updated!";
}
else
{ // Display edit box
require_once("classes/DbConnector.php");
$member = $_GET["edit"];
$db = new DbConnector();
$db->connect();
$query = "SELECT * FROM members WHERE username='$member'";
$result = $db->query($query);
$rows = $db->fetchArray($result);
echo "<center><br/><b>Edit your profile</b><br/>
<form action=\"member.php?edit=".$_GET["edit"]."&update\" method='POST'>
<textarea name='presentation' rows='10' cols='80' align='left'>"
.$rows["presentation"].
"</textarea><br/>
<input type='submit' value='Update' name='submit'>
</form>
</center>
";
}
}
}If we now call members.php?edit=username an edit box will appear that allows the user to edit his presentation. When the edit form is submitted we update the database with the new presentation.
Next part: Friends and guestbook
By Stefan
July 31st, 2007 at 08:57pm
Under MySQL+ PHP+ Tutorials
In this part of the tutorial we are going to create the back end database that will hold all the information that we need to save for our script. We will create the table structure to prepare the database for the information we want it to hold. We will also create a wrapper class for MySQL that we will use to make our database calls as painless and clean as possible.
Pre requirements
In order to complete this tutorial you will need a MySQL database. Some basic knowledge of the SQL syntax would’nt hurt as well.
Creating the database tables
For our basic community script we are going to need three different tables in our database.
- members - Holds user information (username, password etc).
- guestbook - Used by our guestbook class to hold posts in our members guestbooks.
- friends - Keeps a list of friends.
The structure of the table members looks like this:
CREATE TABLE `members` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`presentation` mediumtext NOT NULL,
PRIMARY KEY (`id`)
) ;
If you open up PHPMyAdmin or your database administration tool of choice and paste the code above in the query window you should have the first table loaded into the database. The fields should be pretty self explanatory, basically we save the username and password of the current member along with his personal presentation.
Moving on to the next table “guestbook” the structure looks like this:
CREATE TABLE `guestbook` (
`id` int(11) NOT NULL auto_increment,
`owner` varchar(32) NOT NULL,
`postedby` varchar(32) NOT NULL,
`post` varchar(1024) NOT NULL,
`time` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ;
What all the fields in the table does will become clear once we get to implement the guest book so don’t get discouraged if you are not sure how this is supposed to work. Anyway the idea is to save all posts by all users on every guest book in a single table for the simple reason that it is the easiest way to do it. The field “owner” identifies who the current post belongs to.
The simplest table of them all is the table used to hold the relationships between members.
CREATE TABLE `friends` (
`id` int(11) NOT NULL auto_increment,
`member` varchar(32) NOT NULL,
`friendwith` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ;
The field “member” identifies a current member of the community and “friendwith” tells us who he has added as a friend to his presentation.
The database class
Now when we got the database up and running we will need a way to put some data into it. PHP comes with a nice set of functions by default that is quite easy to use. However i prefer to use my own wrapper class around the standard PHP functions to make things a bit easier to use and to improve readability of the code.
<?php
////////////////////////////////////////////////////////////////////////////////////////
// Class: DbConnector
///////////////////////////////////////////////////////////////////////////////////////
require_once 'DbVars.php';
class DbConnector extends Dbvars {
var $theQuery;
var $link;
var $dbname;
var $host;
var $user;
var $pass;
function DbConnector(){
// Load settings from parent class
$settings = Dbvars::getSettings();
// Get the main settings from the array we just loaded
$this->host = $settings['dbhost'];
$this->dbname = $settings['dbname'];
$this->user = $settings['dbusername'];
$this->pass = $settings['dbpassword'];
}
function setDatabase($ndbname)
{
$this->dbname = $ndbname;
}
function connect()
{
// Connect to the database
$this->link = mysql_connect($this->host, $this->user, $this->pass)or die(mysql_error());
mysql_select_db($this->dbname);
}
function query($query) {
$this->theQuery = $query;
$res = mysql_query($query, $this->link)or die(mysql_error());
return $res;
}
function fetchArray($result) {
return mysql_fetch_array($result);
}
}
?>
We also need to create a file that will hold our database information.
<?php
// DbVars.php
class Dbvars {
var $settings;
function getSettings() {
// Database variables change accordingly
$settings['dbhost'] = 'localhost';
$settings['dbusername'] = 'username';
$settings['dbpassword'] = 'password';
$settings['dbname'] = 'community';
return $settings;
}
}
?>
We now have everything we need to start reading and writing to the database. To set up a connection and get some data from the database we simply do the following.
require_once("DbConnector.php"); // Include the database class
$db = new DbConnector(); // Create an instance of the database class
$db->connect(); // Connect to the database
$query = "SELECT * FROM table; // Perform a query to the database
$result = $db->query($query);
$rows = $db->fetchArray($query); // Get an array with the results
echo $rows["data"];
Next part: Register and login with sessions
By Stefan