Page 1 of 1

MySQL Table Results to Process Another Table Results

Posted: Mon Nov 05, 2012 1:51 pm
by amie
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: Select all


<?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>';
}

}
?>

Re: MySQL Table Results to Process Another Table Results

Posted: Tue Nov 06, 2012 4:45 am
by Mordred
1. Subqueries:

SELECT * FROM qpons WHERE id IN (SELECT qpon_id FROM participation WHERE shop_id='{$session->user_id}')

2. Select only the columns you need:

SELECT id, service, start_date, end_date FROM ...
and then
while ($row = fetch_array) $qpons[] =$row; //no need for a copy

3. After $qpons = array() there's no need to check isset($qpons), it IS set

4. You can check for two things at once:

if (isset ($qpons)) {
if (!empty ($qpons)) {

Can be

if (isset($qpons) && !empty($qpons))

5. Always escape (or convert to int where applicable) before injecting variables in a query. Read the article on SQL injection from my sig.