PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Sep 25, 2018 1:03 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 1 post ] 
Author Message
PostPosted: Mon Aug 02, 2004 1:03 pm 
Offline
Forum Regular

Joined: Fri Aug 15, 2003 12:25 pm
Posts: 577
Location: Montreal, Canada
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!
Syntax: [ Download ] [ Hide ]
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)
Syntax: [ Download ] [ Hide ]
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.
Syntax: [ Download ] [ Hide ]
<?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…
Syntax: [ Download ] [ Hide ]
$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
Syntax: [ Download ] [ Hide ]
SELECT column_name(s) FROM table_name

Okay here an example:
Syntax: [ Download ] [ Hide ]
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 :
Syntax: [ Download ] [ Hide ]
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 ( =, !=, &lt;, &gt; etc)
Finally WHERE can be use to find a “pattern”. Like every username that start with an A:
Syntax: [ Download ] [ Hide ]
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
Syntax: [ Download ] [ Hide ]
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

Yeah, really easy. Examples of course
Syntax: [ Download ] [ Hide ]
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
Syntax: [ Download ] [ Hide ]
INSERT INTO users (id, username)
VALUES (‘‘, ‘userwithnopass’)

Taddam!! Easy… Too easy for you!!

UPDATE
Syntax: [ Download ] [ Hide ]
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
Syntax: [ Download ] [ Hide ]
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.
Syntax: [ Download ] [ Hide ]
UPDATE users SET username = 'Nina', password = '321'
WHERE id = '1'


DELETE
Syntax: [ Download ] [ Hide ]
DELETE FROM table_name
WHERE column_name = some_value
Okay the last one we will learn in this tutorial. DELETE.
Syntax: [ Download ] [ Hide ]
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.
Syntax: [ Download ] [ Hide ]
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.
Syntax: [ Download ] [ Hide ]
<!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
&lt;?php include ("includes/db.php"); ?&gt;
Okay I created a file named db.php in a folder named includes where ALL THERE IS is this
Syntax: [ Download ] [ Hide ]
<?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.
Syntax: [ Download ] [ Hide ]
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.
Syntax: [ Download ] [ Hide ]
<?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)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group