PHP Question: The Chill Out Zone

Home
Search

Oh no! Where's the JavaScript?
Your Web browser does not have JavaScript enabled or does not support JavaScript. Please enable JavaScript on your Web browser to properly view this Web site, or upgrade to a Web browser that does support JavaScript.

I know this does not relate to php-fusion but I'm hoping one of the php gurus on the forum will point me in the right direction.

I have set up a form that allows registered players to pick the oscar categories that they think will win and their picks are entered into a mysql table. What has to be done to set up a table that will track the winning categories and then be able to track which player has picked the correct category? I would then like to list the players by the number of correct answers...highest to lowest.

Any help will be much appreciated.

0 replies

Something along the lines of:

Table: Questions

Quote

QuestionID,
EffectiveDateTime,
QuestionText,
CorrectAnswerText

Table: Answers

Quote

AnswerID,
QuestionID,
AnswerText,
AnswerDateTime,
UserID (From Users table)

Quote

SELECT COUNT(*), username
FROM users u
INNER JOIN Answers a ON a.UserID = u.UserID
INNER JOIN Questions q ON q.QuestionID = a.QuestionID
AND q.CorrectAnswerText = a.AnswerText
WHERE
q.QuestionID = ???
GROUP BY username
HAVING COUNT(*) > 0
ORDER BY COUNT(*)

0 replies

Thanks for your quick response and offer to help. In reading your answer, I noticed that my initial post was misleading in that I mentioned that the users are registered, which they are not. The sql used to set up the table is as follows:

Code Download source

CREATE TABLE oscars2013 (
   id int(11) NOT NULL auto_increment,
   name varchar(64),
   email varchar(96),
   picture varchar(24),
   director varchar(24),
   actor varchar(24),
   actress varchar(4),
   supactor varchar(24),
   supactress varchar(24),
   origscreen varchar(24),
   adaptscreen varchar(24),
   animfeature varchar(24),
   forfilm varchar(24),
   cinematography varchar(24),
   editing varchar(24),
   artdir varchar(24),
   design varchar(24),
   makeup varchar(24),
   origscore varchar(24),
   origsong varchar(24),
   sndmixing varchar(24),
   sndediting varchar(24),
   viseffects varchar(24),
   docufeature varchar(24),
   docushort varchar(24),
   animshort varchar(24),
   actionshort varchar(24),
PRIMARY KEY (id)
);

How would I use the tables you suggest (questions and answers) based on the table that has already been set up? Am I misunderstanding your response?

0 replies

At the moment, I assume you have a "user table" stored somewhere in your database. This oscar2013 table is assume is a representation of an "oscar" hence the name.

In that case, I would use an extra column in the questions table which is a related field name column. Thus your table will look like the following:

Quote

Table: Questions
QuestionID,
EffectiveDateTime,
QuestionText,
CorrectAnswerText,
RelatedFieldName

Your Answers table would be the same.

Now you can use PHP with this. When setting up your question, you will fill in the RelatedFieldName, a field that is in the Oscar2013 table.

Let say the following is populated:

Quote

1,
NOW(),
"What is my favourite oscar's e-mail address?",
"None",
"email"

Note "email" is in the Oscar2013 table and it is the same letter case.

Using PHP, you would do the along the lines of:

Quote

$my_sql = "
SELECT COUNT(*), username
FROM users u
INNER JOIN Answers a ON a.UserID = u.UserID
INNER JOIN Questions q ON q.QuestionID = a.QuestionID
WHERE q.QuestionID = 1
INNER JOIN (
SELECT *, a.QuestionID
FROM Oscar2013
WHERE ".$field_name." = ".$_POST['my_answer']."
) AS a ON a.QuestionID = q.QuestionID
GROUP BY username
HAVING COUNT(*) > 0
ORDER BY COUNT(*)
";

Note your $field_name is pre loaded with something from mysql like:

Quote

SELECT RelatedFieldName FROM Questions WHERE QuestionID = 1

At the moment, I did not show any or not much of PHP, but do ask for help.

Edited by ronald1985 on 12-01-2013 00:09, 12 y

0 replies

Again, thanks for your response but unfortunately, it is way above my limited knowledge of both php and mysql. If you go to http://www.fredsfollies.com/oscars201...rs2013.php you will see the entry form that the players will be completing. The Oscars pool relates to the Academy Awards that will air on television at the end of February.

The form works and the selections are entered into the database using the field names shown in an earlier one of my posts. Where I am stuck, and where you are trying to help me is how to set up something that will check the actual winner of a specific category (Best Actor) against what the player has selected for that category. At that point the player will earn a numerical amount (to be determined). At the end of the show, the player with the highest number will be declared the winner.

I expected the answer to my question to be an if/then situation, but obviously it is much more complex than that.

0 replies

Are these questions stored in a database?

I will come up with some code.

0 replies

They are really not questions. They are options for the player to choose from. As an example, under the Best Picture category, there are 9 different movies. The player is asked which of those movies will win the Best Picture oscar. He will then do the same with all of the other categories (24) and submit his picks. Those selections (picks) are stored in a database.

When the award ceremony takes place, the winners of each category is announced and what I am trying to do is determine which of the players will win the pool based on the highest number of wins, or points allocated to each category.

Thanks again for your help and patience.

0 replies

Quote

ronald1985 wrote:

Are these questions stored in a database?

I will come up with some code.

Just checking to see if you are making any progress.

0 replies

Since I am working on this based off my assumptions (because there are no code provided by yourself), it is pretty much very basic from my end.

The form to be posted to with code:

Quote

<?php
if(isset($_POST) && count($_POST) > 0) {
mysql_connect('localhost';
mysql_select_db('testing';

foreach($_POST as $key => $value) {
if(!in_array($key, array('Submit', 'email', 'name')) {
$sql = '
INSERT INTO Answers (
QuestionText,
AnswerText,
Name,
EmailAddress,
DateTime
)
SELECT "'.$key.'", "'.$value.'", "'.$_POST['name'].'", "'.$_POST['email'].'", NOW()
';
mysql_query($sql);
}
}
mysql_close();
echo 'Done.'
}
?>

The code above does the adding the answers to the database. Since I have no clue about your current structure, the structure I used is:

Quote

AnswerIdentity INT AI
QuestionText VARCHAR(50)
AnswerText VARCHAR(50)
Name VARCHAR(50)
EmailAddress VARCHAR(50)
DateTime DATETIME

The report I would pull now would via SQL:

Quote

SELECT COUNT(*) AS RowCount, Name, EmailAddress, QuestionText
FROM Answers
WHERE (
(QuestionText = 'actress' AND AnswerText = 'Jessica Chastain for Zero Dark Thirty'
OR
(MORE CRITERIA HERE)
OR
...........
)
GROUP BY Name, EmailAddress, QuestionText
ORDER BY COUNT(*) DESC

Where you see:

Quote

OR
(MORE CRITERIA HERE)
OR
...........

This is where you put your IF and ELSE stuff. So at the top at the moment,
For the answer for "Actress", the answer would be for "Jessica Chastain for Zero Dark Thirty"

Like I said, I cannot help much on this since there are no codes around here.

Cheers and hope this helps.

0 replies

I obviously misunderstood as I did not realize you were expecting some code. I can post the code I used to develop the form that will be used by the players to submit their picks for who is going to win each of the categories. The schema used for that database table is found in post # 3 above. I was going to use a similar form to post the answers, without the name and email fields at which point I was trying to figure out the code needed to check the posted picks against the posted answers. I can try to see if the code you provided in your last post will accomplish my goal.

0 replies

Category Forum

General Discussion

Labels

None yet

Statistics

Views 0 views
Posts 9 posts
Votes 0 votes
Topic users 2 members

2 participants

Notifications

Track thread

You are not receiving notifications from this thread.

PHP Question

9 posts

PHPFusion

Resources

Community

Development