database query help required

[seanstuart]

i have would like to know how to code a query..... I have a categories section and when a item is added to a category it shows a number next to it to show how many items are in that category.... blogs (1)  and so on. thanks Sean!

[seanstuart]

my existing code looks like this........

<?php do { ?>
	<li><a href="<?php echo("$path");?><?php echo $row_categRL['categseoname']; ?>/"><?php echo $row_categRL['categoryname']; ?></a></li>
<?php
$row_categRL = mysql_fetch_assoc($categRL);
if (!isset($nested_categRL)) { $nested_categRL= 1; }
} while ($row_categRL);
?>

[revraz]

Let's see the category table layout.

[seanstuart]

table structure.....

CREATE TABLE `categorylisting` (
  `catlistid` int(4) NOT NULL auto_increment,
  `categoryname` varchar(255) NOT NULL default '',
  `categtitle` varchar(255) NOT NULL default '',
  `categdescription` varchar(255) NOT NULL default '',
  `categkeyword` varchar(255) NOT NULL default '',
  `categenable` char(1) NOT NULL default 'Y',
  `categseoname` varchar(255) NOT NULL default '',
  `parentid` int(11) NOT NULL default '0',
  `categauxC` varchar(255) NOT NULL default '',
  UNIQUE KEY `catlistid` (`catlistid`)
) TYPE=MyISAM AUTO_INCREMENT=38 ;

[revraz]

So safe to assume you have a Item table that has a catagory id number?

 

Maybe do a SELECT COUNT on the itemtable WHERE the catagory id matches the id you want?

 

 

[seanstuart]

these are my other tables.. NO item table!

 

 

