queries help please

[Deano]

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

[awulf]

sure thing, post some code and maybe the database schema...

[Deano]

Ok here goes excuse the clutter of 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 :

$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 :

$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 :

$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

[Deano]

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

[awulf]

sure, post the mysql INSERT's too

[Deano]

Ok here goes -

#
# 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

[awulf]

i seem to be getting ok results so far, but which query exactly is not returning the correct result?

[Deano]

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.

[awulf]

is this what you're lookin for? $cat_id is the id of the category you want to get the subcategories out of...

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

[Rebort]

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.

[Deano]

So this is the query you came up with for the library_subcats ?

Ill test it and fingers crossed

[awulf]

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?

[Deano]

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.

[Deano]

zipped file

Regards Deano

[Deano]

yeah thats right

[awulf]

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

$library_subcats = @mysql_query($query);

instead of

$library_cat = @mysql_query($query);

[Deano]

Excellent - thx for your help, by the way

attatchments pending approval :-O whats that ?

[awulf]

hmph. try this:
http://inversiondesigns.com/asdf/display_subcats.zip

[awulf]

oh, and i also added:

$cat_id = (int)$_GET['cid'];

before the $query was set...

[Deano]

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

[awulf]

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

[Deano]

I like the way you re-worked the script - this way, its makes for betterunderstanding than my efforts :-D

Thankx Deano

[awulf]

[Deano]

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

[awulf]

a''ite, i tracked it down. the problem was in the GROUP BY part of the query. it needed to be changed to:

GROUP BY library_subcats.ID, library_subcats.name

instead of:

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