Posted By

crypticsoft on 12/04/13

Tagged

mysql

Versions (?)

Last Edited at 12/04/13 10:51pm

Statistics

Viewed 1038 times

Favorited by 1 user(s)

Related snippets

Pinnacle Cart PDO MySQL : Product Class

/ Published in: PHP

While the Pinnacle Cart API can handle some of this, I found it easier to query the database directly.
This PCProduct class was developed for integrating Pinnacle Cart with WordPress with the following methods:
- get_product($id)
- get_product_by_product_id($id)
- get_product_box($row)
- get_top($cid,$cnt,$side)
- get_products_by_category($cid)
- get_categories($parent,$order,$level)
- get_recent($limit)

You will need to edit the following methods to match your cart paths / database settings:
- cart_path()
- cart_uri()
- get_connection() -- This is the PDO connection, fill in the host, dbname, user, password
- getProductUrl() -- Check the SEO settings, I'm using the 3.7 custom URL: %CategoryName%/%ProductTitle%/

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

class PCProduct {
 
	private function cart_path() {
		return "/home/mydomain/domains/mydomain.com/public_html/cart/";
	}
	private function cart_uri() {
		return "http://mydomain.com/cart/";
	}
	private function get_connection() {
		$conn = new PDO("mysql:host=localhost;dbname=mydatabase", 'root', 'root');
	    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	    return $conn;
	}
 
/* THESE ARE CUSTOM FUNCTIONS FOR PINNACLE CART */
 
	public function imageFileName($product_id){
		$s = strtolower(trim($product_id));
		for($i=0; $i<strlen($s); $i++){
			if(in_array($s[$i], array("\\", "/", "*", ":", "?", "<", ">", "|", '"', "'")))$s[$i] = "_";
		}
		return $s;
	}
 
	//////////////////////////////////////
	//check for product thumb
	public function ProductHasThumb($product_id){
 
		$product_thumbs_dir = self::cart_path() . 'images/products/thumbs';
		$product_imgs_dir = self::cart_path() . 'images/products';
		$product_preview_dir = self::cart_path() . 'images/products/preview';
 
		$prod_thumbs = self::cart_uri() . 'images/products/preview';
		$prod_large = self::cart_uri() . 'images/products';
		$pi = self::imageFileName($product_id);
 
		// products/preview/123.jpg
		if(file_exists($product_preview_dir."/".$pi.".jpg")){
			return $prod_thumbs."/".$pi.".jpg";
		}
		// products/123.jpg
		elseif(file_exists($product_imgs_dir."/".$pi.".jpg")){
			return $prod_large."/".$pi.".jpg";
		}
		// products/123.gif
		elseif(file_exists($product_imgs_dir."/".$pi.".gif")){
			return $prod_large."/".$pi.".gif";
		}
		// products/preview/123.png
		elseif(file_exists($product_preview_dir."/".$pi.".png")){
			return $prod_thumbs."/".$pi.".png";
		}
		// products/preview/123.gif
		elseif(file_exists($product_thumbs_dir."/".$pi.".gif")){
			return $prod_thumbs."/".$pi.".gif";
		}
		else{
			return self::cart_uri() . "images/images/english/imageNoImageSmall.gif";
		}
	}
 
	public function get_product($id) {
 
		# PDO CONNECT
		try {		
			$conn = self::get_connection();
			# PDO PREPARED STATEMENT
			$stmt = $conn->prepare('SELECT * FROM products where pid= :id limit 1');//LIMIT 0 , $num"
 
			$stmt->execute(array(
				'id' => $id
			));	
			$result = $stmt->fetchAll();
 
			if ( count($result) ) {
 
				$data = array();
 
				foreach( $result as $key => $row ){
					//output
					$data[$key] = $row;
					$data[$key]['thumb'] = self::ProductHasThumb($row['product_id']);
					$data[$key]['url'] = self::getProductUrl($row['pid']);
					var_dump($row);
				}
				return $data;
			}
 
		} catch(PDOException $e) {
		    echo 'ERROR: ' . $e->getMessage();
		}
 
 
	}
 
	public function get_product_by_product_id($id) {
 
		# PDO CONNECT
		try {		
			$conn = self::get_connection();
			# PDO PREPARED STATEMENT
			$stmt = $conn->prepare('SELECT * FROM products where product_id= :id or pid = :id limit 1');//LIMIT 0 , $num"
 
			$stmt->execute(array(
				'id' => $id
			));	
			$result = $stmt->fetchAll();
 
			if ( count($result) ) {
 
				$data = array();
 
				foreach( $result as $key => $row ){
					//output
					$data[$key] = $row;
					$data[$key]['thumb'] = self::ProductHasThumb($row['product_id']);
					$data[$key]['url'] = self::getProductUrl($row['pid']);
				}
				return $data;
			}
 
		} catch(PDOException $e) {
		    echo 'ERROR: ' . $e->getMessage();
		}
 
 
	}	
 
	public function get_product_box( $row ) {
			if ( is_array( $row ) ) {
				$product_thumb = self::ProductHasThumb($row['product_id']);
				$product_url = self::getProductUrl($row['pid']);				
				$output = '<div class="product-box">
				<div class="thumb"><a title="Click here to shop our ' . $row['title'] . '" href="' . $product_url . '">
				<img src="' . $product_thumb . '" alt="'.$row['title'].'" title="'.$row['title'].'" /></a></div>
				<a title="Click here to shop our ' . $row['title'] . '" href="' . $product_url . '"><strong>' . $row['title'] . '</strong></a>
				<br /><small>Product ID: ' . $row['pid'] . '</small><br />
				<span class="price">$'.number_format($row['price'], 2, '.', '').'</span>
				<form action="' . self::cart_uri() . 'index.php?" method="post">
				<input type="hidden" name="oa" value="AddItem"/>
				<input type="hidden" name="oa_id" value="'.$row['product_id'].'"/>
				<input type="hidden" name="oa_quantity" maxlength="6" size="2" value="1"/>				
				<table cellpadding="0" cellspacing="0" border="0" style="margin:0 auto;width:100%"><tr><td class="CatalogItemLight" align="center"><input  type="image" src="' . self::cart_uri() . 'images/buttons/english/buttonAddToCart.gif" alt="Add To Cart"/></td></tr></table>
				</form></div>';					
 
				//}
				return $output;
			}
	}
	public function get_top($id,$cnt,$side=false) {
 
		# PDO CONNECT
		try {		
			$conn = self::get_connection();
			# PDO PREPARED STATEMENT
			$stmt = $conn->prepare('SELECT * FROM products WHERE cid = :id ORDER BY rand() LIMIT :cnt');
			$stmt->execute(array(
				'id' => $id,
				'cnt' => $cnt
			));	
			// fetch assoc array
			$result = $stmt->fetchAll();
			//output vars
			if ( count($result) ) {
				if($side==true){$float='';}else{$float='float:left;';}
				$output = '<div style="width:600px;height:auto;margin:20px 0 0 10px">';
 
				foreach( $result as $prods ){
 
					//loop through each product and display 
					$prod_thumb = self::ProductHasThumb($prods['product_id']);
					$output .= '<div style="width:190px;'.$float.'text-align:center;margin:0 0 0 10px;height:220px;"><div style="width:150px;height:90px;margin:0 auto;"><a title="Click here to shop our '.$prods['title'].'" href="' . self::getProductUrl($prods['pid']) . '"><img src="'.$prod_thumb.'" style="border: 1px solid rgb(204, 204, 204); margin: 0pt 10px 0px 10px; padding: 5px; background: rgb(241, 241, 241) none repeat scroll 0% 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;max-height:95px;max-width:95px"  alt="'.$prods['title'].'" title="'.$prods['title'].'" /></a></div><br style="clear:both" /><a title="Click here to shop our '.$prods['title'].'" href="' . self::getProductUrl($prods['pid']) . '"><strong>'.$prods['title'].'</strong></a><br /><small>Product ID: '.$prods['pid'].'</small><br /><span style="color:red;font-weight:bold;font-size:14px;">$'.number_format($prods['price'], 2, '.', '').'</span><form action="' . self::cart_uri() . 'index.php?" method="post">
							<input type="hidden" name="oa" value="AddItem"/>
							<input type="hidden" name="oa_id" value="'.$prods['product_id'].'"/>
							<input type="hidden" name="oa_quantity" maxlength="6" size="2" value="1"/>				
											<table cellpadding="0" cellspacing="0" border="0" style="margin:0 auto;">
								<tr>
									<td class="CatalogItemLight"><input  type="image" src="' . self::cart_uri() . 'images/buttons/english/buttonAddToCart.gif" alt="Add To Cart"/></td>
								</tr>
							</table>
							</form></div>';
 
				}
				$output .= "</div>";
				return $output;
			}
 
		} catch(PDOException $e) {
		    echo 'ERROR: ' . $e->getMessage();
		}
	}
 
	public function get_products_by_category($id) {
 
		# PDO CONNECT
		try {		
			$conn = self::get_connection();
			# PDO PREPARED STATEMENT : Get all related products
			$p_stmt = $conn->prepare('SELECT * FROM products_categories WHERE cid = :id');
 
			$p_stmt->execute(array(
				'id' => $id
			));
			$products = $p_stmt->fetchAll();
			$data = array();
			// check each product to see if its available
			foreach( $products as $product ){
 
				$stmt = $conn->prepare('SELECT * FROM products WHERE pid = :id AND is_visible = "Yes"');//LIMIT 0 , $num"
				$stmt->execute(array(
					'id' => $product['pid']
				));	
 
				$result = $stmt->fetch();
 
				if ( is_array($result) ) {
					$data[] = $result;
				}
			}
			return $data;
 
		} catch(PDOException $e) {
		    echo 'ERROR: ' . $e->getMessage();
		}
 
	}
 
public function get_categories($parent=0,$order='priority',$level=1) {
	# PDO CONNECT
	try {		
		$conn = self::get_connection();
		# PDO PREPARED STATEMENT
		$stmt = $conn->prepare('SELECT * FROM catalog 
		WHERE level = :level AND parent = :parent AND is_visible = "Yes"
		ORDER BY :order ASC');
 
		$stmt->execute(array(
			'level' => $level,
			'parent' => $parent,
			'order' => $order
		));	
 
		foreach($stmt->fetchAll() as $cats) {
			$categories[] = array(
				'key_name' => $cats['key_name'],
				'name' => $cats['name'],
				'description' => $cats['description'],
				'cid' => $cats['cid']
			);
		}//end while
		return $categories;		
 
	} catch(PDOException $e) {
	    echo 'ERROR: ' . $e->getMessage();
	}
 
}
 
 
	public function get_recent($limit=5) {
 
		# PDO CONNECT
		try {		
			$conn = self::get_connection();
			$row_number = 0;
			# PDO PREPARED STATEMENT
			$stmt = $conn->prepare('SELECT * FROM products ORDER BY added DESC LIMIT ?, ?');
			$stmt->bindValue(1, $row_number, PDO::PARAM_INT);
			$stmt->bindValue(2, $limit, PDO::PARAM_INT);
			$stmt->execute();
			$result = $stmt->fetchAll();
			return $result;
 
		} catch(PDOException $e) {
		    echo 'ERROR: ' . $e->getMessage();
		}
 
	}
 
	public function getProductUrl($pid){
			// SEO setting for product page: %CategoryName%/%ProductTitle%/		
			// custom url: ~/category-name/product-title/
 
			$conn = self::get_connection();
			# PDO PREPARED STATEMENT : Get category name and product title for custom URL
			$stmt = $conn->prepare('SELECT t2.name, t1.title
			FROM products AS t1
			INNER JOIN catalog AS t2 ON t2.cid = t1.cid
			WHERE t1.pid = :pid');
 
			$stmt->execute(array(
				'pid' => $pid
			));
 
			$result = $stmt->fetchAll();
 
			if ( count($result) ) {
				$data = array();
				foreach( $result as $key => $row ){
					$data['category_name'] = self::url_string( $row['name'] );
					$data['product_title'] = self::url_string( $row['title'] );
				}
				$custom_url = self::cart_uri() . $data['category_name'] . "/" . $data['product_title'] . "/";
				return $custom_url;
			}
 
	}
	public function url_string($str) {
		if($str){
			$str = strtolower(trim($str));
			$str = str_replace(" ","-",$str);
			return $str;
		}
	}
 
}

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.