Inserts and Queries
Instructions on how to create some basic queries and inserts are here. At the completion of this tutorial you will be able create a page which will let people create usernames and passwords.
- First create a new .php file. You’ll need to create a basic form which posts to itself. The form needs to have 2 input text fields named “username” and “password” along with a submit button. The code for this is displayed below, I recommend just copying the info below and pasting it into your php file.
- Now that you have a form setup which will send information to you we get on to the php coding.
- Inside the code at the top of the page (above everything) create a section for your PHP code <?php ?>
- Your first step will be to receive the variables sent by the form, afterall if no variables are sent theres no reason to waste loading time. So add to the script a bit with the following. Form data is contained within a global variable labled $_POST. You can then access the
individual fields by changing the name from “username” to whatever you wish.<?php
$username=$_POST['username'];
?>
- Now we’ll need to test to see if there was any information in that variable. This code just performs a check, to see if what is inside the parenthesis () is true.
If it is true then it will do something else (that something is contained within the { } tags. In the statement if ($username) we are saying if something happened to $username do the following.
<?php
if ($username)
{}
?>
- Finally we’re on to the real query section. Here we will need to define the other variables of $password and $uid connect to the database. Check to see if any other users already have the chosen username. The insert the new username and password combination into the database. First lets connect to the database. Remember all of this code will be within the { } tags from the if statement in step 5.
<?php
if ($username)
{
/* you need to connect using the proper username and password if
you installed the wamp server and didn't change any settings your
username will be 'root' and your password wil be blank
the below is a standard connect with password and usernamemysql_connect("localhost", 'yourusername', 'yourpassword')
the below is what you can use if you still have the default wamp
mysql_connect("localhost", 'root')
installation settings*/
or
die("could not connect");
/* by adding the "or die" on to this you make the system report
an error message. If theres a problem*/
mysql_select_db("school");
//Please choose the database name you created in the last tutorial;
}
?>
- Ok now that you are connected to the database we should create the variables you’ll be using. First the password, this is basically a repeat of Step 4.
<?php
$password=$_POST['password'];
?>
- Now lets create the final variable the Unique ID. This will allow you keep track of data in an anonymouse way. In this step there are several functions including a random number creator, and a while loop.The random number creator will come first, this allows us to create a new ID for this user’s account.
<?php
/* the statement below will set $uid as a random number
between 1 and 999,999. This gives us a 6 digit unique
identification number.*/
$uid=rand(1,999999);
?>
Now we’ll run a check to see if there another ID equal to the newly generated one above. This is the first query.
<?php /*query the table, this returns information
regarding a row with the random id above.*/
$query="SELECT * FROM users WHERE uid='$uid';";
$result = mysql_query($query) or die(mysql_error());
//place the results of the query into a variable
$row = mysql_fetch_array($result);
/*create a dummy variable with the value of username from the table.
This will be blank if there aren't any users with the same id number.*/
$dummy=$row['username'];
?>
The following which is a loop which will means it will happen again and again as long as the requirements are met.
<?php
//while the $dummy != (is not equal) to "" (nothing) do what is inside the {}
while ($dummy!="")
{
//first make dummy equal to nothing
$dummy="";
//set the uid to a new random number
$uid=rand(1,999999);
//query the database
$query="SELECT * FROM users WHERE uid='$uid';";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
/*set dummy as equal to the username, if the uid was
unique then the username will now be equal to ""*/
$dummy=$row['username'];
}
//finish the loop with a }
?>
- Now we’ll check to make sure the username isn’t already in the database and then either show an error message or insert the new user/password into the database.
<?php
/*query the database, similar to the first part of step 5,
however instead of uid, we're looking at username*/
$query="SELECT * FROM users WHERE username='$username';";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
/*set dummy as equal to the uid, if the username was
unique then the uid will now be equal to ""*/
$dummy=$row['uid']; //if the username alraedy has information in it then display an error mesage
if ($dummy!="")
{
echo "Sorry $username is already registered, please choose another username";
/*notice I used $ in front of username. This will make the
variable appear in the error message*/
} /*or else do this (so if the if statement above isn't true insert a
new row into the database.*/
else
{
$query="INSERT INTO `users` ( `username` , `password` , `uid` )
VALUES ('$username', '$password', '$uid');";
$result = mysql_query($query) or die(mysql_error());
echo "username: $username, password: $password created sucessfully";
// the echo gives a success message which details the username and password.
} ?>
