SELECT problem cont.

Anon

I posted a problem with a select query for a mySQL database here yesterday. Thank you for all of your responses but none of them work and I cannot find the answer to what seems to be a simple problem. There are three possible scenarios. The database could have a 1, 0, or it will be empty for that column and username. I want to check the database to see if there is a 1 or 0 in it. If there is I want to redirect to a new page or otherwise move on. The code is for a test and if the student has already answered the question I want to forward them to a different page so they cannot update the form again. It seems like a simple select query and then if statement. So far I have tried these code bits.

mysql_query("SELECT questionone FROM test WHERE id = $session[id]");
if ( $questionone == '0' || $questionone == '1' )
{
header("Location: http://www.newpage.net/moduleone/answererror.php?".SID );
}

This code bit will forward to the newpage but the problem is if you try to go to a question where the database is empty it still forwards to the newpage. Somehow the database query is not recognizing the database is empty and skipping this block.

mysql_query("SELECT questionone FROM test WHERE id = $session[id]");
if ( questionone == '0' || questionone == '1' )
{
header("Location: http://www.newpage.net/moduleone/answererror.php?".SID );
}

If you take away the $ in front of the questionone in the if statement then the code is simply ignored and is skipped no matter what is in the database 1,0 or empty. Not what I need.

Finally I tried this.

$query = mysql_query("SELECT questionone FROM test WHERE id = $session[id]");
$answer = mysql_fetch_row($query);
if (!$answer[0] || ( $answer[0] == '1' || '0' ))
{
header("Location:http://www.newpage.net/moduleone/answererror.php?".SID );
}

Same thing here. This code does not recognize the database is empty and continues to execute to the new Location header. I don't understand why these code bits do not do what they are told. If there is a 1 or 0 in the database then go to the new location or otherwise skip. They do not have this action. Maybe I could check to see if the database is empty first and then redirect according to those results? I'm beginning to think this sort of thing is not possible but it has to be. It is so simple in concept but neither I or anyone else is able to solve this problem so far.

Should I try to go about this in a different way?

Thanks again for your reply's

Anon

the first value of the $answer array is the first field of a record, are the 0 and 1 situated on the 1st field. If not check on which. U can also use fetch_array... then u fill in $answer["field name"]

Anon

Maybe if you give a little background on your project, someone here might be able to suggest a different approach. I do not fully understand what you need but, it appears you are trying to do it the hard way.

Anon

I'm guessing here but I've had similar problems in the past.

Where I need to have 2 possible states in a field, I tend to avoid zero as one of the valid options. This is because I've had situations where (probably due to my crappy coding) PHP appeared to interpret zero and null as the same thing.

If you were to use say 1 and 2 as the states then you could set your select to only include records where $questionone > 0 and id = $session[id].

Hope this helps

Paul

Anon

Basically I have a php test that is collecting answers from questions and putting them into a database. The test works great but I want to deny the student from UPDATEing the database more than once. My approach was to check to see if the student has already answered the question and then redirect to a different page if they did. If the database was empty then they see the question to answer it. I set it up this way so the student could return to the test if they were not able to complete it in one try. They can only see one question at a time so I liked this approach but it doesn't seem to be working very well.

Anon

Finally I tried this.

  $query = mysql_query("SELECT questionone FROM test WHERE id
   = $session[id]");
  $answer = mysql_fetch_row($query);

Finally you are on the right track, since this is the first time you've actually retrieved the results. However, you might have more success if you just let the database decide whether the column is null or not, i.e.:

"SELECT questionone FROM test WHERE id $session[id] and questionone is not null"

If you do this, you don't even have to retrieve the data; a simple count of the result rows tells you whether or not the column is null:

if (mysql_num_rows($query) > 0)
  header('Location: ...

Secondly, your comparison statement is still incorrect. The string value '0' is <i>always</i> true in a logical comparison ('cause it's a valid string!) You need to say ($answer[0] == '1' || $answer[0] == '0').

Anon

Kirk Parker your advice on testing the NULL of the row was what I was looking for and seems to be the only thing that worked!!! I was curious if it is possible to make a SELECT statement that selected two or three rows and then checked to see if anyone of them was NULL or if you would just make three statements?

Thanks again.

Anon

possible to make a SELECT statement that selected two or three rows

Rows, or columns? If the former, then it will take as many different
queries as you have different combinations of criteria. I.e. if you
want to find out how many people haven't answered question 3
yet, you could say

select count(*) from table where question3 is null;

but maybe that's not what you're asking.

Anon

Colums was what I was asking. To make it work I made up different select statements and then one if statement as shown below. It seemed to work fine for what I was doing. This system of checking the database vs. using UNIQUE values works for me because if the power went out or the computer turned off the student could return to the part of the test they left off on by clicking links on the answererror page instead of denying access to the entire test. Using UNIQUE values and making complicated tables wasn't what I was looking for in this project. I couldn't use NOT NULL statements because they were inserting values into the database before the student arrived at the question. Since this is my first project I wanted to keep it fairly simple until my programming skills improve.

Thanks.

$query = mysql_query("SELECT questionone FROM testtwo WHERE id = $session[id] and
questionone is not null");
$query1 = mysql_query("SELECT questiontwo FROM testtwo WHERE id = $session[id] and
questiontwo is not null");
$query2 = mysql_query("SELECT questionthree FROM testtwo WHERE id = $session[id] and
questionthree is not null");
if ((mysql_num_rows($query) > 0) && (mysql_num_rows($query1) > 0) &&
(mysql_num_rows($query2) > 0))
{
header("Location:...
}