PHP with mysql

tenderlaΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 8 μήνες)

94 εμφανίσεις

PHP with
mysql


Lecture
5

Simple review

There are many different versions of SQL
available for usage.


Oracle


MySQL


SQLite


DB
2


MySQL
is a database.

The data in MySQL is stored in database objects
called tables.

A table is a collection of related data entries and
it consists of columns and rows.

Databases are useful when storing information
categorically. A company may have a database
with the following tables: "Employees",
"Products", "Customers" and "Orders".


What is MySQL?

Databases
_ creation

CREATE TABLE
tableName

(

name VARCHAR(
55
),

sex CHAR(
1
),


age INT(
3
),

birthdate DATE,

salary DECIMAL(
10
,
2
),

primary key(name)

);


Types of attributes:
char,
varchar
,
int
,
smallint
, decimal, date, float, etc.

*
varchar

is a string with varying # of characters. In our example,
55
is the
characters longest possible string allowed.

*decimal(
10
,
2
) indicated
2
places after the decimal point and
10
total
digits (including the decimal numbers)

Databases _ creation
2

CREATE TABLE
tableName

(

name VARCHAR(
55
),

sex CHAR(
1
) NOT NULL,


age INT(
3
),

birthdate DATE,

salary DECIMAL(
10
,
2
) DEFAULT ‘
0.00
’,

primary key(name)

);


Primary key:
primary key is a UNIQUE value. For every entry in
your database this must be unique and not null and every DB
must have one.

NOT NULL:
column must have a value

DEFAULT:
you can set a default value if no other value is inputted
for that column.

Databases _ indexed primary keys

Instead of specifying a column as a primary key you can have the
database create a column of numbers that will automatically
increment with each entry inserted into the DB. Example:


CREATE TABLE
tableName

(

id INT AUTO_INCREMENT,

name VARCHAR(
55
),

sex CHAR(
1
),


age INT(
3
),

birthdate DATE,

salary DECIMAL(
10
,
2
),

primary key(id)

);

.

Databases _ deletion

DROP TABLE
tableName
;

Databases _ insertion

Inserting data in the database:

INSERT INTO
tableName
(
name,sex,age
)

VALUES(‘Mr. Freeze’,’M’,
42
);


Also valid:

INSERT INTO
tableName
(
sex,name,age
)

VALUES(‘F’,’
Mr
. Freeze’,
42
);


Order doesn’t matter.

Databases _ the meat

Always in the form of:

SELECT ….

FROM ….

WHERE ….


So

select

a column from your database.

From

a database

Where

x meets y condition.


*

Except in the case of modification

Databases _ updating

Suppose we want to change Mr. Freeze’s age to
52
.


UPDATE
tableName

SET age = ’
52


WHERE name LIKE ‘Mr. Freeze’


And so forth.

Databases _ aggregates

This is the actual meat of using SQL. These are where you set
your conditions, narrow down your table into a usable set. Here
are the usable functions, I’ll show a quick example with each. The
only way to really know this stuff is practice.


Group by

Count

Sum

Avg

Min/Max

Order by

Databases _ group by

This is the actual meat of using SQL. These are where you set
your conditions, narrow down your table into a usable set. Here
are the usable functions, I’ll show a quick example with each. The
only way to really know this stuff is practice. Group by lumps all
the common attributes into one row.


SELECT
employee_id
, MAX(salary)

FROM
Works_In

GROUP BY
dept_id
;


* MAX selects the maximum value in its () likewise for MIN


Databases _ count

Count counts the number of columns with the specified attribute.


SELECT term, COUNT(
course_id
)

FROM teaches

GROUP BY term;


We counted the number of courses taught during x term. AVG &
SUM function pretty much the same way.


PHP MySQL Connect to a Database

The free MySQL database is very often used with PHP.

Create a Connection to a MySQL Database

Before you can access data in a database, you must create a connection to
the database.

In PHP, this is done with the
mysql_connect
() function.

Syntax

mysql_connect
(
servername,username,password
);


Description

Optional. Specifies the server to connect to. Default value is
"
l
127.0.0.1
"

P慲慭整敲

獥rv敲湡ee

佰瑩潮慬⸠印Sci晩敳f瑨攠畳敲湡e攠t漠o潧oi渠睩瑨⸠䑥f慵汴av慬略a
“root” is
瑨攠t慭a映瑨攠t獥爠r桡琠t睮猠瑨攠獥rv敲e灲潣敳s

