Okay so last time (if you did read my first tutorial) we spoke about designing a database (roughly).
So Now as I said we'll get into actual code.
So let's start with the beginning.
Like everything in life, before coding you must be organized. So believe me when I say this, YOU DON’T WANT to start coding before designing a proper DB.
Let’s create a Database people!
Code: Select all
CREATE DATABASE database_nameNow we are going to create a simple users table.
Table name: users (note that I rarely use capital letters. just a habit)
Fields: id, username, password
So like you already now id is my PK.
HOW do we create that particular table?
Well boys and girls, you just won’t have to learn the mysql syntax. Phpmyadmin is free and does it for you.
But I still will explain a bit (the minimum you need to know)
Code: Select all
CREATE TABLE `users` (
`usersid` INT(6) NOT NULL,
`username` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
PRIMARY KEY (`usersid`)
);But there’s something you should be asking yourself
What’s INT(6), NOT NULL, and VARCHAR.
I won’t talk about it too much you can go here. You have numeric, Date and time and string type.
So INT is an integer the value 6 is just to say it could have up to 6 digits (ie: could be 1 or 123456 but 1234567 is not a valid data)
Varchar is a string type column. With up to 255 character that could be saved. (20) means I want the data entered to be UP TO 20 characters.
Okay so I created my table. Easy!
Now I need to insert information in it. Phpmyadmin offer you the insert option.
But that’s too easy! You’re a soldier you want it the OLD FASHION way! You want to work hard for it!
Okay I hear you. We’re going to process data from a form on the your website (or your local host).
So you need to connect to your database. Okay what you need is
1 A username / password for the admin (you) to access your DB
2 Some basic knowledge of php
3 Your Database name
----------------------------------------
1 we will use ROOT as a username, and there is no password to it. Phpmyadmin comes with this user as a default.
2 The basic knowledge you need to know is how does variable work.
Code: Select all
<?php
$a = “string”;
echo(“$a”);?>3 Our DB name is bibi (remember)
So let’s make a connection baby…
Code: Select all
$host = "localhost";
$user = "ROOT";
$pswd = "";//that means there is no pasword
$dbname = "bibi";
/*Okay first 2 php function we’ll learn mysql_connect AND mysql_select_db
mysql_connect : pretty simple, you pass the 3 variable you need (host, user, pass) that’s it
mysql_select_db : It actually doesn’t do more then it’s says. You can have any number of database (there’s a limit but, you’re a noob, you won’t need that much DB ) so you must select the database you wich to work on*/
$connect = mysql_connect($host, $user, $pswd)
or die("Could not connect: " . mysql_error());
$database = mysql_select_db("$dbname")
or die(MySQL_Error());Now what?
Well before we go further we’ll study the MINIMUM syntax you need to know in mysql.
SELECT
Code: Select all
SELECT column_name(s) FROM table_nameCode: Select all
SELECT username, password FROM usersYou can also add WHERE statement to the SELECT. WHERE is a way to SELECT precise data. Exemple :
Code: Select all
SELECT username, password FROM users WHERE id = 1The WHERE can be used with different operator ( =, !=, <, > etc)
Finally WHERE can be use to find a “pattern”. Like every username that start with an A:
Code: Select all
SELECT username, password FROM users WHERE id LIKE ‘%A’INSERT
Code: Select all
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)Code: Select all
INSERT INTO users
VALUES ('', 'Camilla', '123')YES, I didn’t specify columns. I just entered values, well It’s still good!
Yep, when I’m entering value in all my fields I just put the VALUES in.
You must note that values are entered in ORDER. My first field is id I entered nothing (it’s auto-increment it’ll enter a value automatically) then username and password. If I wanted to enter just an id and username (no password) I would’ve use this syntax
Code: Select all
INSERT INTO users (id, username)
VALUES (‘‘, ‘userwithnopass’)UPDATE
Code: Select all
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_valueLet’s give you people example about what I am talking about
Code: Select all
UPDATE users SET username = 'Nina'
WHERE id = '1'Code: Select all
UPDATE users SET username = 'Nina', password = '321'
WHERE id = '1'Code: Select all
DELETE FROM table_name
WHERE column_name = some_valueCode: Select all
DELETE FROM users WHERE id = '1'Code: Select all
DELETE * FROM usersWe are going to create a form for people to INSERT their username/password in our database.
Code: Select all
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Register</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<?php include ("includes/db.php"); ?>
</head>
<body>
<form method="post" action="confirm/register.php">
<div align="center">
Username : <input type="text" name="username" /><br />
Password : <input type="password" name="password" /><br /><br />
<input type="submit" name="submit" value="Login" />
</div>
</form>
</body>
</html><?php include ("includes/db.php"); ?>
Okay I created a file named db.php in a folder named includes where ALL THERE IS is this
Code: Select all
<?php
$host = "localhost";
$user = "ROOT";
$pswd = "";
$dbname = "bibi";
$connect = mysql_connect($host, $user, $pswd)
or die("Could not connect: " . mysql_error());
$database = mysql_select_db("$dbname")
or die(MySQL_Error());
?>A little note on the side. I always organize my files in this way :
Form in root, I have a folder named confirm where I put the code that will communicate with the database.
Code: Select all
ROOT
CONFIRM
Register.php /*this is where my php parse data that comes from register.php in ROOT*/
Register.php /* this is my form*/Code: Select all
<?php
$host = "localhost";
$user = "ROOT";
$pswd = "";
$dbname = "bibi";
$connect = mysql_connect($host, $user, $pswd)
or die("Could not connect: " . mysql_error());
$database = mysql_select_db("$dbname")
or die(MySQL_Error());
$username = $_POST["username"];
$password = $_POST["password"];
/*this simply look if there’s already the same username in the DB since I decided to have only one possible username, then I set the sql to insert my info IF my username is not taken */
$sql_check = "SELECT * FROM users WHERE username='$username'";
$sql_insert = "INSERT INTO users VALUES ('', '$username', '$password') ";
/*now I execute the query and I put it in a variable*/
$result_check = mysql_query($sql_check) or die(mysql_error());
/*now we are going to get the number of row returned, If there’s 0 that mean there is no other SAME username if it return 1 that means IT ALREADY have this username taken so choose another one*/
$numrow = mysql_num_rows($result_check);
if ($numrow != "0"){ //means if number of row is different than 0
echo ("Username already taken go back and choose another username please");
}else{
/*Well there is no rows returned with the username entered by the dude, so let’s insert his new info*/
$result_insert = mysql_query($sql_insert) or die(mysql_error());
echo ("Your information has been submitted to the database, Welcome in our super friendly group $username");
}
mysql_close($connect);
?>Wow we are good, we do not only add stuff but CHECK if we should add it or not! That puts YOU over the masses OH YEAH now you’re a real champ!
Go take a break I’ll Come back with PART II SEQUEAL soon
ENJOY