Mysql sorting, simple question. URGENT!

[Designer_101]

Hey
I host a website for a sports team, theres a nominations page where each member, when logged in, can cast a vote to whoever in the team they want to be captain for a tournament.

The Problem is when someone votes i store the id, name and there vote (nothing complicated), however when i want to retreive the data im using this.

if ($_SESSION['loggedin'] == true) {
$login_test = true;
$getvotes = "SELECT vote FROM `votes` where vote=1";
$collect = mysql_query($getvotes) or die (mysql_error());
$one = mysql_num_rows($collect);

$getvotes = "SELECT vote FROM `votes` where vote=2";
$collect = mysql_query($getvotes) or die (mysql_error());
$two = mysql_num_rows($collect);

$getvotes = "SELECT vote FROM `votes` where vote=3";
$collect = mysql_query($getvotes) or die (mysql_error());
$three = mysql_num_rows($collect);

$getvotes = "SELECT vote FROM `votes` where vote=4";
$collect = mysql_query($getvotes) or die (mysql_error());
$four = mysql_num_rows($collect);

}

the variable $one, $two ect.. are just the user id of the person being voted for.
This works fine however, i need to add 32 more candidates. This amount of query would take forever.

Is there any way i can collect it as an array the then sort it.
any help would be greatly appreciated

Thanks

[architact]

Hey
I host a website for a sports team, theres a nominations page where each member, when logged in, can cast a vote to whoever in the team they want to be captain for a tournament.

The Problem is when someone votes i store the id, name and there vote (nothing complicated), however when i want to retreive the data im using this.

if ($_SESSION['loggedin'] == true) {
$login_test = true;
$getvotes = "SELECT vote FROM `votes` where vote=1";
$collect = mysql_query($getvotes) or die (mysql_error());
$one = mysql_num_rows($collect);

$getvotes = "SELECT vote FROM `votes` where vote=2";
$collect = mysql_query($getvotes) or die (mysql_error());
$two = mysql_num_rows($collect);

$getvotes = "SELECT vote FROM `votes` where vote=3";
$collect = mysql_query($getvotes) or die (mysql_error());
$three = mysql_num_rows($collect);

$getvotes = "SELECT vote FROM `votes` where vote=4";
$collect = mysql_query($getvotes) or die (mysql_error());
$four = mysql_num_rows($collect);

}

the variable $one, $two ect.. are just the user id of the person being voted for.
This works fine however, i need to add 32 more candidates. This amount of query would take forever.

Is there any way i can collect it as an array the then sort it.
any help would be greatly appreciated

Thanks

try this

