[PHP] Mysql Basics

Threaded Mode | Linear Mode

[PHP] Mysql Basics

Author Message

Virtual
Senior Member

Posts: 384
Group: Active
Joined: Mar 2006
Status: Offline
Reputation: 0

Post: #1

[PHP] Mysql Basics

I'm going to teach you the basics of MySQL and showing the contents of a database table.

Read from here if you haven't got phpMyAdmin

first of all you'll have to create a database and a table of course
sorry for the ones who haven't got PHP MyAdmin, but hey don't be afraid register @ FreeSQL.org and you have it for free!

first for the ppl who haven't got PHP MyAdmin open the link and scroll down you'll see a form
enter the username, password, database name, and just a few numbers in the creation code. Below email adress should be MySQL selected, if not select it
then just submit. I don't know if you get an e-mail I think so.
okay after registration scroll up, and on the right you see Database Administration then just click phpMyAdmin enter your username and password and phpMyAdmin opens!

Let's get started

phpMyAdmin is open now
normally u see this now:

MySQL
Create a new database [Documentation]
and an input box

okay just enter the name you want and submit after you clicked the submit button u should see a text area that's where u have to enter the sql code:

Code:

CREATE TABLE `products` (

`id` INT( 8 ) NOT NULL AUTO_INCREMENT ,

`name` VARCHAR( 255 ) NOT NULL ,

`price` INT( 8 ) NOT NULL ,

PRIMARY KEY ( `id` )

);

okay let's explain
CREATE TABLE is obvious I think
`products` is the name of the table
`id` this is the number that belongs to the product
NOT NULL means this value must be filled in
AUTO_INCREMENT means that if you add a product with a form, the id number will increase automaticly with 1
`name` is just the name of the product
VARCHAR means 1 or more characters ( 225 ) is the maximum characters
NOT NULL is obvious I will not mention this anymore
`price` the price of the product
INT means only numbers no numbers with a , in it
so not �1,5 or $50,99
PRIMARY KEY ( `id` ) is the key to your table, so you only need the id then you automaticly have the entire information of the product that belongs to that id it was of course also possible that `name` was the primary key but id is better

okay just click the first Start button
good the table is ready!

Adding records to the db table

Here you will learn to add products to your database later in the tut this will happen with a form

to add a record, we use the INSERT commando

Code:

INSERT INTO `products` (`name` , `price` )

VALUES ('Mousepad', '10');

let's explain
INSERT INTO is obvious
`products` is the name of the table
(`name` , `price` ) are the rows of the tables
VALUES ('Mousepad', '10'); these are the values of course

don't do this:

Code:

INSERT INTO `products` (`name` , `price` )

VALUES ('10', 'Mousepad');

I hope you see why
the values are not in the correct order!!! the price is Mousepad

okay you've added your first record!

Select records from your table

now I'm going to teach you how you can select your records
to do that, we use the SELECT commando. To select all the records (I know it's only 1) there is just 1 commando

Code:

SELECT * FROM `products`

* doesn't mean all records, but all table rows so id, name and price
if you do this

SELECT name FROM `products` only the name will appear

let's add some more records

Code:

INSERT INTO `products` (`name` , `price` )

VALUES ('Graphix card', '150');

INSERT INTO `products` (`name` , `price` )

VALUES ('Keyboard', '50');

INSERT INTO `products` (`name` , `price` )

VALUES ('Windows XP', '200');

INSERT INTO `products` (`name` , `price` )

VALUES ('PHP Designer 2005', '110');

INSERT INTO `products` (`name` , `price` )

VALUES ('Adobe Photoshop CS 2', '150');

okay this is our shop =)
if u do this now again
SELECT * FROM `products`
you will see the entire table, but most of the times you only want to see just 1 product. well thank you php because that's why we use the WHERE clausule
oh.. PHP

Code:

SELECT * FROM `products` WHERE `name` = 'PHP Designer 2005';

I think it's obvious let's explain it anyway it simply just selects all information where the name of the products = PHP Designer 2005 from the table products

it's just that simple!

Code:

SELECT price FROM `products` WHERE `name` = 'PHP Designer 2005';

this only selects the price of the product where name = PHP Designer 2005 from
the table products

the same for price and id

Code:

SELECT * FROM `products` WHERE `id` = '1';

