DragonPrime - LoGD Resource Community

Coding Support => Coding Support Desk => Topic started by: Zanzaras on September 08, 2005, 12:39:48 PM



Title: SQL Queries for Dummies
Post by: Zanzaras on September 08, 2005, 12:39:48 PM
I need a little shove in the right direction. I know absoulutely nothing about sql queries, so this will probably be laughably simple to some of you guys/gals out there.

I need to run a query to find all the players who are members of a particular clan and randomly choose one of them.

Any help that anyone can give me on this would be greatly appreciated. I'll take anything from the actual code to a "there's a pretty good example of how to do this in such-such module".

Thanks!


Title: Re:SQL Queries for Dummies
Post by: Talisman on September 08, 2005, 12:46:16 PM
Why not take a look in the clans module itself?  There's a query there that shows the member list of each clan.


Title: Re:SQL Queries for Dummies
Post by: Nightborn on September 08, 2005, 12:54:16 PM
Line 96 should be what you are looking for  :)



Title: Re:SQL Queries for Dummies
Post by: Zanzaras on September 08, 2005, 12:55:33 PM
Thanks guys... looking at it now.  ;)


Title: Re:SQL Queries for Dummies
Post by: Nightborn on September 08, 2005, 01:01:58 PM
Code:
   $sql = "SELECT name,login,clanrank,clanjoindate,dragonkills FROM " . db_prefix("accounts") . " WHERE clanid=$detail ORDER BY clanrank DESC,clanjoindate";
   $result = db_query($sql);
        $numberofrowsoftheresult=db_num_rows($result);
   while ($row=db_fetch_assoc($result))
             {
             $current_name=$row['name'];
             }    


:) the $sql contains the select query text... if you don't know sql, just read it out to start with... this query -> selects all name,login,clanrank,clanjoindate, dragonkills from the table (with prefix maybe) accounts ... where (conditions) clanid equals $detail (this might be the user entry or so) and is ordered by the field clanrank descending and afterwards by clanjoindate.

db_query starts the select query

db_num_rows lets you know how many rows the query found

db_fetch_assoc reads the results out, row by row. with the while you do things like print every name in a table (like it is in clan.php) or so... you access the fields of a single row via (here) $row['fieldname']

that should be a *little* start. I am also a beginner :( sorry


Title: Re:SQL Queries for Dummies
Post by: Zanzaras on September 08, 2005, 02:42:19 PM
You, sir, are absolutely wonderful!

Thanks for the explaination, that helped a lot!  ;D


Title: Re:SQL Queries for Dummies
Post by: Kendaer on September 08, 2005, 04:09:02 PM
You can also modify that code to pick a random person in a given clan.

Let assume you already have the clanid of the given clan.

In that case you would want to do
Code:
$query = "select name from ". db_prefix("accounts") where clanid='$clanid' and clanrank > " . CLAN_APPLICANT . " ORDER BY rand(".e_rand().") LIMIT 1";
$res = db_query($sql);
$row = db_fetch_assoc($res);
$name = $row['name'];
Now you have the name of a random clan member who's been accepted into a clan, and you only have one member returned.

The clanrank restriction is so that you don't get people who've merely applied.. If you want to include applicants as well, just remove that 'and clanrank > .....' bit :)


Title: Re:SQL Queries for Dummies
Post by: Zanzaras on September 10, 2005, 09:22:30 AM
Thanks Kendaer, that works perfectly!   :D


Title: Re:SQL Queries for Dummies
Post by: snehal on September 14, 2005, 08:06:16 PM
i want to run one query:- can u say how i can?
list the firstname and lastname of the staff who have not hired any vehicle.


Title: Re:SQL Queries for Dummies
Post by: Talisman on September 14, 2005, 08:11:38 PM
First AND Last name of staff who have rented VEHICLES?   ???

Ummm...what game are you doing this in??!?


Title: Re:SQL Queries for Dummies
Post by: XChrisX on September 14, 2005, 09:53:44 PM
if you google "SQL Queries for Dummies", dragonprime is the first entry... ;D


Title: Re:SQL Queries for Dummies
Post by: Talisman on September 14, 2005, 09:57:46 PM
Heh...most people tend to determine what the nature of a site is before posting in it...


Title: Re:SQL Queries for Dummies
Post by: XChrisX on September 14, 2005, 11:00:34 PM
aaaah... That requires reading...  :P  ;)


Title: Re:SQL Queries for Dummies
Post by: Elessa on September 14, 2005, 11:06:14 PM
aaaah... That requires reading...  :P  ;)

/me smacks her palm to her forehead

and wouldn't ye know it? this just happens to be a forum for a text-based game!  oh heavens, ye are right.  reading, what a concept!  ;D


Title: Re:SQL Queries for Dummies
Post by: XChrisX on September 14, 2005, 11:15:16 PM
Reading is by far the WORST spoiler in this game... And still many people don't manage to get those hints, even if you make them bright yellow (`^) between pale gray (`7)... ;)


Title: Re:SQL Queries for Dummies
Post by: Talisman on September 14, 2005, 11:47:03 PM
Now if we could just figure out what got that DP post to the top of the Google listing and bottle that!   ;D


Title: Re:SQL Queries for Dummies
Post by: XChrisX on September 15, 2005, 12:09:15 AM
Just type in the topic's name ;)

Would make a good name for a book, don't you think? We could earn a lot of money! We WILL earn a lot of money! We are on top of google without having done anything! There MUST be some benefit from this!

/me mutters on in that way and gets his first cup of coffee for today - seems necessary...


Title: Re:SQL Queries for Dummies
Post by: Nightborn - working on September 15, 2005, 02:31:27 AM
Uhm... well ;) I talked about my mathechat ... it went up googles listing and now kiddies access it :D

a bless...or a curse...

hehe

© 2019 DragonPrime - LoGD Resource Community
Email Talisman: talisman -at- gmail.com
&oeXs)2U7=V BmܲV.U e=;p\}eG )Jj} C5EH7ˤH=j } mo|*Ŋw{drV_@IV>/- TFQJ׀̀Ve}l1,V O iNYx͘$e$;
Forums: Powered by SMF 1.1.21 | SMF © 2006-2007, Simple Machines