Sorting a script

[civic]

I have a problem. My script outputs what I need but I want it to sort by DESC active recruits. Does anyone know how or where to put that code in? Thank you!

 

	function recruiters() {		

	global $skin, $members, $db;

	$users = $members->fetch_rows();
	$data = '';

	foreach($users as $user) {

		$total_recruits = $db->result($db->query('SELECT COUNT(*) FROM `members` WHERE `recruiter` = "' . $user['id'] . '"'));
		$active_recruits = $db->result($db->query('SELECT COUNT(*) FROM `members` WHERE `recruiter` = "' . $user['id'] . '" AND `status` <> (`status` | 64 )'));

		$ratio = $active_recruits / $total_recruits;

		if($total_recruits > 0 and $ratio > 0) {

			$total_recruits = number_format($total_recruits, 0, '.', ',') . '%';
			$active_recruits = number_format($active_recruits, 0, '.', ',');
			$ratio = number_format($ratio * 100, 2, '.', ',');

			$data .= '<tr><td class="normal">' . $members->link($user['id']) . '</td><td class="normal">' . $total_recruits . '</td><td class="normal">' . $active_recruits . '</td><td class="normal">' . $ratio . '</td></tr>';

		}
  
	}

	return $skin->form_start('Top Recruiters') .
	'<table style="width:400px"><tr><td class="normal"><b>Member</b></td><td class="normal"><b>Total Recruits</b></td><td class="normal">Active Recruits</td><td class="normal">Percentage Active</td></tr>'
	. $data		
	. '</table>' . $skin->form_end();

}

}

[p2grace]

Try it in both queries

 

<?php
function recruiters() {		

	global $skin, $members, $db;

	$users = $members->fetch_rows();
	$data = '';

	foreach($users as $user) {

		$total_recruits = $db->result($db->query('SELECT COUNT(*) FROM `members` WHERE `recruiter` = "' . $user['id'] . '" ORDER BY `recruiter` DESC'));
		$active_recruits = $db->result($db->query('SELECT COUNT(*) FROM `members` WHERE `recruiter` = "' . $user['id'] . '" AND `status` <> (`status` | 64 ) ORDER BY `recruiter` DESC') );

		$ratio = $active_recruits / $total_recruits;

		if($total_recruits > 0 and $ratio > 0) {

			$total_recruits = number_format($total_recruits, 0, '.', ',') . '%';
			$active_recruits = number_format($active_recruits, 0, '.', ',');
			$ratio = number_format($ratio * 100, 2, '.', ',');

			$data .= '<tr><td class="normal">' . $members->link($user['id']) . '</td><td class="normal">' . $total_recruits . '</td><td class="normal">' . $active_recruits . '</td><td class="normal">' . $ratio . '</td></tr>';

		}
  
	}

	return $skin->form_start('Top Recruiters') .
	'<table style="width:400px"><tr><td class="normal"><b>Member</b></td><td class="normal"><b>Total Recruits</b></td><td class="normal">Active Recruits</td><td class="normal">Percentage Active</td></tr>'
	. $data		
	. '</table>' . $skin->form_end();

}

}
?>

[civic]

What do you mean? Those 2 queries go with the script...

[BlueSkyIS]

did you take notice of the changes posted by p2grace?

[civic]

You notice there are no changes?

[civic]

Any help please?

[peranha]

Yes there are changes, P2grace added DESC at the end of the queries.  Try what he suggested, it should work.

[Barand]

Yes there are changes, P2grace added DESC at the end of the queries.  Try what he suggested, it should work.

 

I doubt it.

Each of those queries returns a single row with a single value.

[pre]

+---------------+          +---------------+

| COUNT(*)  ASC |          | COUNT(*) DESC |

+---------------+          +---------------+

|      50      |          |      50      |

+---------------+          +---------------+[/pre]

 

You would need a single query with a join with the member table for the ORDER BY to make any difference

 

eg (assumes your $members query, not shown, pulled rows from same member table)

 

SELECT a.name,
COUNT(b.recruiter) as total_recruits,
SUM (IF(b.`status` <> (b.`status` | 64 ), 1, 0)) as active_recruits
FROM members a
LEFT JOIN members b ON a.id = b.recruiter
ORDER BY active_recruits DESC

