PHP, SQL & MySQL howto

Databaser / PHP, SQL & MySQL howto

Note: Unless explicity mentioned the method is to be performed using PHP, the command is to be run at the MySQL prompt.

Working with databases

Show databases:

show databases;

Create database (database name: test2):

create database test2;

Select database:

use test2;

Connect to MySQL server and database using PHP:
To connect and login using MySQL server user root with no password set:

<?php
mysql_connect('localhost', 'root', NULL) or die(mysql_error());
echo 'Connected to MySQL<br/>';
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database<br/>';

mysql_close();
?>

or

<?php
mysql_connect('localhost', 'root', '') or die(mysql_error());
echo 'Connected to MySQL<br/>';
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database<br/>';

mysql_close();
?>

The syntax of the mysql_connect string is: mysql_connect('name_of_computer_on_which_mysql_server_is_running', 'username_in_MySQL_server', 'password_for_MySQL_server');

localhost = Name of computer on which MySQL server is running.
root = Username in MySQL server.
NULL = User root's password for MySQL server (in this case no password is set for user root).

In the first mysql_connect example we tell PHP that user root has no password by usint the term NULL.
In the second mysql_connect example we tell PHP that user root has no password by using two single quotes with no space between.

Note: if you use single quotes to specify that root has no password, all mysql_connect lines must be changed accordingly. Running a MySQL server with no password set for root is not a good practice though, when it comes to security. So;

Set a password for root:

set password for root@localhost = password('password');

Create table (table name: companies) in database test2 using PHP:

<?php
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>';
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database<br/>';
// Create a MySQL table in the selected database
mysql_query('CREATE TABLE companies(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(50),
website VARCHAR(100),
address_visit VARCHAR(100),
address_postal VARCHAR(100),
phone_number VARCHAR(50),
mobile_phone_number VARCHAR(50),
email_address VARCHAR(100),
keywords VARCHAR(400))')
or die(mysql_error());

echo 'Table Created!';

mysql_close();
?>

Show tables in database test2:

show tables from test2;

Insert rows of information into the table "companies" using PHP:

<?php
// Insert rows of information into the table "companies"
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database<br/>'."\n";
mysql_query('INSERT INTO companies (name, website, keywords) VALUES("Fruit Inc", "http://www.fruit.com/", "apples")')
or die(mysql_error());

mysql_query('INSERT INTO companies (name, website, keywords) VALUES("Bananacom", "http://www.banana.com", "bananas")')
or die(mysql_error());

echo 'Data inserted';

mysql_close();
?>

Retrieve all the data from the table "companies" and print out the result using PHP:

<?php
// Retrieve all the data from the table "companies"
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database'."\n"."\n";
$result = mysql_query('SELECT * FROM companies')
or die(mysql_error());

// Get number of rows in table.
$num = mysql_num_rows($result);

mysql_close();

// Loop through and display the contents in the rows.
$i=0;
while($i < $num){
$name=mysql_result($result,$i,"name");
$website=mysql_result($result,$i,"website");
$keywords=mysql_result($result,$i,"keywords");

echo '<p>'."\n";
echo $name.'<br/>'."\n";
echo $website.'<br/>'."\n";
echo $keywords."\n";
echo '</p>'."\n"."\n";
$i++;
}
?>

Create user

Manually:

grant usage on *.* to admin@localhost identified by '1admin';

or by using PHP:

<?php
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>';

mysql_query("grant usage on *.* to admin@localhost identified by '1admin'") or die(mysql_error());

echo 'User "admin" created.';

mysql_close();
?>

localhost = Name of computer on which MySQL server is running.
admin = Username in MySQL server.
1admin = User admin's password for MySQL server.

Cleaning up

Delete user:

drop user admin@localhost;

Delete data in table:

use test2;
delete table companies;

or

use test2;
truncate table companies;

Delete table:

use test2;
drop table companies;

Delete database:

drop database test2;

Various operations

Rename table:

rename table tbl_name to new_tbl_name;

Back up database

Backing Up and Restoring Your MySQL Database

mysqldump -u username -p password  database_to_backup > backupfile.sql

username = Username in MySQL server.
password = User's password for MySQL server
database_to_backup = The name of the database
backupfile.sql = The file to which the backup should be written.

Restore database backup:

Note: Before trying to restore at database from a .sql backup file, the database must first be created inside MySQL server.

mysql -u username -p password  database_to_restore < backupfile.sql

username = Username in MySQL server.
password = User's password for MySQL server
database_to_restore = The name of the database
backupfile.sql = The backup file that should be restored.

Create user

<?php
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";

mysql_query("GRANT USAGE ON *.* TO admin@localhost IDENTIFIED BY '1admin'")
or die(mysql_error());

echo 'User "admin" created.';

mysql_close();
?>

How-To create a MySQL database and set privileges to a user
Adding new users to MySQL Databases

