We hope you enjoy your visit.

You're currently viewing our forum as a guest. This means you are limited to certain areas of the board and there are some features you can't use. If you join our community, you'll be able to access member-only sections, and use many member-only features such as customizing your profile, sending personal messages, and voting in polls. Registration is simple, fast, and completely free.


Join our community!


If you're already a member please log in to your account to access all of our features:

Username:   Password:
Add Reply
  • Pages:
  • 1
Tricky PHP/SQL problem
Topic Started: May 5 2005, 07:03 PM (801 Views)
JCink May 5 2005, 07:03 PM Post #1
Member
[ * * * * * ]
 Stumped on this one. Not sure if theres a way out or a way to do it. :/ Okay, here is my problem.

http://jcinkcom.ho8.com/quickarcade/q/Arca...on=leaderboards

Okay. Heres how it works. That's a leaderboard. Every time someone is "champion" of a game on that thing, their number on the leaderboard goes up. If the "champion" gets taken away from them, it goes down. This information is drawn from several places.

That entire row of names is drawn by querying the table

PhpQuickArcade_account_data ORDER BY username ASC

Now the winnings are stored in a seperate table. PhpQuickArcade_user_champs. So, to get those numbers there of champs, I use something like this:

$heh = $scores["username"];
$champtable = mysql_query("SELECT * FROM PhpQuickArcade_user_champs WHERE username = '$heh'");

Okay. Then I can count that, and then stick the count var in the leaderboard HTML and it counts nicely.

My main problem is: How am I going to get this to put the user with the most "wins" or "champs" at the top? I just can't see any way else I can do it since I have things setup the way I do.

Is there a way?

Thanks for any help on this...
Offline Profile Quote Post Goto Top
 
Seth May 5 2005, 07:16 PM Post #2
Member Avatar
I has a pony
[ * * * * * * * * * ]
 My eyes >_< Randomly titled variables, huge table names, selecting *, ack.

Use a JOIN.

Code:
 
$query = "SELECT COUNT(*) AS c FROM PhpQuickArcade_user_champs LEFT JOIN PhpQuickArcade_account_data ON PhpQuickArcade_user_champs.username = PhpQuickArcade_account_data.username ORDER BY c ASC";
$result = mysql_query($query);


I made an assumption that you have a username field in both tables. I don't know your db structure though.
Offline Profile Quote Post Goto Top
 
JCink May 5 2005, 07:31 PM Post #3
Member
[ * * * * * ]
 Hmm, okay... shield your eyes >_< and also I just realized that this code here isn't working right anyway:

Code:
 
$scoreboard = mysql_query("SELECT * FROM PhpQuickArcade_account_data ORDER BY username DESC");


if ($scores = mysql_fetch_array($scoreboard)) {



 echo "<div class='tableborder'><table width=100%% cellpadding='5' cellspacing='1'>";
 echo "<td width=2%% align=left class=scoresblock>UsersName</td><td width=30%% align=center class=scoresblock>Total Champs</td>";
 do {
   printf("<tr><td class=arcade1><div align='center'>%s</div></td><td class=arcade1><div align='center'>$whats_the_rows</div></td></td></tr>\n", $scores["username"], $scores["thescore"], $scores['comment'], $scores['phpdate']);

$heh = $scores["username"];

$champtable = mysql_query("SELECT * FROM PhpQuickArcade_user_champs WHERE username = '$heh'");

$whats_the_rows = mysql_num_rows($champtable);



 } while ($scores = mysql_fetch_array($scoreboard));

echo "</td>
         </table></div>";


//hmm
echo "</table></div>";
}


With that setup, how do I use that? I don't understand. Thanks. And, I know the mysql_num_rows isn't right... I forgot about Select count, but I'll fix that. I've never used a JOIN once before.
Offline Profile Quote Post Goto Top
 
Seth May 5 2005, 07:33 PM Post #4
Member Avatar
I has a pony
[ * * * * * * * * * ]
 Ow ow ow ow ow ow.