畳敲湡ee

佰瑩潮慬⸠印Sci晩敳f瑨攠灡psw潲搠t漠o潧oi渠睩瑨⸠䑥f慵汴ai猠∢

灡psw潲d

Example

In
the following example we store the connection in a
variable ($con) for later use in the script. The "die" part
will be executed if the connection fails:

< ?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


// some code

?>

Closing a Connection


The connection will be closed automatically when the script ends. To
close the connection before, use the
mysql_close
() function:

< ?
php

$con =
mysql_connect
("localhost","peter","abc
123
");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


// some code


mysql_close
($con);

?>



Create a Database

.
Syntax

CREATE DATABASE
database_name.To

get PHP to execute the statement
above we must use the
mysql_query
() function. This function is used to
send a query or command to a MySQL connection.

Example

<
?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


if (
mysql_query
("CREATE DATABASE my_
db
",$con))

{

echo "Database created";

}

else

{

echo "Error creating database: " .
mysql_error
();

}


mysql_close
($con);

?>



Create a Table

The CREATE TABLE statement is used to create a table in MySQL.

Syntax

CREATE TABLE
table_name

(

column_name
1
data_type
,

column_name
2
data_type
,

column_name
3
data_type
,

....

)


We must add the CREATE TABLE statement to the
mysql_query
() function to execute the command.

Example

<
?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}

//
Create
database

if (
mysql_query
("CREATE DATABASE
my_db",$con
))

{

echo "Database created";

}

else{

echo
"Error creating database: " .
mysql_error
();

}

//
Create
table

mysql_select_db
("
my_db
", $con);

$
sql

= "CREATE TABLE Persons

(

FirstName

varchar
(
15
),

LastName

varchar
(
15
),

Age
int

)";

//
Execute
query

mysql_query
($
sql
,$con);

mysql_close
($con);

?>

Important: A database must be selected
before a table can be created. The database is
selected with the
mysql_select_db
() function.

Note: When you create a database field of
type
varchar
, you must specify the maximum
length of the field, e.g.
varchar
(
15
).

The data type specifies what type of data the
column can hold. For a complete reference of
all the data types available in MySQL,

PHP MySQL Insert Into

The INSERT INTO statement is used to insert new records in a table.

Insert Data Into a Database Table

The INSERT INTO statement is used to add new records to a database
table.

Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will
be inserted, only their values:

INSERT INTO
table_name

VALUES (value
1
, value
2
, value
3
,...)

The second form specifies both the column names and the values to
be inserted:

INSERT INTO
table_name

(column
1
, column
2
, column
3
,...)

VALUES (value
1
, value
2
, value
3
,...)


Example

The
following example adds two new records to the "Persons" table:

< ?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


mysql_select_db
("
my_db
", $con);


mysql_query
("INSERT INTO Persons (
FirstName
,
LastName
, Age)

VALUES ('Peter', 'Griffin',
35
)");


mysql_query
("INSERT INTO Persons (
FirstName
,
LastName
, Age)

VALUES ('Glenn', 'Quagmire',
33
)");


mysql_close
($con);

?>


Insert Data From a Form Into a
Database

Now
we will create an HTML form that can be used to add new records to the
"Persons" table.

Here is the HTML form:

< html>

< body>


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

Firstname
: <input type="text" name="
firstname
" />

Lastname
: <input type="text" name="
lastname
" />

Age: <input type="text" name="age" />

< input type="submit" />

< /form>


< /body>

< /html>


When a user clicks the submit button in the
HTML form in the example above, the form
data is sent to "
insert.php
".

The "
insert.php
" file connects to a database,
and retrieves the values from the form with the
PHP $_POST variables.

Then, the
mysql_query
() function executes the
INSERT INTO statement, and a new record will
be added to the "Persons" table.

Here is the "
insert.php
" page

<?
php

$con =
mysql_connect
("localhost","peter","abc
123
");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


mysql_select_db
("
my_db
", $con);


$
sql
="INSERT INTO Persons (
FirstName
,
LastName
, Age)

VALUES

('$_POST[
firstname
]','$_POST[
lastname
]','$_POST[age]')";


if (!
mysql_query
($
sql
,$con))

{

die('Error: ' .
mysql_error
());

}

echo "
1
record added
";

mysql_close
($con)

?>

Select Data From a Database Table

The
SELECT statement is used to select data from a database.

Syntax

SELECT
column_name
(s) FROM
table_name

.


Example

<
?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


mysql_select_db
("
my_db
", $con);


$result =
mysql_query
("SELECT * FROM Persons");


while($row =
mysql_fetch_array
($result))

{

echo $row['
FirstName
'] . " " . $row['
LastName
'];

echo "<
br

/>";

}


mysql_close
($con);

?>

The output of the code above will be:

Peter Griffin

Glenn Quagmire

Display the Result in an HTML Table

The following example selects the same data as the example above, but will display the data in
an HTML table:

< ?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}

mysql_select_db
("
my_db
", $con
)

$
result =
mysql_query
("SELECT * FROM Persons");

echo
"<table border='
1
'>

<
tr
>

<
th
>
Firstname
</
th
>

<
th
>
Lastname
</
th
>

< /
tr
>";

while
($row =
mysql_fetch_array
($result
))

{

echo "<
tr
>";

echo "<td>" . $row['
FirstName
'] . "</td>";

echo "<td>" . $row['
LastName
'] . "</td>";

echo "</
tr
>";

}

echo "</table>";

mysql_close
($con
);

?>


Firstname

Lastname

Glenn

Quagmire

Peter

Griffin

The output of the code
above will be

Example about where

The
following example selects all rows from the "Persons" table where
"
FirstName
='Peter':

< ?
php

$con =
mysql_connect
("

127.0.0.1
","root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


mysql_select_db
("
my_db
", $con);


$result =
mysql_query
("SELECT * FROM Persons

WHERE
FirstName
='Peter'");


while($row =
mysql_fetch_array
($result))

{

echo $row['
FirstName
'] . " " . $row['
LastName
'];

echo "<
br

/>";

}

?>

The output of the code above
will be:

Peter Griffin



PHP MySQL Order By Keyword

The ORDER BY keyword is used to sort the data in a
recordset
.

The ORDER BY Keyword

The ORDER BY keyword is used to sort the data in a
recordset
.

The ORDER BY keyword sort the records in ascending order by
default.

If you want to sort the records in a descending order, you can
use the DESC keyword.

Syntax

SELECT
column_name
(s)

FROM
table_name

ORDER BY
column_name
(s) ASC|DESC


Example

The
following example selects all the data stored in the "Persons" table, and sorts the result
by the "Age" column:

< ?
php

$con =
mysql_connect
("
l

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


mysql_select_db
("
my_db
", $con);


$result =
mysql_query
("SELECT * FROM Persons ORDER BY age");


while($row =
mysql_fetch_array
($result))

{

echo $row['
FirstName
'];

echo " " . $row['
LastName
'];

echo " " . $row['Age'];

echo "<
br

/>";

}


mysql_close
($con);

?>

The output of the code above will be:

Glenn Quagmire
33

Peter Griffin
35

Order by Two Columns

It is also possible to order by more than one
column. When ordering by more than one
column, the second column is only used if the
values in the first column are equal:

SELECT
column_name
(s)

FROM
table_name

ORDER BY column
1
, column
2


Update Data In a Database

The
UPDATE statement is used to update existing records in a table.

Syntax

UPDATE
table_name

SET column
1
=value, column
2
=value
2
,...

WHERE
some_column
=
some_value



Example

Earlier in the tutorial we created a table named "Persons".

FirstName

LastName

Age

Peter

Griffin

35

Glenn

Quagmire

33

<?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


mysql_select_db
("
my_db
", $con);


mysql_query
("UPDATE Persons SET Age=
36

WHERE
FirstName
='Peter' AND
LastName
='Griffin'");


mysql_close
($con);

?>

FirstName

LastName

Age

Peter

Griffin

36

Glenn

Quagmire

33

After the update, the "Persons" table will look like
this
:




Delete Data In a Database

The
DELETE FROM statement is used to delete
records from a database table.

Syntax

DELETE FROM
table_name

WHERE
some_column

=
some_value


FirstName

LastName

Age

Peter

Griffin

35

Glenn

Quagmire

33

Example

Look at the following "Persons" table
:

<?
php

$con =
mysql_connect
("

127.0.0.1
",“root","");

if (!$con)

{

die('Could not connect: ' .
mysql_error
());

}


mysql_select_db
("
my_db
", $con);


mysql_query
("DELETE FROM Persons WHERE
LastName
='Griffin'");


mysql_close
($con);

?>

FirstName

LastName

Age

Glenn

Quagmire

33