Semis Lecture 2

Question 1

Very often when you write code, you want to perform different actions for different conditions. You can use ___ in your code to do this.

Answer 1

conditional statements

Question 2

In PHP we have the following conditional statements: (4)

Answer 2

• if statement
• if…else statement
• if…elseif….else statement
• switch statement

Question 3

executes some code if one condition is true

Answer 3

if statement

Question 4

executes some code if a condition is true and another code if that condition is false

Answer 4

if…else statement

Question 5

executes different codes for more than two conditions

Answer 5

if…elseif….else statement

Question 6

selects one of many blocks of code to be executed

Answer 6

switch statement

Question 7

Syntax of the if statement

Answer 7

if (condition) {
code to be executed if condition is true;
}

Question 8

this code block is an example of:

$t = date(“H”);

if ($t < “20”) {
echo “Have a good day!”;
}

Answer 8

if statement

Question 9

Syntax of the if… else statement

Answer 9

if (condition) {
code to be executed if condition is true;
} else {
code to be executed if condition is false;
}

Question 10

this code block is an example of:

$t = date(“H”);

if ($t < “20”) {
echo “Have a good day!”;
} else {
echo “Have a good night!”;
}

Answer 10

if… else statement

Question 11

Syntax of the if… elseif… else statement

Answer 11

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;
}

Question 12

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!”;
}

Answer 12

if… elseif… else statement

Question 13

Syntax of the switch statement

Answer 13

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;
}

Question 14

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.

Answer 14

Switch statement

Question 15

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!”;
}

Answer 15

switch statement

Question 16

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.

Answer 16

loops

Question 17

In PHP, we have the following looping statements: (4)

Answer 17

• while
• do…while
• for
• foreach

Question 18

loops through a block of code as long as the specified condition is true

Answer 18

while

Question 19

loops through a block of code once, and then repeats the loop as long as the specified condition is true

Answer 19

do…while

Question 20

loops through a block of code a specified number of times

Answer 20

for

Question 21

loops through a block of code for each element in an array

Answer 21

foreach

Question 22

Syntax of while loop

Answer 22

while (condition is true) {
code to be executed;
}

Question 23

$x = 1;

while($x <= 5) {
echo “The number is: $x
<br></br>”;
$x++;
}

Answer 23

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++)

Question 24

Syntax of do… while loop

Answer 24

do {
code to be executed; }
while (condition is true);

Question 25

