Below you will find my code, it's not it all in it's entirety, but shows what's important. I have some custom functions that are being used just to note out. What I have my code doing is searching a MySQL table and displaying all of the results based on the current session user id. Then I loop through those results and for each one I search another table. This works perfectly, but I just want to see if there is a better way to do this without having to loop and search a table for every result row.

Code: http://pastie.org/5191376

Member Avatar for diafol

Is there a reason you did a pastie? Could you not post your code here? It's really awkward to try and provide an answer with the code on a different site.
Have a look at JOINS syntax for MySQL. That should avoid running queries within a loop. Too awkward for me to say anymore until the code appears here.

Pastie has nice syntax highlighting

<?php
$table_sql = "SELECT * FROM participation WHERE shop_id = '{$session->user_id}'";
$result = $database->query($table_sql);
$participation = array();
while ($row = $database->fetch_array($result)) {
    $participation[] = array (
        'shop_id'           => $row['shop_id'],
        'qpon_id'           => $row['qpon_id'],
        'distributor_id'    => $row['distributor_id']
        );
}
$qpons = array();
if (isset ($participation)) {
    if (!empty ($participation)) {
        foreach ($participation as $qpon) {
            $table_sql = "SELECT * FROM qpons WHERE id = '{$qpon['qpon_id']}'";
            $result = $database->query($table_sql);

            while ($row = $database->fetch_array($result)) {
                $qpons[] = array (
                    'id'            => $row['id'],
                    'service'       => $row['service'],
                    'start_date'    => $row['start_date'],
                    'end_date'      => $row['end_date']
                    );
            }
        }
    }
}


if (isset ($qpons)) {
if (!empty ($qpons)) {
foreach ($qpons as $value) {

echo '<tr>';
echo '<td>' . $value['service'] . '</td>';
echo '<td>' . $value['start_date'] . '</td>';
echo '<td>' . $value['end_date'] . '</td>';
echo '<td>' . $value['id'] . '</td>';
echo '<td class="action_icon"><a href="admin-edit.php?id=' . $value['id'] . '"><img src="images/icons/view.png" alt="View"></a></td>';
echo '</tr>';

}

} else {
echo '<tr>';
echo '<td>No Results Found!</td>';
echo '<td></td>';
echo '<td></td>';
echo '<td></td>';
echo '<td></td>';
echo '<td></td>';
echo '<td></td>';
echo '</tr>';
}

}
?>

JOIN worked, kind sir! Thank you

Member Avatar for diafol

OK, mark as solved

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.