Page 1 of 1

query not displaying all results

Posted: Fri Dec 20, 2013 5:55 pm
by mricketts
I have a database with 3 tables. One table contains people, the other table contains categories and the third table is the many to many relationship table. My php file is a report showing all the people in each category. For some reason, my query is not displaying the first record in each category and I can't figure out how to fix it.

Code: Select all

<?php require_once('Connections/meritBadges.php');
$result = mysql_query('SELECT id, badge_name FROM meritbadgestable ORDER BY badge_name');

while ($row = mysql_fetch_array($result)) {
	$badgenumber = $row['id'];


$badgename = mysql_query("SELECT id, badge_name FROM meritbadgestable WHERE id=$badgenumber");
while($badge = mysql_fetch_array($badgename))

  { $badge_name = $badge['badge_name'];
  }


$counselors = mysql_query("SELECT id, firstname, lastname, counselorid, meritbadgeid FROM counselorstable, meritbadgetocounselortable WHERE id=counselorid AND meritbadgeid='$badgenumber' ORDER BY lastname");

$counselor = mysql_fetch_array($counselors);

if(empty($counselor)){echo '<h2>Merit Badge: ' . $badge_name . '</h2><p class="counselortable">There are no counselors for this merit badge.</p>';
}
	else {echo '<h2>Merit Badge: ' . $badge_name . '</h2>

	<table width="950" border=1 cellpadding="2" cellspacing="0" class="counselorstable">
  	<tr id="tabletop">
    	<td width="162" bgcolor="#000000">ID</td>
	    <td width="160" bgcolor="#000000">Name</td>
	</tr>';}

while ($counselor = mysql_fetch_array($counselors)) {
  echo "<tr valign='top'>\n";
  $id = htmlspecialchars($counselor['id']);
  $firstname = htmlspecialchars($counselor['firstname']);
  $lastname = htmlspecialchars($counselor['lastname']);

  echo "<td>$id</td>\n";
  echo "<td>$firstname $lastname</td>\n";
  echo "</tr>\n";
}

  echo '</table><br />';


}
?>


Re: query not displaying all results

Posted: Fri Dec 20, 2013 7:44 pm
by Celauran

Code: Select all

$result = mysql_query('SELECT id, badge_name FROM meritbadgestable ORDER BY badge_name');
while ($row = mysql_fetch_array($result)) {
	$badgenumber = $row['id'];
	$badgename = mysql_query("SELECT id, badge_name FROM meritbadgestable WHERE id=$badgenumber");
You're running the exact same query to get information you already have. Also, if you find yourself running a query inside of a loop, you're probably doing it wrong. Stop and look at what you're trying to accomplish and see if maybe there isn't a better way.

Maybe something like this?

Code: Select all

SELECT m.id AS badge_id, m.badge_name
	c.id AS counselor_id, c.firstname, c.lastname
	j.counselorid, j.meritbadgeid
FROM meritbadgestable AS m
JOIN meritbadgetocounselortable AS j ON m.id = j.meritbadgeid
JOIN counselorstable AS c ON c.id = j.counselorid
Lastly, don't use the mysql extension. It has been deprecated and will be removed. Spend a little time looking at PDO instead.