MySQLi and PHP - Object oriented style

mysqli::__construct( $host, $username, $passwd, $dbname, $port, $socket ) Open a new connection to the MySQL server
mysqli::select_db( $dbname ) Selects the default database for database queries
mysqli::$connect_errno Returns the error code from last connect call
mysqli::$connect_error Returns a string description of the last connect error
mysqli::$error Returns a string description of the last error
mysqli::query( $query, $resultmode ) (resultmode: MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT) Performs a query on the database
mysqli::$affected_rows Gets the number of affected rows in a previous MySQL operation
$mysqli_result::num_rows Gets the number of rows in a result
mysqli_result::fetch_array( $resulttype) (resulttype: MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.) Fetch a result row as an associative, a numeric array, or both
mysqli_result::fetch_assoc() Fetch a result row as an associative array
mysqli_result::free() Frees the memory associated with a result
mysqli::$insert_id Returns the auto generated id used in the latest query
mysqli::close() Closes a previously opened database connection

PHP Connect to the MySQL Server

// define our database connection
  define('DB_TYPE', 'mysql');
  define('DB_PREFIX', '');
  define('DB_CHARSET', 'utf8');
  define('DB_SERVER', 'localhost');
  define('DB_USERNAME', 'username');
  define('DB_PASSWORD', 'password');
  define('DB_DATABASE', 'mysql_database');

Object oriented style ... connect to database

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

      if ($conn->connect_errno) {
         die("Connection failed: " . $conn->connect_error);
      }

echo 'Connected successfully';
$conn->close();

Create a Database CREATE DATABASE DatabaseName

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

     if ($conn->connect_errno) {
         die("Connection failed: " . $conn->connect_error);
     }

$sql="CREATE DATABASE my_db2 DEFAULT CHARACTER SET utf8";

if ($conn->query($sql) === TRUE) {
  echo 'Database successfully created';
} else {
 echo 'Error: '. $conn->error;
}

Create a Table CREATE TABLE TableName

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD);

$conn->select_db('my_db2');

$sql = "CREATE TABLE users ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL,
 email VARCHAR(45), reg_date TIMESTAMP,UNIQUE KEY username (username),
UNIQUE KEY email (email) ) CHARACTER SET utf8";

if ($conn->query($sql) === TRUE) {
  echo 'Table successfully created';
} else {
 echo 'Error: '. $conn->error;
}

Insert data into a MySQL table INSERT INTO

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

$sql = "INSERT INTO users (username,password,email) VALUES ('user', 'password', 'user@example.com')";

$result = $conn->query($sql);
if ($result){
  echo $conn->affected_rows. " user inserted into database.";
  echo " The user ID :" . $conn->insert_id;
  //header("Location: /goto.php"); //Redirect page
} else {
  //already exists
 echo 'Error: '. $conn->error;
 echo " The user was not added.";
}

PHP MySql Select SELECT

The the most common operators in MySQL WHERE expressions:

Operator	Description	Example
=	Equals	WHERE id=5
<	Less than	WHERE id<5
>	Greater than	WHERE id>5
<=	Less than or equal to	WHERE id<=5
>=	Greater than or equal to	WHERE id>=5
!= (also <>)	Not equal to	WHERE id!=5
IS NOT NULL	Has a value	WHERE id IS NOT NULL
IS NULL	Does not have a value	WHERE email IS NULL
BETWEEN min AND max	Within a specific range (min and max)	WHERE id BETWEEN 2 and 4
NOT BETWEEN min AND max	Not within a specific range (min and max)	WHERE id NOT BETWEEN 2 and 4
IN(val1, val2, val3)	Found within a list of values	WHERE id IN(1, 3, 4)
OR	Where one of two conditionals is true	WHERE id>5 OR name='user'
AND	Where both conditionals are true	WHERE id>5 AND name='user'

SELECT ALL COLUMNS FROM table_name. To sellect all columns use asterisk (*)

SELECT * FROM table_name

LIMIT With the LIMIT option you can determine the number of records that MySQL will return.

SELECT * FROM table_name ORDER BY Name LIMIT 1

