Thread: Query problem
View Single Post
  #1 (permalink)  
Old August 12th, 2004, 12:11 PM
asinning asinning is offline
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query problem

I'm having the strangest result with a query on across several tables. I'd be most grateful for any insight! Thanks!

-- Andrew

The table gameResults has a primary key userGameEventID

  INTEGER (11) NOT NULL AUTO_INCREMENT

This is an integer, so I don't expect to need to put a quote around the value in my query, but when I exclude the quotes from the very last word in following query, I get zero records back:

  SELECT * FROM gameResults, games, users, questionResults, questions
  WHERE gameResults.gameID = games.gameID
  AND gameResults.userID = users.userID
  AND gameResults.userGameEventID = questionResults.userGameEventID
  AND questions.gameID = gameResults.gameID
  AND questionResults.quesID = questions.quesID
  AND gameResults.userGameEventID = '3'

i.e: if the last line is just

  AND gameResults.userGameEventID = 3

then I get zero results.

Also very strange, and even more troubling, since I don't have a work-around: if I try to specify

  ORDER BY questions.quesID

I also get zero records.


Below is the structure of the tables in the query:

    // Table structure for table 'users'
    $sql = "CREATE TABLE users (
        userID char(50) NOT NULL default '',
        firstName char(20) default NULL,
        lastName char(20) default NULL,
        password char(20) default NULL,
        status char(20) NOT NULL default '',
        KEY status (status),
        KEY userID (userID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);

    // Table structure for table 'games'
    $sql = "CREATE TABLE games (
        gameFile varchar(50) default NULL,
        gameFolder tinytext,
        description text,
        allowAll tinyint(3) unsigned NOT NULL default '0',
        allowRepeat tinyint(3) unsigned NOT NULL default '0',
        allowResultsView tinyint(3) unsigned NOT NULL default '0',
        allowQuestionReview tinyint(3) unsigned NOT NULL default '0',
        allowCorrectView tinyint(3) unsigned NOT NULL default '0',
        showResultsAfter tinyint(3) unsigned NOT NULL default '0',
        allowAnonAccess tinyint(3) unsigned NOT NULL default '0',
        gameID int(11) NOT NULL auto_increment,
        PRIMARY KEY (gameID),
        UNIQUE KEY gameID (gameID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);

    // Table structure for table \"questions\"
    $sql = "CREATE TABLE questions (
        gameID int(11) NOT NULL default '0',
        quesID char(10) default '',
        category char(100) default '',
        type char(10) default '',
        format char(10) default '',
        pointValue smallint(6) NOT NULL default '0',
        stem char(255) default '',
        respCount tinyint(4) default '0',
        resp1 char(255) default '',
        resp2 char(255) default '',
        resp3 char(255) default '',
        resp4 char(255) default '',
        correct1 tinyint(4) default '0',
        correct2 tinyint(4) default '0',
        correct3 tinyint(4) default '0',
        correct4 tinyint(4) default '0',
        KEY quesID (quesID),
        KEY gameID (gameID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);


    // Table structure for table 'gameResults'
    $sql = "CREATE TABLE gameResults (
        gameID int(11) NOT NULL default '0',
        userGameEventID int(11) NOT NULL auto_increment,
        userID char(50) NOT NULL default '',
        startTime datetime default NULL,
        endTime datetime default NULL,
        score mediumint(9) NOT NULL default '0',
        pointsEarned mediumint(9) NOT NULL default '0',
        pointsLost mediumint(9) NOT NULL default '0',
        questionsCount smallint(6) NOT NULL default '0',
        seenCount smallint(6) NOT NULL default '0',
        answeredCount smallint(6) NOT NULL default '0',
        correctCount smallint(6) NOT NULL default '0',
        PRIMARY KEY (userGameEventID),
        KEY score (score),
        KEY gameID (gameID),
        KEY userID (userID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);


    // Table structure for table 'questionResults'
    $sql = "CREATE TABLE questionResults (
        userGameEventID int(11) NOT NULL default '0',
        quesID char(10) default '',
        seen tinyint(4) NOT NULL default '0',
        answered tinyint(4) NOT NULL default '0',
        correct tinyint(4) NOT NULL default '0',
        award smallint(6) NOT NULL default '0',
        answer smallint(6) NOT NULL default '0',
        KEY quesID (quesID),
        KEY userGameEventID (userGameEventID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);




Reply With Quote