Connect mysql database using php

                        You must connect mysql database to insert and retrieve add from it. In php, you can connect mysql server easily. It needs only two steps.
               
              1. Open connection to mysql server.
              2. Select database to add and retrieve data from mysql.

Open connection to mysql:

                         You must open connection to mysql server to connect database. 

mysql_connect():

                        The mysql_connect() function is used to open connection on mysql. The syntax for open connection in mysql as follows as:

<?php

     mysql_connect('localhost','root','');

?>

             where,

                         'localhost' is the server name.

                         root is the user name of mysql server. The 'root' is default username in mysql.

                         '' is the password of mysql server. The empty is default password in mysql.

Select database in mysql using php:

                          After opened connection to mysql database, you've to select database to add and retrieve data from it.

mysql_select_db():

                          It is used to select the database in mysql server. The syntax for select database in mysql as follows:

<?php

       mysql_select_db('new');

?>

             where,

                    'new' is the database name in mysql.

Connect mysql database in php:

                    Now combined the above two codes to one for connect mysql database using php. The php code as follows as:

<?php

   mysql_connect('localhost','root','') or die('Cannot connect mysql server');

   mysql_select_db('new') or die('cannot connect database');

?>

                  Now you can check whether the connection is opened or not to mysql. You got error message if either mysql server is not connect or database is not selected.

Related Post:

How to create mysql database using php
How to create mysql table using php

Create mysql database using php

                       All of you know, create mysql database in phpmyadmin. Now we are going to know how to create database using php.

The mysql query is:
            CREATE DATABASE db_name

 Then you use the particular database. The mysql query is:
                      USE db_name

The php coding as follows as:

<?php
mysql_connect('localhost','root','');
$query=mysql_query('create database new') or (mysql_error());
$qu=mysql_query('use new')  or (mysql_error());
if($query)
 {
   echo'Database is created';
 }
  else
 {
   echo'Database is not created';
  }
?>

Where,
           - mysql_connect() is used to connect the database.
           - 'localhost' is server name.
           - 'root' is user name of database.
           - '' represent the password of database.
           - mysql_query() is used for execute the mysql query.
           - 'new' is the database new.
           - mysql_error() give error message while the mysql query is execute.
           - die() terminate the excution.

       Now you'll get output as " Database is created ". Then you can see the new database in your phpmyadmin.

      The database 'new' is created and ready for use.

Related Post:

How to connect mysql database using php
How to create mysql table using php

Reset auto increment initial value in mysql

                     Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key. By default auto_increment is start with 1. Suppose you want to auto_increment starts with 'N' values. Then you can change the auto_increment initial value in mysql. IT is possible. First you create table with auto_increment. Then use following mysql query :

                   Alter table table_name AUTO_INCREMENT=N

Where, N can be any numbers like 100,1000,10000...

Create table with auto_increment in mysql:

                      You can add auto_increment while creating new table in mysql. The mysql query is:

                       CREATE TABLE table1 

                               (No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 

                                 Game_name varchar(15), Image varchar(15)) 

Now the table is created with auto increment. If you add any values, then auto_increment starts with 1 in mysql. Consider following example. You add 3 values to mysql table. ie,

                   insert into table1 values('','sample1','sample1.jpg')
                   insert into table1 values('','sample3','sample2.jpg')
                   insert into table1 values('','sample3','sample3.jpg')

Now your table look like this:

Change auto_increment initial value in mysql:    

                 
                                 You change auto_increment initial value to 100. The mysql query is:

                   Alter table table1 AUTO_INCREMENT=100

Now you insert data to your mysql table. Then it look like this:

Now you can see the row starts with 100. You can changed starting value of auto_increment in mysql


Related Post:

distinct in mysql
Order by in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
Primary key in mysql

Primary key in mysql

                         The primary key in mysql is used to identify the unique values in table. It don't allow duplicate entries in mysql table. The primary key column should be NOT NULL. It don't allow null values in mysql table. The mysql table can have only one primary key.

How to create primary key in mysql:

                         The mysql query for  create primary key is:

         Create table table_name (column_name datatype(length), primary key(column_name))

For example,
                        create table table1(name varchar(30), address varchar(100), primary key(name))

Where the column 'name' is primary key.

How to add primary key to existing table in mysql:

                       You created table without primary like this:

                

                   create table table1(name varchar(30),address varchar(100))

                     But you need to add primary key in order to remove duplicate entries in mysql. At the time you can add primary key to existing table in mysql. Following mysq query is used for add primary key.

                 Alter table table_name add primary key(column_name)

For example,

                 Alter table table1 add primary key(num)   

How to remove primary key in mysql:

                You created table with primary. But you need not primary key for particular table. Then how can remove primary key from table in mysql. The following mysql query is used to drop the primary key from table in mysql.

                    alter table table_name drop primary key

For example,

               

                      alter table table1 drop primary key 

          where, 

                       The primary key is removed from table 'table1'. Now you can add duplicate values to your mysql table.

Related Post:

distinct in mysql
Order by in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
How to reset auto increment initial value in mysql

Remove auto_increment from column in mysql

                       You created a table in which the column with specific definitions in mysql. You created table column with auto increment. To add auto increment to existing column, visit  add-auto-increment-to-existing-column in mysql. Then how can you remove it from column in mysql.  The following mysql query is used to remove auto increment from column in mysql.

Mysql Query:

                         Alter table table_name DROP PRIMARY KEY,
                          change column_name column_name datatype(length) definition

Consider the example:
The structure of table1 in database new look like this:

                        
                       Now we are going to remove auto increment from column 'No' in mysql table 'table1'.  The mysql query is:

                     Alter table table1 DROP PRIMARY KEY,
                      change No No int(5) NOT NULL

Now your structure look like this:

 
               Now you can see the column name 'No' is normal. There is no auto increment.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to reset auto increment initial value in mysql
How to use order by before group by in mysql
Select distinct values in mysql

Select distinct values in mysql

                       If your column in mysql table contains duplicate values, then you need to display distinct values by mysql command 'DISTINCT' and 'GROUP BY'.

Select unique values in Mysql by distinct:

                       The mysql command 'DISTINCT' is used to select distinct values. The mysql query for select distinct values as follows as:

                 SELECT DISTINCT column_name FROM table_name

Consider the following table. table1 is name of this table.

                              Now we are going to get unique values in column 'id' by 'DISTINCT' mysql command like this:

                      SELECT DISTINCT id FROM table1

Now you'll get output like this:

                    You can see the table 'table1' which have unique values.

Select unique values in Mysql by 'Group by':

                   
                              You can also display unique values using 'GROUP BY' mysql command. If you want to select distinct values from one column and also other values from table, then you can use mysql command 'GROUP BY'.  The mysql query is:

                       SELECT * FROM table_name GROUP BY column_name 

 Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

                       SELECT * FROM table1 GROUP BY id 

Now you'll get output like this:

                           You can see the column 'id' values in mysql table 'table1' are unique.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to use order by before group by in mysql

How to remove auto increment from column in mysql
How to reset auto increment initial value in mysql