Category: PHP

WordPress pagination with custom MySQL query

Simple pagination with custom query


$per_page = 50; # Set per page
$paged = ( get_query_var('paged') != 0 ) ? get_query_var('paged') : 1 ; ## get paged number
$offset = (( $paged - 1 ) * $per_page); ## set offset

$trial_list = football_trial_list(null, $offset, $per_page);

$trial_total = football_trial_list(null,0,-1);
$total = count($trial_total);

$max_num_pages = ceil( $total / $per_page );

$pagination = football_pagination($max_num_pages);

function football_trial_list($post_id = null, $offset = 0, $per_page = 3, $request = null){
global $wpdb;
$user_id = get_current_user_id();
$table_team = $wpdb->prefix . 'trial';

$distance = "";
if(isset($request) && !empty($request)){
if(isset($request['post_code']) && !empty($request['post_code'])){

$address_data = football_get_latitude_longitude($request['post_code']);
$lat = $address_data['lat'];
$lng = $address_data['lng'];

$distance = ", ( 3959 * acos( cos( radians(".$lat.") ) * cos( radians( tr.lat ) ) * cos( radians( tr.lng ) - radians(".$lng.") ) + sin( radians(".$lat.") ) * sin( radians( tr.lat ) ) ) ) AS distance";
}
}

$query = "SELECT tr.*, p.post_title, p.post_content $distance FROM $table_team AS tr ";
$query .= " INNER JOIN ".$wpdb->prefix."posts AS p ON ( tr.post_id = p.ID ) ";
$query .= " WHERE p.post_type = 'trials' AND p.post_status = 'publish' ";

// if get single post data
if(!empty($post_id) && $post_id != null){
$query .= " AND p.ID = $post_id ";
}

if(isset($request) && !empty($request)){
// if(isset($request['post_code']) && !empty($request['post_code'])){
// $query .= " AND tr.postcode LIKE '%".$request['post_code']."%'";
// }

if(isset($request['trial_name']) && !empty($request['trial_name'])){
$query .= " AND p.post_title LIKE '%".$request['trial_name']."%'";
}
if(isset($request['trial_age']) && !empty($request['trial_age'])){
if($request['trial_age'] >= 35)
$query .= " AND tr.trial_age >= ".$request['trial_age']."";
else
$query .= " AND tr.trial_age <= ".$request['trial_age']."";
}

// if(!empty($request['post_code']) && !empty($request['distance_min']) && !empty($request['distance_max'])){
if(!empty($request['post_code'])){
$query .= " HAVING distance BETWEEN ".$request['distance_min']." AND ".$request['distance_max']." ";
}

// Upcoming Trials
if(isset($request['upcoming']) && $request['upcoming'] = true){
$query .= " AND tr.trial_date >= CURDATE()";
}
}
else{
$query .= " AND tr.user_id = $user_id ";
}

// Upcoming Trials
if(isset($request['upcoming']) && $request['upcoming'] = true)
$query .= " ORDER BY tr.trial_date ASC ";
else
$query .= " ORDER BY p.post_date DESC ";

if($per_page != -1){
$query .= " LIMIT $offset, $per_page";
}

// echo $query; #die;
return $wpdb->get_results( $query, OBJECT );
}
// =============================================
// Default pagination
// =============================================
function football_pagination($max_num_pages){

if($max_num_pages > 1){
$big = 999999999;
$pagination = "<div class='toolbar'>";
$pagination .= paginate_links( array(
'type' => 'list',
'base' => str_replace( $big, '%#%', esc_url( get_pagenum_link( $big ) ) ),
'format' => '?paged=%#%',
'current' => max( 1, get_query_var('paged') ),
'total' => $max_num_pages,
'prev_next' => true,
'prev_text' => 'Prev',
'next_text' => 'Next',
) );
$pagination .= "</div>";

return $pagination;
}
else{
return '';
}
}

 

Advertisements

WordPress file protected with user login

Some time we deed to give option to user download PDF, Doc file the protected with user login

On Download action code as like below


foreach($download_list as $list) {
$title = $list['title'];
$download = $list['download']['url'];

$path = get_home_url().'/';
$dir = '';
$resource_src = str_replace($path, $dir, $download);
$data = base64_encode($resource_src.'^'.get_current_user_id());

$url = get_site_url()."/download.php?file=$data";
?>
<li><a href="<?php echo $url; ?>" target="_blank"><?php echo $title; ?></a></li>
<?php
}

Create “download.php” file from root and add below code.


include('wp-load.php');

if( !get_current_user_id() )
{
wp_redirect(site_url('login/'));
exit;
}
$rew = explode('^',base64_decode($_GET['file']));
if($rew[1] != get_current_user_id())
{
wp_redirect(site_url('login/'));
exit;
}
$file = $rew[0];
$all = $_GET['all'];
if(isset($all)) {
if(json_decode($all)) {
$allFiles = json_decode($all);
} else {
$allFiles = explode(',',$all);

}

$zipname = 'images-'.date('YmdHis').'.zip';
$zip = new ZipArchive;
$zip->open($zipname, ZipArchive::CREATE);

foreach ($allFiles as $imgfile) {
$path_parts = pathinfo($imgfile);
$ext = strtolower($path_parts["extension"]);
if($ext != 'php') {
$zip->addFile($imgfile, basename($imgfile));
}
}
$zip->close();
header("Content-type: application/zip");
header("Content-Disposition: attachment; filename=$zipname");
header("Pragma: no-cache");
header("Expires: 0");

readfile($zipname);
unlink($zipname);
}
if(isset($file)) {
download_file($file);
}
function download_file( $fullPath ){

// Must be fresh start
if( headers_sent() )
die('Headers Sent');

// Required for some browsers
if(ini_get('zlib.output_compression'))
ini_set('zlib.output_compression', 'Off');

// File Exists?
if( file_exists($fullPath) ){

// Parse Info / Get Extension
$fsize = filesize($fullPath);
$path_parts = pathinfo($fullPath);
$ext = strtolower($path_parts["extension"]);

// Determine Content Type
switch ($ext) {
case "pdf": $ctype="application/pdf"; break;
case "exe": $ctype="application/octet-stream"; break;
case "zip": $ctype="application/zip"; break;
case "doc": $ctype="application/msword"; break;
case "xls": $ctype="application/vnd.ms-excel"; break;
case "ppt": $ctype="application/vnd.ms-powerpoint"; break;
case "gif": $ctype="image/gif"; break;
case "png": $ctype="image/png"; break;
case "jpeg":
case "jpg": $ctype="image/jpg"; break;
default: $ctype="application/force-download";
}

if($ext != 'php') {
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Type: $ctype");
header("Content-Disposition: attachment; filename=\"".basename($fullPath)."\";" );
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".$fsize);
ob_clean();
flush();
readfile( $fullPath );
} else
die('Invalid File');

} else
die('File Not Found');

}

Notepad++ php custom shortcut key

Add Notpad++ plugin WebEdit after install successfully restart N++ and go to Plugins -> WebEdot -> EditConfig to open WebEdit.ini file. Add below code in [Tags] section to work Alt + Enter key.


pp=<?php echo "<pre>";\n\tprint_r(|);\necho "</pre> "; ?>
p=echo "<pre>";\n\tprint_r(|);\necho "</pre> ";
php=<?php ?>
if=if(){\n\n}else{\n\n}
pif=<?php if(!empty()): ?>\n\n<?php endif; ?>
for=foreach(){\n\n}
con=console.log();
e=echo
em=if(!empty()):\n endif;
br=echo '<br/>';

WordPress WooCommerce import product with CSV file

When we need to import product data using this script easily add or modified existing product data. also we can download last uploaded CSV file.

add_action( 'admin_menu', 'product_data_admin_menu' );
function product_data_admin_menu() {
	add_submenu_page( 'edit.php?post_type=product','Product import', 'Product import', 'manage_options', 'product_import', 'product_import_admin_section', 'dashicons-analytics', 58  );
}


