[RESOLVED] making a report from data in the database

[Desbrina]

I'm looking to make a report very similar to an existing one i've got (except the report is for a single person in the database whereas the new one if for everyone)

There are several tables involved in this (you can find their details in the attachment)
smdbs_members
smdbs_badges
smdbs_member_badge_req
smdbs_badges_requirements

What i'm looking for with this is a report that lists all the badge requirments for badges which are in a particular section (ie 3) and display all the members along the top that are in the same section, and put an x (or similar) where the person has the requirement filled, like in the 1 person example image at the bottom

The code i use for a single person is below if its of any help

$person = $db->sql_escape($_GET['person']);

$psql = "SELECT * FROM " . MEMBERS . " WHERE mem_id = " . $person . " LIMIT 1";    

$presult = $db->sqlQuery($psql);
$prow = $db->sqlFetchRow($presult);

$sql = "SELECT m.first_name, m.last_name, b.name, b.url, b.section AS bsection, m.section AS msection, br.requirement
FROM smdbs_members AS m
LEFT JOIN smdbs_member_badge_req AS r ON r.mem_id = m.mem_id
AND m.mem_id = " . $person . "
RIGHT JOIN smdbs_badges_requirements AS br ON br.rid = r.req_id
LEFT JOIN smdbs_badges AS b ON b.id = br.bid
WHERE b.section
IN ( " . $prow['section'] . ", 6, 7 )
ORDER BY b.challenge DESC , b.altname, br.rid"; 

The sql files can be found at the following location
http://calona.net/stuff/

Heres the current example

[Bjom]

and your question is?

[Desbrina]

i have no idea how to get it layed out how i want

[Desbrina]

ok, i've got it done with this code pretty much, but it takes a long time to load, and if theres a lot of members i get an internal server error since my host is killing it due to the resources it uses

Can anyone help make it more efficient please

$section = $db->sql_escape($_GET['section']);	

$sql = "SELECT *
FROM " . BADGE_REQUIREMENTS . " AS r
LEFT JOIN " . BADGES . " AS b ON b.id = r.bid
WHERE b.section IN (" . $section  . ", 7, 8 )
ORDER BY b.challenge DESC , b.altname, r.rid";
$result = $db->sqlQuery($sql);
while($row = $db->sqlFetchRow($result))
{
	if($badge != $row['name'])
	{
		$reqs[] =  '<tr>
			<td style="height:170px; text-align: center;">
				<img src="'.$row['url'].'" alt="'.$row['name'].'" /><br/>
				<span class="standout">	
					' . $row['name'] . '
				</span>
			</td>
		  </tr>';;
	}

	$reqs[] = '<tr>
	<td style="height:170px">' . $row['requirement'] . '</td>
  </tr>';

  $badge = $row['name'];

}

$psql = "SELECT * FROM " . MEMBERS . " WHERE section = " . $section;	
$presult = $db->sqlQuery($psql);
while($prow = $db->sqlFetchRow($presult))
{	
	$kid[] = '<tr>
				<td style="height:170px">'.$prow['first_name'].'<br/>'.$prow['last_name'].'
				</td>
			  </tr>';

$sql = "SELECT m.first_name, m.last_name, b.name, b.url, b.section AS bsection, m.section AS msection, br.requirement
FROM smdbs_members AS m
LEFT JOIN smdbs_member_badge_req AS r ON r.mem_id = m.mem_id
AND m.mem_id = " . $prow['mem_id'] . "
RIGHT JOIN smdbs_badges_requirements AS br ON br.rid = r.req_id
LEFT JOIN smdbs_badges AS b ON b.id = br.bid
WHERE b.section
IN ( " . $prow['section'] . ", 7, 8 )
ORDER BY b.challenge DESC , b.altname, br.rid";
$result = $db->sqlQuery($sql);
while($row = $db->sqlFetchRow($result))
{
	if($badge != $row['name'])
	{
		$kid[] = '<tr><td style="height:170px"></td></tr>';
	}
	$badge = $row['name'];

	$data = (is_null($row['first_name'])) ? '<tr><td style="height:170px"></td></tr>' : '<tr><td style="height:170px; text-align: center;"><strong>X</strong></td></tr>';

	if(($prow['section'] == 1) && ($user->check_perm($user->data['mem_id'], 45) == 1) && ($user->check_perm($user->data['mem_id'], 10) == 1) )
	{
		$kid[] = $data;
	}
	if(($prow['section'] == 2) && ($user->check_perm($user->data['mem_id'], 45) == 1) && ($user->check_perm($user->data['mem_id'], 14) == 1) )
	{
		$kid[] = $data;
	}
	if(($prow['section'] == 3) && ($user->check_perm($user->data['mem_id'], 45) == 1) && ($user->check_perm($user->data['mem_id'], 18) == 1) )
	{
		$kid[] = $data;
	}
	if(($prow['section'] == 4) && ($user->check_perm($user->data['mem_id'], 45) == 1) && ($user->check_perm($user->data['mem_id'], 48) == 1) )
	{
		$kid[] = $data;
	}
	if(($prow['section'] == 5) && ($user->check_perm($user->data['mem_id'], 45) == 1) && ($user->check_perm($user->data['mem_id'], 6) == 1) )
	{
		$kis[] = $data;
	}
	if(($prow['section'] == 6) && ($user->check_perm($user->data['mem_id'], 45) == 1) && ($user->check_perm($user->data['mem_id'], 2) == 1) )
	{
		$kid[] = $data;
	}
}
$k = implode('' , $kid);
$kids[] = '<table border="0" style="float:left;">' . $k . '</table>';
unset($kid, $k);
}

$reqs = isset($reqs) ? '<table border="0"><tr><td style="height:170px"></td></tr>'.implode("\n" , $reqs).'</table>' : '';
$kids = isset($kids) ? implode('', $kids) : '';

$reqs = strip_tags($reqs, '<table><td><tr><img><a><br/>');

[bradgrafelman]

Some comments regarding the SQL query:

• Don't do SELECT * - always SELECT only the columns that you actually need data from.

• Have you tried adding 'EXPLAIN' before the query to see how the query analyzer is executing the query?

• Why is 'challenge' a VARCHAR column if you only use it to store yes/no values? That's probably about the most ineffecient way I can think of to do so. :p

  Try using a BOOL (which is basically just a TINYINT(1) for now) or even an ENUM type instead.

• That's a lot of ordering you're doing there; if you can't optimize the SQL query/schema enough, consider offloading the ordering work to PHP instead.

[Desbrina]

never mind, solved it. It was hanging on the perm_check function because of the amount of time it took to run. I took it outside of the second while statement so it ran once per user, not 2000+ per user

[dalecosp]

Glad to hear it.

If you would, please consider marking this thread as "RESOLVED". Thankies!