Start Free Trial

asked on

One to many records inside of accordion

I am trying to group records by a specific field (parent) and display the child records inside of an accordion for that parent record. I first tried to use GROUP BY but that only returned one child record. Also, as the accordions work with id numbers, I have to increment that id for each accordion that is displayed.

I changed my SQL to ORDER BY instead of GROUP BY but now it obviously doesn't group them.

SELECT `edition`, `rep`, `rep_name`, `serial` FROM `vouchers` as `v`
INNER JOIN `reps` as `r` ON r.`rep_id` = v.`rep`
WHERE `sp_id` = :sp_id ORDER BY `edition`

Open in new window

I managed to get the accordion ID bit working but can't figure out how to get it to show the $value->edition (parent) as the accordion title and the $value->serial (child) records inside the accordion.

<div class="m-accordion m-accordion--bordered" id="m_accordion_2" role="tablist">
            <?php 
                $i = 0;
                    foreach ($data['voucher_history'] as $history=>$value):
                $i++;
            ?>
    <div class="m-accordion__item">
        <div class="m-accordion__item-head collapsed" role="tab" id="m_accordion_2_item_1_head" data-toggle="collapse" href="#m_accordion_2_item_<?php echo $i; ?>_body" aria-expanded="    false">
            <span class="m-accordion__item-icon">
                <i class="fa flaticon-user-ok"></i>
            </span>
            <span class="m-accordion__item-title">
                <?php echo sanitize($value->edition) . ' - ' . $value->rep_name; ?> 
            </span>
            <span class="m-accordion__item-mode"></span>
        </div>
        <div class="m-accordion__item-body collapse" id="m_accordion_2_item_<?php echo $i; ?>_body" class=" " role="tabpanel" aria-labelledby="m_accordion_2_item_2_head" data-parent="#m_accordion_2">
            <div class="m-accordion__item-content">
                <p>
                    <?php echo sanitize($value->serial); ?>
                </p>
            </div>
        </div>
    </div>
    <?php endforeach; ?>
</div>

Open in new window

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks, ste5an. Makes more sense to use the record id for the accordion if I understand you correctly. Changing the database query did not solve my problem though. It is showing every parent record with one child record inside it instead of one parent record with many child records?

oops. my fault. I forgot the key part: You need two loops. One for the heads, one for the content. This means that you need a do/while loop to flatten them. E.g. (just an outline)

<div class="m-accordion m-accordion--bordered" id="m_accordion_2" role="tablist">
<?php
    $i=0;
    while (true) {
        \\read row
        \\when row different edition than previous output header
?>
    <div class="m-accordion__item">
        <div class="m-accordion__item-head collapsed" role="tab" id="m_accordion_2_item_1_head" data-toggle="collapse" href="#m_accordion_2_item_<?php echo $i; ?>_body" aria-expanded="    false">
            <span class="m-accordion__item-title">
                <?php echo sanitize($value->edition) . ' - ' . $value->rep_name; ?>
            </span>
        </div>
        <div class="m-accordion__item-body collapse" id="m_accordion_2_item_<?php echo $value->edition_id; ?>-<?php echo $value->rep_id ?>_body" role="tabpanel" aria-labelledby="m_accordion_2_item_2_head" data-parent="#m_accordion_2">
            <div class="m-accordion__item-content">

        \\otherwise only the current value
                <p>
                    <?php echo sanitize($value->serial); ?>
                </p>



            </div>
        </div>
    </div>
<?php
    }
?>
</div>

Open in new window

No time right now, sorry.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi Chris, I am using PDO and like that idea but I have a database class ( I hear some people groaning, :-) and not sure if it will work.

<?php


class Database {
	
	private $host = DB_HOST;
	private $user = DB_USER;
	private $pass = DB_PASS;
	private $dbname = DB_NAME;
	
	private $dbh;
	private $stmt;
	private $error;
	
	public function __construct() {
		$dsn = "mysql:host=$this->host;dbname=$this->dbname";
		$options = array(
			PDO::ATTR_PERSISTENT => true,
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
		);
		
		try {
			$this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
			
		} catch(PDOexception $e) {
			$this->error = $e->getMessage();
			echo $this->error;
		}
	}
	
	// Prepare statement with query
	public function query($sql) {
		$this->stmt = $this->dbh->prepare($sql);
	}
	