function product_import_admin_section(){

?>

<div class="wrap parents_import">
	<h2>Product CSV Import</h2>
	<?php 
	
	global $wpdb,$post;
	
	$date = get_option('last_product_import_time');	

	$old_date_timestamp = strtotime($date);
	$new_date = date('Y-m-d h:i', $old_date_timestamp);
	$csv_name = get_option('last_product_import_file');
	
	if(!empty($csv_name)):
		echo '<p>You can <a href="'.get_template_directory_uri().'/csv/'.$csv_name.'" target="_blank" class="blue"><strong>download last upload file </strong></a> for reference.</p>';
		echo '<p> Last uploaded date is '.$new_date.' </p>';
	endif;	

	?>



	<form action="" method="post" id="prodcut_import" enctype="multipart/form-data">	
		<div class="fileUpload ">
			<span>Upload</span>
			<input id="uploadBtn" name="uploadfile" type="file" class="upload" />
		</div>
		<input type="submit" name="submit" class="button button-primary imp-submit-btn" value="Submit">			
		</form>
	</div>

	<?php
	
	
if (isset($_POST['submit'])){
		set_time_limit(0); 
		error_reporting(E_ERROR | E_WARNING | E_PARSE);
		
		if ($_FILES['uploadfile']['size'] != 0)
		{
			
		global $wpdb,$post;
		$filename = $_FILES['uploadfile']['name'];
		$allowed =  array('csv');
		$filename = $_FILES['uploadfile']['name'];
		$fileTmp_name = $_FILES['uploadfile']['tmp_name'];
		$fileData = file($fileTmp_name);
		$user_count = count($fileData);
		$ext = pathinfo($filename, PATHINFO_EXTENSION);
		if(in_array($ext,$allowed) ) {
			
			
			$taxonomy = 'product_cat';
			$taxonomy_brand = 'brand';
			$user_id  = 1;		

			$row = 0;
			$errorData = array();
			$countusers = array();
			if (($handle = fopen($fileTmp_name, "r")) !== FALSE) {
			while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) {
						
				if($row == 0){ 
					$row++; continue;
				}
				$data = array_map("utf8_encode", $data );
				
				$SKU 				= $data[0]; # Item SKU
				$post_thumb 		= $data[1];	# Product Image
				$post_title 		= $data[2];	# Product Name
				$content 			= $data[3];	# Long Product Description
				$post_content		= (!empty($content)) ? $content : '';
				$price	 			= str_replace('$', '', $data[4]);	#Price
				$price				= preg_replace("/[^0-9\.]/", '', $price);
				$category   		= ucfirst($data[5]);	#Product Category
				$qty   				= $data[6];		#Quantity On Hand
				$stock_status			= ($qty > 0 ) ? 'instock' : 'outofstock';		#Out of Stock / In Stock
				
				$product_slider_temp	= (!empty($data[12]) ? $data[12].',':'');	#Main Image URL
				$product_slider_temp1	= $data[13];	#Product Gallery URL(s)
				
				$product_slider		= $product_slider_temp.$product_slider_temp1;
				$brand	= ucfirst($data[14]);	#Brand
				
				$distributor_a = array('regular_price'=>$distributor_discount_a,'selling_price'=>'');
				$distributor_b = array('regular_price'=>$distributor_discount_b,'selling_price'=>'');
				$distributor_c = array('regular_price'=>$distributor_discount_c,'selling_price'=>'');
				
				
				$SKUD = wc_get_product_id_by_sku($SKU);
				if($SKUD == 0){
					$my_post = array(
						'post_author' 	=> $user_id,
						'post_title'    => $post_title,
						'post_name'     => $post_title,
						'post_content'  => '',
						'post_status'   => 'publish',
						'post_type'     => 'product'
					);
								
					$lastPostid = wp_insert_post( $my_post, $wp_error );
					$ID = $lastPostid;
					if(!empty($post_thumb)){
						Generate_Featured_Image($post_thumb, $ID);
					}
					update_post_meta( $ID, '_visibility', 'visible' );
					update_post_meta( $ID, '_wpr_cp', 'no' );
					update_post_meta( $ID, '_edit_last', 1 );
					update_post_meta( $ID, '_downloadable', 'no' );
					update_post_meta( $ID, 'total_sales', 0 );
					update_post_meta( $ID, '_virtual', 'no' );
					update_post_meta( $ID, '_purchase_note', '' );
					update_post_meta( $ID, '_featured', 'no' );
					update_post_meta( $ID, '_weight', '' );
					update_post_meta( $ID, '_length', '' );
					update_post_meta( $ID, '_width', '' );
					update_post_meta( $ID, '_height', '' );
					update_post_meta( $ID, '_product_attributes', 'a:0:{}' );
					update_post_meta( $ID, '_sale_price_dates_from', '' );
					update_post_meta( $ID, '_sold_individually', '' );
					update_post_meta( $ID, '_manage_stock', 'yes' );
					update_post_meta( $ID, '_backorders', 'no' );
					update_post_meta( $ID, '_upsell_ids', 'a:0:{}' );
					update_post_meta( $ID, '_crosssell_ids', 'a:0:{}' );
					update_post_meta( $ID, '_product_version', '2.5.6' );
					update_post_meta( $ID, '_product_image_gallery', '' ); 
					
					if(!empty($category)){
						wp_set_object_terms( $ID, $category, $taxonomy, true );	
					}
					
					
					if(!empty($price)){
						update_post_meta( $ID, '_price', $price );
						update_post_meta( $ID, '_regular_price', $price  );		 
					}
					
					if(!empty($qty)){
						update_post_meta( $ID, '_stock', $qty  );
					}
					if(!empty($stock_status)){
						update_post_meta( $ID, '_stock_status', $stock_status  );
					}
					
					if(!empty($SKU)){
						update_post_meta( $ID, '_sku', $SKU  );
					}
					
					if(!empty($brand)){
						wp_set_object_terms( $ID, $brand, $taxonomy_brand, true );	
					}						
					
				}
				else{
					$SID = $SKUD;		
					$my_post = array(
						'ID'           => $SID,
						'post_title'    => $post_title,
						'post_name'     => $post_title,
						'post_content'  => '',
						'post_status'   => 'publish',
						'post_type'     => 'product'
					);
							
					$lastPostid = wp_insert_post( $my_post, $wp_error );
					
					if(!empty($post_thumb)){
						Generate_Featured_Image($post_thumb,$SID);
					}
					if(!empty($category)){
						wp_set_object_terms( $SID, $category, $taxonomy, true );	
					}
					
					if(!empty($price)){
						update_post_meta( $SID, '_price', $price );
						update_post_meta( $SID, '_regular_price', $price  );		 
					}
					
					if(!empty($qty)){
						update_post_meta( $SID, '_stock', $qty  );
					}
					if(!empty($stock_status)){
						update_post_meta( $SID, '_stock_status', $stock_status  );
					}
					
					
					if(!empty($brand)){
						wp_set_object_terms( $SID, $brand, $taxonomy_brand, true );	
					}
				}	
			}	## while end		
			fclose($handle);
			} 	## if (($handle = fopen($fileTmp_name, "r")) !== FALSE) {
			
	
		
		  
			  
	   echo '<div class="notice notice-success is-dismissible">';
	   echo '<p><strong>Import data successfully.</strong></p>';
	   echo '</div>';
			
		$CurrentDate = date('j-m-y-h-i');
		$CurrentDateSave = date('Y-m-d h:i');
		$newname = "product_date-".$CurrentDate.".".$ext; 
		$target = get_template_directory().'/csv/'.$newname;
		
		if (!file_exists(get_template_directory().'/csv/')) {
			mkdir(get_template_directory().'/csv/', 0777, true);
		}
		move_uploaded_file($_FILES['uploadfile']['tmp_name'],$target);
		
		add_option( 'last_product_import_file', $newname, '', 'yes' );
		add_option( 'last_product_import_time', $CurrentDateSave, '', 'yes' );

		}
		else{
			echo '<div class="notice notice-warning is-dismissible">';
			echo '<p><strong>Please upload csv file only.</strong></p>';
			echo '</div>';
		}
		
	} // Empty check
	else{
			echo '<div class="notice notice-warning is-dismissible">';
			echo '<p><strong>Please upload file.</strong></p>';
			echo '</div>';
		}
	}// upload action
}