I'll take 3 hours and get back to you :ermm:
Offline Profile Quote Post Goto Top
 
Seth May 5 2005, 07:38 PM Post #5
Member Avatar
I has a pony
[ * * * * * * * * * ]
 Basic idea:

Code:
 
$query = "SELECT username, COUNT(*) AS c FROM PhpQuickArcade_user_champs";
$query .= "LEFT JOIN PhpQuickArcade_account_data ON PhpQuickArcade_user_champs.username = PhpQuickArcade_account_data.username";
$query .= "ORDER BY c DESC";

$result = mysql_query($query);
while ($fetch = mysql_fetch_array($result))
{
// $fetch contains $fetch['username'] and $fetch['c'], have fun
}
Offline Profile Quote Post Goto Top
 
JCink May 5 2005, 07:56 PM Post #6
Member
[ * * * * * ]
 Okay. Thanks for the code. But, I have nothing to order by. Which is the main problem. I'll try to describe it better.

Code:
 
CREATE TABLE `PhpQuickArcade_user_champs` (
 `id` int(11) NOT NULL auto_increment,
 `username` varchar(255) NOT NULL default '',
 `thescore` int(11) NOT NULL default '0',
 `gamename` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `username` (`username`)
) TYPE=MyISAM AUTO_INCREMENT=1;


This is the user champs table. How it works is, there can only be one good score in there by somebody. Example from here:

INSERT INTO `PhpQuickArcade_user_champs` VALUES (15, 'Sean', 2388, 'MonsterHatch');

From that, all I do is count the number of times WHERE username = '$var'; ... the var is the username var it comes from logging into their account to display the total wins. No actual number is being stored, I'm just counting the number of rows a certain username comes up.

How this even works out is, if the top score of a game is beaten, it opens that table, deletes, removes their name, and updates the score. It knows what game and what to delete because flash sends out the "IDNAME" which is what I call it...

So basically I have nothing to ORDER BY which is the problem. Either that or I just dont understand the code you gave me.
Offline Profile Quote Post Goto Top
 
Seth May 5 2005, 08:00 PM Post #7
Member Avatar
I has a pony
[ * * * * * * * * * ]
 You're ORDER'ing by c, which is a count of how many times their username appears in that table.
Offline Profile Quote Post Goto Top
 
JCink May 5 2005, 08:19 PM Post #8
Member
[ * * * * * ]
 It doesn't seem to work for me. I thought I should set "c" to something? I'm sorry, I was taught wrong how to count the number of rows in a table, so this is confusing me a bit.


Thanks,
-jcink
Offline Profile Quote Post Goto Top
 
Seth May 5 2005, 11:25 PM Post #9
Member Avatar
I has a pony
[ * * * * * * * * * ]
 Could you dump your databases and e-mail them to me? seth@invisionfree.com

I'll make sure it works with your dbs :)
Offline Profile Quote Post Goto Top
 
JCink May 6 2005, 12:30 PM Post #10
Member
[ * * * * * ]
 Really? :blink: Thanks a bunch. Okay then I sent it over.

heres a copy here, since GMail doesn't always work right:

Code:
 

-- phpMyAdmin SQL Dump
-- version 2.6.1-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 06, 2005 at 11:23 AM
-- Server version: 4.0.22
-- PHP Version: 4.3.11
--
-- Database: `jcinkcom_arcadebb`
--

-- --------------------------------------------------------

--
-- Table structure for table `PhpQuickArcade_account_data`
--

