Getting details from two tables at once (MySql)
Posted: Wed Apr 14, 2004 3:02 pm
Probably a pretty basic thing, but something I've not done before.
I'll post the tables first to give you some background:
So I've got two tables and I'm trying to put an index together. Basically it should list the names of the link categories, and then show how many links there are (in brackets) alongside it. Here was the code I drew up:
The problem is at the moment it is showing a total of zero links against all the categories (which isn't the case).
Where have I gone wrong, and what should the general principles be when trying to get data from two tables at the one time?
Thanks in advance.
I'll post the tables first to give you some background:
Code: Select all
CREATE TABLE links (
siteid int(15) NOT NULL auto_increment,
sitemark int(11) default '0',
siteentered int(15) NOT NULL default '0',
sitecat int(11) default NULL,
siteurl varchar(100) NOT NULL default '',
sitename varchar(100) NOT NULL default '',
sitedescription text,
sitehits int(15) default NULL,
siteactive varchar(10) default NULL,
PRIMARY KEY (siteid),
UNIQUE KEY siteid (siteid)
) TYPE=MyISAM;Code: Select all
CREATE TABLE links_cat (
catid int(15) NOT NULL auto_increment,
catname varchar(100) NOT NULL default '',
PRIMARY KEY (catid),
UNIQUE KEY siteid (catid)
) TYPE=MyISAM;Code: Select all
<?php
// Database constants
define("DATABASE_HOST", "localhost");
define("DATABASE_USER", "username");
define("DATABASE_PASSWORD", "password");
define("DATABASE_NAME", "username");
// Establish connection since we are going to use the database in every screen
$dbLink = mysql_connect(DATABASE_HOST, DATABASE_USER,
DATABASE_PASSWORD);
if(!$dbLink) {
print "Unable to connect to the database, please contact
Sysadmin asap.";
} else {
$dbUse = mysql_select_db(DATABASE_NAME, $dbLink);
}
// SQL QUERY
$sql = mysql_query("SELECT * FROM links_cat ORDER BY 'catname' ASC");
//LOOP TO GET IT OUT OF THE DB - *NOTICE THE WAY I AM BREAKING OUT OF PHP, INTO HTML*
while($news = mysql_fetch_array($sql)){
echo nl2br ("<a href="showcat.php?cid=$news[catid]">$news[catname]</a>");
$rows = mysql_query("SELECT * FROM links WHERE 'sitecat' = $news[catid]");
$cattotal = mysql_num_rows($rows);
echo nl2br (" ($cattotal)\n");
}
mysql_free_result($sql);
mysql_close($dbLink);
?>Where have I gone wrong, and what should the general principles be when trying to get data from two tables at the one time?
Thanks in advance.