PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: PHP
PHP - phponwebsites.com
Showing posts with label PHP. Show all posts

17 Apr 2014

Pagination using php and mysql

                       Pagination in php is very simple concepts. You can learn easily. Just follow the below steps.

      1. Find the Start values based on page number:


 $limit=10;
 $page=$_GET['p'];
 if($page=='')
 {
  $page=1;
  $start=0;
 }
 else
 {
  $start=$limit*($page-1);
 }

     Where,
                  $limit is the number rows per page.
                  $page is page number.
                  $start is starting point of limit in mysql query.
If the page number is 1, then the start is 0 which is find by $start=$limit*($page-1).
                  $start=10*(1-1)
                  $start=10*(0)
                  $start=0
                 
If the page number is 2, then the start is 10.
Similarly, if the page number is 3, then the start is 20.

                    2. Find total number of records in mysql table:

Then we need to calculate the total number of data in table. Then find the maximum pages using php script like below:

$tot=mysql_query("SELECT * FROM table1")  or die(mysql_error());
$total=mysql_num_rows($tot);
$num_page=ceil($total/$limit);


     Where,
                mysql_num_rows() returns the numbers results in numeric.
                ceil() returns the whole digit number. ie, ceil(2.3) => 3.
                $maxpage returns the number of pages.


                     3. Function for pagination:

        The php script for pagination function is:

function pagination($page,$num_page)
{
  echo'<ul style="list-style-type:none;">';
  for($i=1;$i<=$num_page;$i++)
  {
     if($i==$page)
{
 echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
 echo'<li style="float:left;padding:5px;"><a href="pagination.php?p='.$i.'">'.$i.'</a></li>';
}
  }
  echo'</ul>';
}     

           Where,
                        You need to use for loop for display number of pages in mysql table.


                       4.The whole php code for pagination as follows as:

<?php
error_reporting('E_ALL ^ E_NOTICE');
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$page=$_REQUEST['p'];
$limit=10;
if($page=='')
{
 $page=1;
 $start=0;
}
else
{
 $start=$limit*($page-1);
}
$query=mysql_query("select * from table1 limit $start, $limit") or die(mysql_error());
$tot=mysql_query("select * from table1") or die(mysql_error());
$total=mysql_num_rows($tot);
$num_page=ceil($total/$limit);
echo'<table><th>Reg.Id</th><th>Name</th><th>Category</th>';
while($res=mysql_fetch_array($query))
{
  echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['game_Title'].'</td><td>'.$res['category_Name'].'</td></tr>';
}
echo'</table>';
function pagination($page,$num_page)
{
  echo'<ul style="list-style-type:none;">';
  for($i=1;$i<=$num_page;$i++)
  {
     if($i==$page)
{
 echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
 echo'<li style="float:left;padding:5px;"><a href="pagination.php?p='.$i.'">'.$i.'</a></li>';
}
  }
  echo'</ul>';
}
if($num_page>1)
{
 pagination($page,$num_page);
}
?> 


Now you'll get output like this:


simple pagination using php


                        Now you can paginated to next page.

Related Post:

13 Apr 2014

Display results in html table using php and mysql

                       Normally, you displayed data results without order in php. If you want to display single data, then you can easily display it using ' <?php echo $name; ?> '. But if you have to display large amount of data from mysql table, then you should be display it properly. So only, you can understand the displayed results. So the best option for display data is table. You can display results orderly in table using php. The following mysql query is used to select the values from mysql table.

                        SELECT * FROM table_name


Display results in table using php and mysql:

                       

                           You can retrieve data from mysql table using php. The php script for display data in table as follows as:


<html>
<body>
<style type="text/css">
th,td{
border-width:0px 1px 1px 0px;
}
</style>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query("select * from table1 limit 0,10")  or die(mysql_error());
echo'<table border="1" ><th >Reg.Id</th><th>Name</th><th>Category</th>';
while($res=mysql_fetch_array($query))
{
  echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['game_Title'].'</td><td>'.$res['category_Name'].'</td></tr>';
}
echo'</table>';
?>
</body>
</html>