SELECT column_names FROM table_name LIMIT skip_count, show_count
- skip_count : is optional, it tells the database how many rows to skip from results.
- show_count : sets the maximum number of records to return.

ORDER BY

SELECT * FROM table_name ORDER BY column_name ASC|DESC

LIKE | NOT LIKE
% - matches any sequence of characters or none
_ - (an underscore), matches exactly one character

sql = "SELECT * FROM table_name WHERE username LIKE 'usr%'";

This WHERE clause matches all words with begin letters usr

sql = "SELECT * FROM table_name WHERE username NOT LIKE '%usr%'";

This WHERE clause matches all words without letters , usr

sql = "SELECT * FROM table_name WHERE username LIKE 'usr_'";

This WHERE clause will return all rows where username begins with usr_ and has 4 characters

mysqli_result::fetch_array()

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

$sql = "SELECT id, username, password, email FROM users";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_array()) {
    echo '<br /> id: '. $row['id']. ' - name: '. $row['username']. ' - pass: '. $row['password']. ' - email: '. $row['email'];
  }
} else {
   echo 'No users in your database';
}

mysqli_result::fetch_assoc()

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

$sql = "SELECT id, username, password, email FROM users";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo '<br /> id: '. $row['id']. ' - name: '. $row['username']. ' - pass: '. $row['password']. ' - email: '. $row['email'];
  }
} else {
   echo 'No users in your database';
}

SELECT Records found & No records found

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

$sql = "SELECT id, username, password, email FROM users";

$result = $conn->query($sql);

/* determine number of rows result set */
$row_cnt = $result->num_rows;
     
if($result->num_rows>0) {
   echo "YES we found $row_cnt records";
} else {
   echo "No records found";
}

PHP MySql Update UPDATE

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

$sql = "UPDATE users SET email='newUsersEmail@example.com' WHERE username='user' AND id='1' LIMIT 1";

$result = $conn->query($sql);
if ($conn->affected_rows){
  echo $conn->affected_rows. " row updated.";
  //header("Location: /goto.php"); //Redirect page
} else {
  //already exists
 echo 'Error: ';
 echo " The user data was not updated.";
}

PHP MySql Delete DELETE

@$conn = new mysqli(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

$sql = "DELETE FROM users WHERE username='user' AND id='1'";

$result = $conn->query($sql);

if ($conn->affected_rows){
    echo 'user removed';
} else {
  echo 'user is not removed';
}

Delete table DROP TABLE

$conn->query("DROP TABLE users");

Alter table ADD

$conn->query("ALTER TABLE users ADD country varchar(40) DEFAULT ");

Alter table ADD UNIQUE ALTER IGNORE TABLE TableName ADD UNIQUE (FieldName);

$conn->query("ALTER TABLE users ADD UNIQUE email varchar(40) DEFAULT ''");

ALTER IGNORE = Duplicate records will be deleted. First row is used of rows with duplicates, the other duplicate rows are deleted.

Alter table RENAME

ALTER TABLE Old_TableName RENAME TO New_TableName;

RENAME TABLE  `Old_TableName` TO  `New_TableName`;

Alter table MODIFY

ALTER TABLE  clients MODIFY COLUMN id int(11) auto_increment PRIMARY KEY;

Alter table ADD AFTER
ALTER TABLE TableName ADD userid int(11) AFTER unixtime;

MySql Free memory

$result->free();

Then on end close MySql connection

$conn->close();

Links:

PHP MySQL Improved Extension
MySQLi and PHP - Procedural Style
PHP MySql Prepared statement
MySQL Operators
MySQL Data Types
mysqli::__construct(), mysqli::select_db(), mysqli::$connect_errno, mysqli::$connect_error, mysqli::$error, mysqli::query(), mysqli::$affected_rows, $mysqli_result::num_rows, mysqli_result::fetch_array(), mysqli_result::fetch_assoc(), mysqli_result::free(), mysqli::$insert_id, mysqli::close() php.net

^ Top

Previous page: Convert a MySQL database to UTF-8
Next page: Format Factory