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

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);

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

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";

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.

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

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

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?

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

This question has already been answered. Start a new discussion instead.