WordPress search in custom post meta

Searching in custom post meta as like WooCommerce SKU.

add_filter( 'posts_where', 'codyfy_search_where' );
function codyfy_search_where( $where ){
    global $pagenow, $wpdb;
    $class_status = $_GET['s'] ? $_GET['s'] : ''; 
    if ($_GET['s'] != '') {
        $where = preg_replace(
       "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
       "(".$wpdb->posts.".post_title LIKE $1) OR (".$wpdb->postmeta.".meta_key LIKE '_sku' AND ".$wpdb->postmeta.".meta_value LIKE $1) ", $where );
    }
	
    return $where;
}

add_filter('posts_join', 'codyfy_search_join' );
function codyfy_search_join ($join){
    global $pagenow, $wpdb;
    if ( $_GET['s'] != '') {    
        $join .='LEFT JOIN '.$wpdb->postmeta. ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.post_id ';
    }
    return $join;
}

install dependencies or use composer at all in windows

For very Beginner Windows Users

  • Download the installer for windows from here
  • Check the installation by executing composer command at command prompt
  • Now, create the composer.json file at the same level where you have Vendor directory. (Not inside Vendor)
  • Move to the directory where you have composer.json through command prompt and run following command composer install
  • It shows the messages like ‘packages are downloaded’ and ‘autoload.php file created. (Check your /app/Vendor/ Directory)
  • now insert this code at the beginning of /app/config/core.php
  • require_once dirname(__DIR__) . '/Vendor/autoload.php';the above code will load all the classes automatically.