if ($_SESSION['loggedin'] == true) {
$login_test = true;
$getvotes = "SELECT vote FROM `votes` ORDER by vote ASC";
$collect = mysql_query($getvotes) or die (mysql_error());
$one = mysql_num_rows($collect);

[Designer_101]

hey

thanks for the reply

if ($_SESSION == true) {
$login_test = true;
$getvotes = "SELECT vote FROM `votes` ORDER by vote ASC";
$collect = mysql_query($getvotes) or die (mysql_error());
$one = mysql_num_rows($collect);

wouldnt that just collect all the votes in ascending order and store how many votes have been taken in the $one.

Or am i mistaken?

Help is really appreciated
Thanks

[R0bb0b]

hey

thanks for the reply

wouldnt that just collect all the votes in ascending order and store how many votes have been taken in the $one.

Or am i mistaken?

Help is really appreciated
Thanks

maybe this is what you are looking for?
This will pull the total votes for each distinct vote.

$getvotes = "SELECT count(vote) as total, vote FROM `votes` group by vote order by vote";

[Designer_101]

hey
thanks loads

you really saved me there

thanks again
;D

[Designer_101]

hey again
erm.....

any ideas on how i would store each individual sum (11 votes for member number 2) in a variable ($two = 11).

Really need help with this one.

Thanks in advanced.

[R0bb0b]

hey again
erm.....

any ideas on how i would store each individual sum (11 votes for member number 2) in a variable ($two = 11).

Really need help with this one.

Thanks in advanced.

This should do it

<?
$query = "SELECT count(vote) as total, vote FROM `votes` group by vote order by vote";
$result = mysql_query($query);
$total = mysql_num_rows($result);

$variablekeys = array();
$variablekeys[] = "zero";
$variablekeys[] = "one";
$variablekeys[] = "two";
$variablekeys[] = "three";
$variablekeys[] = "four";
$variablekeys[] = "five";
$variablekeys[] = "six";
$variablekeys[] = "seven";
$variablekeys[] = "eight";
$variablekeys[] = "nine";
$variablekeys[] = "ten";
$variablekeys[] = "eleven";
$variablekeys[] = "twelve";
$variablekeys[] = "thirteen";
$variablekeys[] = "fourteen";
$variablekeys[] = "fifteen";
$variablekeys[] = "sixteen";
$variablekeys[] = "seventeen";
$variablekeys[] = "eightteen";
$variablekeys[] = "nineteen";
$variablekeys[] = "twenty";

for($i = 0; $i < $total; $i++)
{
	$key = mysql_result($result, $i, "vote");
	$value = mysql_result($result, $i, "total");
	
	$$variablekeys[$key] = $value;
}
?>

[Designer_101]

heya

thanks for the reply, that seems like what iv been looking for, will it work if my database is numeric (member '2' instead of 'two').

And could you explain a bit more into what its actually doing.

Again thankyou so much, i would be helpless without you all. Especially Rob

Thanks again

[R0bb0b]

OK, the following code is, probably as you already know, brings the data down from the database and adds the total number of rows of data brought down into the variable $total.

$query = "SELECT count(vote) as total, vote FROM `votes` group by vote order by vote";
$result = mysql_query($query);
$total = mysql_num_rows($result);

Next we are creating an array where the key == to its English representation(0 = "zero", 1 = "one" etc...). There may be a better way to do this, but I don't know what it might be.

$variablekeys = array();
$variablekeys[] = "zero";
$variablekeys[] = "one";
$variablekeys[] = "two";
$variablekeys[] = "three";
$variablekeys[] = "four";
$variablekeys[] = "five";
$variablekeys[] = "six";
$variablekeys[] = "seven";
$variablekeys[] = "eight";
$variablekeys[] = "nine";
$variablekeys[] = "ten";
$variablekeys[] = "eleven";
$variablekeys[] = "twelve";
$variablekeys[] = "thirteen";
$variablekeys[] = "fourteen";
$variablekeys[] = "fifteen";
$variablekeys[] = "sixteen";
$variablekeys[] = "seventeen";
$variablekeys[] = "eightteen";
$variablekeys[] = "nineteen";
$variablekeys[] = "twenty";

The next bit of code involves using this array and the values pulled from the database to create what is called a variable variable. The following code loops through each row of data and does the following with each row:
1: assign vote to $key
2: assign total number of votes to $value
3: using "vote" from the database in conjunction with our array make a variable named $variablekeys[$key] with the value of $value, meaning take the value of the associated cell of the array("zero", "one", "two" etc...) and create a variable with the vale that associates with the column "vote" from the database and assign the column "total" to the value of that variable.

for($i = 0; $i < $total; $i++)
{
	$key = mysql_result($result, $i, "vote");
	$value = mysql_result($result, $i, "total");
	
	$$variablekeys[$key] = $value;
}

I'm probably not the best teacher but, does that help?

[Designer_101]

Yes it does

thats fantstic
thankyou so much
i understand it all!

Thanks again

[Designer_101]

Hey
Your advice was extreamly helpfull!
It made me think about it loads more and i thought about a much simpler way from the information you gave me.

$query = "SELECT count(vote) as total, vote FROM `votes` group by vote order by vote";
$result = mysql_query($query);
$total = mysql_num_rows($result);

$one = mysql_result($result, 0);
$two = mysql_result($result, 1);
$three = mysql_result($result, 2);
$four = mysql_result($result, 3);

And thats all i needed. But i understood where you were coming from and its taught me alot.

People like you are what make daniweb such a reliable and fantastic way to find a way around problems. Thankyou so much