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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

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

Post 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.
bgaming
Forum Newbie
Posts: 6
Joined: Fri Dec 19, 2003 11:10 pm

Post 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.
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

that code doesnt display the other 39 entries below the name specified... i need that :(
bgaming
Forum Newbie
Posts: 6
Joined: Fri Dec 19, 2003 11:10 pm

Post 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)
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post 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.
bgaming
Forum Newbie
Posts: 6
Joined: Fri Dec 19, 2003 11:10 pm

Post 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
...
Last edited by bgaming on Mon Jan 19, 2004 3:49 pm, edited 1 time in total.
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post 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...)
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

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

Post 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.
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post 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...
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post 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.
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post 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....
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
lizlazloz
Forum Commoner
Posts: 64
Joined: Mon Dec 29, 2003 7:29 am

Post by lizlazloz »

thanks, looks like it should work :?
Post Reply