LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 05-25-2004, 01:28 PM   #1
Gerardoj
Member
 
Registered: May 2003
Location: Somewhere over the Rainbow
Distribution: Slackware 9.x, Knoppix, Damn Small Linux, RedHat.
Posts: 465

Rep: Reputation: 30
PHP Calculate, Compare.. Pts


Hi, I would like some help calculating points. I have 2 tables on mySQL database, MatchData and PredictionData with the following columns:

MatchData: lid, matchdate, hometeam, awayteam, homescore, awayscore

PredictionData: lid, username, matchdate, hometeam, awayteam, homescore, awayscore

I would like to compare MatchData.homescore with PredictionData.homescore and MatchData.awayscore
with PredictionData.awayscore. If the results are the same give the user 3 point, but if the results are different give 1 point.

Something like:

3 point if the user predict 3-1 and the result is 3-1
1 point if the user predict 1-1 and the result is 2-2

Some help creating this?
Some suggestions?

Thanks a lot..
 
Old 05-25-2004, 01:37 PM   #2
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Is this a homework assignment?

I'd just write a PHP function with as big an if/then/else block as you need.

But ....

Strong suggestion - take a look at JPgraph:

http://www.aditus.nu/jpgraph/

It's very simple to install and to use - and it sounds like it could really complement your application.

Just a thought .. PSM
 
Old 05-25-2004, 01:58 PM   #3
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,552

Rep: Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872
PHP Code:
$sql "SELECT MatchData.homescore, PredictionData.homescore, MatchData.awayscore, PredictionData.awayscore FROM MatchData, PredictionData WHERE MatchData.homescore=PredictionData.homescore AND MatchData.awayscore=PredictionData.awayscore";

$result mysql_query($sql$connection_id);

if( 
mysql_num_rows($result) )

    
give user 3 point;
}
else
{
    
give user 1 point;

[ edit ]
syntax color enabled

Last edited by keefaz; 05-25-2004 at 02:05 PM.
 
Old 05-25-2004, 02:14 PM   #4
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Oh - you were asking how to do a SQL *join*???
 
Old 05-25-2004, 03:02 PM   #5
Gerardoj
Member
 
Registered: May 2003
Location: Somewhere over the Rainbow
Distribution: Slackware 9.x, Knoppix, Damn Small Linux, RedHat.
Posts: 465

Original Poster
Rep: Reputation: 30
Thanks all for your reply's. I was looking and you have the idea, but the poblem is that each user can have a lot of predictions. I was trying something like:

SELECT homescore, awayscore FROM MatchData where matchdate = PredictionData.matchdate and hometeam=\"$reshometeam\" and awayteam=\"$resawayteam\" and homescore is not null";

I mean $res* can be the result of the compare function. But I dont have any idea how can I make that function.

I think I need to make an INSERT after calculate function.. I need suggestions.. Please

PredictionData looks like:
PHP Code:
+-----+----------+---------------------+----------+----------+-----------+-----------+
lid username matchdate           hometeam awayteam homescore awayscore 
+-----+----------+---------------------+----------+----------+-----------+-----------+
|  
30 admin    2004-05-14 13:00:00 SUE      NEZ      |         |         |
|  
29 admin    2004-05-18 13:00:00 CHE      CHI      |         |         |
|  
28 admin    2004-05-20 13:00:00 ING      DEN      |         |         |
|  
27 gery     2004-05-14 13:00:00 SUE      NEZ      |         |         |
|  
26 gery     2004-05-18 13:00:00 CHE      CHI      |         |         |
|  
25 gery     2004-05-20 13:00:00 ING      DEN      |         |         |
+-----+----------+---------------------+----------+----------+-----------+-----------+ 
MatchData looks like

PHP Code:
+-----+---------------------+----------+----------+-----------+-----------+
lid matchdate           hometeam awayteam homescore awayscore |
+-----+---------------------+----------+----------+-----------+-----------+
|   
2004-05-14 13:00:00 SUE      NEZ      |         |         |
|   
2004-05-18 13:00:00 CHE      CHI      |         |         |
|   
2004-05-20 13:00:00 ING      DEN      |         |         |
+-----+---------------------+----------+----------+-----------+-----------+ 

Last edited by Gerardoj; 05-25-2004 at 03:06 PM.
 
Old 05-25-2004, 03:33 PM   #6
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
OK - I understand your problem a bit better ... but frankly, I still don't understand it well enough to give you a solution.

Suggestions:
1. If this *is* a homework assignment ... either do it yourself ... or at *least* cut/paste
*exactly* what your instructor told you to do. If there's a clearer "problem definition",
it would be extremely helpful to see it.

2. Treat the data access (e.g. the SQL query) and the points calculation as *two
separate problems*. Once you've got the solution, you might be able to combine
them (for example, do the query and the calculation in one really clever SQL
statement).

I would very strongly suggest:
a) Write a PHP function to compute the points (based on some
hard-coded dummy data)
b) Verify whether or not the solution is correct (to all possible predictions/scores)
c) Figure out the query to pass in the actual data (it looks like you've already
done this)
d) Optimize the solution (perhaps doing the bulk of the work in PHP, for example,
using arrays; or perhaps re-writing some/all of your PHP function in SQL).

Be advised, however, the mySQL can be *extremely* limited. My version, for example, doesn't even support sub-selects or ANSI-92 "join" syntax... So you'd probably find it easiest to do as much as you possible in PHP...

'Hope that helps .. PSM
 
Old 05-25-2004, 03:48 PM   #7
Gerardoj
Member
 
Registered: May 2003
Location: Somewhere over the Rainbow
Distribution: Slackware 9.x, Knoppix, Damn Small Linux, RedHat.
Posts: 465

Original Poster
Rep: Reputation: 30
Hey Thanks fro your reply.

1. Is NOT a homework... Dont worry about that..
2. I will begin code function to compute the points.

3. Thanks for all Suggestions. I will take note about them

 
Old 05-25-2004, 07:35 PM   #8
Gerardoj
Member
 
Registered: May 2003
Location: Somewhere over the Rainbow
Distribution: Slackware 9.x, Knoppix, Damn Small Linux, RedHat.
Posts: 465

Original Poster
Rep: Reputation: 30
Ok ... I made a SQL query

PHP Code:
SELECT PredictionData.lid FROM PredictionData LEFT JOIN MatchData ON
PredictionData
.matchdate MatchData.matchdate WHERE PredictionData.homescore =
MatchData.homescore AND PredictionData.awayscore MatchData.awayscore
Currently display a list of lid's of correct guessers. How can I give 3 points to the
lid's of correct guessers?

The question now is how can I INSERT or UPDATE into my Point's Table the point for each user..

My Point Table looks like;



PHP Code:
+-----+----------+--------+----------+--------+
lid username Team   Division Points |
+-----+----------+--------+----------+--------+ 
 
Old 05-26-2004, 12:54 AM   #9
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
You don't.

You query each prediction, and the corresponding results.

You pass this into a function (either collectively, or a prediction at a time. It doesn't matter).

Then, for each prediction, your function computes a single value: the points (1..3).

You can do with this value whatever you like. It sounds like perhaps you want a third table - one to associate lid's with their accumulated points. If so, you'd do an INSERT into this hypothetical "contestant points" table.

But the key thing you need to figure out at this point is how to take ONE prediction, compare it with ONE game's results, and compute a SINGLE "points" value.

If a single contestant made one prediction for one game, then you've got one value.

If that same contestant made THREE predictions for that one game: well then, it's up to you how you want to handle it. You can keep the "winning value" and throw the other two way. You can average the three values. You can save all three predictions in your hypotehtical "contestant points" table and then give a prize to the individual who accumulated the most points over the entire season. It all depends on your preference.

'Hope that helps .. PSM

PS:
Sorry for razzing you about the homework thing ;-)!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
php read from file and compare. xushi Programming 11 07-14-2005 01:10 PM
Why do I get a broken connection when I try to compare SID with a variable (PHP 4) vharishankar Programming 6 07-09-2005 10:57 AM
how to compare 2 text files by using php code antony_csf Programming 3 10-14-2004 05:52 AM
pts/0, pts/1 devices ?? nadine.mauch Linux - General 1 10-07-2004 11:00 AM
php date compare omarswan Programming 2 10-02-2002 04:41 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 04:38 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration