Intro to Database PART II

Tutorials on PHP, databases and other aspects of web development. Before posting a question, check in here to see whether there's a tutorial that covers your problem.

Moderator: General Moderators

Post Reply
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Intro to Database PART II

Post by Draco_03 »

Hi again, it's your friendly neighbour ME.

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_name
That wasn’t really hard.. o let’s say our DB name is bibi

Now 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`)
);
So it’s pretty self-explanatory.
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”);?>
if you can understand that, your good to go.
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());
Well that was instructive. You opened a connection to your DB.
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_name
Okay here an example:

Code: Select all

SELECT username, password FROM users
See, The SELECT does as it say, it select the information my fields hold.

You 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 = 1
Well now now, this won’t simply get all my TABLE info, It’ll get only the username and password from the first row (because my first row, the id will be equals to 1). EASY HUH!

The 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’
% is a wildcard, it’s like using * when you search in windows. (or any OS)

INSERT

Code: Select all

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
Yeah, really easy. Examples of course

Code: Select all

INSERT INTO users 
VALUES ('', 'Camilla', '123')
Okay now YOU MUST have found something wrong or else your not following! Check it again.

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’)
Taddam!! Easy… Too easy for you!!

UPDATE

Code: Select all

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value
Okay you guessed it, sometimes, we need to MODIFY existing values. UPDATE is our friend.
Let’s give you people example about what I am talking about

Code: Select all

UPDATE users SET username = 'Nina'
WHERE id = '1'
That’s all there is too it, You can update more then 1 field at a time, just add a comma.

Code: Select all

UPDATE users SET username = 'Nina', password = '321'
WHERE id = '1'
DELETE

Code: Select all

DELETE FROM table_name
WHERE column_name = some_value
Okay the last one we will learn in this tutorial. DELETE.

Code: Select all

DELETE FROM users WHERE id = '1'
Nothing real hard. You could delete everything in a tableOkay the last one we will learn in this tutorial. DELETE.

Code: Select all

DELETE * FROM users
Okay let’s go on with this tutorial.

We 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>
Let’s explain this
<?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()); 
?>
That’s IT, so include take this file and just paste it. WHY? Well first if you have 120 pages with 120 conenct, if you change server instead of making 120 changes, with include you just open your file db.php change user pass database and BAM it changes on all the 120 pages.

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*/
So now we have to create the register.php that will go in folder confirm.

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);
?>
This is the code you put between your body (in your html page.. err I mean php page)

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 8)
Post Reply