CREATE TABLE `PhpQuickArcade_account_data` (
 `id` int(11) NOT NULL auto_increment,
 `username` varchar(255) NOT NULL default '',
 `ipaddress` varchar(255) NOT NULL default '',
 `date` varchar(255) NOT NULL default '',
 `email` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=54;

--
-- Dumping data for table `PhpQuickArcade_account_data`
--

INSERT INTO `PhpQuickArcade_account_data` VALUES (11, 'jcink', 'ip', '27th April 2005 - 04:25 PM', '');

-- --------------------------------------------------------

--
-- Table structure for table `PhpQuickArcade_user_champs`
--

CREATE TABLE `PhpQuickArcade_user_champs` (
 `id` int(11) NOT NULL auto_increment,
 `username` varchar(255) NOT NULL default '',
 `thescore` int(11) NOT NULL default '0',
 `gamename` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `username` (`username`)
) TYPE=MyISAM AUTO_INCREMENT=33;

--
-- Dumping data for table `PhpQuickArcade_user_champs`
--

INSERT INTO `PhpQuickArcade_user_champs` VALUES (28, 'Sean', 2644, 'MonsterHatch');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (14, 'X', 2173, 'Snake');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (32, 'Sean', 10000, 'Clickzorz');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (10, 'Mvpzero', 10873649, 'FallDown2');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (11, 'Stevo', 2245, 'Gunslinger');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (13, 'Sean', 1736, 'Brainiac');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (16, 'X', 68687, 'Tetris');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (18, 'Sivart', 3102, 'UFO101');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (19, 'X', 831, 'mariooverrun');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (20, 'Sean', 100, 'Pop_Picos');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (21, 'Sean', 68300, 'sportssmash');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (22, 'Sean', 15736, 'LunarLander');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (23, 'Sean', 10100, 'debate');
INSERT INTO `PhpQuickArcade_user_champs` VALUES (24, 'Sean', 10340, 'match');
Offline Profile Quote Post Goto Top
 
Seth May 6 2005, 12:53 PM Post #11
Member Avatar
I has a pony
[ * * * * * * * * * ]
 Got it. Will do tonight.
Offline Profile Quote Post Goto Top
 
JCink May 6 2005, 11:11 PM Post #12
Member
[ * * * * * ]
 Thanks. I'm going to try to learn a bit about that code though that you gave already right now. If I come up with something; I'll post here... Thanks again.
Offline Profile Quote Post Goto Top
 
Seth May 6 2005, 11:15 PM Post #13
Member Avatar
I has a pony
[ * * * * * * * * * ]
 Ok ^_^
Offline Profile Quote Post Goto Top
 
Seth May 7 2005, 09:22 PM Post #14
Member Avatar
I has a pony
[ * * * * * * * * * ]
 Got it!

Code:
 
SELECT PhpQuickArcade_account_data.username, COUNT(PhpQuickArcade_user_champs.username) AS dracula FROM PhpQuickArcade_account_data
LEFT JOIN PhpQuickArcade_user_champs ON PhpQuickArcade_account_data.username = PhpQuickArcade_user_champs.username
GROUP BY PhpQuickArcade_user_champs.username
ORDER BY dracula DESC
Offline Profile Quote Post Goto Top
 
JCink May 7 2005, 09:39 PM Post #15
Member
[ * * * * * ]
 Thanks that worked perfectly. I just inserted it over my old query and it worked. PHEW! Thanks! Okay... one last thing... let me see if I got this right as to what you did :

Code:
 
SELECT PhpQuickArcade_account_data.username, COUNT(PhpQuickArcade_user_champs.username)


First you select the username, and then count the number of times it appears? That's what this piece does?

Code:
 
AS dracula FROM PhpQuickArcade_account_data


I guess; that; you defined defined the count thing AS dracula? That's what AS does? and of course you get the info FROM PhpQuickArcade_account_data... I think.

Code:
 
LEFT JOIN PhpQuickArcade_user_champs ON PhpQuickArcade_account_data.username = PhpQuickArcade_user_champs.username


This I looked up on w3schools earlier :D

and the rest I get... I just want to make sure I know the two top parts and what they do

and Thanks again!
Offline Profile Quote Post Goto Top
 
1 user reading this topic (1 Guest and 0 Anonymous)
Go to Next Page
« Previous Topic · Technology Chat · Next Topic »
Add Reply
  • Pages:
  • 1