Top 10 from database

[Streakerstyle]

Hello,

 

i'm trying to make a "top 10 voters" page,

the only thing is, i'm quite new with the use of php in combination with sql.

I've searched online a lot after i've figured out how to get, and store the username in the database,

and if it already existed it adds 1 to "votes".

 

Now i want to use that data to create a top 10 voters page.

The only problem, i can't really find a tutorial for making a top 10.

Maybe someone here could help me out with it?

 

I've started writing the code a bit,

but i got stuck between the query and fetching the data and looping it.

The error i get:

 

Warning: mysqli_errno() expects parameter 1 to be mysqli, boolean given in ....

 

The full code as it is now:

<?
/* MYSQL connectie */
$conn = new Mysqli("localhost", "username", "password", "tylerwx94_cwvote") or die ('Error: ' . mysqli_errno($conn));


/* mysql selectie */
$results = mysqli_query($conn, "SELECT `players` FROM `tylerwx94_cwvote` ORDER BY `votes` DESC LIMIT 10") or die('Error: ' . mysqli_errno($results));


while ($row = mysqli_fetch_assoc($results)) {
echo $row['players'] . "\n";


}
?>

 

Any help with my code or link to a good tutorial would be appreciated!

Sorry if it is in the wrong section,

don't know if it belongs in mysql or php.

Edited January 4, 2013 by Streakerstyle

[Barand]

Basically

$con = new mysqli( params);

$results = $con->query( sql string);

while ($row = $results->fetch_assoc() {
   // process row
}

[Streakerstyle]

well after using your example i'm still getting the warning.

I tried a vardump on the query and it returned NULL.

So the problem will be in selecting the table and ordering them by "votes".

 

$results = $con->query("SELECT `players` FROM `tylerwx94_cwvote` ORDER BY `votes` DESC LIMIT 10");

 

I tried changing the query to:

$results = $con->query("SELECT `name` FROM `players` ORDER BY `votes` DESC LIMIT 10");

but then i end up with a white page.

 

the database:

Edited January 5, 2013 by Streakerstyle

[cpd]

You get the original error because your passing the results into the mysqli_errno() method instead of the connection as you do in your first use.

 

mysqli_errno($conn);

// NOT
mysqli_errno($results);

 

That specific error has nothing to do with your query although I expect its being triggered through your query failing.

Edited January 5, 2013 by cpd

[Streakerstyle]

@cpd

Thanks for that one!

i'll change that asap.

 

----

 

I'm still not sure why the query failed though,

if someone knows let me know please,

i'd appreciate it. in the mean time, i'll also be looking myself.

[Barand]

I ran this test script. Note that because you have "votes" as varchar, you will get an alphabetic sort and not numeric. 300 should be at the top. Change it to int.

 

<?php
include("testDBconnect.php");

$mysqli->query("DROP TABLE IF EXISTS player") or die($mysqli->error);

$sql = "CREATE TABLE player (
    id int not null auto_increment PRIMARY KEY,
    name varchar(50),
    votes varchar(20)
    )";
$mysqli->query($sql) or die($mysqli->error);

$sql = "INSERT INTO player (name, votes) VALUES
    ('aaa', 50),
    ('bbb', 300),
    ('ccc', 80),
    ('ddd', 45),
    ('eee', 25),
    ('fff', 75)";
$mysqli->query($sql) or die($mysqli->error);

$sql = "SELECT name, votes FROM player ORDER BY votes DESC";
$res = $mysqli->query($sql) or die($mysqli->error);
echo '<pre>';
while ($row = $res->fetch_assoc()) {
   vprintf ('%-5s%5d<br />', $row);
}
?>

results:

ccc	 80
fff	 75
aaa	 50
ddd	 45
bbb    300
eee	 25

[Streakerstyle]

@barand

You just solved everything.

I can't thank you enough, i barley had some sleep due to this.

I kept wondering and thinking why won't it work,

i applied your code, edited few things, added my connection en changed the table into INT

and on the first try, it worked!

 

Many thanks.

[Barand]

BTW, I forgot to mention that INT(250) is a bit ambitious. You are not going to hold an int with 250 digits.

 

The largest value you can hold is just over 4 billion. Anything beyond INT(11) is pointless.