Page 1 of 1

a really easy MySQL question from a N00b....

Posted: Mon Jan 19, 2004 1:59 pm
by lizlazloz

Code: Select all

select id,name,strength,money from game ORDER BY 'strength' DESC  LIMIT 0  , 40
ok, that code will select all that stuff from a mysql database, 40 fo them, ordered by strength, starting at the first entry.

but, how can i do it so that it will select them starting from a variable called $name, which is in the 'name' field of the table?????

please help.

Posted: Mon Jan 19, 2004 2:36 pm
by bgaming
WHERE name LIKE '$name%'

Code: Select all

select id,name,strength,money from game WHERE name LIKE '$name%' ORDER BY 'strength' DESC  LIMIT 0  , 40
you may also want to add variables to the LIMIT keyword, so it can start and end at different rows.

Just in case...

Code: Select all

if (isset($start)) { // If it's the first page, $start won't have a value.
    $end = $start + $count; // you can have an option to set $count
    $query = "SELECT id,name,strength,money FROM game WHERE name LIKE '$name%' ORDER BY 'strength' DESC LIMIT $start, $end";
} else {
    $query = "SELECT id,name,strength,money FROM game WHERE name LIKE '$name%' ORDER BY 'strength' DESC LIMIT 0, $count";
}
This is just a suggestion. Use it if you want.

Posted: Mon Jan 19, 2004 3:23 pm
by lizlazloz
that code doesnt display the other 39 entries below the name specified... i need that :(

Posted: Mon Jan 19, 2004 3:28 pm
by bgaming
if it doesn't show the other 39, then they probably don't start with the same characters.

note: the script I gave you is case-sensitive. (so "Text" and "text" wouldn't become results of the same query)

Posted: Mon Jan 19, 2004 3:32 pm
by lizlazloz
eek. The other 39 are compeltely different, ther is no alphabetical pattern.

If you see what i am doing, i am selecting the 'strength' of 40 members of my game, with $name being the top 1.

Posted: Mon Jan 19, 2004 3:35 pm
by bgaming
Give me a list of examples of field values [make them up if you want]

use this format:

name -> strength
name2 -> strength2
name3 -> strength3
...

Posted: Mon Jan 19, 2004 3:40 pm
by lizlazloz
ok....

$name = 'bob'

bob - 70
tom - 56
jim - 45
fred - 32
sally - 22
sam - 19


something like that... (When they have been sorted by power, otherwise they would be in any order...)

Re: a really easy MySQL question from a N00b....

Posted: Mon Jan 19, 2004 3:43 pm
by microthick
lizlazloz wrote:

Code: Select all

select id,name,strength,money from game ORDER BY 'strength' DESC  LIMIT 0  , 40
ok, that code will select all that stuff from a mysql database, 40 fo them, ordered by strength, starting at the first entry.

but, how can i do it so that it will select them starting from a variable called $name, which is in the 'name' field of the table?????

please help.
How about you do a query before this to find what row 'Bob' appears on and save it into a variable.

Then, in this (above) second query, use that variable instead of 0.

Posted: Mon Jan 19, 2004 3:44 pm
by lizlazloz
how would i do that? like i said, i'm a noob....

the bit i dont understand is thow to find the row it is on. i know how to then put that in the next bit of SQL...

Posted: Mon Jan 19, 2004 3:51 pm
by microthick
lizlazloz wrote:how would i do that? like i said, i'm a noob....

the bit i dont understand is thow to find the row it is on. i know how to then put that in the next bit of SQL...
The sloppy, easy way would be like this:

Code: Select all

$sql = "select name from game ORDER BY 'strength' DESC";
$result = mysql_query($sql, $conn);

$currRow = 0;
$bobRow = 0;
while ($row = mysql_fetch_array($result)) {
     $currRow++;
     if ($row["name"] == "Bob") {
          $bobRow = $currRow;
          break;
     }
}
Basically, we get all the database records ordered by strength. Then we loop through them until we find one with the name Bob. We record the row number then exit the loop.

Now we have a variable called $bobRow that contains the row number that Bob appeared on.

Posted: Mon Jan 19, 2004 3:56 pm
by lizlazloz
thats gonna mean running throught the whole table, ouch. i guess that will have to do for the moment tho if no-one else knows anything, thanks.

Posted: Tue Jan 20, 2004 2:32 am
by lizlazloz
hmm... using that code is giving me wrong results... i'm not sure why... but $bob row is always coming out as a much higher number than it should be....

Posted: Tue Jan 20, 2004 4:34 am
by twigletmac
You could also do it by selecting Bob's strength and then ensuring that only strength's equal to or less than Bob's are returned by the query:

Code: Select all

$name = 'bob';

// get Bob's strength
$sql = "SELECT strength FROM game WHERE name='$name'";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$bob_strength = mysql_result($result, 0);

// get the 40 records needed:
$sql = "SELECT id, name, strength, money FROM game WHERE strength <= $bob_strength ORDER BY strength DESC LIMIT 0,40";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
Mac

Posted: Tue Jan 20, 2004 7:53 am
by lizlazloz
thanks, looks like it should work :?