#
# Now we create the user paul and give him full 
# permissions on the new database
mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost;
Query OK, 0 rows affected (0.00 sec)

#
# Next we set a password for this new user
#
mysql> set password for paul = password('mysecretpassword');
Query OK, 0 rows affected (0.00 sec)

#
# Cleanup and ext
mysql> flush privileges;

Delete user:

MySQL: Check if the user exists and drop it

drop user user_name@localhost;

Create table

<?php
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database<br/>."\n"';
// Create a MySQL table in the selected database
mysql_query('CREATE TABLE companies(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(50),
website VARCHAR(100),
address_visit VARCHAR(100),
address_postal VARCHAR(100),
phone_number VARCHAR(50),
mobile_phone_number VARCHAR(50),
email_address VARCHAR(100),
keywords VARCHAR(400))')
or die(mysql_error());

echo 'Table Created.';

mysql_close();
?>

Add data to table

<?php
// Insert rows of information into the table "companies"
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database<br/>'."\n";
mysql_query('INSERT INTO companies (name, website, keywords) VALUES("Fruit Inc", "http://www.fruit.com/", "apples")')
or die(mysql_error());

mysql_query('INSERT INTO companies (name, website, keywords) VALUES("Bananacom", "http://www.banana.com", "bananas")')
or die(mysql_error());

echo 'Data inserted.';

mysql_close();
?>

Display database data

<?php
// Retrieve all the data from the table "companies"
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database'."\n"."\n";
$result = mysql_query('SELECT * FROM companies')
or die(mysql_error());

// Get number of rows in table.
$num = mysql_num_rows($result);

mysql_close();

// Loop through and display the contents in the rows.
$i=0;
while($i < $num){
$id=mysql_result($result,$i,"id");
$name=mysql_result($result,$i,"name");
$website=mysql_result($result,$i,"website");
$keywords=mysql_result($result,$i,"keywords");

echo '<p>'."\n";
echo 'ID: '.$id.'<br/>'."\n";
echo $name.'<br/>'."\n";
echo $website.'<br/>'."\n";
echo $keywords."\n";
echo '</p>'."\n"."\n";
$i++;
}
?>

Search for keywords

<?php
$s_word = 'PEAR';

// Retrieve data from the table "companies"
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database'."\n"."\n";

$result = mysql_query('SELECT * FROM companies WHERE keywords LIKE "%'.$s_word.'%"')
//$result = mysql_query('SELECT * FROM companies')
or die(mysql_error());

// Get number of rows in table.
$num = mysql_num_rows($result);

mysql_close();

$i=0;
while($i < $num){
$id=mysql_result($result,$i,"id");
$name=mysql_result($result,$i,"name");
$website=mysql_result($result,$i,"website");
$keywords=mysql_result($result,$i,"keywords");

echo '<p>'."\n";
echo $num.'<br/>'."\n";
echo $name."\n";
echo '</p>'."\n";

$i++;
}
?>

Search for keywords (2)

<?php
$s_word = 'fruit';

// Retrieve data from the table "companies"
mysql_connect('localhost', 'root', 'password') or die(mysql_error());
echo 'Connected to MySQL<br/>'."\n";
mysql_select_db('test2') or die(mysql_error());
echo 'Connected to Database'."\n"."\n";

$result = mysql_query('SELECT * FROM companies')
//$result = mysql_query('SELECT * FROM companies')
or die(mysql_error());

// Get number of rows in table.
$num = mysql_num_rows($result);

mysql_close();

$i=0; while($i < $num){
$id=mysql_result($result,$i,"id");
$name=mysql_result($result,$i,"name");
$website=mysql_result($result,$i,"website");
$keywords=mysql_result($result,$i,"keywords");

$main_string=$name.$keywords;

if(stristr($main_string,$s_word)){
echo '<p>'."\n";
echo 'String contains '.$s_word.'<br/>'."\n";
echo $name.'<br/>'."\n";
echo '</p>'."\n";
} else {
echo '<p>'."\n";
echo '';
//echo 'String does not contains '.$s_word.'<br/>'."\n";
echo '</p>'."\n";
}

$i++;
}
?>

PHP pagination

How can I limit the number of rows returned by MySQL? (Pagination)
Optimized Pagination using MySQL
Create Awesome PHP/MYSQL Pagination
PHP Freaks - Basic Pagination
How do I order MySQL data?

When you query your MySQL database, you can sort the results by any field in an ascending or descending order by just adding 'ORDER BY' at the end of your query. You would use ORDER BY field_name ASC for an ascending sort, or ORDER BY field_name DESC for a descending sort.

session_is_registered
Alternative for deprecated session_is_registered
if ( isset( $_SESSION['user'] ) ){}

Internet links & references:

Getting Started with MySQL
MySQL - Adding User Accounts
MySQL - GRANT Syntax
MySQL - DELETE Syntax
Delete All Data in a MySQL Table
MySQL - DROP USER Syntax
PHP/MySQL Tutorial - Displaying Data