Qotd

[peterisgb]

i'm having some problems getting this script to work, not sure if its anything i am doing wrong but could someone point out my error.

the database is set up correctly,

table name is Question

tables inside are question, correct which are varchar 255

My Prbblem seems to be its not selecting the info from the database and showing it.

Well heres the code

<?php
session_start();
include "globals.php";
global $db,$ir,$c,$h,$userid;

$q=$db->query("SELECT * FROM Question WHERE question AND correct",$c);

print "<br />
<form name='form1' method='post' action=''>
Todays Question: {$q['question']} <br /><br />
Your Answer.<input name='answer' type='text' id='answer'><br />
<input type='submit' name='Submit' value='Submit'>
</form>";
$dis = "";
if (isset($_POST['Submit']))
{
$password = $_POST['answer'];
if($answer == "{$q['correct']}")
{
print"Correct, You have been awarded 1,000 Gold,
<META HTTP-EQUIV=Refresh CONTENT='10; URL=questions.php'>";
$db->query("UPDATE users SET crystals=crystals+1000 WHERE userid=$userid");
$db->query("UPDATE users SET question=1 WHERE userid=$userid");
event_add("Well done, you got todays Question Correct, you Received 1,000 Gold");
}
else
{
$db->query("UPDATE users SET question=1 WHERE userid=$userid");
event_add("Unlucky, you got today's question wrong");
$dis = "Unlucky, Try again tomorrow
<META HTTP-EQUIV=Refresh CONTENT='10; URL=questions.php'>";
}
}
print "";
print "";


$h->endpage();
?>

[Octarine]

try echo'ing mysql_error() immediately following the query on line 6 - might provide you with an clue.

[peterisgb]

i added the auto id, as to the echo, i tried that but failed, not good at echos lol, but the question still aint displaying lol.

$q=$db->query("SELECT * FROM qotd WHERE question AND correct");

if (!$q)

{

die(mysql_error());

}

mysql_close($q);

like that?

This is what i have now, i've not put the die thingy as it didnt change anything lol

<?php
session_start();
include "globals.php";
global $db,$ir,$c,$h,$userid;

$q=$db->query("SELECT * FROM `qotd` WHERE id AND `question` AND `correct` ORDER BY `id` ASC");



print "

<br />
<form name='form1' method='post' action=''>
Todays Question: <font color='green'>{$q['question']}</font> <br />
Your Answer.<input name='answer' type='text' id='answer'><br />
<input type='submit' name='Submit' value='Submit'>
</form>

";


$dis = "";
if (isset($_POST['Submit']))
{
$answer = $_POST['answer'];
if($answer == "{$q['correct']}")
{
print"Correct, You have been awarded 1,000 Gold,
<META HTTP-EQUIV=Refresh CONTENT='10; URL=questions.php'>";
$db->query("UPDATE users SET crystals=crystals+5000 WHERE userid=$userid");
$db->query("UPDATE users SET question=1 WHERE userid=$userid");
event_add("Well done, you got todays Question Correct, you Received 5,000 Gold");
}
else
{
$db->query("UPDATE users SET question=1 WHERE userid=$userid");
event_add("Unlucky, you got today's question wrong");
$dis = "Unlucky, Try again tomorrow
<META HTTP-EQUIV=Refresh CONTENT='10; URL=questions.php'>";
}
}
print "";


$h->endpage();
?>

Edited August 30, 2012 by peterisgb

[Octarine]

Consider the statement:

SELECT 1 AND '' AND ''

Now examine your WHERE clause; what is it doing?

[peterisgb]

$qotd=$db->query("SELECT * FROM `qotd` ORDER BY `id` ASC");

???

and the where, well if i read this right

 

$qotd=$db->query("SELECT * FROM `qotd` WHERE `id` AND `question` AND `correct` ORDER BY `id` ASC");

ok databse query, select stuff from qotd and the where is asking which tables it needs info and order by the id ASC which erm... displays last question only? :s

[Octarine]

SELECT 0;       -- > 0 (obvious)
SELECT 1;       -- > 1 (-- " --)
SELECT 0 AND 0; -- > 0 (boolean AND operation)
SELECT 0 AND 1; -- > 0 (      -- " --        )
SELECT 1 AND 0; -- > 0 (      -- " --        ) 
SELECT 1 AND 1; -- > 1 (      -- " --        )

 

So... Again I say consider your WHERE clause.

If you were to look at the results (using *your* data) of the following operation:

SELECT `id` FROM `qotd`;
SELECT `question` FROM `qotd`;
SELECT `correct` FROM `qotd`;

and think about how those values are being interpreted in your WHERE clause...

[peterisgb]

$de = $db->query("SELECT question, correct, prize, prizeamount FROM qotd WHERE id ORDER BY `id` DESC");

$qotd = $db->fetch_row($de);

this works :) ty for the help, just need to sort the forms out now.

[Octarine]

I can still see all manner of problems; you are still not addressing the WHERE clause.

Take for instance this example.

The WHERE clause and associated direction (ASC/DESC) does not in fact return the last row as CavellA alluded to; you would need a LIMIT statement for that as shown.

You could also search for a particular `id` (... WHERE `id` = $some_value); in fact, as far as I can this would seem the more sane approach. The order by and limit statement would become redundant then of course.

BTW; the WHERE clause in that example is itself redundant - as I suspect it is in your code; WHERE `field`; will return every row for a non-zero (or non NULL) `field` value.

[peterisgb]

erm :s i kinda get you, i'm still learning,

[Octarine]

The best way I can think of to explain is: you don't ask somebody "where"... you ask them "where ... something is"; ie you clarify the statement. If you simply ask "where", then the chances are you will get an answer but it is highly unlikely to be the answer you want; the same holds true for SQL. Be explicit.

[KyleMassacre]

You can maybe try something like this and just get rid of the whole "WHERE" clause:

$de = $db->query("SELECT question, correct, prize, prizeamount FROM qotd ORDER BY RAND()");

$qotd = $db->fetch_row($de);

The only problem I can see with this is you have a pretty good chance to duplicate the question being asked.

What you may also do is add an extra column in the table called something like "asked" then somewhere after you print it you can update that column with a 1 and try something like this:

$de = $db->query("SELECT question, correct, prize, prizeamount FROM qotd ORDER BY RAND() where asked ==0");
$qotd = $db->fetch_row($de);
print "This is the QOTD";
$db->query("UPDATE qotd SET asked=1 WHERE id={$de['id']}");

 

This is just going off what you have provided us there is still a lot more that should/could be done to make it fully functional.

[peterisgb]

its done now, i managed to get it sorted, thanks for the helps peps