[Bagelwolf]

Hi all, I'm trying to wrap my brain around what is likely a very simple concept. I'm creating a lesson plan builder, and it relies on a series of MySQL queries to output the finished product. In plain terms, I need to list objectives, with teaching models listed therein, and specific strategies listed within the teaching models - so the general look of the data output would be something like this:

<lesson>
    <objective>
        <model>
            <strategy />
            <strategy />
        </model>
        <model>
            <strategy />
            <strategy />
        </model>
    </objective>
    <objective>
        <model>
            <strategy />
            <strategy />
        </model>
        <model>
            <strategy />
            <strategy />
        </model>
    </objective>
</lesson>

Here are my PHP/MySQL statements:

// Get all objectives pertaining to the lesson and their assessments.
$oQuery = "SELECT description, assessment, objective_id FROM objectives WHERE fk_lesson_id = $_GET[lid]";
$oResult = mysqli_query($link, $oQuery);

// Get all strategies pertaining to the objective and their parent models.
$sQuery = "SELECT strategy_name, model_name, fk_objective_id FROM strategies, models, objective_strategies, objectives WHERE objectives.fk_lesson_id = $_GET[lid] AND strategies.strategy_id = objective_strategies.fk_strategy_id AND objective_strategies.fk_objective_id = objectives.objective_id AND strategies.fk_model_id = models.model_id ORDER BY objective_strategies.fk_objective_id AND strategies.fk_model_id ASC";
$sResult = mysqli_query($link, $sQuery);

All is fine so far; I've tested the query, and it works. Now we come to the PHP loops to make it display as noted above:

<label>Learning Objectives:</label>
<ol class="objectives">
<?php while($oRow = mysqli_fetch_array($oResult, MYSQLI_ASSOC)) { ?>
    <li class="objective">
        <label><?php echo $oRow['description']; ?></label>
        <p><label>Assessment Criteria:</label><?php echo $oRow['assessment']; ?></p>
        <div class="inputRow">
            <div class="leftCol">
                <label>Teaching Model<?php if($sCount > 1) { echo 's'; } ?>:</label>
            </div>
            <div class="rightCol">
                <label>Strateg<?php if($sCount > 1) { echo 'ies'; } else { echo 'y'; } ?>:</label>
            </div>
            <ul class="none">
                <?php while($sRow = mysqli_fetch_array($sResult, MYSQLI_BOTH)) {
                    if($sRow['fk_objective_id'] == $oRow['objective_id']) { ?>
                        <li>
                            <div class="leftCol">
                                <?php echo $sRow['model_name']; ?>
                            </div>
                            <div class="rightCol">
                                <?php echo $sRow['strategy_name']; ?>
                            </div>
                        </li>
                <?php } } ?>
            </ul>
        </div>
    </li>
    <?php } ?>
    <div class="clearfix"></div>
</ol>

The objective blocks filter by lesson number and iterate just fine. For some reason, though, only the strategies and models for the first objective display. All other objectives have no models or strategies listed. When I added the comparison of $oRow['objective_id'] to $sRow['fk_objective_id'] I was able to get it to stop listing *all* the strategies in the first objective, but they did not move to their respective associated objectives either.

I would also like to only show each model name once, instead of repeating it for each strategy of that model type. I'm thinking this is where something like array_unique() would come in handy?

(I'm also still trying to figure out the best method for counting the items returned so the labels have proper pluralization when appropriate, but that's a minor issue.)

Any help would be appreciated; thank you!

[aaron1178]

Maybe your sql query can only find on result? do a simple mysqli_num_rows()

[codeprada]

Consider using objects that can manipulate XML. PHP has a few of them built in like DOM and SimpleXML.

Are you trying to create XML or a webpage?

[JackOfAllTrades]

Within the first run through your outer loop you go through all the entries in the second query. You may be able to use mysqli_data_seek to accomplish a reset of the inner result set with each iteration of the outer loop, but I question on the whole whether you're doing this the right way.

[Bagelwolf]

aaron1178, on 16 August 2011 - 11:13 PM, said:

Maybe your sql query can only find on result? do a simple mysqli_num_rows()

Thanks, Aaron. I'm getting multiple rows returned: either all the rows appear in the first objective, regardless of their association to the objective, or only those associated with the first objective.

@ the rest: Thanks for the suggestions. I'm going to see if I can target some objective other than the first in the hope that it sheds some light on how to fix this.

[Bagelwolf]

JackOfAllTrades, on 17 August 2011 - 07:44 AM, said:

Within the first run through your outer loop you go through all the entries in the second query. You may be able to use mysqli_data_seek to accomplish a reset of the inner result set with each iteration of the outer loop, but I question on the whole whether you're doing this the right way.

Turns out you were exactly right: the pointer kept moving and it needed to be reset on each pass. So now that it's working exactly right, can you expound on what makes you question if this is the proper way to handle this kind of a data loop?

[JackOfAllTrades]

Not sure, but I want to say that if you post your schema, we may be able to come up with a single query using joins to accomplish what you're seeking. That, or we can come up with a more appropriate schema for your DB that will help.