Pinnacle Cart PDO MySQL : Product Class


/ Published in: PHP
Save to your folder(s)

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%/


Copy this code and paste it in your HTML
  1. class PCProduct {
  2.  
  3. private function cart_path() {
  4. return "/home/mydomain/domains/mydomain.com/public_html/cart/";
  5. }
  6. private function cart_uri() {
  7. return "http://mydomain.com/cart/";
  8. }
  9. private function get_connection() {
  10. $conn = new PDO("mysql:host=localhost;dbname=mydatabase", 'root', 'root');
  11. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12. return $conn;
  13. }
  14.  
  15. /* THESE ARE CUSTOM FUNCTIONS FOR PINNACLE CART */
  16.  
  17. public function imageFileName($product_id){
  18. $s = strtolower(trim($product_id));
  19. for($i=0; $i<strlen($s); $i++){
  20. if(in_array($s[$i], array("\\", "/", "*", ":", "?", "<", ">", "|", '"', "'")))$s[$i] = "_";
  21. }
  22. return $s;
  23. }
  24.  
  25. //////////////////////////////////////
  26. //check for product thumb
  27. public function ProductHasThumb($product_id){
  28.  
  29. $product_thumbs_dir = self::cart_path() . 'images/products/thumbs';
  30. $product_imgs_dir = self::cart_path() . 'images/products';
  31. $product_preview_dir = self::cart_path() . 'images/products/preview';
  32.  
  33. $prod_thumbs = self::cart_uri() . 'images/products/preview';
  34. $prod_large = self::cart_uri() . 'images/products';
  35. $pi = self::imageFileName($product_id);
  36.  
  37. // products/preview/123.jpg
  38. if(file_exists($product_preview_dir."/".$pi.".jpg")){
  39. return $prod_thumbs."/".$pi.".jpg";
  40. }
  41. // products/123.jpg
  42. elseif(file_exists($product_imgs_dir."/".$pi.".jpg")){
  43. return $prod_large."/".$pi.".jpg";
  44. }
  45. // products/123.gif
  46. elseif(file_exists($product_imgs_dir."/".$pi.".gif")){
  47. return $prod_large."/".$pi.".gif";
  48. }
  49. // products/preview/123.png
  50. elseif(file_exists($product_preview_dir."/".$pi.".png")){
  51. return $prod_thumbs."/".$pi.".png";
  52. }
  53. // products/preview/123.gif
  54. elseif(file_exists($product_thumbs_dir."/".$pi.".gif")){
  55. return $prod_thumbs."/".$pi.".gif";
  56. }
  57. else{
  58. return self::cart_uri() . "images/images/english/imageNoImageSmall.gif";
  59. }
  60. }
  61.  
  62. public function get_product($id) {
  63.  
  64. # PDO CONNECT
  65. try {
  66. $conn = self::get_connection();
  67. # PDO PREPARED STATEMENT
  68. $stmt = $conn->prepare('SELECT * FROM products where pid= :id limit 1');//LIMIT 0 , $num"
  69.  
  70. $stmt->execute(array(
  71. 'id' => $id
  72. ));
  73. $result = $stmt->fetchAll();
  74.  
  75. if ( count($result) ) {
  76.  
  77. $data = array();
  78.  
  79. foreach( $result as $key => $row ){
  80. //output
  81. $data[$key] = $row;
  82. $data[$key]['thumb'] = self::ProductHasThumb($row['product_id']);
  83. $data[$key]['url'] = self::getProductUrl($row['pid']);
  84. var_dump($row);
  85. }
  86. return $data;
  87. }
  88.  
  89. } catch(PDOException $e) {
  90. echo 'ERROR: ' . $e->getMessage();
  91. }
  92.  
  93.  
  94. }
  95.  
  96. public function get_product_by_product_id($id) {
  97.  
  98. # PDO CONNECT
  99. try {
  100. $conn = self::get_connection();
  101. # PDO PREPARED STATEMENT
  102. $stmt = $conn->prepare('SELECT * FROM products where product_id= :id or pid = :id limit 1');//LIMIT 0 , $num"
  103.  
  104. $stmt->execute(array(
  105. 'id' => $id
  106. ));
  107. $result = $stmt->fetchAll();
  108.  
  109. if ( count($result) ) {
  110.  
  111. $data = array();
  112.  
  113. foreach( $result as $key => $row ){
  114. //output
  115. $data[$key] = $row;
  116. $data[$key]['thumb'] = self::ProductHasThumb($row['product_id']);
  117. $data[$key]['url'] = self::getProductUrl($row['pid']);
  118. }
  119. return $data;
  120. }
  121.  
  122. } catch(PDOException $e) {
  123. echo 'ERROR: ' . $e->getMessage();
  124. }
  125.  
  126.  
  127. }
  128.  
  129. public function get_product_box( $row ) {
  130. if ( is_array( $row ) ) {
  131. $product_thumb = self::ProductHasThumb($row['product_id']);
  132. $product_url = self::getProductUrl($row['pid']);
  133. $output = '<div class="product-box">
  134. <div class="thumb"><a title="Click here to shop our ' . $row['title'] . '" href="' . $product_url . '">
  135. <img src="' . $product_thumb . '" alt="'.$row['title'].'" title="'.$row['title'].'" /></a></div>
  136. <a title="Click here to shop our ' . $row['title'] . '" href="' . $product_url . '"><strong>' . $row['title'] . '</strong></a>
  137. <br /><small>Product ID: ' . $row['pid'] . '</small><br />
  138. <span class="price">$'.number_format($row['price'], 2, '.', '').'</span>
  139. <form action="' . self::cart_uri() . 'index.php?" method="post">
  140. <input type="hidden" name="oa" value="AddItem"/>
  141. <input type="hidden" name="oa_id" value="'.$row['product_id'].'"/>
  142. <input type="hidden" name="oa_quantity" maxlength="6" size="2" value="1"/>
  143. <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>
  144. </form></div>';
  145.  
  146. //}
  147. return $output;
  148. }
  149. }
  150. public function get_top($id,$cnt,$side=false) {
  151.  
  152. # PDO CONNECT
  153. try {
  154. $conn = self::get_connection();
  155. # PDO PREPARED STATEMENT
  156. $stmt = $conn->prepare('SELECT * FROM products WHERE cid = :id ORDER BY rand() LIMIT :cnt');
  157. $stmt->execute(array(
  158. 'id' => $id,
  159. 'cnt' => $cnt
  160. ));
  161. // fetch assoc array
  162. $result = $stmt->fetchAll();
  163. //output vars
  164. if ( count($result) ) {
  165. if($side==true){$float='';}else{$float='float:left;';}
  166. $output = '<div style="width:600px;height:auto;margin:20px 0 0 10px">';
  167.  
  168. foreach( $result as $prods ){
  169.  
  170. //loop through each product and display
  171. $prod_thumb = self::ProductHasThumb($prods['product_id']);
  172. $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">
  173. <input type="hidden" name="oa" value="AddItem"/>
  174. <input type="hidden" name="oa_id" value="'.$prods['product_id'].'"/>
  175. <input type="hidden" name="oa_quantity" maxlength="6" size="2" value="1"/>
  176. <table cellpadding="0" cellspacing="0" border="0" style="margin:0 auto;">
  177. <tr>
  178. <td class="CatalogItemLight"><input type="image" src="' . self::cart_uri() . 'images/buttons/english/buttonAddToCart.gif" alt="Add To Cart"/></td>
  179. </tr>
  180. </table>
  181. </form></div>';
  182.  
  183. }
  184. $output .= "</div>";
  185. return $output;
  186. }
  187.  
  188. } catch(PDOException $e) {
  189. echo 'ERROR: ' . $e->getMessage();
  190. }
  191. }
  192.  
  193. public function get_products_by_category($id) {
  194.  
  195. # PDO CONNECT
  196. try {
  197. $conn = self::get_connection();
  198. # PDO PREPARED STATEMENT : Get all related products
  199. $p_stmt = $conn->prepare('SELECT * FROM products_categories WHERE cid = :id');
  200.  
  201. $p_stmt->execute(array(
  202. 'id' => $id
  203. ));
  204. $products = $p_stmt->fetchAll();
  205. $data = array();
  206. // check each product to see if its available
  207. foreach( $products as $product ){
  208.  
  209. $stmt = $conn->prepare('SELECT * FROM products WHERE pid = :id AND is_visible = "Yes"');//LIMIT 0 , $num"
  210. $stmt->execute(array(
  211. 'id' => $product['pid']
  212. ));
  213.  
  214. $result = $stmt->fetch();
  215.  
  216. if ( is_array($result) ) {
  217. $data[] = $result;
  218. }
  219. }
  220. return $data;
  221.  
  222. } catch(PDOException $e) {
  223. echo 'ERROR: ' . $e->getMessage();
  224. }
  225.  
  226. }
  227.  
  228. public function get_categories($parent=0,$order='priority',$level=1) {
  229. # PDO CONNECT
  230. try {
  231. $conn = self::get_connection();
  232. # PDO PREPARED STATEMENT
  233. $stmt = $conn->prepare('SELECT * FROM catalog
  234. WHERE level = :level AND parent = :parent AND is_visible = "Yes"
  235. ORDER BY :order ASC');
  236.  
  237. $stmt->execute(array(
  238. 'level' => $level,
  239. 'parent' => $parent,
  240. 'order' => $order
  241. ));
  242.  
  243. foreach($stmt->fetchAll() as $cats) {
  244. $categories[] = array(
  245. 'key_name' => $cats['key_name'],
  246. 'name' => $cats['name'],
  247. 'description' => $cats['description'],
  248. 'cid' => $cats['cid']
  249. );
  250. }//end while
  251. return $categories;
  252.  
  253. } catch(PDOException $e) {
  254. echo 'ERROR: ' . $e->getMessage();
  255. }
  256.  
  257. }
  258.  
  259.  
  260. public function get_recent($limit=5) {
  261.  
  262. # PDO CONNECT
  263. try {
  264. $conn = self::get_connection();
  265. $row_number = 0;
  266. # PDO PREPARED STATEMENT
  267. $stmt = $conn->prepare('SELECT * FROM products ORDER BY added DESC LIMIT ?, ?');
  268. $stmt->bindValue(1, $row_number, PDO::PARAM_INT);
  269. $stmt->bindValue(2, $limit, PDO::PARAM_INT);
  270. $stmt->execute();
  271. $result = $stmt->fetchAll();
  272. return $result;
  273.  
  274. } catch(PDOException $e) {
  275. echo 'ERROR: ' . $e->getMessage();
  276. }
  277.  
  278. }
  279.  
  280. public function getProductUrl($pid){
  281. // SEO setting for product page: %CategoryName%/%ProductTitle%/
  282. // custom url: ~/category-name/product-title/
  283.  
  284. $conn = self::get_connection();
  285. # PDO PREPARED STATEMENT : Get category name and product title for custom URL
  286. $stmt = $conn->prepare('SELECT t2.name, t1.title
  287. FROM products AS t1
  288. INNER JOIN catalog AS t2 ON t2.cid = t1.cid
  289. WHERE t1.pid = :pid');
  290.  
  291. $stmt->execute(array(
  292. 'pid' => $pid
  293. ));
  294.  
  295. $result = $stmt->fetchAll();
  296.  
  297. if ( count($result) ) {
  298. $data = array();
  299. foreach( $result as $key => $row ){
  300. $data['category_name'] = self::url_string( $row['name'] );
  301. $data['product_title'] = self::url_string( $row['title'] );
  302. }
  303. $custom_url = self::cart_uri() . $data['category_name'] . "/" . $data['product_title'] . "/";
  304. return $custom_url;
  305. }
  306.  
  307. }
  308. public function url_string($str) {
  309. if($str){
  310. $str = strtolower(trim($str));
  311. $str = str_replace(" ","-",$str);
  312. return $str;
  313. }
  314. }
  315.  
  316. }

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.