DROP TABLE IF EXISTS `adsense`;
CREATE TABLE `adsense` (
  `adsenseid` int(4) NOT NULL auto_increment,
  `adsensecode` longtext NOT NULL,
  UNIQUE KEY `adsenseid` (`adsenseid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


------------------------------------


CREATE TABLE `confset` (
  `indc` int(4) NOT NULL auto_increment,
  `path` varchar(255) NOT NULL default '/',
  `paypal` varchar(255) NOT NULL default 'yourname@paypal.com',
  `paypalcurrency` varchar(255) NOT NULL default 'USD',
  `contactmail` varchar(255) NOT NULL default 'yourname@yourdomain.com',
  `adminmail` varchar(255) NOT NULL default 'yourname@yourdomain.com',
  `currency` varchar(255) NOT NULL default '1',
  `minbid` varchar(9) NOT NULL default '1.00',
  `minup` varchar(9) NOT NULL default '1.00',
  `currencymail` varchar(255) NOT NULL default '1',
  `pathmail` varchar(255) NOT NULL default '/',
  `frommail` varchar(255) NOT NULL default 'yourname@yourdomain.com',
  `contactmailsubject` varchar(255) NOT NULL default 'message from yoursite.com',
  `domainname` varchar(255) NOT NULL default 'www.yourdomain.com',
  `sitetitle` varchar(255) NOT NULL default 'Your Site Title Slogan',
  `custom` varchar(255) NOT NULL default '',
  `lbdtemplate` varchar(255) NOT NULL default 'default',
  `alphacat` char(1) NOT NULL default 'C',
  `prenabled` char(1) NOT NULL default 'Y',
  `screenshot` char(1) NOT NULL default 'Y',
  `buymailsubject` varchar(255) NOT NULL default 'Your submission at LinkBidScript.com',
  `buymailmessage` longtext NOT NULL,
  `bidmailsubject` varchar(255) NOT NULL default 'Thank you for your bid!',
  `bidmailmessage` longtext NOT NULL,
  `pr_google` char(1) NOT NULL default 'Y',
  `pr_yahoo` char(1) NOT NULL default 'Y',
  `pr_msn` char(1) NOT NULL default 'Y',
  `pr_alexa` char(1) NOT NULL default 'Y',
  `prp_latest` char(1) NOT NULL default 'Y',
  `prp_category` char(1) NOT NULL default 'Y',
  `prp_details` char(1) NOT NULL default 'Y',
  `prp_home` char(1) NOT NULL default 'Y',
  `prp_links` char(1) NOT NULL default 'Y',
  `prp_list` char(1) NOT NULL default 'Y',
  `prp_search` char(1) NOT NULL default 'Y',
  `cntIM` char(1) NOT NULL default 'N',
  UNIQUE KEY `indc` (`indc`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;



----------------------------------------------


CREATE TABLE `currency` (
  `curid` int(4) NOT NULL auto_increment,
  `curenc` varchar(255) NOT NULL default '',
  `cursign` varchar(255) NOT NULL default '',
  UNIQUE KEY `curid` (`curid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


---------------------------------------------



CREATE TABLE `do_ban` (
  `do_do` int(4) NOT NULL auto_increment,
  `do_ban` varchar(255) NOT NULL default '',
  `do_enable` varchar(255) NOT NULL default 'Y',
  UNIQUE KEY `do_do` (`do_do`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

------------------------------------------


CREATE TABLE `google_cse` (
  `cse_id` int(4) NOT NULL auto_increment,
  `cse_main` longtext,
  `cse_on` char(1) NOT NULL default 'Y',
  UNIQUE KEY `cse_id` (`cse_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


----------------------------------------------


CREATE TABLE `ig_admin` (
  `adid` int(4) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `pass` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  UNIQUE KEY `adid` (`adid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=13 ;

---------------------------------------------


CREATE TABLE `ip_ban` (
  `ip_id` int(4) NOT NULL auto_increment,
  `ip_ip` varchar(255) NOT NULL default '',
  `ip_enable` varchar(255) NOT NULL default 'Y',
  UNIQUE KEY `ip_id` (`ip_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-----------------------------------------------


CREATE TABLE `linknumber` (
  `lnbid` int(4) NOT NULL auto_increment,
  `lnbhome` varchar(255) NOT NULL default '5',
  `lnbcategory` varchar(255) NOT NULL default '2',
  `lnbalphabet` varchar(255) NOT NULL default '2',
  `lnbtoplinks` varchar(255) NOT NULL default '30',
  `lnbnewlinks` varchar(255) NOT NULL default '30',
  UNIQUE KEY `lnbid` (`lnbid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


--------------------------------------------


CREATE TABLE `main` (
  `dtu` int(4) NOT NULL auto_increment,
  `categ` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `site` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `descr1` varchar(255) NOT NULL default '',
  `descr2` longtext NOT NULL,
  `mdesc` varchar(255) NOT NULL default '',
  `mword` varchar(255) NOT NULL default '',
  `deeptf` varchar(255) NOT NULL default 'Deep Link 1',
  `deepts` varchar(255) NOT NULL default 'Deep Link 2',
  `deeptt` varchar(255) NOT NULL default 'Deep Link 3',
  `dfurl` varchar(255) NOT NULL default 'http://',
  `dsurl` varchar(255) NOT NULL default 'http://',
  `dturl` varchar(255) NOT NULL default 'http://',
  `bid` int(4) NOT NULL default '0',
  `stad` date NOT NULL default '0000-00-00',
  `stod` date NOT NULL default '0000-00-00',
  `stup` date NOT NULL default '0000-00-00',
  `avail` varchar(255) NOT NULL default '',
  `paid` varchar(255) NOT NULL default '',
  `ujbid` varchar(255) NOT NULL default '',
  `maincategory` varchar(255) NOT NULL default '17',
  `google` varchar(255) NOT NULL default '',
  `yahoo` varchar(255) NOT NULL default '',
  `msn` varchar(255) NOT NULL default '',
  `alexa` varchar(255) NOT NULL default '',
  `pr_date` date default NULL,
  UNIQUE KEY `dtu` (`dtu`),
  KEY `bid` (`bid`),
  KEY `bid_2` (`bid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=154 ;

--------------------------------------------

[revraz]

So what is considered an "item" in your post below?

 

i have would like to know how to code a query..... I have a categories section and when a item is added to a category it shows a number next to it to show how many items are in that category.... blogs (1)   and so on. thanks Sean!

[seanstuart]

sorry...!    when a link is submitted i class it as a item

[revraz]

Provide an example

[seanstuart]

http://demo.linkbidscript.com/

[seanstuart]

is anyone able to help?