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:

Create database (database name: test2):

Select database:

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

or

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:

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

Show tables in database test2:

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

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

Create user

Manually:

or by using PHP:

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:

Delete data in table:

or

Delete table:

Delete database:

Various operations

Rename table:

Back up database

mysqldump -u username -p database_to_backup > backupfile.sql

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

The mysqld command above will ask you for the selected user's password for MySQL server.

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 database_to_restore < backupfile.sql

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

The mysql command above will ask you for the selected user's password for MySQL server.

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.

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

To top