	// Bind values (added & before $value)
	public function bind($param, &$value, $type = null) {
		if(is_null($type)) {
			switch(true) {
				case is_int($value):
					$type = PDO::PARAM_INT;
					break;
				case is_bool($value):
					$type = PDO::PARAM_BOOL;
					break;
				case is_null($value):
					$type = PDO::PARAM_NULL;
					break;
				default:
					$type = PDO::PARAM_STR;
			}
		}
		
//		$this->stmt->bindValue($param, $value, $type);
		$this->stmt->bindParam($param, $value, $type);
	}
	
	// Execute the prepared statement
	public function execute() {
		return $this->stmt->execute();
	}
	
	// Get result set as array object
	public function resultSet() {
		$this->execute();
		return $this->stmt->fetchAll(PDO::FETCH_OBJ);
	}
	
	// Get single record as object
	public function single() {
		$this->execute();
		return $this->stmt->fetch(PDO::FETCH_OBJ);
	}
	
	// Get row count
	public function rowCount() {
		return $this->stmt->rowCount();
	}
	
	// Get last id
	public function lastId() {
		return $this->dbh->lastInsertId();
	}
	
	public function beginTransaction() {
		return $this->dbh->beginTransaction();
	}
	
	public function rollBack() {
		return $this->dbh->rollBack();
	}
	
	public function commit() {
		return $this->dbh->commit();
	}
}

Open in new window

ASKER

Hmm. But I may be able to add FETCH_GROUP to the database class like these

// Get result set as array object
	public function resultSet() {
		$this->execute();
		return $this->stmt->fetchAll(PDO::FETCH_OBJ);
	}
	
	// Get single record as object
	public function single() {
		$this->execute();
		return $this->stmt->fetch(PDO::FETCH_OBJ);
	}

Open in new window

ASKER

Something like this?

	public function resultArray() {
		$this->execute();
		return $this->stmt->fetchAll(PDO::FETCH_GROUP)
	}

Open in new window

ASKER

Almost there (I think). It is showing me the parent record but gives this error for the child records:

Trying to get property of non-object in

haha - nothing wrong with a DB class :)

Couple of ways you could do it. Your suggestion is probably the cleanest, as it keeps everything encapsulated in your class - probably name it something like groupedResult().

You do have this method on your class:

public function execute() {
    return $this->stmt->execute();
}

Open in new window

So technically you could also just use that:

$result = $myDbClass->execute();
$grouped = $result->fetchAll(PDO::FETCH_GROUP)

You'd be using PDO outside of your class which probably feels a little 'off' but you do have the option.

ASKER

