Solved

PHP recursive functions and class with PDO?

Posted on 2015-01-26
8
451 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:Caracena
  • 3
  • 3
  • 2
8 Comments
 
LVL 108

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:Caracena
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 108

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
 
LVL 6

Author Comment

by:Caracena
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 33

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:Caracena
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 108

Expert Comment

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now