$x = 1; do { echo "The number is: $x
"; $x++; } while ($x <= 5);

Answer 25

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

Question 26

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.

Answer 26

AFTER; at least once

Question 27

The example below sets the $x variable to 6, then it runs the loop, and then the condition is checked:

Answer 27

$x = 6; do { echo "The number is: $x
"; $x++; } while ($x <= 5);

Question 28

Syntax for for Loop

Answer 28

for (init counter; test counter; increment counter) { code to be executed; }

Question 29

Explain the Parameters: for (init counter; test counter; increment counter) { code to be executed; }

Answer 29

* 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

Question 30

example code block for? for ($x = 0; $x <= 10; $x++) { echo "The number is: $x
"; }

Answer 30

for loop

Question 31

Syntax for foreach loop

Answer 31

foreach ($array as $value) { code to be executed; }

Question 32

foreach ($array as $value) { code to be executed; }

Answer 32

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

Question 33

The following example demonstrates a loop that will output the values of the given array ($colors):

Answer 33

$colors = array("red", "green", "blue", "yellow"); foreach ($colors as $value){ echo "$value
"; }

Question 34

There are two ways to create indexed arrays:

Answer 34

1. The index can be assigned automatically, or 2. the index can be assigned manually:

Question 35

index can be assigned automatically:

Answer 35

(index always starts at 0), like this: * $cars = array("Volvo", "BMW", "Toyota");

Question 36

index can be assigned manually:

Answer 36

$cars[0] = "Volvo"; $cars[1] = "BMW"; $cars[2] = "Toyota";

Question 37

The following example creates an indexed array named $cars, assigns three elements to it, and then prints a text containing the array values:

Answer 37

$cars = array("Volvo", "BMW", "Toyota"); echo "I like " . $cars[0] . ", " . $cars[1] . " and " . $cars[2] . ".";

Question 38

The ___ function is used to return the length (the number of elements) of an array:

Answer 38

count()

Question 39

$cars = array("Volvo", "BMW", "Toyota"); echo count($cars);

Answer 39

OUTPUT: 3

Question 40

To loop through and print all the values of an indexed array, you could use a for loop, like this:

Answer 40

$cars = array("Volvo", "BMW", "Toyota"); $arrlength = count($cars); for($x = 0; $x < $arrlength; $x++) { echo $cars[$x]; echo "
"; } OUTPUT: Volvo BMW Toyota

Question 41

___ are arrays that use named keys that you assign to them

Answer 41

Associative arrays

Question 42

There are two ways to create an associative array:

Answer 42

First: $age = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); Second: $age['Peter'] = "35"; $age['Ben'] = "37"; $age['Joe'] = "43";

Question 43

The named keys in the Associative Array can then be used in a script

Answer 43

$age = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); echo "Peter is " . $age['Peter'] . " years old."; OUTPUT: Peter is 35 years old.

Question 44

To loop through and print all the values of an associative array, you could use a foreach loop, like this:

Answer 44

$age = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); foreach($age as $x => $x_value) { echo "Key=" . $x . ", Value=" . $x_value; echo "
"; }

Question 45

The elements in an array can be sorted in alphabetical or numerical order, descending or ascending

Answer 45

Sorting Arrays

Question 46

sort arrays in ascending order

Answer 46

sort()

Question 47

sort arrays in descending order

Answer 47

rsort()

Question 48

sort associative arrays in ascending order, according to the value

Answer 48

asort()

Question 49

sort associative arrays in ascending order, according to the key

Answer 49

ksort()

Question 50

sort associative arrays in descending order, according to the value

Answer 50

arsort()

Question 51

sort associative arrays in descending order, according to the key

Answer 51

krsort()

Question 52

Sort Array in Ascending Order - sort() The following example sorts the elements of the $cars array in ascending alphabetical order:

Answer 52

$cars = array("Volvo", "BMW", "Toyota"); sort($cars); $clength = count($cars); for($x = 0; $x < $clength; $x++) { echo $cars[$x]; echo "
"; } OUTPUT: BMW Toyota Volvo

Question 53

Sort Array in Ascending Order - sort() The following example sorts the elements of the $numbers array in ascending numerical order:

Answer 53

$numbers = array(4, 6, 2, 22, 11); sort($numbers); OUTPUT: 2 4 6 11 22

Question 54

Sort Array (Ascending Order), According to Value - asort() The following example sorts an associative array in ascending order, according to the value:

Answer 54

$age = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); asort($age); OUTPUT: Key=Peter, Value=35 Key=Ben, Value=37 Key=Joe, Value=43

Question 55

Sort Array (Ascending Order), According to Key - ksort() The following example sorts an associative array in ascending order, according to the key:

Answer 55

$age = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); ksort($age); OUTPUT: Key=Ben, Value=37 Key=Joe, Value=43 Key=Peter, Value=35

Question 56

Sort Array (Descending Order), According to Value - arsort() The following example sorts an associative array in descending order, according to the value:

Answer 56

$age = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); arsort($age); OUTPUT: Key=Joe, Value=43 Key=Ben, Value=37 Key=Peter, Value=35

Question 57

Sort Array (Descending Order), According to Key - krsort() The following example sorts an associative array in descending order, according to the key:

Answer 57

$age = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); krsort($age); OUTPUT: Key=Peter, Value=35 Key=Joe, Value=43 Key=Ben, Value=37

Question 58

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:

Answer 58

$cars = array("Volvo","BMW","Toyota"); var_dump($cars); OUTPUT: array(3) { [0]=> string(5) "Volvo" [1]=> string(3) "BMW" [2]=> string(6) "Toyota" }

Question 59

The real power of PHP comes from its ___.

Answer 59

functions

Question 60

PHP has ___ built-in functions

Answer 60

more than 1000

Question 61

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

Answer 61

function

Question 62

Create a User Defined Function in PHP A user-defined function declaration starts with the word function: Syntax

Answer 62

function functionName() { code to be executed; } * Function names are NOT case-sensitive.

Question 63

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:

Answer 63

function writeMsg(){ echo "Hello world!"; } writeMsg(); OUTPUT: Hello world!

Question 64

___ can be passed to functions through arguments. An ___ is just like a variable.

Answer 64

Information; argument

Question 65

* ___ are specified after the function name, inside the parentheses. You can add as many arguments as you want, just separate them with a comma.

Answer 65

Arguments

Question 66

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:

Answer 66

function familyName($fname) { echo "$fname Refsnes.
"; } familyName("Jani"); familyName("Hege"); familyName("Stale"); familyName("Kai Jim"); familyName("Borge"); OUTPUT: Jani Refsnes. Hege Refsnes. Stale Refsnes. Kai Jim Refsnes. Borge Refsnes.

Question 67

The following example has a function with two arguments ($fname and $year):

Answer 67

function familyName($fname, $year) { echo "$fname Refsnes. Born in $year
"; } 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

Question 68

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:

Answer 68

function setHeight($minheight = 50) { echo "The height is : $minheight
"; } setHeight(350); setHeight(); setHeight(135); setHeight(80); OUTPUT: The height is : 350 The height is : 50 The height is : 135 The height is : 80

Question 69

To let a function return a value, use the return statement:

Answer 69

function sum($x, $y) { $z = $x + $y; return $z; } echo "5 + 10 = " . sum(5,10) . "
"; echo "7 + 13 = " . sum(7,13) . "
"; echo "2 + 4 = " . sum(2,4); OUTPUT: 5 + 10 = 15 7 + 13 = 20 2 + 4 = 6

Question 70

___ is an important part of any web application. You often need to open and process a file for different tasks

Answer 70

File handling

Question 71

PHP has several functions for... (in context of file handling)

Answer 71

creating, reading, uploading, and editing files

Question 72

Be careful when manipulating files!

Answer 72

* 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.

Question 73

The ___ function reads a file and writes it to the output buffer.

Answer 73

readfile()

Question 74

Assume we have a text file called "webdictionary.txt", stored on the server, that looks like this:

Answer 74

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

Question 75

AJAX

Answer 75

Asynchronous JavaScript and XML

Question 76

CSS

Answer 76

Cascading Style Sheets

Question 77

HTML

Answer 77

Hyper Text Markup Language

Question 78

PHP

Answer 78

PHP Hypertext Preprocessor

Question 79

SQL

Answer 79

Structured Query Language

Question 80

SVG

Answer 80

Scalable Vector Graphics

Question 81

XML

Answer 81

EXtensible Markup Language

Question 82

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.

Answer 82

readfile()

Question 83

A better method to open files is with the ___ function. This function gives you more options than the readfile() function.

Answer 83

fopen()

Question 84

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:

Answer 84

$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

Question 85

The file may be opened in one of the following modes: (in context of fopen()) (8)

Answer 85

r, w, a, x, r+, w+, a+, x+

Question 86

fopen, r

Answer 86

r | Open a file for read only. File pointer starts at the beginning of the file

Question 87

fopen, w

Answer 87

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

Question 88

fopen, a

Answer 88

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

Question 89

fopen, x

Answer 89

x | Creates a new file for write only. Returns FALSE and an error if file already exists

Question 90

fopen, r+

Answer 90

r+ | Open a file for read/write. File pointer starts at the beginning of the file

Question 91

fopen, w+

Answer 91

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

Question 92

fopen, a+

Answer 92

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

Question 93

fopen, x+

Answer 93

x+ | Creates a new file for read/write. Returns FALSE and an error if file already Exists

Question 94

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.

Answer 94

fread()

Question 95

The following PHP code reads the "webdictionary.txt" file to the end:

Answer 95

fread($myfile,filesize("webdictionary.txt"));

Question 96

The ___ function is used to close an open file.

Answer 96

fclose()

Question 97

It's a good programming practice to... You don't want an open file running around on your server taking up resources!

Answer 97

close all files after you have finished with them.

Question 98

The fclose() requires the name of the file (or a variable that holds the filename) we want to close:

Answer 98

$myfile = fopen("webdictionary.txt", "r"); // some code to be executed.... fclose($myfile);

Question 99

The ___ function is used to read a single line from a file

Answer 99

fgets()

Question 100

The example below outputs the first line of the "webdictionary.txt" file:

Answer 100

$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.

Question 101

The ___ function checks if the "end-of-file" (EOF) has been reached.

Answer 101

feof()

Question 102

The ___ function is useful for looping through data of unknown length.

Answer 102

feof()

Question 103

The example below reads the "webdictionary.txt" file line by line, until end-of-file is reached:

Answer 103

$myfile = fopen("webdictionary.txt", "r") or die("Unable to open file!"); // Output one line until end-of-file while(!feof($myfile)) { echo fgets($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

Question 104

The ___ function is used to read a single character from a file.

Answer 104

fgetc()

Question 105

The example below reads the "webdictionary.txt" file character by character, until end-of-file is reached:

Answer 105

$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.

Question 106

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.

Answer 106

fopen()

Question 107

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).

Answer 107

fopen()

Question 108

The example below creates a new file called "testfile.txt". The file will be created in the same directory where the PHP code resides:

Answer 108

$myfile = fopen("testfile.txt", "w")

Question 109

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.

Answer 109

PHP File Permissions

Question 110

The ___ function is used to write to a file.

Answer 110

fwrite()

Question 111

The first parameter of fwrite() contains the ___ and the second parameter is the ___.

Answer 111

name of the file to write to; string to be written

Question 112

The example below writes a couple of names into a new file called "newfile.txt":

Answer 112

$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);

Question 113

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.

Answer 113

If we open the "newfile.txt" file it would look like this: John Doe Jane Doe

Question 114

Now that "newfile.txt" contains some data we can show what happens when we open an existing file for writing.

Answer 114

All the existing data will be ERASED and we start with an empty file.

Question 115

In the example below we open our existing file "newfile.txt", and write some new data into it:

Answer 115

$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

Question 116

PHP ftp_fput() Function Example * Open local file, and upload it to a file on the FTP server:

Answer 116

// 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);

Question 117

The ___ function uploads from an open file and saves it to a file on the FTP server.

Answer 117

ftp_fput()

Question 118

Syntax of ftp_put()

Answer 118

ftp_fput(ftp_connection,remote_file,open_file,mode,startpos);

Question 119

Parameters for the ftp_put() ftp_connection => a remote_file => b open_file => c mode => d mode => e

Answer 119

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

Question 120

* With PHP, you can connect to and manipulate ___. * ___ is the most popular database system used with PHP.

Answer 120

databases; MySQL

Question 121

What is MySQL? part 1

Answer 121

* 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

Question 122

What is MySQL? part 2

Answer 122

* 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

Question 123

The data in a MySQL database are stored in tables. A ___ is a collection of related data, and it consists of columns and rows.

Answer 123

table

Question 124

___ are useful for storing information categorically.

Answer 124

Databases

Question 125

A company may have a database with the following tables:

Answer 125

* Employees * Products * Customers * Orders

Question 126

PHP combined with MySQL are ___ (you can develop in Windows and serve on a Unix platform)

Answer 126

cross-platform

Question 127

PHP 5 and later can work with a MySQL database using: * MySQLi extension (the "i" stands for improved) * PDO (PHP Data Objects)

Answer 127

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Question 128

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:

Answer 128

* 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.

Question 129

MySQL Examples in Both MySQLi and PDO Syntax

Answer 129

In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL: * MySQLi (object-oriented) * MySQLi (procedural) * PDO

Question 130

MySQLi Installation

Answer 130

* 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

Question 131

Open a Connection to MySQL

Answer 131

* Before we can access data in the MySQL database, we need to be able to connect to the server:

Question 132

Example (MySQLi Object-Oriented)

Answer 132

$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";

Question 133

Example (MySQLi Procedural)

Answer 133

$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";

Question 134

Example (PDO)

Answer 134

$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(); }

