Implode the query results (PHP)

Problem: Some times we need to implode the results of a query to use them in another query or for some other reason.

Solution:

include "connection.php"; $query="select * from country";
$result=mysql_query($query);
$array =array();
while($row=mysql_fetch_array($result))
{
$array[]=$row['country'];
}
$string=implode(",",$array);
echo $string;

How to remove "Tab" from column data (SQL)

Problem:
Remove leading or trailing "Tab" from data values
Eg:
ColumnA

    val1
val2
val3
    val4
val5

So to remove these "tab" from data values we cannot use trim function directly

update testtable set ColumnA=trim(ColumnA);

Solution:
To solve this problem we can slightly modify the trim function and include Replace function also in it by specifying ascii value of "tab" which is 9.

update testtable set ColumnA=trim(replace(ColumnA,Char(9),Char(32)));

How to Apply DISTINCT condition on only one Column

Problem:- Lot of time we need to apply Distinct on only one column, but the DISTINCT keyword doesn't provided by SQL applies distinct to whole row.

Solution:- The following query can solve the problem.

Select * from TestTable T where Testno IN(Select MAX(Testno) from TestTable where T.Testname=Testname)

In this query Testno is the Testname is the column by which you want to distinct the rows, and Testno is the column which is not distinct (for e.g. Primary key)

How to maintain status of Check Box in Multiple Pages/Pagination

Problem:- How to maintain the status of checkbox on multiple pages/Pagination in a webpage.

Technology :- PHP,AJAX

Demo: View Checkbox Demo

Solution:

First Page "test.php"- Simple Page, Check box are dynamically generated by PHP code, contains a link for next page and Java-script to be included in this page

<input type="submit" value="Check All" onclick="javascript:checkedAll(frm1)">Â
<input type="submit" value="Clear All" onclick="javascript:clearAll(frm1)"><br/>
<form name="frm1" id="frm1" action="" method="POST">
<?php for($i=1;$i<=5;$i++)
{
if(strpos($_SESSION['views'],$i."|")===false)
{
$s="";
}
else
{
$s="checked=true";
}
echo "<input type=\"checkbox\" value=\"$i\" ".$s." onClick=\"javascript:xyz($i)\"/><br/>";
}
?>
</form>
<!--<p>Checkbox:<span id="txtHint"></span></p>-->
<a href="test1.php">Next</a>

Second Page "test1.php" Similar to previous page, contains a link for previous page

<input type="submit" value="Check All" onclick="javascript:checkedAll(frm1)">&nbsp;
<input type="submit" value="Clear All" onclick="javascript:clearAll(frm1)"><br/>
<form name="frm1" id="frm1" action="" method="POST">
<?php for($i=6;$i<=10;$i++)
{
if(strpos($_SESSION['views'],$i."|")===false)
{
$s="";
}
else
{
$s="checked=true";
}
echo "<input type=\"checkbox\" value=\"$i\" ".$s." onClick=\"javascript:xyz($i)\"/><br/>";
}
?>
</form>

<!--<p>Checkbox:<span id="txtHint"></span></p>-->
<a href="test.php">Previous</a>

Third file javascript - To be included in both the above pages, this page contains the AJAX code, when checkbox is clicked, it goes to AJAX to store the value in PHP session

function xyz(value) { //For checking/unchecking single checkbox
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari

xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}

xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getvalue.php?q="+value,true);
xmlhttp.send();
}

function checkedAll (frm1) { //For CheckAll button
var aa= document.getElementById('frm1');
checked=true;
var cboxes="";

for (var i =0; i < aa.elements.length; i++)
{
if(aa.elements[i].checked==false)
{
aa.elements[i].checked = checked;if(!isNaN(aa.elements[i].value)){ cboxes += aa.elements[i].value+"|";}
}
}

if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari

xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}

xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getvalue.php?q1="+cboxes,true);
xmlhttp.send();
}

function clearAll (frm1) { //For Clear All Button
var aa= document.getElementById('frm1');
checked = false;
var cboxes1="";
for (var i =0; i < aa.elements.length; i++)
{
aa.elements[i].checked = checked;if(!isNaN(aa.elements[i].value)){
cboxes1 += aa.elements[i].value+"|";}
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}

xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getvalue.php?q2="+cboxes1,true);
xmlhttp.send();
}

Fourth File (getvalue.php) This contains the code to add or remove the checkbox value in PHP session

<?php
session_start();
// For single value
if(isset($_GET["q"])) //get checkbox value from ajax
{
$q=$_GET["q"];
if(strpos($_SESSION['views'],$q."|")===false)
{
$_SESSION['views'] .= $q."|"; //add value to session if not present
}
else
{
$string=str_replace($q."|","",$_SESSION['views']); //remove value from session if already present
$_SESSION['views']=$string;
}

}

//For Check All option
if(isset($_GET["q1"]) and $_GET["q1"] != "")
{
$q1=$_GET["q1"];
$arr=array();
$arr=explode("|",$q1);
foreach($arr as $i => $value)
{
if($arr[$i]!="")
{
if(strpos($_SESSION['views'],$arr[$i]."|")===false)
{
$_SESSION['views'] .= $arr[$i]."|"; //Add all the values checked one by one
}
}
}
}
//For Un Check All option
if(isset($_GET["q2"]) and $_GET["q2"] != "")
{
$q2=$_GET["q2"];
$arr1=array();
$arr1=explode("|",$q2);
foreach($arr1 as $i => $value)
{
if($arr1[$i]!="")
{
$string1=str_replace($arr1[$i]."|","",$_SESSION['views']); //Remove all the values one by ones
$_SESSION['views']=$string1;

}
}
}
echo $_SESSION['views'];
?>