Mysqli Code PHP to Database Mysql

1.Perispan database :Download XAMPP-Install dan aktifkan

2.Buat database dan tabel :

Database name: db_akademik

CREATE TABLE `tb_kelas` (
  `kode_kelas` varchar(15) NOT NULL,
  `nama_kelas` varchar(30) NOT NULL,
  `kode_ruang` varchar(15) NOT NULL,
  `kode_guru` varchar(15) NOT NULL,
  `periode` varchar(30) NOT NULL,
  `catatan` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Demikian pula buat tabel-tabel yang lainnya.....

3. Buat Koneksi php ke mysqli

 

Connect

Define connection parameters:

1 2 3 4

$DBServer = 'server name or IP address'; // e.g 'localhost' or '192.168.1.100' $DBUser   = 'DB_USER'; $DBPass   = 'DB_PASSWORD'; $DBName   = 'DB_NAME';

Connection using the object oriented way (RECOMMENDED).

1 2 3 4 5 6

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);   // check connection if ($conn->connect_error) {   trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR); }

Connection using the procedural way (NOT RECOMMENDED).

MySQLi also offers a procedural API, except the object-oriented API. Procedural API might be easier for newcomers to understand, as it is similar with the old PHP MySQL extension API. Here is an example:

1 2 3 4 5 6

$conn = mysqli_connect($DBServer, $DBUser, $DBPass, $DBName);   // check connection if (mysqli_connect_errno()) {   trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR); }

(I will not quote further examples using the proceduaral API, as I recommend to use the object-oriented API. Of course it is available in PHP MySQLi documentation). A useful summary is also available.

Select

Use the following syntax:

1 2 3 4 5 6 7 8 9

$sql='SELECT col1, col2, col3 FROM table1 WHERE condition';   $rs=$conn->query($sql);   if($rs === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); } else {   $rows_returned = $rs->num_rows; }

Iterate over recordset

Using column names - recommended

1 2 3 4

$rs->data_seek(0); while($row = $rs->fetch_assoc()){     echo $row['col1'] . '<br>'; }

Using column index

1 2 3 4

$rs->data_seek(0); while($row = $rs->fetch_row()){     echo $row[0] . '<br>'; }

Store all values to array

1 2 3 4 5 6 7 8 9 10

$rs=$conn->query($sql);   if($rs === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); } else {   $arr = $rs->fetch_all(MYSQLI_ASSOC); } foreach($arr as $row) {   echo $row['co1']; }

Using MYSQLI_ASSOC an associated array is returned, MYSQLI_NUM an enumerated one and MYSQLI_BOTH both of them.

WARNING: fetch_all is available only with MySQL Native Driver.

Store row values to array

The following example will return an array with first row (using $rs->data_seek(n); we can get any row).

1 2 3 4 5 6 7 8

$rs=$conn->query($sql);   if($rs === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); } else {   $rs->data_seek(0);   $arr = $rs->fetch_array(MYSQLI_ASSOC); }

Record count

1	$rows_returned = $rs->num_rows;

Move inside recordset

1	$rs->data_seek(10);

Free memory

Optional:

1	$rs->free();

Insert

Use the following syntax:

real_escape_string is used to escape special characters NUL (ASCII 0), \n, \r, \, ', ", and Control-Z in string values before insert to Database (mainly to prevent SQL injection).

WARNING: real_escape_string does not add quotes, you have to do it manually.

1 2 3 4 5 6 7 8 9 10

$v1="'" . $conn->real_escape_string('col1_value') . "'";   $sql="INSERT INTO tbl (col1_varchar, col2_number) VALUES ($v1,10)";   if($conn->query($sql) === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); } else {   $last_inserted_id = $conn->insert_id;   $affected_rows = $conn->affected_rows; }

Update

Use the following syntax:

1 2 3 4 5 6 7 8 9

$v1="'" . $conn->real_escape_string('col1_value') . "'";   $sql="UPDATE tbl SET col1_varchar=$v1, col2_number=1 WHERE id>10";   if($conn->query($sql) === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); } else {   $affected_rows = $conn->affected_rows; }

Delete

Use the following syntax:

1 2 3 4 5 6 7

$sql="DELETE FROM tbl WHERE id>10";   if($conn->query($sql) === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); } else {   $affected_rows = $conn->affected_rows; }

Transactions

Use the following syntax:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

