Control Flow Functions in Mysql

In this post i describe about how to use conditional operation in sql command for retrieving a record form table.
using these function record display according to it's condition.
  these operator are following:-

1. Case
2. If
3. IfNull
4. Nullif  

Case function:-
   syntax:-
                case condition when value_to_compare1 then result1 when value_to_compare2 then result2 else result3 end;

In above syntax  when condition value is equal to value_to_compare1 then result1 will output other check value_to_compare2 then result2 will output if both comparison fail then result3 execute.

example:-
CASE var WHEN NULL THEN SELECT 'Hi'; ELSE SELECT 'friend.'; END CASE;


If:-
Syntax:-
           select if(condition,true,false) from table ;
In above syntax if condition is true then true comes in result other wise false.


example:-
      select if(id=vlaue,emp_name,emp_dept) from emp where id=value;




IFNULL

    syntax:

           ifnull(value1,value2)

    

if value1 is not NULL, IFNULL() returns value1; otherwise it returns value2.

 IFNULL() returns a numeric or string 

example:-SELECT IFNULL(1,0);

Nullif:-

   syntax:- nullif(value1,value2);
if value1=value2 then return null other vise value1

Fetch Record form search string in php

fetch unique  row set passing query sting as parameter and search by its all word of string one by one merge all record and find unique row set and display them.

$dbc=mysql_query('host','username','pass');
mysql_select_db('databasename',$dbc);

$getTag=$_REQUEST['tags'];  //searching string

$arrFirst=array();
$arrSecond=array();


$queryExecute="select * from music where name like('%".$getTag."%') and status='y' or singer like('%".$getTag."%') and status='y' or language like('%".$getTag."%') and status='y' or main_cate like('%".$getTag."%') and status='y' or genure like('%".$getTag."%') and status='y' or placename like('%".$getTag."%') and status='y' or other_tag like ('%".$getTag."%') and status='y'";


$queryResult=mysql_query($queryExecute,$dbc);

while($row=mysql_fetch_array($queryResult)){

$arrFirst=array_merge($arrFirst,array($row[0]));

}

$findKeyTag=explode(" ",$getTag);

for($i=0;$i
$executequery="select * from music where name like('%".$findKeyTag[$i]."%') and status='y' or language='$findKeyTag[$i]' and status='y' or main_cate='$findKeyTag[$i]' and status='y' or guru='$findKeyTag[$i]' and status='y' or genure='$findKeyTag[$i]' and status='y' or lordname='$findKeyTag[$i]' and status='y' or other_tag like('%".$findKeyTag[$i]."%') and status='y'";

$result=mysql_query($executequery,$dbc);
while($row=mysql_fetch_array($result)){

$arrSecond=array_merge($arrSecond,array($row[0]));

}


}

$arrFinal1=array();
$arrFinal=array_unique(array_merge($arrFirst,$arrSecond));

$rsCount=count($arrFinal);
for($i=0;$i<$rsCount;$i++){

$arrFinal1=array_unique(array_merge($arrFinal1,$arrFinal));

}

if($rsCount>$display){

$pages=ceil($rsCount/$display);

}else{
$pages=1;
}



showRecord1($arrFinal1,$getTag,$dbc,$startNo);




function showRecord1($arr,$tag,$dbc,$start,$userId,$url){


$totalRecord=count($arr);
for($i=$start;$i<$start+10;$i++){


if($arr[$i]==""||!$arr[$i]){

     break;
}


$q="select * from music where id=$arr[$i]";
$result=mysql_query($q,$dbc);

while($row3=mysql_fetch_array($result)){

echo $row["id"];
}

   }

}

Navigation Using Mysql Query in php

If u want to navigate record without passing id array or other value sting following code will help u to move forward and backword on basis of present display id.

<div>

<?php

$selPre="select id from table where id<$imgId  and status='Yes' order by id Desc";

$preRs=mysql_query($selPre,$dbc)or die('error in previous id : '.mysql_error());

$preId=mysql_fetch_array($preRs);

if(mysql_num_rows($preRs)>0){

?>

 <a title="Previous" href="Selffilename.php?Id=<?php echo $preId["id"];?>" class="no-underline">

<span style="color: rgb(105, 105, 105); font-weight: bold;">PREVIOUS</span>

<span class="paginate-first">&lt;&lt;</span>

</a>

</div>

<div align="right" style="width:50%; float:left">

<?php

}

$selNxt="select id from table where id>$Id and status='Yes' order by id ASC";

$NxtRs=mysql_query($selNxt,$dbc)or die('error in previous id : '.mysql_error());

$nxtId=mysql_fetch_array($NxtRs);

if(mysql_num_rows($NxtRs)>0){

?>

<a title="Last Page" href="Selffilename.php?Id=<?php echo $nxtId["id"];?>" class="no-underline">

<span class="paginate-last">&gt;&gt;</span>

<span style="color: rgb(105, 105, 105); font-weight: bold;">NEXT</span>

</a>

<?php

}?>

Create Auto Generated Table according to no of record in table

If we want to create table automatic according to no of record come from database;

first we declare how much no of col in table want to display.

<table width="810" border="0" cellspacing="0" cellpadding="0">

<?php

$colNo;

$i=0;

$slectQuery="select title from table";

$Result=mysql_query($selectQuery,$dbc)or die('error in fetch record:-'.mysql_error());

while($row=mysql_fetch_row($Result)){

if($i==0){

echo '<tr>';

}

echo '<td>

$i++;

if($i==$colNo){

echo '</tr>';

$i=0;

}

?>

</table>