this selects all records with the id 1 (it's only 1 record of course)

Code:

SELECT * FROM `products` WHERE `price` = '150';

this selects all records where the price = 150
so I think it was photoshop and a new graphix card

So far the select commando and where clausule

Operators

we can use the = sign but we can also use operators for example: we only want to show the products that are more expensive than 100

Code:

SELECT * FROM `products` WHERE `price` > '100';

simple, isn't it?

or you want to select all the records that are cheaper than 50?

Code:

SELECT * FROM `products` WHERE `price` < '50';

u can also use the >= or <= signs

Code:

SELECT * FROM `products` WHERE `price` <= '50';

this selects the records where the price is = or cheaper than 50

I don't think you ever thought it was that simple ?!

More functions with WHERE

there's more,
you want to select the IDs of the products that are smaller than 3 and you want to select the products that are cheaper than 100?
no problem! PHP will take care of it

here we will use the AND command

Code:

SELECT * FROM `products` WHERE `price` < '100' AND `id` < '3';

and there's more...
you want to select the products that are cheaper and 50 but also that are more expensive than 200 (I don't know why somebody would do that but it's just an example)
here we use the OR command

Code:

SELECT * FROM `products` WHERE `price` < '50' OR `price` > '200';

Simple eh

Order your records

You want to order your records from cheap to expensive? No problem PHP will take care of it

Code:

SELECT * FROM `products` ORDER BY `price`

you'll get your entire list of products ordered from cheap to expensive but you don't want it this way, but from expensive to cheap? No problem! Just add DESC after price

Code:

SELECT * FROM `products` ORDER BY `price` DESC

DESC means descending

you only want to see a specified ammount of products then you just do this:
SELECT * FROM `products` ORDER BY `price` LIMIT 0,3
0 indicates that he will start counting from 0 and he will take the 3 numbers after 0
so, 1,2,3

SELECT * FROM `products` ORDER BY `price` LIMIT 2,3
he will start counting from 2 and takes the 3 numbers after it
3,4 and 5

UPDATE your records
OMG!!? I've entered the wrong price, I will lose customers!
no problem, that's why we have PHP if you want to update your records, u use the UPDATE command

Code:

UPDATE `products` SET `price` = '250' WHERE `name` = 'Graphix Card';

This will update the price of the graphix card from 150 to 250
(geh thanx, I will lose customers anyway )

if you do this:

Code:

UPDATE `products` SET `price` = '250';

all prices of all your products will change to 250 so beware!

Also here you can use the AND or OR commands in the WHERE clausule!

Combine MySQL and PHP

first of all you'll have to connect to the database open up a new php doc and paste this:

PHP Code:

$server = "localhost";
$user = "username;
$password = "password";
$db = "db";

$connection = mysql_connect($server,$user,$password)
or die ("Could not connect to the database");
mysql_select_db($db,$connection)
or die ("Could not select the db"); 

server is most of the times localhost, but for freesql.org users it's freesql.org I thought. user is the username u used to register and password as well db is the name of the database u gave while registering ok save this file as connect.php we'll include this file in our other files

open up a new doc
and paste this:

PHP Code:

<?php
include "connect.php";
$query = "SELECT * FROM `products` WHERE `prijs` > '50' ";
$sql = mysql_query($query);
?>

include "connect.php"; just means that all content of that file also are in force in this file.
$query = "SELECT * FROM `products` WHERE `prijs` > '50' ";
this makes the query
$sql = mysql_query($query); this executes the query

now we only have to show all products more expensive than 50

PHP Code:

<?php
include "connect.php"; //make connection
$query = "SELECT * FROM `products` WHERE `prijs` > '50' ";
$sql = mysql_query($query) or die ( mysql_error( ) );

while($record = mysql_fetch_object($sql)){
echo"".$record->name."
";
}
?>

This shows all the products more expensive than 50 or you can do this

PHP Code:

<?php
include "connect.php"; //make connection
$query = "SELECT * FROM `products` WHERE `prijs` > '50' ";
$sql = mysql_query($query) or die ( mysql_error( ) );

while($record = mysql_fetch_object($sql)){
echo"You can buy ". $record->name ." for ". $record->price ." each."
";
}
?>

it always makes a new table
so u better do this:

PHP Code:

<table>
// here starts the php
<?php
include "connect.php"; //make connection
$query = "SELECT * FROM `products` WHERE `prijs` > '50' ";
$sql = mysql_query($query) or die ( mysql_error( ) );

while($record = mysql_fetch_object($sql)){
echo"<tr><td><b>Product</b></td><td><b>Price</b></td></tr>
<tr>".$record->name."</td><td>".$record->price."</td></tr>";
}
?>
// and close the table
</table> 

well this is not pretty easy to explain but you'll understand after some tries or if u know the while structure

it's not that difficult eh?

More functions with PHP & MySQL
hey I forgot how many products I had no problem PHP takes care of it

PHP Code:

<?php
include "connect.php"; //make connection
$query = "SELECT * FROM `products` WHERE `prijs` > '50' ";
$sql = mysql_query($query) or die ( mysql_error( ) );
$records = mysql_num_rows($sql);
echo ('There are ".$records." in the database');
?>

mysql_num_rows($sql); counts how many records there are in the table

Like I said before, we're going to add records with a form that's exactly what we're going to do now
first we're going to check if the submit button has been clicked
if not: the form will show up
else: we will insert the input into the database

name this doc: insert.php :

PHP Code:

<?php
if ($_POST['submit']) { //if the submit button has been clicked
include"connection.php"; //includes connection.php

$query = "INSERT INTO `products` (`name` , `price` ) //makes the query
VALUES ('".$_POST['name']."', '".$_POST['price']."');"
$sql = mysql_query($query) or die(mysql_error()); //executes the query
echo"You have entered the new product";

}else{
?>
<form method="post" action="insert.php">
Product name: <input type="text" name="name"><br>
Product price: <input type="text name="price"><br><br>
<input type="submit" value="Submit!">
<?
}
?>

I hope this tut helped you out.
Still having problems? don't PM me! just ask in this topic Smile2

Current Favo Signature :: Clicky
Current C4D Signature ::<3
My Full Signature Tutorial :: Clicky

This post was last modified: 12-19-2007 12:01 AM by Xiao.

03-18-2006 03:40 PM

Xiao
Administrator

Posts: 2,988
Group: Administrators
Joined: Dec 2005
Status: Offline
Reputation: 1

Post: #2

RE: [PHP] Sessions & Cookies

you can use [php] tags to make it more obvious what's script and what's comment Wink2

I'm the only one around here that can ban people, what else do you need to know? Unsure

03-18-2006 04:20 PM

Virtual
Senior Member

Posts: 384
Group: Active
Joined: Mar 2006
Status: Offline
Reputation: 0

Post: #3

RE: [PHP] Sessions & Cookies

I know but I also wrote this tut on another forum and I just copied the text. You'd be insane to write this tut all over again

Current Favo Signature :: Clicky
Current C4D Signature ::<3
My Full Signature Tutorial :: Clicky

03-18-2006 04:54 PM

Xiao
Administrator

Posts: 2,988
Group: Administrators
Joined: Dec 2005
Status: Offline
Reputation: 1

Post: #4

RE: [PHP] Sessions & Cookies

real nice tutorial Smile2

I'm the only one around here that can ban people, what else do you need to know? Unsure

03-18-2006 05:10 PM

Virtual
Senior Member

Posts: 384
Group: Active
Joined: Mar 2006
Status: Offline
Reputation: 0

Post: #5

RE: [PHP] Sessions & Cookies

thanks man Smile2

Current Favo Signature :: Clicky
Current C4D Signature ::<3
My Full Signature Tutorial :: Clicky

03-18-2006 05:11 PM

urmomma
Junior Member

Posts: 2
Group: Registered
Joined: Mar 2006
Status: Offline
Reputation: 0

Post: #6

RE: [PHP] Sessions & Cookies

at the end...I would change insert.php to this:

Code:

<?php

if (isset($_POST['submit'])) { //if the submit button has been clicked

include"connection.php"; //includes connection.php

$name = $_POST['name'];

$price = $_POST['price'];

function cleanup ($name, $price)

{

if (get_magic_quotes_gpc())

{

$name = stripslashes($name);

$price = stripslashes($price);

}

$name = mysql_real_escape_string($name);

$price = mysql_real_escape_string($price);

}

cleanup ($name, $price);

$query = "INSERT INTO `products` (`name` , `price` ) //makes the query

VALUES ('".$name."', '".$price."');"

$sql = mysql_query($query) or die(mysql_error()); //executes the query

echo"You have entered the new product";

}else{

?>

<form method="post" action="insert.php">

Product name: <input type="text" name="name"><br>

Product price: <input type="text name="price"><br><br>

<input type="submit" value="Submit!">

<?

}

?>

Just remember to stripslashes the string, when you want to output Wink2

This will prevent SQL Injection from user input. For example, a user can input something...that would confuse a query into sending a DELETE command.

Also, I would suggest you look into what php.net suggests:

Code:

$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",

mysql_real_escape_string($user),

mysql_real_escape_string($password));

That is safer as well....just another way to look at it.

03-25-2006 10:29 PM

Xiao
Administrator

Posts: 2,988
Group: Administrators
Joined: Dec 2005
Status: Offline
Reputation: 1

Post: #7

RE: [PHP] Sessions & Cookies

I inserted CODE and PHP tags in the parent post Wink2

thanks for your comment urmomma

I'm the only one around here that can ban people, what else do you need to know? Unsure

03-25-2006 10:37 PM

urmomma
Junior Member

Posts: 2
Group: Registered
Joined: Mar 2006
Status: Offline
Reputation: 0

Post: #8

RE: [PHP] Sessions & Cookies

Anytime. Just don't want to see people/webmasters using code that gets their website messed up.

That type of injection, could be manipulated into 100% deletion of Database.

03-30-2006 11:56 PM