Semis Lecture 2 Flashcards
Very often when you write code, you want to perform different actions for different conditions. You can use ___ in your code to do this.
conditional statements
In PHP we have the following conditional statements: (4)
- if statement
- if…else statement
- if…elseif….else statement
- switch statement
executes some code if one condition is true
if statement
executes some code if a condition is true and another code if that condition is false
if…else statement
executes different codes for more than two conditions
if…elseif….else statement
selects one of many blocks of code to be executed
switch statement
Syntax of the if statement
if (condition) {
code to be executed if condition is true;
}
this code block is an example of:
$t = date(“H”);
if ($t < “20”) {
echo “Have a good day!”;
}
if statement
Syntax of the if… else statement
if (condition) {
code to be executed if condition is true;
} else {
code to be executed if condition is false;
}
this code block is an example of:
$t = date(“H”);
if ($t < “20”) {
echo “Have a good day!”;
} else {
echo “Have a good night!”;
}
if… else statement
Syntax of the if… elseif… else statement
if (condition) {
code to be executed if this condition is true;
} elseif (condition) {
code to be executed if this condition is true;
} else {
code to be executed if all conditions are false;
}
this code block is an example of:
$t = date(“H”);
echo “<p>The hour (of the server) is “ . $t;
echo “, and will give the following message:</p>”;
if ($t < “10”) {
echo “Have a good morning!”;
} elseif ($t < “20”) {
echo “Have a good day!”;
} else {
echo “Have a good night!”;
}
if… elseif… else statement
Syntax of the switch statement
switch (n) {
case label1:
code to be executed if n=label1;
break;
case label2:
code to be executed if n=label2;
break;
case label3:
code to be executed if n=label3;
break;
…
default:
code to be executed if n is different from all labels;
}
This is how it works: First we have a single expression n (most often a variable), that is evaluated once. The value of the expression is then compared with the values for each case in the structure. If there is a match, the block of code associated with that case is executed. Use break to prevent the code from running into the next case automatically. The default statement is used if no match is found.
Switch statement
this code block is an example of:
$favcolor = “red”;
switch ($favcolor) {
case “red”:
echo “Your favorite color is red!”;
break;
case “blue”:
echo “Your favorite color is blue!”;
break;
case “green”:
echo “Your favorite color is green!”;
break;
default:
echo “Your favorite color is neither red, blue, nor green!”;
}
switch statement
Often when you write code, you want the same block of code to run over and over again in a row. Instead of adding several almost equal code-lines in a script, we can use ___ to perform a task like this.
loops
In PHP, we have the following looping statements: (4)
- while
- do…while
- for
- foreach
loops through a block of code as long as the specified condition is true
while
loops through a block of code once, and then repeats the loop as long as the specified condition is true
do…while
loops through a block of code a specified number of times
for
loops through a block of code for each element in an array
foreach
Syntax of while loop
while (condition is true) {
code to be executed;
}
$x = 1;
while($x <= 5) {
echo “The number is: $x
<br></br>”;
$x++;
}
The example below first sets a variable $x to 1 ($x = 1). Then, the while loop will continue to run as long as $x is less than, or equal to 5 ($x <= 5). $x will increase by 1 each time the loop runs ($x++)
Syntax of do… while loop
do {
code to be executed; }
while (condition is true);
$x = 1;
do {
echo “The number is: $x <br></br>”;
$x++;
} while ($x <= 5);
The example below first sets a variable $x to 1 ($x = 1). Then, the do while loop will write some output, and then increment the variable $x with 1. Then the condition is checked (is $x less than, or equal to 5?), and the loop will continue to run as long as $x is less than, or equal to 5
Notice that in a do while loop the condition is tested ___ executing the statements within the loop. This means that the do while loop would execute its statements ___, even if the condition is false the first time.
AFTER;
at least once
The example below sets the $x variable to 6, then it runs the loop, and then the condition is checked:
$x = 6;
do {
echo “The number is: $x <br></br>”;
$x++;
} while ($x <= 5);
Syntax for for Loop
for (init counter; test counter; increment counter) {
code to be executed;
}
Explain the Parameters:
for (init counter; test counter; increment counter) {
code to be executed;
}
- init counter: Initialize the loop counter value
- test counter: Evaluated for each loop iteration. If it evaluates to TRUE, the
loop continues. If it evaluates to FALSE, the loop ends. - increment counter: Increases the loop counter value
example code block for?
for ($x = 0; $x <= 10; $x++) {
echo “The number is: $x <br></br>”;
}
for loop
Syntax for foreach loop
foreach ($array as $value)
{
code to be executed;
}
foreach ($array as $value)
{
code to be executed;
}
For every loop iteration, the value of the current array element is assigned to $value and the array pointer is moved by one, until it reaches the last array element
The following example demonstrates a loop that will output the values of the given array ($colors):
$colors = array(“red”, “green”, “blue”, “yellow”);
foreach ($colors as $value){
echo “$value <br></br>”;
}
There are two ways to create indexed arrays:
- The index can be assigned automatically, or
- the index can be assigned manually:
index can be assigned automatically:
(index always starts at 0), like this:
* $cars = array(“Volvo”, “BMW”, “Toyota”);
index can be assigned manually:
$cars[0] = “Volvo”;
$cars[1] = “BMW”;
$cars[2] = “Toyota”;
The following example creates an indexed array named $cars, assigns three elements to it, and then prints a text containing the array values:
$cars = array(“Volvo”, “BMW”, “Toyota”);
echo “I like “ . $cars[0] . “, “ . $cars[1] . “ and “ . $cars[2] . “.”;
The ___ function is used to return the length (the number of elements) of an array:
count()
$cars = array(“Volvo”, “BMW”, “Toyota”);
echo count($cars);
OUTPUT:
3
To loop through and print all the values of an indexed array, you could use a for loop, like this:
$cars = array(“Volvo”, “BMW”, “Toyota”);
$arrlength = count($cars);
for($x = 0; $x < $arrlength; $x++) {
echo $cars[$x];
echo “<br></br>”;
}
OUTPUT:
Volvo
BMW
Toyota
___ are arrays that use named keys that you assign to them
Associative arrays
There are two ways to create an associative array:
First:
$age = array(“Peter”=>”35”, “Ben”=>”37”, “Joe”=>”43”);
Second:
$age[‘Peter’] = “35”;
$age[‘Ben’] = “37”;
$age[‘Joe’] = “43”;
The named keys in the Associative Array can then be used in a script
$age = array(“Peter”=>”35”, “Ben”=>”37”, “Joe”=>”43”);
echo “Peter is “ . $age[‘Peter’] . “ years old.”;
OUTPUT:
Peter is 35 years old.
To loop through and print all the values of an associative array, you could use a foreach loop, like this:
$age = array(“Peter”=>”35”, “Ben”=>”37”, “Joe”=>”43”);
foreach($age as $x => $x_value) {
echo “Key=” . $x . “, Value=” . $x_value;
echo “<br></br>”;
}
The elements in an array can be sorted in alphabetical or numerical order, descending or ascending
Sorting Arrays
sort arrays in ascending order
sort()
sort arrays in descending order
rsort()
sort associative arrays in ascending order, according to the value
asort()
sort associative arrays in ascending order, according to the key
ksort()
sort associative arrays in descending order, according to the value
arsort()
sort associative arrays in descending order, according to the key
krsort()
Sort Array in Ascending Order - sort()
The following example sorts the elements of the $cars array in ascending
alphabetical order:
$cars = array(“Volvo”, “BMW”, “Toyota”);
sort($cars);
$clength = count($cars);
for($x = 0; $x < $clength; $x++) {
echo $cars[$x];
echo “<br></br>”;
}
OUTPUT:
BMW
Toyota
Volvo
Sort Array in Ascending Order - sort()
The following example sorts the elements of the $numbers array in ascending
numerical order:
$numbers = array(4, 6, 2, 22, 11);
sort($numbers);
OUTPUT:
2
4
6
11
22
Sort Array (Ascending Order), According to Value - asort()
The following example sorts an associative array in ascending order, according to the value:
$age = array(“Peter”=>”35”, “Ben”=>”37”, “Joe”=>”43”);
asort($age);
OUTPUT:
Key=Peter, Value=35
Key=Ben, Value=37
Key=Joe, Value=43
Sort Array (Ascending Order), According to Key - ksort()
The following example sorts an associative array in ascending order, according to the key:
$age = array(“Peter”=>”35”, “Ben”=>”37”, “Joe”=>”43”);
ksort($age);
OUTPUT:
Key=Ben, Value=37
Key=Joe, Value=43
Key=Peter, Value=35
Sort Array (Descending Order), According to Value - arsort()
The following example sorts an associative array in descending order, according to the value:
$age = array(“Peter”=>”35”, “Ben”=>”37”, “Joe”=>”43”);
arsort($age);
OUTPUT:
Key=Joe, Value=43
Key=Ben, Value=37
Key=Peter, Value=35
Sort Array (Descending Order), According to Key - krsort()
The following example sorts an associative array in descending order, according to the key:
$age = array(“Peter”=>”35”, “Ben”=>”37”, “Joe”=>”43”);
krsort($age);
OUTPUT:
Key=Peter, Value=35
Key=Joe, Value=43
Key=Ben, Value=37
An array stores multiple values in one single variable.
In the following example $cars is an array. The PHP var_dump() function returns the data type and value:
$cars = array(“Volvo”,”BMW”,”Toyota”);
var_dump($cars);
OUTPUT:
array(3) { [0]=> string(5) “Volvo” [1]=> string(3) “BMW” [2]=> string(6) “Toyota” }
The real power of PHP comes from its ___.
functions
PHP has ___ built-in functions
more than 1000
PHP User Defined Functions
- Besides the built-in PHP functions, we can create our own functions.
- A ** is a block of statements that can be used repeatedly in a program.
- A ** will not execute immediately when a page loads.
- A ** will be executed by a call to the function
function
Create a User Defined Function in PHP
A user-defined function declaration starts with the word function:
Syntax
function functionName() {
code to be executed;
}
- Function names are NOT case-sensitive.
In the example below, we create a function named “writeMsg()”. The opening curly brace ( { ) indicates the beginning of the function code and the closing curly brace ( } ) indicates the end of the function. The function outputs “Hello world!”. To call the function, just write its name:
function writeMsg(){
echo “Hello world!”;
}
writeMsg();
OUTPUT:
Hello world!
___ can be passed to functions through arguments.
An ___ is just like a variable.
Information;
argument
- ___ are specified after the function name, inside the parentheses. You can add as many arguments as you want, just separate them with a comma.
Arguments
The following example has a function with one argument ($fname). When the familyName() function is called, we also pass along a name (e.g. Jani), and the name is used inside the function, which outputs several different first names, but an equal last name:
function familyName($fname) {
echo “$fname Refsnes.<br></br>”;
}
familyName(“Jani”);
familyName(“Hege”);
familyName(“Stale”);
familyName(“Kai Jim”);
familyName(“Borge”);
OUTPUT:
Jani Refsnes.
Hege Refsnes.
Stale Refsnes.
Kai Jim Refsnes.
Borge Refsnes.
The following example has a function with two arguments ($fname and $year):
function familyName($fname, $year) {
echo “$fname Refsnes. Born in $year <br></br>”;
}
familyName(“Hege”,”1975”);
familyName(“Stale”,”1978”);
familyName(“Kai
Jim”,”1983”);
OUTPUT:
Hege Refsnes. Born in 1975
Stale Refsnes. Born in 1978
Kai Jim Refsnes. Born in 1983
The following example shows how to use a default parameter. If we call the function setHeight() without arguments it takes the default value as argument:
function setHeight($minheight = 50) {
echo “The height is : $minheight <br></br>”;
}
setHeight(350);
setHeight();
setHeight(135);
setHeight(80);
OUTPUT:
The height is : 350
The height is : 50
The height is : 135
The height is : 80
To let a function return a value, use the return statement:
function sum($x, $y) {
$z = $x + $y;
return $z;
}
echo “5 + 10 = “ . sum(5,10) . “<br></br>”;
echo “7 + 13 = “ . sum(7,13) . “<br></br>”;
echo “2 + 4 = “ . sum(2,4);
OUTPUT:
5 + 10 = 15
7 + 13 = 20
2 + 4 = 6
___ is an important part of any web application. You often need to open and process a file for different tasks
File handling
PHP has several functions for… (in context of file handling)
creating, reading, uploading, and editing files
Be careful when manipulating files!
- When you are manipulating files you must be very careful.
- You can do a lot of damage if you do something wrong.
Common errors are: editing the wrong file, filling a hard-drive with garbage data, and deleting the content of a file by accident.
The ___ function reads a file and writes it to the output buffer.
readfile()
Assume we have a text file called “webdictionary.txt”, stored on the server, that looks like this:
AJAX = Asynchronous JavaScript and XML
CSS = Cascading Style Sheets
HTML = Hyper Text Markup Language
PHP = PHP Hypertext Preprocessor
SQL = Structured Query Language
SVG = Scalable Vector Graphics
XML = EXtensible Markup Language
AJAX
Asynchronous JavaScript and XML
CSS
Cascading Style Sheets
HTML
Hyper Text Markup Language
PHP
PHP Hypertext Preprocessor
SQL
Structured Query Language
SVG
Scalable Vector Graphics
XML
EXtensible Markup Language
The PHP code to read the file and write it to the output buffer is as follows (the readfile() function returns the number of bytes read on success):
echo readfile(“webdictionary.txt”);
OUTPUT:
AJAX = Asynchronous JavaScript and XML CSS = Cascading Style Sheets HTML =
Hyper Text Markup Language PHP = PHP Hypertext Preprocessor SQL = Structured
Query Language SVG = Scalable Vector Graphics XML = EXtensible Markup
Language236
The ___ function is useful if all you want to do is open up a file and read its contents.
readfile()
A better method to open files is with the ___ function. This function gives you more options than the readfile() function.
fopen()
The first parameter of fopen() contains the name of the file to be opened and the second parameter specifies in which mode the file should be opened. The following example also generates a message if the fopen() function is unable to open the specified file:
$myfile = fopen(“webdictionary.txt”, “r”) or die(“Unable to open file!”);
echo fread($myfile,filesize(“webdictionary.txt”));
fclose($myfile);
OUTPUT:
AJAX = Asynchronous JavaScript and XML CSS = Cascading Style Sheets HTML = Hyper Text Markup Language PHP = PHP Hypertext Preprocessor SQL = Structured Query Language SVG = Scalable Vector Graphics XML = EXtensible Markup Language
The file may be opened in one of the following modes: (in context of fopen()) (8)
r, w, a, x,
r+, w+, a+, x+
fopen, r
r | Open a file for read only. File pointer starts at the beginning of the file
fopen, w
w | Open a file for write only. Erases the contents of the file or creates a new file if it doesn’t exist. File pointer starts at the beginning of the file
fopen, a
a | Open a file for write only. The existing data in file is preserved. File pointer starts at the end of the file. Creates a new file if the file doesn’t exist
fopen, x
x | Creates a new file for write only. Returns FALSE and an error if file already exists
fopen, r+
r+ | Open a file for read/write. File pointer starts at the beginning of the file
fopen, w+
w+ | Open a file for read/write. Erases the contents of the file or creates a new file if it doesn’t exist. File pointer starts at the beginning of the file
fopen, a+
a+ | Open a file for read/write. The existing data in file is preserved. File pointer starts at the end of the file. Creates a new file if the file doesn’t exist
fopen, x+
x+ | Creates a new file for read/write. Returns FALSE and an error if file already Exists
The ___ function reads from an open file.
The first parameter of ___ contains the name of the file to read from and the
second parameter specifies the maximum number of bytes to read.
fread()
The following PHP code reads the “webdictionary.txt” file to the end:
fread($myfile,filesize(“webdictionary.txt”));
The ___ function is used to close an open file.
fclose()
It’s a good programming practice to…
You don’t want an open file running around on your server taking up resources!
close all files after you have finished with them.
The fclose() requires the name of the file (or a variable that holds the filename) we want to close:
$myfile = fopen(“webdictionary.txt”, “r”);
// some code to be executed….
fclose($myfile);
The ___ function is used to read a single line from a file
fgets()
The example below outputs the first line of the “webdictionary.txt” file:
$myfile = fopen(“webdictionary.txt”, “r”) or die(“Unable to open file!”);
echo fgets($myfile);
fclose($myfile);
OUTPUT:
AJAX = Asynchronous JavaScript and XML
After a call to the fgets() function, the file pointer has moved to the next line.
The ___ function checks if the “end-of-file” (EOF) has been reached.
feof()
The ___ function is useful for looping through data of unknown length.
feof()
The example below reads the “webdictionary.txt” file line by line, until end-of-file is reached:
$myfile = fopen(“webdictionary.txt”, “r”) or die(“Unable to open file!”);
// Output one line until end-of-file
while(!feof($myfile)) {
echo fgets($myfile) . “<br></br>”;
}
fclose($myfile);
OUTPUT:
AJAX = Asynchronous JavaScript and XML
CSS = Cascading Style Sheets
HTML = Hyper Text Markup Language
PHP = PHP Hypertext Preprocessor
SQL = Structured Query Language
SVG = Scalable Vector Graphics
XML = EXtensible Markup Language
The ___ function is used to read a single character from a file.
fgetc()
The example below reads the “webdictionary.txt” file character by character, until end-of-file is reached:
$myfile = fopen(“webdictionary.txt”, “r”) or die(“Unable to open file!”);
// Output one character until end-of-file
while(!feof($myfile)) {
echo fgetc($myfile);
}
fclose($myfile);
OUTPUT:
AJAX = Asynchronous JavaScript and XML CSS = Cascading Style Sheets HTML = Hyper
Text Markup Language PHP = PHP Hypertext Preprocessor SQL = Structured Query
Language SVG = Scalable Vector Graphics XML = EXtensible Markup Language
After a call to the fgetc() function, the file pointer moves to the next character.
The ___ function is also used to create a file. Maybe a little confusing, but in PHP, a file is created using the same function used to open files.
fopen()
If you use ___ on a file that does not exist, it will create it, given that the file is opened for writing (w) or appending (a).
fopen()
The example below creates a new file called “testfile.txt”. The file will be created in the same directory where the PHP code resides:
$myfile = fopen(“testfile.txt”, “w”)
If you are having errors when trying to get this code to run, check that you have granted your PHP file access to write information to the hard drive.
PHP File Permissions
The ___ function is used to write to a file.
fwrite()
The first parameter of fwrite() contains the ___ and the second parameter is the ___.
name of the file to write to;
string to be written
The example below writes a couple of names into a new file called “newfile.txt”:
$myfile = fopen(“newfile.txt”, “w”) or die(“Unable to open file!”);
$txt = “John Doe\n”;
fwrite($myfile, $txt);
$txt = “Jane Doe\n”;
fwrite($myfile, $txt);
fclose($myfile);
Notice that we wrote to the file “newfile.txt” twice. Each time we wrote to the file we sent the string $txt that first contained “John Doe” and second contained “Jane Doe”. After we finished writing, we closed the file using the fclose() function.
If we open the “newfile.txt” file it would look like this:
John Doe
Jane Doe
Now that “newfile.txt” contains some data we can show what happens when we open an existing file for writing.
All the existing data will be ERASED and we start with an empty file.
In the example below we open our existing file “newfile.txt”, and write some new data into it:
$myfile = fopen(“newfile.txt”, “w”) or die(“Unable to open file!”);
$txt = “Mickey Mouse\n”;
fwrite($myfile, $txt);
$txt = “Minnie Mouse\n”;
fwrite($myfile, $txt);
fclose($myfile);
If we now open the “newfile.txt” file, both John and Jane have vanished, and only the
data we just wrote is present:
OUTPUT:
Mickey Mouse
Minnie Mouse
PHP ftp_fput() Function
Example
* Open local file, and upload it to a file on the FTP server:
// connect and login to FTP server
$ftp_server = “ftp.example.com”;
$ftp_conn = ftp_connect($ftp_server) or die(“Could not connect to $ftp_server”);
$login = ftp_login($ftp_conn, $ftp_username, $ftp_userpass);
// open file for reading
$file = “test.txt”;
$fp = fopen($file,”r”);
// upload file
if (ftp_fput($ftp_conn, “somefile.txt”, $fp, FTP_ASCII)) {
echo “Successfully uploaded $file.”;
}
else {
echo “Error uploading $file.”;
}
// close this connection and file handler
ftp_close($ftp_conn);
fclose($fp);
The ___ function uploads from an open file and saves it to a file on the FTP server.
ftp_fput()
Syntax of ftp_put()
ftp_fput(ftp_connection,remote_file,open_file,mode,startpos);
Parameters for the ftp_put()
ftp_connection => a
remote_file => b
open_file => c
mode => d
mode => e
a => Required. Specifies the FTP connection to use
b => Required. Specifies the file path to upload to
c => Required. Specifies an open local file. Reading stops at end of file
d => Required. Specifies the transfer mode. Possible values:
FTP_ASCII or FTP_BINARY
e => Optional. Specifies the position in the remote file to start uploading to
- With PHP, you can connect to and manipulate ___.
- ___ is the most popular database system used with PHP.
databases; MySQL
What is MySQL? part 1
- MySQL is a database system used on the web
- MySQL is a database system that runs on a server
- MySQL is ideal for both small and large applications
- MySQL is very fast, reliable, and easy to use
What is MySQL? part 2
- MySQL uses standard SQL
- MySQL compiles on a number of platforms
- MySQL is free to download and use
- MySQL is developed, distributed, and supported by Oracle Corporation
The data in a MySQL database are stored in tables. A ___ is a collection of related data, and it consists of columns and rows.
table
___ are useful for storing information categorically.
Databases
A company may have a database with the following tables:
- Employees
- Products
- Customers
- Orders
PHP combined with MySQL are ___ (you can develop in Windows and serve on a Unix platform)
cross-platform
PHP 5 and later can work with a MySQL database using:
* MySQLi extension (the “i” stands for improved)
* PDO (PHP Data Objects)
Earlier versions of PHP used the MySQL extension. However, this extension
was deprecated in 2012.
Should I Use MySQLi or PDO?
If you need a short answer, it would be “Whatever you like”.
Both MySQLi and PDO have their advantages:
- PDO will work on 12 different database systems, whereas MySQLi will only work
with MySQL databases. - So, if you have to switch your project to use another database, PDO makes the
process easy. You only have to change the connection string and a few queries.
With MySQLi, you will need to rewrite the entire code - queries included. - Both are object-oriented, but MySQLi also offers a procedural API.
- Both support Prepared Statements. Prepared Statements protect from SQL
injection, and are very important for web application security.
MySQL Examples in Both MySQLi and PDO Syntax
In this, and in the following chapters we demonstrate three ways of working with
PHP and MySQL:
* MySQLi (object-oriented)
* MySQLi (procedural)
* PDO
MySQLi Installation
- For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.
- For installation details, go to: http://php.net/manual/en/mysqli.installation.php
- PDO Installation
- For installation details, go to: http://php.net/manual/en/pdo.installation.php
Open a Connection to MySQL
- Before we can access data in the MySQL database, we need to be able to connect to the server:
Example (MySQLi Object-Oriented)
$servername = “localhost”;
$username = “username”;
$password = “password”;
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: “ . $conn->connect_error);
}
echo “Connected successfully”;
Example (MySQLi Procedural)
$servername = “localhost”;
$username = “username”;
$password = “password”;
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die(“Connection failed: “ . mysqli_connect_error());
}
echo “Connected successfully”;
Example (PDO)
$servername = “localhost”;
$username = “username”;
$password = “password”;
try {
$conn = new PDO(“mysql:host=$servername;dbname=myDB”,
$username, $password);
// set the PDO error mode to exception $conn-
>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo
“Connected successfully”;
}
catch(PDOException $e)
{
echo “Connection failed: “ . $e->getMessage();
}
Notice that in the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.
Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.
Close the Connection
The connection will be closed automatically when the script ends. To close the connection before, use the following:
Example (MySQLi Object-Oriented)
$conn->close();
Example (MySQLi Procedural)
mysqli_close($conn);
Example (PDO)
$conn = null;
PHP Create a MySQL Database
- A database consists of one or more tables.
- You will need special CREATE privileges to create or to delete a MySQL database.
- Create a MySQL Database Using MySQLi
- The CREATE DATABASE statement is used to create a database in MySQL.
- The following examples create a database named “myDB”:
Example (MySQLi Object-oriented)
$servername = “localhost”;
$username = “username”;
$password = “password”;
// Create connection
$conn = new mysqli($servername, $username,
$password); // Check connection
if ($conn->connect_error) {
die(“Connection failed: “ . $conn->connect_error);
}
// Create database
$sql = “CREATE DATABASE myDB”;
if ($conn->query($sql) === TRUE) {
echo “Database created
successfully”; } else {
echo “Error creating database: “ . $conn->error;
}
$conn-
>close();
Note: When you create a new database, you must only specify the first three arguments to the mysqli object (servername, username and password).
Tip: If you have to use a specific port, add an empty string for the database-name argument, like this: new mysqli(“localhost”, “username”, “password”, “”, port)
PHP Create MySQL Table
- A database table has its own unique name and consists of columns and rows.
- Create a MySQL Table Using MySQLi
- The CREATE TABLE statement is used to create a table in MySQL.
- We will create a table named “MyGuests”, with five columns: “id”, “firstname”, “lastname”, “email” and “reg_date”:
CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY
KEY, firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT
NULL, email VARCHAR(50),
reg_date TIMESTAMP
)
Notes on the table above:
* The data type specifies what type of data the column can hold. For a complete
reference of all the available data types, go to our Data Types reference.
After the data type, you can specify other optional attributes for each column:
* NOT NULL - Each row must contain a value for that column, null values are
not allowed
* DEFAULT value - Set a default value that is added when no other value
is passed
* UNSIGNED - Used for number types, limits the stored data to positive
numbers and zero
* AUTO INCREMENT - MySQL automatically increases the value of the field by
1 each time a new record is added
* PRIMARY KEY - Used to uniquely identify the rows in a table. The column
with PRIMARY KEY setting is often an ID number, and is often used with
AUTO_INCREMENT
Each table should have a primary key column (in this case: the “id” column). Its
value must be unique for each record in the table.
The following examples shows how to create the table in PHP:
Example (MySQLi Object-oriented)
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDB”;
// Create connection
$conn = new mysqli($servername, $username, $password,
$dbname); // Check connection
if ($conn->connect_error) {
die(“Connection failed: “ . $conn->connect_error);
}
// sql to create table
$sql = “CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY
KEY, firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT
NULL, email VARCHAR(50),
reg_date TIMESTAMP
)”;
if ($conn->query($sql) === TRUE) {
echo “Table MyGuests created
successfully”; } else {
echo “Error creating table: “ . $conn->error;
}
$conn-
>close();
PHP Insert Data Into MySQL
Insert Data Into MySQL Using MySQLi
- After a database and a table have been created, we can start adding data in them.
Here are some syntax rules to follow: - The SQL query must be quoted in PHP
- String values inside the SQL query must be quoted
- Numeric values must not be quoted
- The word NULL must not be quoted
The INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)
Note: If a column is AUTO_INCREMENT (like the “id” column) or TIMESTAMP (like the “reg_date” column), it is no need to be specified in the SQL query; MySQL will automatically add the value.
No tip included
The following examples add a new record to the “MyGuests” table:
Example (MySQLi Object-oriented)
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “myDB”;
// Create connection
$conn = new mysqli($servername, $username, $password,
$dbname); // Check connection
if ($conn->connect_error) {
die(“Connection failed: “ . $conn->connect_error);
}
$sql = “INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)”;
if ($conn->query($sql) === TRUE) {
echo “New record created
successfully”; } else {
echo “Error: “ . $sql . “<br></br>” . $conn->error;
}
$conn-
>close();
Example:
Student Application using PHP and Mysql
aa.html:
<!DOCTYPE HTML>
<html>
<head>
<title>Student
Table</title> </head>
<body>
<div> <h3>Student table
entry</h3> <form>
SName <br></br><input></input><br></br>
Reg.No <br></br><input></input><br></br>
Mark1<br></br><input></input><br></br>
Mark2<br></br><input></input><br></br> <input></input>
</form>
</div>
</body>
</html>
Conn.php:
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “sample”;
// Create connection
$conn = new mysqli($servername, $username, $password,$dbname);
// Check connection
if ($conn->connect_error)
{
die(“Connection failed: “ . $conn->connect_error);
}
echo “Connected
successfully”; //connect table
$sql=”desc student”; if($conn-
>query($sql)==TRUE)
{
echo “<br></br>”;
echo “connected to the table”;
}
else
{
echo “error”;
}
//Inserting the
contents echo “<br></br>”;
//insertion from html
$sname=$_POST[‘sname’];
$regno=$_POST[‘regno’];
$m1=$_POST[‘m1’];
$m2=$_POST[‘m2’];
$sql11=”insert into student values(‘$sname’,$regno,$m1,$m2)”;
if($conn->query($sql11)==TRUE)
{
echo “inserted”;
}
else
{echo “error”;}
echo “<br></br>”;
$sql1=”select * from student”;
$result = $conn->query($sql1);
if ($result->num_rows > 0) {
// output data of each row
echo “<b>Sname Regno M1 M2</b><br></br>”;
while($row = $result->fetch_assoc()) {
echo $row[“sname”].” “. $row[“regno”].” “.$row[“m1”].” “.$row
[“m2”].”<br></br>”;
}
} else {
echo “empty table”;
}
$conn-
>close();
