MySQLi and PHP - Procedural Style

mysqli_connect( $host, $username, $passwd, $dbname, $port, $socket ) Open a new connection to the MySQL server
mysqli_select_db( $mysqli-link , $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( $mysqli-link ) Returns a string description of the last error
mysqli_query( $mysqli-link , $query ) Performs a query on the database
mysqli_affected_rows( $mysqli-link ) Gets the number of affected rows in a previous MySQL operation
mysqli_num_rows( $mysqli-result ) Gets the number of rows in a result
mysqli_fetch_array( $mysqli-result, $resulttype) (resulttype: MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.) Fetch a result row as an associative, a numeric array, or both
mysqli_fetch_assoc() Fetch a result row as an associative array
mysqli_free_result( $mysqli-result ) Frees the memory associated with a result
mysqli_insert_id( $mysqli-link ) Returns the auto generated id used in the latest query
mysqli_close( $mysqli-link ) 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');

Procedural Style usage ... connect to database

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

    if (mysqli_connect_errno()) {
        print('Connect failed: '. mysqli_connect_error());
      exit;
    }
echo 'Connected successfully';

mysqli_close($conn);

Create a Database CREATE DATABASE DatabaseName

@$conn = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD);

    if (mysqli_connect_errno()) {
        print('Connect failed: '. mysqli_connect_error());
      exit;
    }
echo 'Connected successfully';

$sql="CREATE DATABASE my_db DEFAULT CHARACTER SET utf8";
if (mysqli_query($conn,$sql)) {
    echo "Database created successfully\n";
} else {
    echo 'Error creating database: ' . mysql_error() . "\n";
}

Create a Table CREATE TABLE TableName

@$conn = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD);

mysqli_select_db($conn, 'my_db');

$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 (mysqli_query($conn,$sql)) {
     echo "Table category created successfully";
  }  else {
     echo "Error creating table: " . mysqli_error($conn);
  }

Insert data into a MySQL table INSERT INTO

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

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

$result = mysqli_query($conn,$sql);
if ($result) {
  echo mysqli_affected_rows($conn). " user inserted into database.";
  echo " The user ID :" . mysqli_insert_id($conn);
  //header("Location: /goto.php"); //Redirect page
} else {
  //already exists
 echo 'Error: '.  mysqli_error($conn);
 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_fetch_array()

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

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

$result = mysqli_query($conn,$sql);

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

mysqli_fetch_assoc()

@$conn = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
 
$sql = "SELECT id, username, password, email FROM users";

$result = mysqli_query($conn,$sql);

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

SELECT Records found & No records found

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

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

$result = mysqli_query($conn,$sql);

$row_cnt = mysqli_num_rows($result);

if (mysqli_num_rows($result) > 0) {
   echo "YES we found $row_cnt records";
} else {
   echo "No records found";
}

PHP MySql Update UPDATE

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

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

$result = mysqli_query($conn,$sql);
if (mysqli_affected_rows($conn)) {
  echo mysqli_affected_rows($conn). " 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 = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

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

$result = mysqli_query($conn,$sql);

if (mysqli_affected_rows($conn)) {
    echo 'user removed';
} else {
   echo 'user is not removed';
}

Delete table DROP TABLE

mysqli_query($conn, "DROP TABLE users");

Alter table ADD

mysqli_query($conn, "ALTER TABLE users ADD country varchar(40) DEFAULT ");

Alter table ADD UNIQUE

ALTER IGNORE TABLE TableName ADD UNIQUE (FieldName);

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

mysqli_free_result($result);

Then on end close MySql connection

mysqli_close($conn);

Links:

PHP MySQL Improved Extension
MySQL and PHP - Object oriented style
PHP MySql Prepared statements
MySQL Data Types
MySQL Operators
PHP MySQL Improved Extension - php.net
mysqli_connect(), mysqli_select_db(), mysqli_connect_errno(), mysqli_connect_error(),
mysqli_query(), mysqli_affected_rows(), mysqli_fetch_array(), mysqli_fetch_assoc(),
mysqli_free_result(), mysqli_insert_id(), mysqli_close() php.net

^ Top

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