[peranha]

I have a problem. My script outputs what I need but I want it to sort by DESC active recruits. Does anyone know how or where to put that code in? Thank you!

 

 

This is his first post, he says it outputs what he needs, he just wants it in DESC order.  Which is what P2grace did.

[Barand]

P2grace added "ORDER BY DESC" to a queries returning a single value. Where's the point in that? You can see the difference it makes with the two result sets below

 

Each of those queries returns a single row with a single value.

 

[pre]

+---------------+          +---------------+

| COUNT(*)  ASC |          | COUNT(*) DESC |

+---------------+          +---------------+

|      50      |          |      50      |

+---------------+          +---------------+[/pre]

[civic]

So what would I need to replace?

[Barand]

your queries with mine.

[civic]

Appreciate the help, but which part do I need to take out, and put yours in?

[civic]

*BUMP* any help?

[Barand]

Forgot the GROUP BY  bit

SELECT a.name,
COUNT(b.recruiter) as total_recruits,
SUM (IF(b.`status` <> (b.`status` | 64 ), 1, 0)) as active_recruits
FROM members a
LEFT JOIN members b ON a.id = b.recruiter
GROUP BY a.name
ORDER BY active_recruits DESC

 

which will give a row for each member with their total_recruits and total_active recruits, sorted by by descending number of active recruits, so it should replace your foreach loop and the two queries inside it.

[civic]

K well when I put that in I put it in as:

 

function recruiters() {		

	global $skin, $members, $db;



	$users = $members->fetch_rows();
	$data = '';
$db->query('SELECT a.name,
COUNT(b.recruiter) as total_recruits,
SUM (IF(b.`status` <> (b.`status` | 64 ), 1, 0)) as active_recruits
FROM members a
LEFT JOIN members b ON a.id = b.recruiter
GROUP BY a.name
ORDER BY active_recruits DESC');

		$ratio = $active_recruits / $total_recruits;

		if($total_recruits > 0 and $ratio > 0) {

			$total_recruits = number_format($total_recruits, 0, '.', ',') . '%';
			$active_recruits = number_format($active_recruits, 0, '.', ',');
			$ratio = number_format($ratio * 100, 2, '.', ',');

			$data .= '<tr><td class="normal">' . $members->link($user['id']) . '</td><td class="normal">' . $total_recruits . '</td><td class="normal">' . $active_recruits . '</td><td class="normal">' . $ratio . '</td></tr>';


  
	}

	return $skin->form_start('Top Recruiters') .
	'<table style="width:400px"><tr><td class="normal"><b>Member</b></td><td class="normal"><b>Total Recruits</b></td><td class="normal">Active Recruits</td><td class="normal">Percentage Active</td></tr>'
	. $data		
	. '</table>' . $skin->form_end();

}

}

 

But now it won't pull anything, it is just blank with the table, is there anything I have to replace for you a.name and b.recruiter etc...? where did you get that from?

[Barand]

table aliases

 

FROM members a

LEFT JOIN members b

[civic]

What table aliases? In the script it is pulling info from

$total_recruits = $db->result($db->query('SELECT COUNT(*) FROM `members` WHERE `recruiter` = "' . $user['id'] . '" ORDER BY `recruiter` DESC'));
		$active_recruits = $db->result($db->query('SELECT COUNT(*) FROM `members` WHERE `recruiter` = "' . $user['id'] . '" AND `status` <> (`status` | 64 ) ORDER BY `recruiter` DESC') );

 

Also in the code it looks for the $active_recruits and $total_recruits which is not in your query...

[Barand]

I apologise if the concept is too advanced, but that is a query that cannot be done without table aliases since there is one physical members table that needs to be treated as two logical tables.

 

Also in the code it looks for the $active_recruits and $total_recruits which is not in your query...

They are if you bother to look

[civic]

I posted here because I'm not that great at PHP. So YES it is complicated. It seems as if you threw a query up there and said hey! you need array's too. Well, what are arrays? What do I put in as a and b? Not everyone is PHP experts. Thanks!!!