Now you'll get output like this:


Display data in table using php

                         Now you can get your results in table.

9 Apr 2014

Get database list in mysql using php

                       You can retrieve databases in your mysql server using php. On mysql server, you can use mysql query in order to get databases in mysql.

Get database list using mysql query:

                                   The following mysql query is used to display database list.

             SHOW DATABASES

It displays database list in mysql as like below:

show databases in mysql using php


Get database list in mysql using php:


                         You can display database list in mysql using php in two ways.
           1.  Display database list using php and mysql query
           2.  Display database list using php and mysql_list_tables()

Display database list using php and mysql:


                                      Now you can find the database list in mysql server using php scripts:


<?php
$link=mysql_connect('localhost','root','')  or die(mysql_error());
$result = mysql_query("SHOW DATABASES")  or die(mysql_error());     
while ($row = mysql_fetch_array($result)) {      
    echo $row[0]."<br>";      
}
?>

                                                                 OR


Display databases in mysql using php and mysql_list_dbs():


                           The php code for display database list:


<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
$db_list = mysql_list_dbs($link);
while ($row = mysql_fetch_object($db_list))
{
     echo $row->Database . "<br>";
}

?>

Now you'll get output like below:

display databases available in mysql using php

Related Post:

7 Apr 2014

Display tables list in mysql database using php

                      You can display the tables in a mysql database two ways using mysql query.
     1. First you select your mysql database. Then use below mysql query:

                    SHOW TABLES

    2.You can get your tables in mysql database without select database using following mysql query.

                   SHOW TABLES FROM db_name

ex: SHOW TABLES FROM new

Both two ways, you will get output like this:

show tables using mysql_list_tables mysql query

Display table list in database using php:


                            You can display list of tables in database using php in two ways.
           1.  Display table list in database using php and mysql
           2.  Display tables in database using php and mysql_list_tables()

Display table list in database using php and mysql:


                              You can get list of tables in database using mysql query and php. The following php code is used for display list of tables.

<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$result=mysql_query("show tables ")  or die(mysql_error());
while ($row = mysql_fetch_row($result)) {
    echo " {$row[0]}<br>";
}
?>

                                        OR

Display tables in mysql database using php and mysql_list_tables():

                             You can also get list of tables in database using mysql_list_tables function in php. The following php code is used for display list of tables.

<?php
error_reporting('E_ALL ^ E_NOTICE');
mysql_connect('localhost','root','') or die(mysql_error());
$rs=mysql_list_tables('new') ;
while($res=mysql_fetch_row($rs))
{
echo $res[0].'<br>';
}
?>

            Now you'll get output like this:

show tables in mysql database using php

Related Post:

2 Apr 2014

Retrieve data from mysql table using php

                       We are going to see how can fetch the result from mysql table using php.
             1. First you need to connect your Mysql database using php script like below:

mysql_connect('server_name','username','password');
mysql_select_db('db_name');

where,
           - server_name means localhost,
           - username is username of your database
           - password is password of your database
           - db_name is name of your database

          2. Then select the values which is present is table using mysql query.
              " SELECT * FROM table_name "
Use above query, if you select all values in table. Otherwise you need to use 'WHERE' clause to retrieve desired data from table.

          3. Then you have to use MYSQL function to retrieve data from table such as mysql_fetch_array(), mysql_fetch_row(), and mysql_assoc.
          where, 
                   mysql_fetch_array() returns row as numeric array, associative array or both.
                   mysql_fetch_row() returns numeric array corresponds to fetched row.
                   mysql_fetch_assoc() returns row as an associative array.

Consider following example:


                     Fetch result from mysql table using php at phponwebsites

Fetch results from mysql table using mysql_fetch_array() in php:


<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_array($query))
{
  echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

         Now you'll get output like this:


Fetch result from mysql table using mysql_fetch_array in php

Fetch result from mysql table using mysql_fetch_row() in php:


<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_row($query))
{
  echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res[2].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

           Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_row in php

       
      Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use associative index instead numerical, then you will get undefined index error like as follows as:

retrieve data from mysql table using mysql_fetch_row in php

Fetch results from mysql table using mysql_fetch_assoc() in php:


<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_assoc($query))
{
  echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

           Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_assoc in php

 Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use numeric index instead associative, then you will get undefined offset error like as follows as:

Retrieve data from mysql table using mysql_fetch_assoc in php


          Finally you can retrieve data from mysql table using php.

31 Mar 2014

mysql_fetch_array in php and mysql

                        Mysql_fetch_array() returns row as an associative, numeric array or both based on parameters passed in it. Three types of parameters can be passed in mysql_fetch_array function.
                 
  1. MYSQL_BOTH - return both associative and numeric array
  2. MYSQL_ASSOC - return associative array
  3. MYSQL_NUM - return numeric array

Retrieve data from table using mysql_fetch_array() in php:


Consider following example:

                     mysql_fetch_array in php and mysql at phponwebsites

Mysql_fetch_array() with MYSQL_BOTH in php:

                       It returns  result as both associative and numeric array. You can retrieve data using both associative and numeric indices in php.

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_array($query, MYSQL_BOTH))
{
  echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

         Now you'll get output like this:

 mysql_fetch_array with MYSQL_BOTH in php

Where,
          the array should be like this:

     Array(
                  [game_ID] => 1
                  [category_Name] => Adventures
                  [game_Title] => Achilles
               )
                        
                         OR

     Array(
                  [0] => 1
                  [1] => Adventures
                  [2] => Achilles
               )

mysql_fetch_array() with MYSQL_NUM in php:

                              It returns result as numeric array. ie, indices are numeric like 0,1,2...

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_array($query, MYSQL_NUM))
{
  echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res[2].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

           Now you'll get output like this:

mysql_fetch_array with MYSQL_NUM in php

In this type, the array of result should be like below:

     Array(
                  [0] => 1
                  [1] => Adventures
                  [2] => Achilles
               )


          Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use associative index instead numerical, then you will get undefined index error like as follows as:

mysql, php at phponwebsites

mysql_fetch_array() with MYSQL_ASSOC in php:

                             It returns result as an associative array.


<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_array($query,MYSQL_ASSOC))
 {
   echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';
  }
 echo'<table>';
 ?>
</body>
</html>

           Now you'll get output like this:

mysql_fetch_array with MYSQL_ASSOC in php

Where,
            the array should be like this:
     Array(
                  [game_ID] => 1
                  [category_Name] => Adventures
                  [game_Title] => Achilles
               )


 Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use numeric index instead associative, then you will get undefined offset error like as follows as:

mysql_fetch_assoc,php, mysql at phponwebsites


Note:
          mysql_fetch_array() without array_type means return row as an associative, numeric array or both

Related Post:

26 Mar 2014

mysql_fetch_row in php and mysql

                      Mysql_fetch_row()  return row as numeric array. We can't use associative indices to retrieve data from mysql table using php.

Consider following example:

                     mysql_fetch_row in php and mysql at phponwebsites

Retrieve data from table using mysql_fetch_row()  in php:

                       You can retrieve data from mysql table using  mysql_fetch_assoc() in php. The following php script is used for retrieve data.

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_row($query))
{
  echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res[2].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

           Now you'll get output like this:

mysql_fetch_row() in php

Where,
          the result of array should be like this:
             Array(
                         [0] => 1
                         [1] => Adventures
                         [2] => Achilles
                       )      

 Suppose you'll use following php script,
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use associative index instead numerical, then you will get undefined index error like as follows as:

mysql_fetch_row at phponwebsites
 Where,
              'game_Title' is the field name of column. You can't use $res['game_title'] here. Because you retrieved data from table using mysql_fetch_row().

Related Post:

24 Mar 2014

mysql_fetch_assoc in php and mysql

                       Mysql_fetch_assoc() return row as an associative array. Here, we can use numeric indices to get values from mysql data using php.

Consider following example:

                     mysql_fetch_assoc in php and mysql at phponwebsites

Retrieve data from table using mysql_fetch_assoc() in php:

                       You can retrieve data from mysql table using mysql_fetch_assoc() in php. The following php script is used for retrieve data.

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?
        php mysql_connect('localhost','root','') or die(mysql_error());
        mysql_select_db('new') or die(mysql_error());
        $query=mysql_query('select * from table1') or die(mysql_error());
        while($res=mysql_fetch_assoc($query))
         {
             echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';
         }
        echo'<table>';
 ?>
 </body>
</html>


           Now you'll get output like this:

mysql_fetch_assoc in php


Where,
            the result of array should be like this:
     Array(
                  [game_ID] => 1
                  [category_Name] => Adventures
                  [game_Title] => Achilles
               )

 Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use numeric index instead associative, then you will get undefined offset error like as follows as:

mysql_fetch_assoc at phponwebsites

Where,
              $res[0] represents the first column of mysql table. But you can't use this to retrieve data from table. Because you used mysql_fetch_assoc() mysql function to retrieve data.

Related Post:

19 Mar 2014

Avoid duplicate values storing in table using php and mysql

                       We can avoid duplicate values storing in table when we insert data to table. Because the duplicate entry is one of the main problem, while insert data into mysql table . We removed duplicate values using 'DISTINCT' key in mysql query, if it is present in table. We can check whether the value is present in table or not using PHP and Mysql before add data to table.

                        We can also avoid duplicate values storing in mysql table while inserting by primary key.

Consider following example. You've a database named with 'new' and table named with 'user'. You try to add names in user table. Now you've to check the values is present or not while you inserting. The table look like this:

Avoid duplicat values sotring in table using PHP at phponwebsites

Now you try to add name 'guru' again. You can check whether the name 'guru' is present in table or not using below PHP script.

<?php
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','');
mysql_select_db('new');
$name=$_POST['name'];
$query=mysql_query("select * from user where name='".$name."' ") or die(mysql_error());
$duplicate=mysql_num_rows($query);
   if($duplicate==0)
    {
      $query1=mysql_query("insert into user values('".$name."')")  or die(mysql_error());
    }
    else
    {
      echo'The name '.$name.' is already present in the user table';
    }
}
?>
<html>
<body>
<form method='post' action='#'>
name: <input type='text' name='name'>
<input type='submit' name='submit' value='Submit'>
</form>
</body>
</html>

                           
     Where, 
               mysql_query() - execute the query and select if any similar values are present in table.                                    mysql_num_rows() - returns numeric value of selected rows. If its value is 0, then there is no similar values present in table. Otherwise the value is already present in table.
     Where, 
               you can also use  mysql_fetch_row() instead of mysql_num_rows().                       
                        If the value is present in table, it could not be stored in table. Now you can avoid duplicate entries in table using PHP and Mysql.
                
Related Post:

How to store values into mysql table using php

13 Mar 2014

Insert data into mysql table using php

                      You can insert values to mysql table using php. The following mysql query is used for insert data into table.
                        INSERT INTO table_name VALUES(values datatype(length))


Store values into mysql table using php:

                    The following php script is used to store values into mysql table.

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query("insert into table1 values(1,'sample1', 'sample1.jpg')") or die(mysql_error());
if($query)
{
 echo 'Vaules are inserted into table';
}
else
{
 echo'Vaules are not inserted';
}
?>


Note:
          If you declared field name as varchar, then you must insert values between single quotation marks ( 'sample1').

                  Now the values are inserted into table. You can see it in your mysql table at phpmyadmin. Your mysql table look like this:

insert data into mysql tables in php

You inserted values to your table using php.

Get values from user and insert into mysql table using php:

                              Suppose you want to get values from user. First you have to know how to get values from text boxes.

How to get values from textbox using php:


$name=$_POST['name']; if(method is post)
        or
$name=$_GET['name']; if(method is get)
        or
$name=S_REQUEST['name']; if(method is either post or get)

where,
        S_POST[] is used to get values from textbox, If you use POST method.
        S_GET[] is used to get values from textbox, If you use GET method.
        S_REQUEST[] is used to get values from textbox, If you use either GET or POST method.

Then the following php code is used to insert values into table.

 <html>
 <body>
 <?php
      if(isset($_POST['submit']))
       {
            mysql_connect('localhost','root','') or die(mysql_error());
            mysql_select_db('new') or die(mysql_error());
            $id=$_POST['id'];
            $name=$_POST['name'];
            $img=$_POST['img'];
            if($id!='' && $name!='' && $img!='' )
            {
                $query=mysql_query("insert into table4 values('".$id."','".$name."', '".$img."')") or die(mysql_error());
                if($query)
                {
                   echo 'Vaules are inserted into table';
                } 
               else
               {
                   echo'Vaules are not inserted';
               }
            }
           else
           {
                echo'Insert all values';
           }
         }
?>
<form action="#" method='post'>
<table align='center' cellspacing='5'>
<tr><td>ID</td><td><input type='text' name='id'></td></tr>
<tr><td>Name</td><td><input type='text' name='name'></td></tr>
<tr><td>img</td><td><input type='text' name='img'></td></tr>
<tr><td></td><td><input type='submit' name='submit' value='submit'></td></tr>
</table>
 </form>
</body>
</html>

Where, 
           the values will get from users using post method like as below:
            $_POST['textbox_name']

Now you run this file. Your display look like this:

insert form values into mysql table

Then you can get values, if user type anything in textbox. Now i added values as like this:

Get values from user and stored into mysql table using php

Now you'll get output like this:

store values into mysql table using php

Your mysql table have user inserted values.

Related Post:

6 Mar 2014

Create mysql table using php

                        You can create mysql table using php. The following mysql query is used to create mysql table.
                    CREATE TABLE table_name(field_name datatype(length))

The php script as follows as:

<?php
mysql_connect('localhost','root','');
mysql_select_db('new');
$query=mysql_query("create table table1(id int(5),name varchar(40),img varchar(30))") or die(mysql_error());
if($query)
{
 echo 'Table is created';
}
else
{
 echo'Table 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_select_db() is used to select the database for use.
           - 'new' is the database new.
           - mysql_query() is used for execute the mysql query.
           - mysql_error() gives error message when the mysql query is not running successfully.

                    Now the mysql table 'table1' is created. You can see it in your mysql database at phpmyadmin.
Your mysql database should be like this:

Create mysql table using CREATE TABLE mysql command in php

Now you created your mysql table.


Related Post:

24 Feb 2014

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:

21 Feb 2014

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.

create database in mysql using php at phponwebsites

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

Related Post:

6 Feb 2014

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:



remove auto increment from column in mysql using alter, drop and change mysql command
                        
                       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:

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

Related Post:

3 Feb 2014

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.

select distinct values in mysql

                              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:

select unique values using distinct in mysql

                    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:

display unique values using group by in mysql


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

Related Post:

31 Jan 2014

Use order by before group by in mysql

                       Normally you can use order by before group by in mysql. Now the question is rise, can you use order by before group b in mysql? Yes you can use order by before group by in mysql. It is possible. You've to use subquery for this. ie,

      SELECT * FROM (
                   SELECT * FROM `table` ORDER BY column_name DESC 
                                    )    t
                                       GROUP BY column_name
Where,
               t is alias because you use subquery. If you don't add it, then you'll get error message "Every derived table must have its own alias".


Group by in Mysql:


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

use order by before group by in mysql

                              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:

display unique values using group by in mysql


                           You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.


Use order by before group by:


                            On above example, you want to select highest count of each id and id should be unique. Then you have to use subquery to select highest count values. The mysql query as follows as:

      SELECT * FROM (
                   SELECT * FROM `table1` ORDER BY count DESC 
                                    )    t
                                       GROUP BY id

where,
                 SELECT * FROM `table1` ORDER BY count DESC  run first. It ordered the data in descending order by count.
                 SELECT * FROM ( ... ) t GROUP BY id it takes unique values.
Now you'll get output like this:
               
order by before group by in mysql

                  Now you'll get id with highest count values.


Related Post: