Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    Iíve got a bit of a complex problem to get a solution from!

    Basically I have two tables: I want to get a result from one table so I can then formulate a query to get my data from the other table.

    The two tables are:
    Platforms table -holds platforms information (status of platform (0/1/2))
    Reviews table -holds the information and how it is assigned to a platform.


    If I write some sql code this is what I come up with so far.

    Code:
     $query1 = mysql_query("SELECT * FROM platforms 
    WHERE platforms_status !='1'");
    The result is going to give me all the platforms that are not 1 (eg.Non-active platforms)

    So I could get a result like

    Code:
    Platform id	platform_name
    1			abc
    4			qrs
    6			xyz
    From that data I must make a query that goes something like

    Code:
     $query2 = mysql_query("SELECT * FROM reviews 
    WHERE reviews_platoform !='abc' AND reviews_platform 
    !='qrs' AND reviews_platoform !='xyz' ");

    Any help or ruff suggestions are appreciated.
    I know someone must have used complicated stuff like this before,(or is this easy !)
    I was thinking maybe I could use the Ďfor eachí in php, but I donít know how it works!

    Thanks team.

  2.  

  3. #2
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    It'd help if you could post the structures of both tables.

    However, try the following query and see if it helps.

    Code:
    SELECT p.*, r.* FROM platforms AS p, reviews AS r
    WHERE p.platforms_status !='1'
    There and Back Again :Ogre:

  4. #3
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    Here is the structure. I didnt post this before as alot of the fields arent needed in the query.

    I dont really understand what you mean with that code !

    here are the tables structure.

    Code:
     #
    # Table structure for table `platforms`
    #
    
    CREATE TABLE platforms (
      platforms_abbvname varchar(15) NOT NULL default '',
      platforms_abbv_full_name varchar(15) NOT NULL default '',
      platforms_id tinyint(3) NOT NULL auto_increment,
      platforms_status tinyint(3) NOT NULL default '1',
      platforms_rank tinyint(1) NOT NULL default '1',
      platforms_fullname varchar(30) NOT NULL default '',
      platforms_color varchar(7) NOT NULL default '',
      platforms_image varchar(30) NOT NULL default '',
      platforms_image_height tinyint(3) NOT NULL default '0',
      platforms_image_width tinyint(3) NOT NULL default '0',
      PRIMARY KEY  (platforms_id)
    ) TYPE=MyISAM;
    # --------------------------------------------------------
    
    #
    # Table structure for table `reviews`
    #
    
    CREATE TABLE reviews (
      reviews_id int(6) NOT NULL auto_increment,
      reviews_title varchar(30) NOT NULL default '',
      reviews_genre int(10) NOT NULL default '0',
      reviews_publisher int(10) NOT NULL default '0',
      reviews_developer int(10) NOT NULL default '0',
      reviews_author varchar(30) NOT NULL default '',
      reviews_blurb text NOT NULL,
      reviews_body text NOT NULL,
      reviews_rating tinyint(3) NOT NULL default '0',
      reviews_platform varchar(15) NOT NULL default '',
      reviews_date date NOT NULL default '0000-00-00',
      reviews_players varchar(8) NOT NULL default '0',
      reviews_pros text NOT NULL,
      reviews_cons text NOT NULL,
      reviews_verdict text NOT NULL,
      PRIMARY KEY  (reviews_id)
    ) TYPE=MyISAM;

  5. #4
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    From your quries above, I understand that you want to select reviews for all the active platforms (or ignore all the inactive ones).

    Here is a better query for you.
    Code:
    SELECT *
    FROM platforms INNER JOIN reviews
    ON(platforms.platforms_id = reviews.reviews_platform)
    WHERE platforms.platforms_status <> 1
    It'd be better if you had reviews_platform contain the id of the platform from "platform_id" column of the platforms table.
    There and Back Again :Ogre:

  6. #5
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    I know, LOL, it would be alot better if it was that way round.

    But its not, im moding a previously made site, so i cant really change data fields and the properties etc.

    So i have to go the long way.

    Thanks for your help, i will give this way a go, but i have worked out another way. I think it will work.

    Here it is.
    Code:
    $query1 = mysql_query("SELECT * FROM platforms WHERE platforms_status !='1'");
    while ($myrow = mysql_fetch_array($query1))
    {
    
    $currentp = $myrow['platforms_abbvname'];
    
        if (!$not_first)
        {
            $sql = "SELECT * FROM reviews WHERE reviews_platform !='$currentp'";
            $not_first = 1;
        } else {
            $sql .= " AND reviews_platform !='$currentp'";
        }
    }
    $query2 = mysql_query($sql);

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    That's not complex... it's called Third Normal Form.

    In ANSI-92 SQL, you can do that with one query to get your data, like so:
    Code:
    SELECT * FROM reviews 
    WHERE reviews_platoform NOT IN
        (SELECT DISTINCT platform_name
        FROM platforms 
        WHERE platforms_status != 1) AS plats
    (Although personally, I would create my foreign key on the id instead of the name).

    Having said that, you cannot do that with MySQL unless you're using version 4.0 or greater, because older versions don't support sub-queries.

    This is why I don't use MySQL for "complex" projects that require Third Normal Form database design.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:42 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com