try {   /* switch autocommit status to FALSE. Actually, it starts transaction */   $conn->autocommit(FALSE);     $res = $conn->query($sql1);   if($res === false) {     throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);   }     $res = $conn->query($sql2);   if($res === false) {     throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);   }     $res = $conn->query($sql3);   if($res === false) {     throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);   }     $conn->commit();   echo 'Transaction completed successfully!';   } catch (Exception $e) {     echo 'Transaction failed: ' . $e->getMessage();   $conn->rollback(); }   /* switch back autocommit status */ $conn->autocommit(TRUE);

According to http://www.php.net/manual/en/mysqli.commit.php#89976, calling $conn->commit() will NOT automatically set autocommit() back to 'true'. That means that any queries following $conn->commit() will be rolled back when your script exits, if autocommit() will be not switched back to TRUE.

WARNING: some MySQL statements cause an implicit commit, so the cannot be used inside a transaction. For example, you cannot rollback MySQL CREATE TABLE or TRUNCATE TABLE inside a transcaction. A useful comparison is available here.

Quoting and escaping strings

You have probably noticed that every string value is escaped before inserted to database as special characters may break SQL and, moreover, to prevent SQL injection.

1	$safe_string = $conn->real_escape_string($string);

Example: bla"bla\bla will be converted to bla\"bla\\bla.

real_escape_string does not add quotes, you have to do it manually.

However, these are not required, if you use Prepared statements (see below).

Prepared statements

What are Prepared Statements and why they are important?

Prepared Statement objects are used with an SQL statement which, typically but not necessary, takes parameters (using the symbol ? in our case or using other placeholders in different DBMS, e.g. $1, $2 etc in PostgreSQL).

After an SQL Statement has been prepared, the DBMS does not have to recompile it and prepare an execution plan. The Database engine simply runs (executes) the statement. This is can optimize performance. Performance advantage is remarkable when a single session is being used to execute a large number of similar statements.

These parameters inside a prepared statement don't need to be escaped and quoted. Driver takes care of this. So, using of Prepared Statements eliminates the possibility of SQL injection.

If you're not familiar with the use of Prepared Statements, you should do it, as it is very important for web applications security.

Connect to database as decribed above.

Select queries

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

$sql='SELECT lastname, email FROM customers WHERE id > ? AND firstname = ?'; $id_greater_than = 5; $firstname = 'John';   /* Prepare statement */ $stmt = $conn->prepare($sql); if($stmt === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); }   /* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */ $stmt->bind_param('is',$id_greater_than,$firstname);   /* Execute statement */ $stmt->execute();

Iterate over results

1 2 3 4

$stmt->bind_result($lastname, $email); while ($stmt->fetch()) {   echo $lastname . ', ' . $email . '<br>'; }

Store all values to array

1 2	$rs=$stmt->get_result(); $arr = $rs->fetch_all(MYSQLI_ASSOC);

WARNING: get_result is available only with MySQL Native Driver.

Close statement

1	$stmt->close();

Insert queries

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

$sql='INSERT INTO customers (firstname, lastname) VALUES (?,?)'; $firstname = 'John'; $lastname = 'Doe';   /* Prepare statement */ $stmt = $conn->prepare($sql); if($stmt === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); }   /* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */ $stmt->bind_param('ss',$firstname,$lastname);   /* Execute statement */ $stmt->execute();   echo $stmt->insert_id; echo $stmt->affected_rows;   $stmt->close();

Update queries

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

$sql='UPDATE customers SET firstname = ?, lastname = ? WHERE id > ?'; $firstname = 'John'; $lastname = 'Doe'; $id_greater_than = 5;   /* Prepare statement */ $stmt = $conn->prepare($sql); if($stmt === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); }   /* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */ $stmt->bind_param('ssi',$firstname,$lastname,$id_greater_than);   /* Execute statement */ $stmt->execute();   echo $stmt->affected_rows;   $stmt->close();

Delete queries

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

$sql='DELETE FROM customers WHERE id > ?'; $id_greater_than = 5;   /* Prepare statement */ $stmt = $conn->prepare($sql); if($stmt === false) {   trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); }   /* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */ $stmt->bind_param('i',$id_greater_than);   /* Execute statement */ $stmt->execute();   echo $stmt->affected_rows;   $stmt->close();

Disconnect

Optional:

1	$conn->close();