Question 135

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.

Answer 135

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.

Question 136

Close the Connection

Answer 136

The connection will be closed automatically when the script ends. To close the connection before, use the following:

Question 137

Example (MySQLi Object-Oriented)

Answer 137

$conn->close();

Question 138

Example (MySQLi Procedural)

Answer 138

mysqli_close($conn);

Question 139

Example (PDO)

Answer 139

$conn = null;

Question 140

PHP Create a MySQL Database

Answer 140

* 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":

Question 141

Example (MySQLi Object-oriented)

Answer 141

$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();

Question 142

Note: When you create a new database, you must only specify the first three arguments to the mysqli object (servername, username and password).

Answer 142

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)

Question 143

PHP Create MySQL Table

Answer 143

* 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":

Question 144

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 )

Answer 144

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.

Question 145

The following examples shows how to create the table in PHP: Example (MySQLi Object-oriented)

Answer 145

$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();

Question 146

PHP Insert Data Into MySQL Insert Data Into MySQL Using MySQLi

Answer 146

* 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

Question 147

The INSERT INTO statement is used to add new records to a MySQL table:

Answer 147

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Question 148

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.

Answer 148

No tip included

Question 149

The following examples add a new record to the "MyGuests" table: Example (MySQLi Object-oriented)

Answer 149

$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 . "
" . $conn->error; } $conn- >close();

Question 150

Example: Student Application using PHP and Mysql aa.html:

Student table entry

SName

Reg.No

Mark1

Mark2

Answer 150

Conn.php: connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully"; //connect table $sql="desc student"; if($conn- >query($sql)==TRUE) { echo "
"; echo "connected to the table"; } else { echo "error"; } //Inserting the contents echo "
"; //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 "
"; $sql1="select * from student"; $result = $conn->query($sql1); if ($result->num_rows > 0) { // output data of each row echo "Sname Regno M1 M2
"; while($row = $result->fetch_assoc()) { echo $row["sname"]." ". $row["regno"]." ".$row["m1"]." ".$row ["m2"]."
"; } } else { echo "empty table"; } $conn- >close();