Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 12th, 2004, 12:11 PM
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
  #2 (permalink)  
Old August 12th, 2004, 01:11 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I fixed the problem by specifying a LEFT JOIN for each table relationship. I don't recall why I wasn't doing this before. I think it was because I learned how to write SQL using Access with ASP, where nested SELECTs are allowed. When I migrated to MySQL/PHP, I had to learn a whole new way to write these complex JOINs. At the time I thought I would never get along without nested SELECTs.

Here's my new FROM statement

SELECT * FROM
gameResults LEFT JOIN games ON gameResults.gameID = games.gameID
LEFT JOIN users ON gameResults.userID = users.userID
LEFT JOIN questionResults ON gameResults.userGameEventID = questionResults.userGameEventID
LEFT JOIN questions ON (gameResults.gameID = questions.gameID AND questionResults.quesID = questions.quesID)
WHERE gameResults.gameID = 3
GROUP BY questions.quesID

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Problem. rupen Access 3 April 27th, 2007 07:43 AM
Query Problem bundersuk VB Databases Basics 0 December 30th, 2006 07:50 AM
problem with query harpua Classic ASP Databases 1 January 24th, 2005 12:36 PM
Problem in query leo_vinay Classic ASP Databases 5 January 21st, 2005 06:32 AM
query problem mateenmohd SQL Server 2000 7 September 9th, 2003 11:58 PM



All times are GMT -4. The time now is 07:30 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.