frankbergere
8/26/2016 - 12:56 PM

event_tickets_plus_update_sku.php

<?php
// The script updates the SKU of all products by naming them as event_title-event_start_date-product_title
// Todo: if product is wootickets_product before first while
// 1- Use on a WordPress Install with only Events Tickets Plus Products
// 2- Make a backup of the database before running the script
// 3- Change parameters of $mysqli connection
// 4- Change "db_prefix_" with the right prefix
// 5- Put this file at the same level as wp-config.php
// 6- Run the script via the browser (e.g http://yourdomain.com/event_tickets_plus_update_sku.php)
// 7- Check some events tickets SKU
ini_set('display_errors', '1');

$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("SET NAMES 'utf8'");
			
$sql_select_products = 'SELECT `post_title`, `ID` FROM `db_prefix_posts` WHERE `post_type` = "product"';

$req_select_products = $mysqli->query($sql_select_products); 

$count = 0;

while($r_title=$req_select_products->fetch_assoc()) {
    $product_title = $r_title['post_title'];
    $product_id = $r_title['ID'];

	$sql = 'SELECT `meta_value` FROM `db_prefix_postmeta` WHERE `post_id` = '.$product_id.' AND `meta_key` = "_tribe_wooticket_for_event"';

	$req = $mysqli->query($sql); 
	


	while($r=$req->fetch_assoc()) {

	    $frk_event_ID = $r['meta_value'];
		$sql2 = 'SELECT `db_prefix_posts`.`post_title`, `db_prefix_postmeta`.`meta_value` FROM `db_prefix_posts`,`db_prefix_postmeta` WHERE `db_prefix_postmeta`.`post_id` = '.$frk_event_ID.' AND `db_prefix_postmeta`.`meta_key` = "_EventStartDate" AND `db_prefix_posts`.`ID` = `db_prefix_postmeta`.`post_id`';
		
		$req2 = $mysqli->query($sql2); 
		
		while($r2=$req2->fetch_assoc()) {
		    $frk_event_title = $r2['post_title'];
		    $frk_event_start_date = $r2['meta_value'];
		    $frk_event_start_date = date("d/m/Y \à H:i",strtotime($frk_event_start_date));;
		}
	} 

	$product_sku = $frk_event_title.' - '.$frk_event_start_date.' - '.$product_title;

	$update_sku = 'UPDATE `db_prefix_postmeta` SET `meta_value` = "'.$product_sku.'" WHERE `post_id` = '.$product_id.' AND `meta_key` = "_sku"';
	$mysqli->query($update_sku);
	$count++;

}
echo $count.' products updated';

?>