MySQL Cheat Sheet in PHP

Introduction

In this article I explain the MySQL cheat sheet in PHP. I will use common MySQL commands during this article to assist you to apply with MySQL and effectively. Here, first is a discussion of working with a database. Using the following queries, you can approriately work on MySQL. The following queries describe that.

Working with Database

When a database does not exist the database server creates a new database. Such as:

<?php

$con = mysql_connect ("localhost","root","");

$db="Vinod";

if (!$con)

  {

  die ('Could not connect: ' . mysql_error());

  }

if (mysql_query ("CREATE DATABASE If Not exists $db",$con))

  {

  echo "Your Database Created Which Name is : $db";

  }

else

  {

  echo "Error creating database: " . mysql_error();

  }

mysql_close ($con);

?>

To drop the database use this query:

<?php

$con = mysql_connect ("localhost","root","");

$db="Vinod";

if (!$con)

  {

  die ('Could not connect: ' . mysql_error());

  }

if (mysql_query ("DROP DATABASE IF EXISTS $db",$con))

  {

  echo "Your Database removed Which Name is : $db";

  }

else

  {

  echo "Your database $db not extst: " . mysql_error();

  }

mysql_close ($con);

?>

To show all the databases in your database server use:

da1.jpg

Working with table

To list all tables in your database, you can use this query:

Create table in your database.

da2.jpg

CREATE table IF NOT EXISTS table_name(create_clause) [table_options]

[[IGNORE|REPLACE] select];

 

Delete table permanently.

DROP table IF EXISTS table_name [ name,]

[RESTRICT | CASCADE];

 

Get information about table or column.

 

DESCRIBE table [column_name]

desc table [column_name];

 

Working with Index

 

Create an index on your table.

 

CREATE [UNIQUE|FULLTEXT] INDEX index_name

ON table (colu_name,);

 

Remove index from table.

 

DROP INDEX index_name;

 

Retrieving data from table

Retrieving all data from your table.

SELECT * FROM table_name;

 

Retrieve specific column data from your database table.

 

SELECT column_name, column_name2….

FROM table_name;

 

Retrieve unique record from your database.

SELECT DISTINCT (column_name)

FROM table_name;
 

Retrieve data from multiple table then using join.

SELECT * FROM tab_name1

INNER JOIN tab_name2 ON conditions;

 

SELECT * FROM tab_name1

LEFT JOIN tab_name2 ON conditions;

 

SELECT * FROM tab_name1;
 

Counting number of rows from your database tables.

SELECT COUNT (*) FROM table_name;


Sorting data ascending or descending.

 

SELECT column_name1, column_name2

FROM table_name

ORDER BY column_name1 ASC [DESC], column_name2 ASC [DESC];
 

Retrieve data from your table in group.

SELECT * FROM table_name

GROUP BY column_name;


Similar Articles