SELECT syntax help please

[realjumper]

Hi,

I'm not sure how to go about this so perhaps someone can help me?

This is the code (in simple terms) that I wish to use...

[code]

// Retrieve all the data from the table
$result01 = mysql_query("SELECT * FROM kanji.$username WHERE a='a' AND b='b' AND c='z'")
or die(mysql_error());

// store the record of the table into $row01
$row01 = mysql_fetch_array( $result01 ); 

echo "$row01[a]";
echo "<br>";
echo "$row01[b]";
echo "<br>";
echo "$row01[c]";


[/code]

Obviously this will return nothing since all the conditions are not true. But, for sure a=a and b=b even though c does not equal z.

How can I write a select query that will return only the records that are true and meet the WHERE condition? In the above code sample I want to return 'a' and 'b', since they are true, but not return 'c' since this is not true.

[Moon-Man.net]

Have a look for a "OR" statment.
"SELECT field FROM table WHERE field1='1' OR field2='2' OR field3='55' ;"
Should return field 1 and field 2 but not field 3.
If there is a OR statment

[crashmaster]

not..It will return only FIELD..

Look at expression "SELECT [b]field[/b] ... "
so than you do : $r = mysql_fetch_array($query);
and $r[field] ....
Nothing Else

[realjumper]

Moon-Man.net: No, that will return only 'field 1' since that is the correct condition before the next 'OR'

crashmaster: I don't see how what you are saying is different to what I already have.

I just want to return 'a' and 'b' (in my query above) since they are both true, but not return 'c', since it isn't true

[AndyB]

[quote]How can I write a select query that will return only the records that are true and meet the WHERE condition? In the above code sample I want to return 'a' and 'b', since they are true, but not return 'c' since this is not true.[/quote]

You can't.  [b]ALL[/b] of the conditions must be true for the WHERE to find a record that matches your conditions.

[realjumper]

Yup, that's what I was afraid of AndyB...but I was clutching at straws in hope!!! Thanks

[AndyB]

Don't be afraid of something that acts rationally. Perhaps there's a way around the problem (whatever the real problem is). Find all matches for a='a' AND b='b', but only display/process the ones where c='c' by  adding some conditional logic to the record display loop/statement.

[realjumper]

Okay, the crux of the matter is this:

At the college here we teach Japanese. The application presents a sentence (in japanese) with a certain part of the sentence underlined, which is in effect a question. There are 20 questions. Each user gets their own table created, $username, and each users table has the columns 'actual_sentence', 'correct_answer', 'your_answer'. This is repeated 20 times so we end up with 'actual_sentence1', 'correct_answer1', 'your_answer1'.......'actual_sentence2', 'correct_answer2', 'your_answer2'...all the way up to 20. The questions (sentences) are displayed one at a time in a random sequence. The total pool of questions is over 500, but I am selecting 20 at one time, at random.

Lets suppose a user gets a final score of 16 correct and 4 incorrect. I want to let the user resit their incorrectly answered questions in the same way. ie to display the 4 incorrect questions, at random, one at time....until eventually they end up with all questions correctly answered. They can then choose another test, which will reset the fields to 'NULL' before re-populating them with another 20 questions.

I know this would be more simple if I made 20 fields, rather than 20 columns, but for many reasons it can't be that way.

So at the end of the test, I want to allow the users to re-sit their wrong answers. I have all the info in the table, it's just a matter of getting the info out!!

In laymans terms, I want to select * where 'your_answer1 != correct_answer1' AND WHERE 'your_answer2 != correct_answer2 etc etc.'

In the end, I want to be able to show the incorrectly answered questions, at random one at a time, and let the user answer them until eventually (hopefully) they have 100% correct. Then they can choose a new test.

I'll go and have a coffee and a think :-)

[AndyB]

While you're having coffee and a think ... think about altering your database tables.

Assuming your Q and A table has fields like question_id, question, answer and you have a second table for results with fields id, user_id, question_id, correct.  Pull 20 questions to display - if right or wrong answer add a record to the results table.  When all 20 are answered, SELECT the data WHERE user_id = this user.  That'll tell you how many they got right/wrong and allow you to determine which were wrong, repeat them, ad nauseam until all records for that user_id have YES for correct.  Then, but only then, DELETE records WHERE user_id is that user ...

... approximately, since I also need coffee  ;D

[realjumper]

Thanks Andy....I kind came to a similar conclusion while I was having coffee. I was hoping to keep the db as compact as humanly possible and I was searching for ways to this without adding another table but I think that will probably be the only way. No big deal really I guess as the tables will get dropped when the user logs out or the session expires.

Thanks :-)