Reference 

Search in custom post type title and post meta using MySql query

Search in custom post type title and post meta using MySql query.

global $wpdb;
	$search_val = '';
	if(!empty($post['search_key'])){
		$search_val = '%' . mysql_real_escape_string($post['search_key']) . '%';
	}
	$query = "SELECT p1.ID FROM $wpdb->posts AS p1 ";
	$query .= " INNER JOIN $wpdb->postmeta AS p2 ON (p1.ID = p2.post_id) WHERE 1=1 ";
	
	if(!empty($search_val)){
		$query .= " AND ( (p1.post_title LIKE '$search_val')  ";
		$query .= " OR (p2.meta_key = 'people_title' AND CAST(p2.meta_value AS CHAR) LIKE '$search_val') ";
		$query .= " OR (p2.meta_key = 'office_location' AND CAST(p2.meta_value AS CHAR) LIKE '$search_val') ) ";
	}
	
	$query .= " AND p1.post_type = 'people'  ";
	$query .= " AND p1.post_status = 'publish' ";
	$query .= " GROUP BY p1.ID ORDER BY p1.menu_order ";	

Single qutes search In WordPress and PHP

In WordPress

$search = html_entity_decode($searchKey, ENT_QUOTES,  'UTF-8' );
	 
$args = array('post_status'=>"publish",'posts_per_page' => $limit,'offset' => $offset, 'order' => 'DESC' ,'s' => $search, 'post_type' => Array('post', 'page', 'markets' , 'people', 'post','projects','services') );

$result = new WP_Query( $args );

In PHP

$search_val = '';
if(!empty($post['search_key'])){
	$search_val = '%' . mysql_real_escape_string($post['search_key']) . '%';

$query = "SELECT p1.ID FROM $wpdb->posts AS p1 ";
$query .= " INNER JOIN $wpdb->postmeta AS p2 ON (p1.ID = p2.post_id) WHERE 1=1  (p1.post_title LIKE '$search_val')";
}