Jump to content

munster

New Members
  • Posts

    7
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

munster's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. [!--quoteo(post=336617:date=Jan 14 2006, 09:44 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 14 2006, 09:44 PM) 336617[/snapback][/div][div class=\'quotemain\'][!--quotec--] I don't follow at all... you refer to members and pictures, and I don't see either in your query. But if you want to get the group-wise maximum (e.g. of a COUNT), then you're going to need a subselect or a temporary table -- you can't do this with JOINs. Hi Fenway, What i have is a table named "pictures" with colums "id","submitter"(as a number) another table called "members" with colums "id" (which relates exactly to the submitter colum above), "nickname" and another called "challenges" with colums "id", "champion_id","champion_votes","challenger_id","challenger_votes","winner"(holds the id number of the picture that one) what i`m trying to do is display the top ten members nicknames that have one the most battles. this select: SELECT winner, COUNT(*) as ccount FROM challenges WHERE winner IS NOT NULL group by winner order by ccount desc limit 10 selects the top ten pictures that have one the most battles but i now need to find out who submitted them pictures and display there nicknames in order. Now the "id" in the pictures table holds the related value to the result the select above produces and the "submitter" value in the pictures table then tells you who submitted that picture then its just a case of incorporating the original line you helped me with to convert the "submitter" value to his nickname. Is that at all possible with one select or do i have to do it some other way. Excuse my ignorance i`m searching and reading books like crazy but getting no where. All the best munster
  2. Hey guys, Ive got another problem ive spent 3 days other and a book and still cant get I`m now trying to get the top ten members nicknames who have one the most battles... Obviously i know how to get the nickname thanks to your help earlier in this thread and the formula below ive worked out will get the top 10 pictures that have one the most battles : SELECT winner, COUNT(*) as ccount FROM challenges WHERE winner IS NOT NULL group by winner order by ccount desc limit 10 But this simply lists the top ten picture id`s where as i want the submitters nickname of these pictures. The id from the above formula is the id in the "pictures" table which also holds the submitter id from earlier in this thread so i think it should be possible but ive just drawn blanks I hope that makes sense if anyone can help out there it would be much appreciated. Regards Munster
  3. Hey, Lazyjones and Fenway got it now. Many thanks Both
  4. Hey Fenway, I`ve still got problems the original line gave an error so i took out the s.nickname which now supplies the first 30 pictures fine but still displays the number of the submitter underneath instead of the actual nickname of the submitter that i wanted. I tried a number of differant arrangments but all either caused errors or no differance. Many thanks for your continued help. Munster <?php $conn=@mysql_connect('localhost', '*****', '******'); $rs=mysql_select_db('******', $conn); $sql="select p.submitter, p.code from pictures as p, members as m where p.submitter = m.id limit 30"; $rs=mysql_query($sql, $conn); while ( $row=mysql_fetch_array($rs) ) { echo("<img src=pictures/tiny".$row["code"].".jpg><br>"); echo("submitter: ".$row["submitter"]."<br>"); } ?>
  5. Hey Fenway, Thanks for the reply ive been playing around with it but just get errors. The script was written by someone else for me some time ago and i have very limited (guess you no that!) knowledge of php. Basically as i said i`m trying to display all pictures uploaded by a certain member. I have two tables one called pictures holding: ID | SUBMITTER | CODE .... and a few more such as picture height and width etc. submitter contains a number which relates to his nickname in the table below, code is the name of the relavant picture. Second table called members holding: ID | NICKNAME ... and again various other imformation. The submitter value taken from the first table relates directly to the ID number in this table (and nickname is the one i want) ... i have no idea why the nickname isn`t simply displayed as a string in the first table. I hope this makes it a bit clearer maybe you could tell me where i`m going wrong. So the end result is im just trying to match the submitters number up with his nickname and display the picture but i`m just so confused!! Many thanks Munster <?php $conn=@mysql_connect('localhost', '*****', '******'); $rs=mysql_select_db('*******', $conn); $sql="select p.submitter, s.nickname, p.code from pictures as p, submitters as s where p.submitter=s.submitter and p.submitter>0"; $rs=mysql_query($sql, $conn); while ( $row=mysql_fetch_array($rs) ) { echo("<img src=pictures/tiny".$row["p.code"].".jpg><br>"); echo("submitter: ".$row["s.nickname"]."<br>"); } ?>
  6. Hi guys, Can anyone tell me if its at least possible or point me in the direction of some help on the subject as i can`t find anything that makes sense. Thanks Munster
  7. Hi Guys, Ive got this peice of code to pic out and display the number of the member that submitted the picture: <?php $conn=@mysql_connect('*****', '******', '******'); $rs=mysql_select_db('*******', $conn); $sql="select submitter, code from pictures where small_width=221 and submitter<>0"; $rs=mysql_query($sql, $conn); while ( $row=mysql_fetch_array($rs) ) { echo("<img src=pictures/tiny".$row["code"].".jpg><br>"); echo("submitter: ".$row["submitter"]."<br>"); } ?> What i want though is to display the members nickname which is held in another table but have no idea how to do this. So the submitter value i retrieved above is the id number for the nickname i am trying to retrieve in the other table i just don`t now how to get it. What i`m actually trying to do is display all pictures with who submitted them underneath. Any help would be much apprciated. Many thanks Munster
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.