...

View Full Version : direct user based on lookup info



murphyz
12-28-2003, 06:26 PM
The scenario is I'm building a riddle site, and I want riddles to be hidden from a user until they have passed the previous one.

therefore, riddle.php is only viewable when the user is logged into the site - that bit is easy.

Answering the riddle on this page leads the user onto, for example, welldone.php. As well as the usual check to ensure the user is logged in on this page, I want to do another check to make sure they have the correct answer specified on riddle.php - meaning they can continue from where they left off.

I think I would need the answers to be stored under the username as tney pass the riddles and then, when returning to riddle.php (or any of the other riddle pages) there is a lookup performed on the database to see where they were last time.

Any ideas as to the best way to go about this, or specific areas I need to look up on?

Many thanks

Mxx

Spookster
12-28-2003, 06:48 PM
In your database you should have at a minium something like this for tables

TableName: riddles
Columns:
riddle_id, riddle, answer

TableName: users
Columns:
userid, password, last_riddle

When they login you check to see what the last riddle_id was stored in the last_riddle column for their record. Based on that you display the corresponding riddle they should start with for that session.

murphyz
12-28-2003, 07:13 PM
yip, that makes sense, thanks.

for the riddle table, the info would be something such as:

riddleid = 1
riddle = riddle number one
answer = your answer

and as you say, the user_riddle field under the users table would need to update with the riddleid as they put the answer in correctly.

I have no idea what code I would use on the riddle page to check the answer with the one in the database and, if that is correct, increment the user_riddle field by one.

At the moment I am using php to check they are logged in, but using cgi to check they have the correct answer.

mucho confusion - need more coffee :)

Mxx

Spookster
12-28-2003, 07:50 PM
I would hope as far as the answer goes you are giving them a list of answers to choose from? Otherwise it would be pretty much impossible to check for a correct answer using an answer the user types in.

And yes as they answer a questions correctly you should update the field in the users table to whatever question they last answered correctly. As for incrementing the number that would not be a good idea unless you are absolutely controlling the order all riddles are entered into the system. The riddle_id field can just be an autonumber field that increments when a riddle is added to the system. You should grab that value along with the correct answer when displaying the riddle to the user. If they answer correctly then store the riddle_id that was pulled into the field in the users table.

murphyz
12-28-2003, 08:10 PM
In which case I have hit a snag :D

The user does not get a choice of answers, the answer gets typed in and has to match my answer word for word, space for space. I am currently using cgi to check the answer they type and, if incorrect, refresh the page, if correct, move onto the next page.

What I want to be able to do is make sure that the answers are secure so that a user cannot skip riddles without having the answers to the previous ones.

Mxx

Spookster
12-28-2003, 09:05 PM
You can do all of this in PHP. If you do not want them to choose from a list of answers you will have to parse their answer and and build up some kind of smart answer checking system because if they don't spell something correctly or put a more than one space between two words on accident doesn't mean they had the answer wrong but just can't type well. You would need a system that knows to check for every possible wrong way of typing something a user might do. Obviously it would be easier to give them a list of possible answers and let them choose which one they think is correct.

murphyz
12-28-2003, 09:11 PM
Unfortunately the optional answer isn't an option for me, and any answers they do give would be very reliant on spelling (or in some cases miss-spelling) so I don't mind not giving them any leniancy on mistakes. All of the users are used to these type of riddles where their answers are very specific so I don't need to worry about that.

Unfortunately I don't know enough php to figure it all out.

Cheers

Mxx

murphyz
01-02-2004, 06:36 PM
I'm approaching this from a slightly different angle to previous, using similar tables as suggested by Spookster above.

I currently have one page which needs to display a specific riddle depending on the user viewing the page and how far they have got.

Therefore, I want it so that if the user_riddle field within phpbb_users for someone says '2', then riddle '2' should be displayed. However, I keep getting an error on line 5 of this code:


