Solved

PHP recursive functions and class with PDO?

Posted on 2015-01-26
8
478 Views
Last Modified: 2015-01-29
Hi guys,

I'm trying to learn some of the new (at least for me) aspects of PHP and I'm struggling with a simple menu I'm trying to fill up from the DB. If I was using the old query method (MYSQL or MYSQLi), it would be easy as throwing a bunch of while and foreach loops with a couple calls to the DB. The thing is that I'm very interested in learning how to work with PDO and prepared statements (not just query()) so if anyone wants to give me some tips on how to look at this it would rock. Actually an example would be better. I learn by looking at structured code. Unfortunately all samples I've seen involve too much or too little (for example no prepared or no iterations).

What I have is a couple of tables. One for categories, another for subcategories and a third one for indexes (like sub-subcategories). For now, being capable of calling stuff from the first two would be just fine:

Table categories:
category_id
category_name

Table subcategories:
subcat_id
subcat_parent_id
subcat_name

And I need to get something like this:
<li><a href="#">FIRST CATEGORY NAME</a></li> // item 1 from 1st table
<ul class="dropdown-menu" aria-labelledby="themes">
<li><a href="#">FIRST SUB-CAT OF FIRST CAT</a></li> // item 1 from 2nd table
<li><a href="#">SECOND SUB-CAT OF FIRST CAT</a></li> // item 2 from 2nd table
</ul>

Open in new window


That's it... something simple BUT, I really want to learn how to do this with PDO, prepared statements and iterations inside a class (very new to classes too - that's something I also want to learn). Any ideas?

Thanks in advance!!!!!!
0
Comment
Question by:Cesar Aracena
  • 3
  • 3
  • 2
8 Comments
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 40572452
I can help you with one or two parts of the question.

For Classes and other things about OOP, you need to read and understand the man pages here:
http://php.net/manual/en/language.oop5.php
http://php.net/manual/en/language.exceptions.php
http://php.net/manual/en/language.references.php

If you have never written object-oriented programming in any other language, this will be a "learning curve" as you get started.  You may want to get a couple of books and give yourself some time to read and absorb the concepts and design patterns.  I can recommend these from my personal experience.
http://www.amazon.com/PHP-Object-Oriented-Solutions-David-Powers/dp/1430210117/
http://www.amazon.com/Objects-Patterns-Practice-Experts-Source/dp/143022925X/

You may want to consult these web sites, too.
https://phpbestpractices.org/ (cf: MySQL for discussion on PDO)
http://www.phptherightway.com/

For some discussion on the use of PDO, please see this article.  It shows the familiar but obsolete MySQL extension and maps some of the common data base activities into the MySQLi and PDO extensions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Iterators are the same, whether you're in procedural or object-oriented code -- they are considered to be control structures, because they control the normal top-to-bottom flow of logic in the PHP code.  You have foreach() and while() that are the most commonly used.  In OOP, you also have these extensions to the language:
http://php.net/manual/en/class.traversable.php

It's a lot to learn (a college undergraduate curriculum would spread this work over two years) so I recommend that you build a library of teaching examples for every problem you encounter.  You'll have dozens, if not hundreds of such examples by the time you've explored all of the topics here.  And with that library as your foundation, you will be able to make progress very rapidly when you encounter new programming problems in the future.

Best of luck! ~Ray
0
 
LVL 6

Author Comment

by:Cesar Aracena
ID: 40574407
Hi Ray, thanks for your guidance! It really helped me get started!

I've done a lot of PHP coding as a aficionado in the past (you've helpme me a lot too ;) ) but every since PHP5 came out I've been somewhat afraid of re-learning everything until now.

With a lot of reading and some examples I've found in the web (specially one but I can't quote the author as I don't even remember where I saw it) I think I'm finally getting somewhere. I do have a problem tho and you (or someone) might be willing help me. There's not iteration and not JOINing YET but I'll get there. For the time being, could you please help me find the error here?
<?php

class MenuBuilder {

	protected $_mFetchWhat;
	protected $_sTable;
	protected $_iFilteredTotal;
	protected $_iTotal;
	protected $_iResult;
	protected static $_dbh;

	public function __construct($mFetchWhat, $sDatabase) {

		if(!isset(self::$_dbh)) self::$_dbh = $sDatabase;
		$this->_mFetchWhat = $mFetchWhat;
		//$this->sQuery();
		switch ($mFetchWhat) {
			case 'cat':
				$this->_sTable = 'categories';
				break;
			case 'subcat':
				$this->_sTable = 'subcategories';
				break;
			case 'indexes':
				$this->_sTable = 'indexes';
				break;
			
			default:
				$this->_sTable = 'categories';
				break;
		}

	}

	protected function sQuery() {

		$sQuery = "SELECT * FROM ".$this->$_sTable;
		$sth = self::$_dbh->query($sQuery);
		$this->_iResult = $sth->fetchAll(PDO::FETCH_NUM);

		/* Data set length after filtering */
		$sQuery = 'SELECT FOUND_ROWS()';
		$sth = self::$_dbh->query($sQuery);
		$aResultFilterTotal = $sth->fetchAll(PDO::FETCH_NUM);
		$this->_iFilteredTotal = $aResultFilterTotal[0][0];

		/* Total data set length */
		$sQuery = 'SELECT COUNT(*) FROM '.$this->_sTable;
		$sth = self::$_dbh->query($sQuery);
		$aResultTotal = $sth->fetchAll(PDO::FETCH_NUM);
		$this->_iTotal = $aResultTotal[0][0];
		return $this;

	}

	public function aaData() {
		$output = array(
			"iTotalRecords" => $this->_iTotal,
			"iTotalDisplayRecords" => $this->_iFilteredTotal,
			"aaData" => array()
		);
		return $output;
	}

	public function iResult() {
		return $this->_iResult;
	}

}

?>

Open in new window

This I call with:
$mFetchWhat = 'cat';
$tLocation = 'top';
$dataTable = new MenuBuilder($mFetchWhat, $tLocation, $conn); // $conn is my connection included in another file
$output = $dataTable->aaData();
$iResult = $dataTable->iResult();
$output['aaData'] = $iResult;
echo '<pre>';
echo json_encode($output);
echo '</pre>';

Open in new window


I get null on all outputs. In the original code I've used as an example, the 3rd line into the contructor (line #16 here) is uncommented. In my case, if I uncomment it I get:
Notice: Undefined variable: _sTable in C:\xampp\htdocs\test\functions.php on line 39

Fatal error: Cannot access empty property in C:\xampp\htdocs\test\functions.php on line 39
Any ideas?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40574953
Sorry, I can't test this right now, so this is just a guess.

It looks like the variable $_sTable is unassigned until after the switch statement at line 17.  So the call to sQuery() on line 16 sends PHP to line 35, where it promptly tries to use the undefined variable $_sTable on line 37, hence the error.

I would try moving line 16 down to about line 31, after the switch statement completes.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 6

Author Comment

by:Cesar Aracena
ID: 40575047
Nothing. Even tried taking the switch statement out just to try. It looks like when calling $this->sQuery(); the var _sTable gets emptied. Actually, I don't even know why $this->sQuery(); is called like that. I can't find an explanation in the docs. But if I don't call it, I don't get the error but I have null results.
<?php

class MenuBuilder {

	protected $_sTable;
	protected $_mFetchWhat;
	protected $_iFilteredTotal;
	protected $_iTotal;
	protected $_iResult;
	protected static $_dbh;

	public function __construct($sDatabase, $mFetchWhat) {

		if(!isset(self::$_dbh)) self::$_dbh = $sDatabase;
		$this->_mFetchWhat = $mFetchWhat;
		$this->_sTable = 'categories';

		/*
		switch ($mFetchWhat) {
			case 'cat':
				$this->_sTable = 'categories';
				break;
			case 'subcat':
				$this->_sTable = 'subcategories';
				break;
			case 'indexes':
				$this->_sTable = 'indexes';
				break;
			
			default:
				$this->_sTable = 'categories';
				break;
		}
		*/

		$this->sQuery();
	}

	protected function sQuery() {

		$sQuery = "SELECT * FROM ".$this->$_sTable;
		$sth = self::$_dbh->query($sQuery);
		$this->_iResult = $sth->fetchAll(PDO::FETCH_NUM);

		/* Data set length after filtering */
		$sQuery = 'SELECT FOUND_ROWS()';
		$sth = self::$_dbh->query($sQuery);
		$aResultFilterTotal = $sth->fetchAll(PDO::FETCH_NUM);
		$this->_iFilteredTotal = $aResultFilterTotal[0][0];

		/* Total data set length */
		$sQuery = 'SELECT COUNT(*) FROM '.$this->_sTable;
		$sth = self::$_dbh->query($sQuery);
		$aResultTotal = $sth->fetchAll(PDO::FETCH_NUM);
		$this->_iTotal = $aResultTotal[0][0];
		return $this;

	}

	public function aaData() {
		$output = array(
			"iTotalRecords" => $this->_iTotal,
			"iTotalDisplayRecords" => $this->_iFilteredTotal,
			"aaData" => array()
		);
		return $output;
	}

	public function iResult() {
		return $this->_iResult;
	}

}

?>

Open in new window

0
 
LVL 34

Assisted Solution

by:Slick812
Slick812 earned 250 total points
ID: 40576217
greetings Caracena, , I have read the code and comments here, and I am trying to "Understand" why you have constructed your    "class MenuBuilder"   the way you have? ? You seem to import the actual PDO object from somewhere else, and just store it in static variable. I would think for your own Practice in Coding a PDO startup you would have that code [ new PDO( ) ] in your class?
In your class variables you have all of them as "protected", this is as good as any for the FINAL finished code work on  "class MenuBuilder", but for development , you might can "Check" values (debug) in your code progression, if they are Public variables (properties). As you wonder about the   $this->_sTable    gets emptied, but can not check it because it's protected.
Also, in class variables (properties) I ALWAYS initialize every one like -
public $_sTable='catagory', $_mFetchWhat='cat', $_iFilteredTotal=0, $_iTotal=-1, $_iResult=false;

this can help me if I need to see (at some point) if a value has the correct  amount, without the PHP telling me the value is "undefined". .

You may also want to make ALL of your methods PUBLIC at first, so you can call them, individually and see if the operation of them is correct, or you need to debug it.

you say -  " I don't even know why $this->sQuery(); is called like that",
if this protected function is NOT called in the initializer  __construct( )  method then your "output" values (_iResult, _iFilteredTotal, _iTotal) are Undefined, as in Non-Existent.

As I said before, There are things in this that may cause you not to learn CLASS construction techniques, instead of way to have a CLASS help you code better.

= = = = = = = = =
since you want an output like this -
<ul class="dropdown-menu" aria-labelledby="themes">
<li><a href="#">FIRST SUB-CAT OF FIRST CAT</a></li> // item 1 from 2nd table
<li><a href="#">SECOND SUB-CAT OF FIRST CAT</a></li> // item 2 from 2nd table
</ul>

you should start with a class method that acually Outputs Your goal as a string  or array with these <ul> and <li> stuff, then add a $row array (not from a database, just a practice), and then output the ul, li with that $row array of arrays, THEN when you get that working, you can try and get the $row in a while loop from a PDO query that USE prepare and execute.
0
 
LVL 6

Author Closing Comment

by:Cesar Aracena
ID: 40576632
Thanks for the tips and links. Great to get me going.

@Slick812: You are right. As I mentiones before I grabbed the PDO code from another guy's example and with some reading from the links Ray gave me, I kinda understand where the new OOP system is aimed and how to build it. Of course that class needs a lot of work to fetch different things and show them in different ways (horizontal menu, vertical menu, etc) but it gave me a good starting point on writing my own ;)

Thanks guys :D
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40576635
Thanks for the points and best of luck with it! ~Ray
0
 
LVL 34

Expert Comment

by:Slick812
ID: 40578191
ok, been distracted for a while, I did most of this, right after my last comment, it is a functioning PDO thing got a Menu, I really really do not like the way most use the <ul> <li> for menus, in this I use <div> <em> and <p> instead, but you can see where it writes the menu string in the doMenu( ) method -

<?php 
ini_set('display_errors', 1);
error_reporting(E_ALL);


class MenuBuilder {

  public $mbPDO = false, $bgColor = '#642', $txtColor = '#fff', $width = '5em';

public function __construct() {
  $this->mbPDO = new PDO("mysql:host=localhost;dbname=cats", 'user', 'pass');
  $this->mbPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//set the error reporting attribute
  $this->mbPDO->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);// IMPORTANT set the emulate prepare to FALSE
  }

public function doMenu($tabl, $cID) {
  $stmt = $this->mbPDO->prepare('SELECT subcat_names, subcat_title FROM '.$tabl.' WHERE category_id = ?');
  $stmt->execute(array($cID));
  $row = $stmt->fetch();
  
  if ($row) {
    $menuAry = explode('~', $row['subcat_names']);
    $str = '<div class="nUl"><em>Menu</em>
<div id="mnSub">
';
    for ($i = 0; $i < count($menuAry); ++$i) $str .= '<p>'.$menuAry[$i].'</p>
';
    $str .= '</div>
</div>';
    } else $str = 'Menu Not Available';
  return $str;
  }

public function doCSS() {
return '#mnSub {display:none; position:absolute; top:1.7em; left:2px; width: '.$this->width.';}
.nUl {position: relative; width: '.$this->width.'; margin: 3px; padding: 2px; 
  list-style-type: none; text-align: center;}
.nUl em {display: block;  font-style: normal; font-size: 117%; background: '.$this->bgColor.'; 
  border: 2px solid blue; color: '.$this->txtColor.'; border-radius: 6px;}
.nUl p {background: #2ac; margin: 1px 1px; border: 1px solid green; 
  border-radius: 6px; cursor: pointer;}
.nUl p:hover {background: #ca2;}
.nUl:hover #mnSub {display: block;}';
}

} // END of class MenuBuilder
//    //    //     //     //     //     ///     //     //

$mb = new MenuBuilder();
$mb->bgColor = '#6a2';
$mb->txtColor = '#e9f';
$mb->width = '7.5em';
$mnOut = $mb->doMenu('categories', 'Two');

?><!doctype html><html><head><title>Create PDO Menu Class</title>
<style>
  body {background: #fea;}
<?php echo $mb->doCSS(); ?>
</style></head>
<body><?php echo $mnOut; ?>
<h2>Create PDO Menu Class</h2>
<p>See The PHP built Menu at Upper Left of Page.</p>
<p>Finished</p>
</body></html>

Open in new window

This should be better coded for a finished product, but I hope it shows you some tech for using a Class.

Please note that I have the PDO object to a Public property as  $mb->mbPDO, that way if you ever need to do any PDO outside of the MenuBuild class you can do it like -
$r = $mb->mbPDO->query('SELECT id from table1');

you say little about how you my use this, so the only auxilery method i have is doCSS( ), which is required to add the css for the menu

sorry i is late on this.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question