Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
lizlazloz
Forum Commoner
Posts: 64 Joined: Mon Dec 29, 2003 7:29 am
Post
by lizlazloz » Mon Jan 19, 2004 1:59 pm
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 » Mon Jan 19, 2004 2:36 pm
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 » Mon Jan 19, 2004 3:23 pm
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 » Mon Jan 19, 2004 3:28 pm
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 » Mon Jan 19, 2004 3:32 pm
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 » Mon Jan 19, 2004 3:35 pm
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 » Mon Jan 19, 2004 3:40 pm
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
Post
by microthick » Mon Jan 19, 2004 3:43 pm
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 » Mon Jan 19, 2004 3:44 pm
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 » Mon Jan 19, 2004 3:51 pm
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 » Mon Jan 19, 2004 3:56 pm
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 » Tue Jan 20, 2004 2:32 am
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....
twigletmac
Her Royal Site Adminness
Posts: 5371 Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK
Post
by twigletmac » Tue Jan 20, 2004 4:34 am
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 » Tue Jan 20, 2004 7:53 am
thanks, looks like it should work