SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 32
  1. #1
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    queries help please

    Hi all Im working on a script something like a download system. I dont have any problems with the other part of coding yet. However I'm really havings problems with the queries.

    Basically I have 3 tables :

    Library_files
    Library_cat
    Library_subcats

    I've all ready entered into the tables a few items manually. The script seems to be working ok accept when I'm trying to pull a certain categories -- contents IE (subcats) it will for some unknown reason pull all the subcategories regardless of which category I've chose.

    Could someone take alook at what Im having difficulties with if I post the code.

    Kind Regards
    Deano

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sure thing, post some code and maybe the database schema...
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  3. #3
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok here goes excuse the clutter of code :
    Code:
    CREATE TABLE library_cat (
      ID int(11) NOT NULL auto_increment,
      Name text,
      description varchar(255) default NULL,
      PRIMARY KEY  (ID)
    ) TYPE=MyISAM PACK_KEYS=0;
    
    
    CREATE TABLE library_files (
      ID bigint(11) NOT NULL auto_increment,
      name text NOT NULL,
      description text NOT NULL,
      scid bigint(11) NOT NULL default '0',
      PRIMARY KEY  (ID)
    ) TYPE=MyISAM;
    
    
    CREATE TABLE library_subcats (
      ID bigint(11) NOT NULL auto_increment,
      name text NOT NULL,
      description varchar(255) NOT NULL default '',
      cid bigint(11) NOT NULL default '0',
      PRIMARY KEY  (ID)
    ) TYPE=MyISAM PACK_KEYS=0;
    Display library_cat :

    PHP Code:
    $query 
      SELECT 
      cat.ID, cat.name, count(*) as total 
      FROM 
      library_cat cat, library_subcats 
      WHERE 
      cat.ID = cid GROUP BY cat.ID, cat.name "
    ;   

    $library_subcats = @mysql_query($query); 

    while (
    $cat mysql_fetch_array($library_subcats)) 

    Display library_subcats :
    PHP Code:
    $query 
      SELECT 
      cat.ID, cat.name, count(*) as total 
      FROM 
      library_subcats cat, library_files 
      WHERE cat.ID = scid 
      GROUP BY 
      cat.ID, cat.name, cat.cid "
    ;   

    $library_files = @mysql_query($query); 

    while (
    $cat mysql_fetch_array($library_files)) 
    Display library_file :

    PHP Code:
    $sql "
         SELECT 
         ID, name, description, scid 
         FROM 
         library_files 
         WHERE 
         scid = " 
    .$_GET['id']; 
         
    $library_files = @mysql_query$sql); 
    if (!
    $library_files) { 

    Thx for your time, its appreciated alot
    Cheers Deano

  4. #4
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it makes it any easier I can zip all the data entries and also the code to go along with it, just an idea

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sure, post the mysql INSERT's too
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  6. #6
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok here goes -

    Code:
    #
    # Table structure for table `library_cat`
    #
    
    CREATE TABLE library_cat (
      ID int(11) NOT NULL auto_increment,
      Name text,
      description varchar(255) default NULL,
      PRIMARY KEY  (ID)
    ) TYPE=MyISAM PACK_KEYS=0;
    
    #
    # Dumping data for table `library_cat`
    #
    
    INSERT INTO library_cat VALUES (1, 'Dogs', '');
    INSERT INTO library_cat VALUES (2, 'Cats', '');
    INSERT INTO library_cat VALUES (3, 'Birds', '');
    # --------------------------------------------------------
    
    #
    # Table structure for table `library_files`
    #
    
    CREATE TABLE library_files (
      ID bigint(11) NOT NULL auto_increment,
      name text NOT NULL,
      description text NOT NULL,
      scid bigint(11) NOT NULL default '0',
      PRIMARY KEY  (ID)
    ) TYPE=MyISAM;
    
    #
    # Dumping data for table `library_files`
    #
    
    INSERT INTO library_files VALUES (1, 'Tweety Pie', 'Im a Yellow Bird', 3);
    INSERT INTO library_files VALUES (2, 'Tom', 'I am a ginger cat', 2);
    INSERT INTO library_files VALUES (3, 'Jake', 'Im a black dog', 1);
    INSERT INTO library_files VALUES (4, 'Jeff', 'Im a black dog', 1);
    # --------------------------------------------------------
    
    #
    # Table structure for table `library_subcats`
    #
    
    CREATE TABLE library_subcats (
      ID bigint(11) NOT NULL auto_increment,
      name text NOT NULL,
      description varchar(255) NOT NULL default '',
      cid bigint(11) NOT NULL default '0',
      PRIMARY KEY  (ID)
    ) TYPE=MyISAM PACK_KEYS=0;
    
    #
    # Dumping data for table `library_subcats`
    #
    
    INSERT INTO library_subcats VALUES (1, 'Black', 'Black dogs', 1);
    INSERT INTO library_subcats VALUES (2, 'Ginger', 'Ginger cats only', 2);
    INSERT INTO library_subcats VALUES (3, 'Yellow Birds', 'Yellow birds only', 3);
    Thx again

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i seem to be getting ok results so far, but which query exactly is not returning the correct result?
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  8. #8
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I goto the library_cat page and select dogs, instead of only diplaying black it seems to display all the subcats in the database which currently are black, ginger and yellow

    Hope this sheds a little light into my nightmare.

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is this what you're lookin for? $cat_id is the id of the category you want to get the subcategories out of...

    Code:
    SELECT library_subcats.ID, library_subcats.name, count( * ) AS total
    FROM library_cat, library_subcats
    WHERE library_cat.ID = library_subcats.cid
    AND library_cat.ID =$cat_id
    GROUP BY library_cat.ID, library_cat.name
    LIMIT 0 , 30
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  10. #10
    SitePoint Member
    Join Date
    Jan 2005
    Location
    San Francisco
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm confused as to the issue; each of the queries works fine. What exactly are you attempting to do here?

    As an aside:

    - Define your primary key ID's as "UNSIGNED" as this will double their number on the positive side (unless, for some reason, you need those keys to be negative).

    - You probably don't need to define the ID's as bigint. Those are awfully large numbers (9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 signed and 0 to 18,446,744,073,709,551,615 unsigned). I'm guessing you will not have that many unique records in your database.

    Postscript: While this may be "show code" only, definitely do not insert a raw _GET value into your SQL string.

  11. #11
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So this is the query you came up with for the library_subcats ?

    Ill test it and fingers crossed

  12. #12
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, the query i gave you will give you back a list of subcategories if you give it a $cat_id for the parent category... this is what you're aiming for yes?
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  13. #13
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It returns an error, maybe I should zip the file up and see what can see what I mean as regards to displaying a certainmain category subcats instead of subcats regardless of its main category.

  14. #14
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    zipped file

    Regards Deano

  15. #15
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah thats right

  16. #16
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i got it workin at:
    http://inversiondesigns.com/asdf/dis...cats.php?cid=1

    i attached the new file for display_subcats... the problem was the query was wrong, and the name of the variable that you stored your second mysql result in was also wrong... it should've been

    Code:
    $library_subcats = @mysql_query($query);
    instead of
    Code:
    $library_cat = @mysql_query($query);
    Attached Files Attached Files
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  17. #17
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent - thx for your help, by the way

    attatchments pending approval :-O whats that ?

  18. #18
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  19. #19
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, and i also added:
    Code:
    $cat_id = (int)$_GET['cid'];
    before the $query was set...
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  20. #20
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Awulf - its wroks perfectly

    If I happen to run into anymore problems, would it be ok for me to ask when needed

    Thx again for your time and effort, its very much appreciated.

    Regards
    Deano

  21. #21
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sure thing! glad it's working for ya.
    yeah, ask anytime. i'm new to the forums, but i try to check in every [other] day, so ask away
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  22. #22
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like the way you re-worked the script - this way, its makes for betterunderstanding than my efforts :-D

    Thankx Deano

  23. #23
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.

  24. #24
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to be the barer of bad news im in a fickle..

    Ive manualy entered more subcats and files - (this is very strange) it will only display 1 subcat allthough theres 2

    Just not my night I guess.

    I've looked at the code but I dont see anything that could cut short the query output

  25. #25
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    texas
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a''ite, i tracked it down. the problem was in the GROUP BY part of the query. it needed to be changed to:
    Code:
    GROUP BY library_subcats.ID, library_subcats.name
    instead of:
    Code:
    GROUP BY library_cat.ID, library_cat.name
    i updated the zip file on my server with the new display_subcats.php

    http://inversiondesigns.com/asdf/display_subcats.zip
    free online calendar: http://inversiondesigns.com
    includes multiple calendars/users/groups, task manager,
    email and sms reminders, permissions (sharing), etc.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •