Jump to content

SELECT syntax help please


realjumper

Recommended Posts

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 :-)
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 :-)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.