Want MySQL FullText Search with InnDB Huh?

MySQL FullText indexing is a popular way to search data from table. FullText indexing is only available if database table type is MyISAM. But, MyISAM table type does not support Foreign keys. InnoDB table type support Foreign keys but does not support FullText indexing. So I have to use a trick to implement fulltext search for a InnoDB type table. I have created a temporary MyISAM table which is identical to my InnoDB table and deleted that table after search complete. check this out……

Provide the search term and limit for search result in function’s parameter.

<?php

function fulltxt_search($q,$lim1,$lim2){ 

$terms =trim(stripslashes(strip_tags($q)));
$rhtml="";
if(empty($terms)){

   $rhtml.= "\n".'<p>No search term entered.</p>';

}else{

   // SQL injection prevention (double quote mark is allowed here but should probably be disallowed)

   $terms = strtr($terms, ',/\*&()$%^@~`?;', '               ');

   $terms = trim($terms);

   $terms = str_replace('#180', '', $terms);

   $rhtml.= "\n".'<p>Searching for <i>'.htmlspecialchars($terms).'</i>... </p>';

   $key = 'text_index';

   // Beware of the re-definition of $s in this query.

   $terms = html_entity_decode($terms, ENT_QUOTES);
	
	mysql_query('CREATE TEMPORARY TABLE `tbl_mirror` (FULLTEXT INDEX (`info`)) Engine=MyISAM SELECT * FROM `values`');
	
   if(!($res = @mysql_query('SELECT * FROM `tbl_mirror` WHERE MATCH(`info`) AGAINST ("'.$terms.'") LIMIT '.$lim1.', '.$lim2.';')or die(mysql_error()))){
	   
	  mysql_query('DROP TEMPORARY TABLE tbl_mirror;')or die(mysql_error());
      echo "\n".'<p>No match for: <i class="red">'.$terms.'</i></p>';

   }else{
	
	mysql_query('DROP TEMPORARY TABLE tbl_mirror;')or die(mysql_error());
	
      if(@mysql_num_rows($res) == 0){

        $rhtml.="\n".'<p>The search engine cannot find the page you are looking for.</p>';

        $rhtml.="\n".'<p>This could be because it could not find any pages containing <i>'.$terms.'</i>';

        $rhtml.="\n".'<p>Alternatively, it might have found too many pages, and could not decide which one you wanted.</p>';

      }else{

         $i = 0;

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

            $s = substr(stristr(strip_tags($row['info']), $terms), 0, 120);		
            $i++;
	    $out[]=$row;
            $rhtml.="\n".'<p>'.$i.'.) <a href="results.php?rez_id='.$row['f_id'].'">'.htmlentities($row['info']).' - '.$row['f_id'].'</a><br />... '.$s.'...'.$row['id'].'/'.$row['e_id'].'...</p>';

         }

      }

   }

}
 
return $rhtml;
}
?>

Export/Backup MySQL Database like phpMyAdmin

If you want to backup you mysql database by php script like phpMyadmin do you can use below function, something like for auto backup system or manual backup from admin panel of your webapp.
Call the function with database name and a path to save backup( leave blank for current dir)

<?php
function db_bkf($dbname, $path=''){
	
ini_set('display_errors',1);
error_reporting(E_ALL);
echo 'Backuping database....'; 
$drop=0;

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

mysql_select_db("logs");

$hostname='localhost'; $username='root'; $password='';

$backupFile = $path.$dbname . date("Y-m-d-H-i-s") . '.sql';

$tab_status = mysql_query("SHOW TABLE STATUS");

while($all = mysql_fetch_assoc($tab_status)):

    $tbl_stat[$all['Name']] = $all['Auto_increment'];

endwhile;

$backup='';
 
$tables = mysql_query("SHOW TABLES FROM $dbname");

echo ($tables=="")?"No db table found !":count($tables)." Tables";

while($tabs = mysql_fetch_row($tables)):

$droptbl=(($drop==1)?"DROP IF EXISTS TABLE `".$tabs[0]."`;":"");

    $backup .= "--\n-- Table structure for `$tabs[0]`\n--\n\n".$droptbl."\nCREATE TABLE IF NOT EXISTS `$tabs[0]` (";

    $res = mysql_query("SHOW CREATE TABLE $tabs[0]");

    while($all = mysql_fetch_assoc($res)):

        $str = str_replace("CREATE TABLE `$tabs[0]` (", "", $all['Create Table']);

        $str = str_replace(",", ",", $str);

        $str2 = str_replace("`) ) TYPE=MyISAM ", "`)\n ) TYPE=MyISAM ", $str);

        $backup .= $str2." AUTO_INCREMENT=".$tbl_stat[$tabs[0]].";\n\n";

    endwhile;

    $backup .= "--\n-- Data to be executed for table `$tabs[0]`\n--\n\n";

    $data = mysql_query("SELECT * FROM $tabs[0]");

    while($dt = mysql_fetch_row($data)):

        $backup .= "INSERT INTO `$tabs[0]` VALUES('".mysql_real_escape_string($dt[0])."'";

        for($i=1; $i<sizeof($dt); $i++):

            $backup .= ", '".mysql_real_escape_string($dt[$i])."'";

        endfor;

        $backup .= ");\n";

    endwhile;

    $backup .= "\n-- --------------------------------------------------------\n\n";

endwhile;

$fh=fopen($backupFile,'w') or die("Backup not done! file error");
fwrite($fh, $backup);
fclose($fh);
echo "<br />Backup Complete !"; 
 
}
?>

Magento + ebay

Count matching values of a array in PHP

Very simple function to count matching values of a array.

function array_match_count($aa)
{
	$bb=array_unique($aa);
	print_r($bb);
	$numb=array();

foreach($aa as $a)
	{
		foreach($bb as $b)
		{	
			if(!isset($numb[$b]))
			{ 
				$numb[$b]=0; 
			}
			
			if($b==$a)
			{
				$numb[$b]++;
			}
		}
	}

	echo '<hr>';
	print_r($numb);

} //func end


$aa=array(
1=>'uk',
2=>'usa',
3=>'uk', 
4=>'uk', 
5=>'roi', 
6=>'roi', 
7=>'uae', 
8=>'bd', 
9=>'bd', 
10=>'bd', 
11=>'bd' );

array_match_count($aa);

Output:
Array ( [1] => uk [2] => usa [5] => roi [7] => uae [8] => bd )

Array ( [uk] => 3 [usa] => 1 [roi] => 2 [uae] => 1 [bd] => 4 )

Magento CMS Page menu

We can put this function in helper of any extension and call from theme. or you we can put this directly from theme. :)
or create a file template/page/html/leftmeu.phtml
and add block

 <block type="page/html_header" name="leftmenu" as="leftmenu" template="page/html/leftmenu.phtml"/> 

in page.xml (above or below breadcrumbs)
then you can call from theme

 <?php echo $this->getChildHtml('leftmenu') ?>

public function getcmsdata()
	{
	$cncollection = Mage::getModel('cms/page')->getCollection()->addStoreFilter(Mage::app()->getStore()->getId());
	$cncollection->getSelect()->where('is_active = 1'); 
	foreach ($cncollection as $page)
		{
			$PageData = $page->getData();
			if($PageData['menu']!= 'false') 
			{
			$lnk[] =array('value'=>Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_WEB).$PageData['identifier'], 'label'=>$PageData['title']);
			}
		}	
		return $lnk;
	}