Just so we are on the same page, I am using Ste5an's query:

		$this->db->query("SELECT `edition`, `rep_name`, `serial` FROM `vouchers` AS `v`
		INNER JOIN `reps` AS `r` ON r.`rep_id` = v.`rep`
		WHERE `sp_id` = :sp_id 
		GROUP BY `edition`, `rep_name`, `serial`
		ORDER BY `edition`, `rep_name`, `serial`");
		$this->db->bind(":sp_id", $_SESSION['sp_id']);
		$results = $this->db->resultArray();
		return $results;

Open in new window

Ahh. This error:

Trying to get property of non-object in

is because in my code examples, I always use Objects. Your queries are probably returning Arrays, so you'd need to change it accordingly:

foreach ($groupChildren as $child):

        // echo $child->edition;
        // echo $child->rep_name;
        echo $child['edition'];
        echo $child['rep_name'];

    endforeach;

Open in new window

As you're clearly well versed in OOP, I would suggest you might start using PDO with Objects instead of arrays - it just feels much nicer (classes / exceptions / properties etc). To do so, just configure your connection options like so:

$options = array(
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
);

Open in new window

ASKER

Thanks Chris, that worked! Good point regarding objects. I will definitely look into that. Thanks again, this problem was doing my head in!

OK. With Ste5an's query, you're effectively flattening the records to edition, rep_name and serial - 1 record for each unique combination. I can't quite see from that which is your parent and which are your children.

If you run that query with the group by, you will get the edition as the parent, and the rep_name and serial as the children. Not sure if that's what you intend.

All good. Sorry - cross-posted.

Glad it all makes sense, and pleased it's working for you :)

ASKER

Actually wanted edition and rep as parents and serial as children but I will play around with the query now...

ASKER

Hmm, I was a bit of an eager beaver there because now I want to show edition and rep name together in one line as the parent and the serial as children.. Not as easy as I thought!

ASKER

Going to have to ask a related question for this...

Hey Black Sulphur,

OK. The PDO GROUP option will only group by the first column in your query, so it may be that it's not suitable in this instance. In which case, you'll need to employ my second suggestion, which was to build a new grouped array from your Data.

To do this, you need to uniquely identify your 'parent' group with some kind of index. The easiest is to just concat the parent fields - edition and rep. Have a look at this and see if it make sense:

$groupedData = [];

while ($record = $this->stmt->fetch()):
    $key = $record->edition . $record->rep;
    $groupedData[$key]['header'] = $record;
    $groupedData[$key]['serials'][] = $record->serial;

endwhile;

Open in new window

Now you will have an array that you can loop over:

foreach ($groupedData as $group):

    echo $group['header']->edition;
    echo $group['header']->rep;

    foreach ($group['serials'] as $serial):
        echo $serial;
    endforeach;

endforeach;

Open in new window

ASKER

Sorry, we posted at the same time. Will check your answer now..

ASKER

I am just confused about how to implement this part as my design is using MVC.

$groupedData = [];

while ($record = $this->stmt->fetch()):
    $key = $record->edition . $record->rep;
    $groupedData[$key]['header'] = $record;
    $groupedData[$key]['serials'][] = $record->serial;

endwhile;

Open in new window

The controller has the results of the DB query in it:

$voucher_history = $this->SpModel->voucherHistory();

Open in new window

And I am then sending that to the view in an array called $data:

$data = [
			
'voucher_history' => $voucher_history

];

$this->view('sprovider/index', $data);

Open in new window

How I would normally access that in the view is to do this:

foreach ($data['voucher_history'] as $history) {

Open in new window

ASKER

Actually, this seems to work correctly as far as I can tell.

<?php 
		$groupedData = [];

		foreach($data['voucher_history'] as $record):
			$key = $record->edition . $record->rep;
			$groupedData[$key]['header'] = $record;
			$groupedData[$key]['serials'][] = $record->serial;

		endforeach;

		foreach ($groupedData as $group):

		echo $group['header']->edition;
		echo $group['header']->rep;

		foreach ($group['serials'] as $serial):
			echo $serial . '<br>';
		endforeach;

	endforeach;
?>

Open in new window

Does the code look okay?

OK. Not sure how you have your Models and Controllers setup, but what you need to do is retreive the results from the DB (from Ste5an's query) and then build the new array from that. It probably belongs in the Model layer, but could also fit in the Controller, depending on your setup. Pass that new array into your view:

// Model
function groupedVoucherHistory() {
    $groupedData = [];
    $dbResults = [get data from db here];

    foreach ($dbResults as $record):

        $key = $record->edition . $record->rep;
        $groupedData[$key]['header'] = $record;
        $groupedData[$key]['serials'][] = $record->serial;

    endforeach;

    return $groupedData;
}

Open in new window

And then in your controller, set the data to $this->SpModel->voucherHistory();, pass it to the view, do the double loop:

foreach ($data['voucher_history'] as $group):

    echo $group['header']->edition;
    echo $group['header']->rep;

    foreach ($group['serials'] as $serial):
        echo $serial;
    endforeach;

endforeach;

Open in new window

Oops - cross-posting again.

Yeah - your code for the view looks good. You're basically building the new grouped array within the view, which is fine. It's really up to you whether you do this at a higher level and pass the prepared data back to the view.

Again, a lot of this is realy a design choice about where your logic should go, and can sometimes be subjective. If you're happy rebuilding the data in the view, then go for it :)

ASKER

Okay, great. I will try work it back to the controller/model. Just dumped it all in the view to try get it to work for now as is easier. Sorry for stretching the question out after accepting an answer. Thanks for your patience and all your help, it is much appreciated!

No worries :)

ASKER

PS:

Did what you said and put this in the model:

// Model
function groupedVoucherHistory() {
    $groupedData = [];
    $dbResults = [get data from db here];

    foreach ($dbResults as $record):

        $key = $record->edition . $record->rep;
        $groupedData[$key]['header'] = $record;
        $groupedData[$key]['serials'][] = $record->serial;

    endforeach;

    return $groupedData;
}

Open in new window

Got it to work :)

Lovely job. I do like to see a well structured approach to development. Kudos for using PDO too ;)