Category: MySQL

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 '';
}
}

 

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
}

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')";
}

Export MySQL Database Using PHP Code

Using bellow snippet we have to download and store on server MySQL database easily. Just change database name and it’s credentials.


EXPORT_TABLES("localhost","username","password","database" ); //or add 5th parameter(array) of specific tables: array("mytable1","mytable2","mytable3")
function EXPORT_TABLES($host,$user,$pass,$name, $tables=false, $backup_name=false ){

$mysqli = new mysqli($host,$user,$pass,$name); $mysqli->select_db($name); $mysqli->query("SET NAMES 'utf8'");
$queryTables = $mysqli->query('SHOW TABLES'); while($row = $queryTables->fetch_row()) { $target_tables[] = $row[0]; } if($tables !== false) { $target_tables = array_intersect( $target_tables, $tables); }
foreach($target_tables as $table){

$result = $mysqli->query('SELECT * FROM '.$table);
$fields_amount=$result->field_count; $rows_num=$mysqli->affected_rows;

$res = $mysqli->query('SHOW CREATE TABLE '.$table);

if(!$res) {
//echo $link->error;
} else {
$TableMLine=$res->fetch_row();
}
$content = (!isset($content) ? '' : $content) . "\n\n".$TableMLine[1].";\n\n";
for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0) {
while($row = $result->fetch_row()) { //when started (and every after 100 command cycle):
if ($st_counter%100 == 0 || $st_counter == 0 ) {$content .= "\nINSERT INTO ".$table." VALUES";}
$content .= "\n(";
for($j=0; $j<$fields_amount; $j++) { $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); if (isset($row[$j])){$content .= '"'.$row[$j].'"' ; }else {$content .= '""';} if ($j<($fields_amount-1)){$content.= ',';} }
$content .=")";
//every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) {$content .= ";";} else {$content .= ",";} $st_counter=$st_counter+1;
}
} $content .="\n\n\n";
}



$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";

/***********/
/* if you have to store on your server
/***********/

/*$rootPath = realpath('../auto-backup');
$rootPath = $rootPath.'/'.$backup_name;

$fp = fopen($rootPath,"wb");
fwrite($fp,$content);
fclose($fp);
/**************/


/***********/
/* if you have to download
/***********/
header('Content-Type: application/octet-stream'); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"".$backup_name."\""); echo $content; exit;
}