<?php
// Request current riddle ID for user
$current = @mysql_query('SELECT `user_riddle` '
. 'FROM `phpbb_users` '
. 'WHERE 1 AND `username` = \'murphyz\' LIMIT 0, 30');
if (!$current) {
die('<P>Error performing query: '. mysql error() .
'</p>');
}
// Display the current riddle in a paragraph
while ( $row = mysql_fetch_array($current) ) {
echo('<p>' . $row['user_riddle'] . '</p>');
}

// Request the text of a specific riddle
$result = @mysql_query('SELECT `riddle` '
. 'FROM `phpbb_riddle` '
. 'WHERE 1 AND `riddleid` = 2 LIMIT 0, 30');
if (!$result) {
die('<p>Error performing query: ' . mysql_error() .
'</p>');
}

// Display the text of selected riddle in a paragraph
while ( $row = mysql_fetch_array($result) ) {
echo('<p>' . $row['riddle'] . '</p>');
}

?>

At the moment the above should just store the user_riddle number into the $current variable and then display it on the page. Once I get this to work I would like to change the line:

. 'WHERE 1 AND `riddleid` = 2 LIMIT 0, 30');
to act something such as

. 'WHERE 1 AND `riddleid` = $current LIMIT 0, 30');

Any help on why the first part of the code isn't working would be greatly appreciated.

Mxx

Nightfire
01-02-2004, 07:00 PM
Need to add backslashes

. 'WHERE 1 AND `username` = \'murphyz\' LIMIT 0, 30');

murphyz
01-02-2004, 07:03 PM
I had those in the script, but the 'php' BBCODE button seems to have altered it on it's own to read 'murphyz'.

If, as moderator, you can edit the first post you can see they are in there.

EDIT: Darn, thought you were a mod :)

Mxx

Nightfire
01-02-2004, 07:06 PM
Jsut noticed, what is this 1 AND in the query doing?

It's probably that that's throwing the error as they're not doing anything

<edit>I'd be dangerous if I was a mod ;) lol</edit>

murphyz
01-02-2004, 07:09 PM
I have no idea what they are doing, I was using the php output produced my phpmyadmin. I attempted removing it, but still nothing.

The second part of the code, dealing with the $result, works fine on it's own.

Mxx

murphyz
01-03-2004, 01:53 AM
hmm,

The above seems to work fine if used on just one line like such:


$result = @mysql_query('SELECT user_riddle FROM phpbb_users WHERE username=\'murphyz\'');

Now, I'm curious as to whether or not I am able to use the $result within another mysql_query?

Can't get the following to work...


$currentriddle = @mysql_query('SELECT riddle FROM phpbb_riddles WHERE riddleid=$result');

Cheers

Mxx

Nightfire
01-03-2004, 02:20 PM
Variables don't work when they're in single quotes. For it to work that way, use double quotes.

$currentriddle = @mysql_query("SELECT riddle FROM phpbb_riddles WHERE riddleid=$result");

murphyz
01-03-2004, 03:08 PM
That comes back with the following error:


Error performing query: You have an error in your SQL syntax near 'id #24' at line 1

Earlier I was testing writing information to a table, such as attempted answers from a particular user. The riddleid when I went to do this kept coming back as Resource #24 - instead of '1'. Now it seems that #24 is appearing in the above error.

any idea why that might be?

Mxx

(thanks for your patience and help)

murphyz
01-05-2004, 01:31 AM
Okay, this thing is driving me insane now.

Please could someone who is bored and has the knowhow set up the following tables and try the following script? Many thanks if you can...

Table
phpbb_users
Fields
username = murphyz
user_riddle = 1

Table
phpbb_riddles
Fields
riddleid = 1
riddle = "any test text here"

Script

<?php

// Request the user_riddle number
$result = @mysql_query("SELECT user_riddle FROM phpbb_users WHERE username = \"murphyz\"");
if (!$result) {
die("<p>Error performing query: " . mysql_error() .
"</p>");
}

// Display the riddle number
while ( $row = mysql_fetch_array($result) ) {
echo("<p>" . $row["user_riddle"] . "</p>");
}


// Request the text of selected riddle

$currentriddle = @mysql_query("SELECT riddle FROM phpbb_riddles WHERE riddleid = $result");
if (!$currentriddle) {
die("<p>Error performing query: " . mysql_error() .
"</p>");
}

// Display the text of selected riddle in a paragraph
while ( $row = mysql_fetch_array($currentriddle) ) {
echo("<p>" . $row["riddle"] . "</p>");
}

?>

The Display Riddle Number part of the script is not really necessary, I just had it in there to make sure that the $result variable was actually picking it up.

Note, using \"murphyz\" instead of $result within the $currentriddle database SELECT line works....but I really need the lookup to be on the riddleid ($result) instead.

Huge thanks to anyone who can try this, even more to anyone who gets it to work.

ta.

Mxx

Nightfire
01-05-2004, 06:14 AM
Ahh, I see what you're doing now. That won't work as your second query is:

SELECT riddle FROM phpbb_riddles WHERE riddleid = @mysql_query("SELECT user_riddle FROM phpbb_users WHERE username = \"murphyz\"");

Try something like this



<?php

// Request the user_riddle number
$result = @mysql_query("SELECT user_riddle FROM phpbb_users WHERE username = \"murphyz\"");
if (!$result) {
die("<p>Error performing query: " . mysql_error() .
"</p>");
}

$num_rows = mysql_num_rows($result); // see if any results are found

if($num_rows == "1"){
// Request the text of selected riddle
while($riddleid = mysql_fetch_row($result)){
$currentriddle = @mysql_query("SELECT riddle FROM phpbb_riddles WHERE riddleid = $riddleid['riddle']");
if (!$currentriddle) {
die("<p>Error performing query: " . mysql_error() .
"</p>");
}

// Display the text of selected riddle in a paragraph
while ( $row = mysql_fetch_array($currentriddle) ) {
echo("<p>" . $row["riddle"] . "</p>");
}
}

?>

murphyz
01-05-2004, 11:00 AM
Thanks, I'll test this when I get home tonight.

This:
SELECT riddle FROM phpbb_riddles WHERE riddleid = @mysql_query("SELECT user_riddle FROM phpbb_users WHERE username = \"murphyz\"");
makes absolute sense now that you point it out.

However, your suggestion confuses me slightly...could you explain what it does?
I see that you have put in the 'while' and the $riddleid variable, but what is the ['riddle'] part for?

Many thanks for this.

I'll get there slowly :thumbsup:

Mxx

Nightfire
01-05-2004, 04:40 PM
Sorry about that, was gonna put comments in to explain but I was running out of time as I had to go to work.




while($riddleid = mysql_fetch_row($result)){
$currentriddle = @mysql_query("SELECT riddle FROM phpbb_riddles WHERE riddleid = $riddleid['riddle']");

if (!$currentriddle) {
die("<p>Error performing query: " . mysql_error() .
"</p>");

}


$riddleid['riddle'] is just the same as what you did for $row["riddle"] in your second while loop. I've just inserted this directly into the query. In youyr while loops, you've put $row = mysql_fetch_array($result) I've just used riddleid instead of row. No difference at all, just makes it easier to see what it's for. The ['riddle'] is just the field name I've got the riddle from

Hope I've made it clear. I'm lacking time once again so I've probably missed something or mentioned something wrong

murphyz
01-05-2004, 08:07 PM
Unfortunately this brings nothing back to the page :(

I'm wondering if - while($riddleid = mysql_fetch_row($result)){ - is correct? Can you specify the 'while' to be something that isn't previously mentioned? I thought that 'while' was used as a way to confirm that something previously mentioned is still true?

Mxx

murphyz
01-05-2004, 09:41 PM
okay, I've broken this down a little bit more, and the reason I think it is failing is that the initial query for the user riddle is storing the variable as a Resource ID and not the value of the field.

I can get - $currentriddle = @mysql_query("SELECT riddle FROM phpbb_riddles WHERE riddleid = $riddleid"); - to work if I specify - $riddleid = 3; But I cannot specify that $riddleid = $result because that would bring back the Resource ID.

Therefore, is there any way that I can specify the $riddleid = the value of the mysql_fetch_row($result)?

I hate php :confused:

Mxx

Nightfire
01-05-2004, 10:34 PM
Right, ignore my script above, I had only been awake 10 mins at the most. I've finally had some sleep and this is what I've come up with and I've tested it this time :)



<?php
// Request the user_riddle number
$result = @mysql_query("SELECT user_riddle FROM phpbb_users WHERE username = \"murphyz\"");
if (!$result) {
die("<p>Error performing query: " . mysql_error() . "</p>");
}

while ( $row = mysql_fetch_array($result) ) {
// Display the riddle number
$user_riddle = $row['user_riddle'];
echo '<p>'.$user_riddle.'</p>';

// Request the text of selected riddle
$currentriddle = @mysql_query("SELECT riddle FROM phpbb_riddles WHERE riddleid = '$user_riddle'");
}

if (!$currentriddle) {
die("<p>Error performing query: " . mysql_error() . "</p>");
}

// Display the text of selected riddle in a paragraph

while ( $row = mysql_fetch_array($currentriddle) ) {
echo("<p>" . $row["riddle"] . "</p>");
}

?>

That gave me

<p>1</p>
<p>Any text here</p>

murphyz
01-05-2004, 11:05 PM
Marry me? :thumbsup:

That's marvellous, and it's amazing what sleep can do. I can't thank you enough for this. Cheers.

Now to grab a beer and move onto the next stage of changing 'murphyz' to 'whoever is logged in', comparing an inputted answer with that in the database, and incrementing that pesky user_riddle if it's correct.

It's going to be a long month! :rolleyes:

Thanks again

Mxx

murphyz
01-07-2004, 01:06 AM
What a surprise - moi again :(

With so much help I am doing very well, and have almost finished what I need to do.

My page now identifies whoever is logged in, takes the riddle number that they are on from the database and uses this to pull a riddle from another table. When they type an answer it is checked against the database and if they type the wrong answer, the page is refreshed and they get a 'bad luck' message, if they type the right answer they get a message saying well done.

At this stage they obviously need to get onto the next riddle, which is where the problem lies.

The field that needs updating is called user_riddle and has an INT set to int(11), not null and with a default of 1 (which is the number I want all users to start on).

When they get a riddle right, I need this number to increment by 1 so that the right riddle is then pulled out of the database and then have the page reload.

As usual the script works with a number put in manually - it's getting it to auto_increment that is the issue.

I'm using the following:


$youranswer = $_POST['youranswer'];
if ( $youranswer == $user_answer )
{
global $userdata;
echo( 'Well done ' .$userdata[username].', that was the right answer' );
$incrementin = $userdata[user_riddle] + 1;
$sql = 'UPDATE `phpbb_users` SET `user_riddle` = \'3\' WHERE `username` = \'anonymous\' ';
if ( @mysql_query($sql) ) {
echo('<p>Update affected ' . mysql_affected_rows() .
' rows.</p>');
} else {
die('<p>Error performing this update: ' . mysql_error() .
'</p>');

and it's this line I have the issue with - $sql = 'UPDATE `phpbb_users` SET `user_riddle` = \'3\' WHERE `username` = \'anonymous\' ';

the $incrementin takes the current user_riddle and adds 1 - and this is the value I need to write update the database with. Is this the way I should be attempting it, or is there a better way?

eeps - long post, sorry.

Mxx



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum