Inner join problem?

[KjB31ca]

I am somewhat of a newbie and think this is a simple fix, but I have been having problems and can't seem to figure it out. I am trying to JOIN two tables, but am not getting the results I would like. I am getting the correct question number displayed whenever I try different instances, but it always defaults to the "DTCAP" question (q_code).

 

I have included the snippet of code and how the table structure looks. Any help would be greatly appreciated!

 

-------------------------------------------------------------------------------------

 

//Testing to ensure that the $_SESSION['code'] is DTCAP, CNROW, or MONFW after user chooses option

echo $_SESSION['code'];

 

$query_question=mysql_query("SELECT * FROM adventure_questions q
            INNER JOIN users_adventures u ON q.q_number = u.ua_question
            WHERE u.ua_username = '$_SESSION[username]' AND u.ua_code='$_SESSION

'");

 

$question_info=mysql_fetch_array($query_question);

 

echo $question_info['q_number'].". ".$question_info['question'];

 

-------------------------------------------------------------------------------------

 

adventure_questions

q_id     q_code       q_number     question

1          DTCAP              1         DTCAP question 1

2          DTCAP              2         DTCAP question 2

26       CNROW              1        CNROW question 1

27       CNROW              2        CNROW question 2

51       MONFW              1        MONFW question 1

52       MONFW              2        MONFW question 2

 

users_adventures

ua_id    ua_username       ua_code      ua_question    ua_score

1                kjb31ca             DTCAP              1                   0

2                kjb31ca            CNROW              2                 10

3                kjb31ca            MONFW              1                  0

Edited May 20, 2014 by KjB31ca

[ginerjm]

What is that last 'thing' in your query statement?  I've never seen an index like that!

 

Try adding a test of your query result - I find it hard to believe that your query is actually producing anything.

[KjB31ca]

Oops, don't know how that happened, when I copied it, but it should be:

 

WHERE u.ua_username = '$_SESSION[username]' AND u.ua_code='$_SESSION[ code ]'");

Edited May 20, 2014 by KjB31ca

[ginerjm]

index names should have quotes on them.  Plus - one should ALWAYS check (!) the results of a query to be sure it actually ran.  Add this:

 

if (!$query)
{
   echo "Query did not run - error msg is: ".MySQL_error();
   exit();
}

after your call to MySQL_query.

 

PS - you REALLY should use a different interface to your db, The MySQL_* functions have been deprecated (not depreciated) and are soon to be removed.  Start learning mysqlI or PDO and use it.

[KjB31ca]

Ok thanks for the tips. My query works perfectly if the session code is DTCAP, but once I change the session code it will still match the question number correctly, but always return the "DTCAP question". It seems like I am properly joining the tables, but the WHERE condition isn't working. Did I set that portion up correctly?

[ginerjm]

echo out the query statement before you try and run it so that we can see the ACTUAL query being submitted.

 

BTW - please tell us what you refer to 'question number displayed'.

[Psycho]

index names should have quotes on them.

 

Not when the array value is parsed within a double quoted string as the OP has. there are some scenarios where the array must be included within curly braces (e.g. multi-dimensional array). But, for a simple array value with a textual index it is perfectly value to not use quotes around the index inside a double quoted (or HEREDOC syntax) string. However, I personally don't do that. I always enclose my variables within curly braces and use quotes around the array indexes.

 

See third echo in example #8: http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.double

 

And later on that page is a reference that states

 

 

. . . quoted keys only work using the curly brace syntax

[KjB31ca]

Here is what I think you were wanting:

 

array(34) { [0]=> string(1) "2" ["q_id"]=> string(1) "2" [1]=> string(5) "DTCAP" ["q_code"]=> string(5) "DTCAP" [2]=> string(1) "2" ["q_number"]=> string(1) "2" [3]=> string(17) "DTCAP question #2" ["question"]=> string(17) "DTCAP question #2" [4]=> string(41) "adventures/dtcap/images/clue02-spiral.png" ["q_pic"]=> string(41) "adventures/dtcap/images/clue02-spiral.png" [5]=> string(6) "spiral" ["q_type"]=> string(6) "spiral" [6]=> string(2) "10" ["q_value"]=> string(2) "10" [7]=> string(1) "3" ["ua_id"]=> string(1) "3" [8]=> string(7) "kjb31ca" ["ua_username"]=> string(7) "kjb31ca" [9]=> string(5) "CNROW" ["ua_code"]=> string(5) "CNROW" [10]=> string(1) "1" ["status"]=> string(1) "1" [11]=> string(1) "2" ["ua_question"]=> string(1) "2" [12]=> string(2) "10" ["ua_score"]=> string(2) "10" [13]=> string(1) "0" ["q_score"]=> string(1) "0" [14]=> string(1) "0" ["ua_wrong"]=> string(1) "0" [15]=> string(1) "0" ["ua_location"]=> string(1) "0" [16]=> string(1) "0" ["ua_challenge"]=> string(1) "0" }

 

***Note: There are extra columns in the tables that I originally ommitted because they have no relevance to my problem, but they are shown up here.

 

 

So to clarify what I am saying:

The users_adventures table shows what question a particular user is on, what adventure, and their score, so they can log out and return to a previous saved session.  I want to match that up with the adventure_questions table so when they return to a previous session it will match up with what question they are on. Whenever I have a session code different from DTCAP it still returns the DTCAP question from the adventure_questions table, so its not distinguishing between the session code.

[ginerjm]

Not even close.  I want to see what gets echoed out from your query statement when you add this line:

 

echo $query_question;   // ADD THIS LINE RIGHT HERE AND THEN PASTE THE OUTPUT TO THE FORUM.
$question_info=mysql_fetch_array($query_question);

[KjB31ca]

ok I put:

 

echo $query_question;
$question_info=mysql_fetch_array($query_question);

 

OUTPUT:

 

Resource id #7

 

 

Now I'm even more lost

[Psycho]

No, create your query as a string and echo that. Don't put the query directly in the query function

 

$query = "SELECT *
          FROM adventure_questions q
          INNER JOIN users_adventures u ON q.q_number = u.ua_question
          WHERE u.ua_username = '$_SESSION[username]' AND u.ua_code='$_SESSION[code]'"
echo $query;
$query_question=mysql_query($query) or die(mysql_error());

 

I think I see the problem, but will take me a couple minutes to write out.

[Psycho]

I think I see the problem. You are only joining the two tables using the q_number/ua_question and that is joining records with different "codes" - which I don';t think you want. [PRO TIP: Give your field names values that make it obvious where foreign keys are used. You can even give them the same field name. What you have now is unintuitive].
 
So, using the example tables you provided above, this would be the result of the JOIN - before the WHERE clause is applied

 

q_id | q_code | q_number | question         | ua_id | ua_username | ua_code | ua_question | ua_score
-----------------------------------------------------------------------------------------------------
 1      DTCAP    1          DTCAP question 1   1       kjb31ca       DTCAP     1             0
>1      DTCAP    1          DTCAP question 1   3       kjb31ca       MONFW     1             0
 2      DTCAP    2          DTCAP question 2   2       kjb31ca       CNROW     2             10
 26     CNROW    1          CNROW question 1   1       kjb31ca       DTCAP     1             0
>26     CNROW    1          CNROW question 1   3       kjb31ca       MONFW     1             0
 27     CNROW    2          CNROW question 2   2       kjb31ca       CNROW     2             10
>51     MONFW    1          MONFW question 1   1       kjb31ca       DTCAP     1             0
 51     MONFW    1          MONFW question 1   3       kjb31ca       MONFW     1             0
 52     MONFW    2          MONFW question 2   2       kjb31ca       CNROW     2             10

On the rows preceeded with '>' the q_code in the adventure_questions table doesn't match the ua_code in the users_adventures table. Perhaps you want to JOIN on both the ids and the code?
 
If the code in the WHERE caluse is any of the three possible values I see above, then at least one of the first three records would be included in your result set (which all contain DTCAP). You are filtering out the records that don't contain the code in the second table, but not those from the first. You then, use the value from the first table.
 
So, if my hunch is correct, this might be the correct query:

$query = "SELECT *
          FROM adventure_questions q
          INNER JOIN users_adventures u
             ON q.q_number = u.ua_question
            AND q.q_code = u.ua_code
          WHERE u.ua_username = '$_SESSION[username]' AND u.ua_code='$_SESSION[code]'";

 

 

However, if that is the case, then your database is likely not structured correctly. It doesn't make sense to duplicate the code in both tables. It probably only needs to apply to the users_adventures table. So, in my opinioin, your current problem is a result of a bad database design.

Edited May 20, 2014 by Psycho

[KjB31ca]

YEEEEEEAAAHHHHH!!!!! Oh man, thanks so much. I just needed to add: AND q.q_code = u.ua_code. All is working good. I appreciate all the time and help.

[Psycho]

YEEEEEEAAAHHHHH!!!!! Oh man, thanks so much. I just needed to add: AND q.q_code = u.ua_code. All is working good. I appreciate all the time and help.

 

OK, but - as I stated - the real problem seems to be with the table structure. That "solution" is really a hack. Is there some reason you need the 'code' defined in both tables?

 

Here is how I would define those tables.

 

adventure_questions
q_id     q_number   question
1          1         DTCAP question 1
2          2         DTCAP question 2
26         1         CNROW question 1
27         2         CNROW question 2
51         1         MONFW question 1
52         2         MONFW question 2
 
users_adventures
ua_id    ua_username   ua_code   q_number   ua_score
1          kjb31ca       DTCAP      1          0
2          kjb31ca       CNROW      2          10
3          kjb31ca       MONFW      1          0

 

Note the use of q_number in BOTH tables. The query then becomes very simple

 

SELECT *
FROM adventure_questions q
INNER JOIN users_adventures u USING(q_number)
WHERE u.ua_username = '$_SESSION[username]' 
  AND u.ua_code='$_SESSION[code]'

[Barand]

Given those sample questions I would say the code belongs in the question table

adventure_question
------------------
  q_id     (PK)
  q_code 
  q_number 
  question

and

users_adventures
-----------------------
ua_id       (PK)
ua_username   
q_id        (FK)
ua_score

[Psycho]

 

Given those sample questions I would say the code belongs in the question table

 

Maybe. I based my decision on the fact that he has two records in the users table that reference the same record in the adventures table. But, the two records in the user's table have different 'codes'.

[Barand]

True. Begs the question - What is the code and what is it's relationship to the two entities?

[KjB31ca]

I haven't worked too much using foreign keys, so I am doing some reading on implementing that. My only question is won't it be harder for me when I am looking at the populated data in the users_adventures table because I don't see a code next to the user, I would only see a q_id#? A user can have multiple codes, meaning that they have multiple "adventures" open at the same time. They can start a "DTCAP" adventure, and answer 10 questions, quit the session, and return another time and open a "CNROW" adventure, quit that session, and start another adventure and so on. I hope that makes sense how I am wording that.

 

 

adventure_questions

q_id     q_code       q_number     question

1          DTCAP              1         DTCAP question 1

2          DTCAP              2         DTCAP question 2

26       CNROW              1        CNROW question 1

27       CNROW              2        CNROW question 2

51       MONFW              1        MONFW question 1

52       MONFW              2        MONFW question 2

 

 

users_adventures (Your version) - How can I tell which is DTCAP adventure, which is CNROW, MONFW,etc, without having to look at the other table???

ua_id    ua_username       q_id        ua_score

1                kjb31ca             1                   0

2                kjb31ca             27                10

3                kjb31ca             51                  0

 

 

users_adventures (My old version)

ua_id    ua_username       ua_code      ua_question    ua_score

1                kjb31ca             DTCAP              1                   0

2                kjb31ca            CNROW              2                 10

3                kjb31ca            MONFW              1                  0

 

 

users_adventures (Suggested version)

ua_id    ua_username       ua_code            q_id          ua_score

1                kjb31ca             DTCAP              1                    0

2                kjb31ca            CNROW              27                 10

3                kjb31ca            MONFW              51                  0

 

 

Can I keep the ua_code in the users_adventures or is that just redundant and bad practice of implementing a database?

[Psycho]

You should not store duplicate data in multiple tables. They are called relational databases because you can relate data between tables. That's the whole point of having associations between tables.

 

If the "code" can be changed each time the test is taken, then the code belongs to the "test" not to the questions or the answers. I would think you need three tables:

 

the question table which holds just the questions and the data associated with the questions. E.g.

question_id (primary key), question_number and question_text

 

Then for each "adventure" you would record in TWO tables. One table would just be to record the basics of the adventure, including the code (without the answers). It could look like that

Table: adventures

adventure_id (primary key), user_id (foreign key), adventure_date, code

 

Then, you would record the users answers for each adventure in a third table. An example:

adventure_answers:

answer_id (primary key)

adventure_id (foreign key)

question_id (foreign key)

answer_id (foreign key - assume there is answer table)

[KjB31ca]

Ok, thanks for all the advice. I will be learning more on databases and will try to implement this to my database.