Page 1 of 1

Getting details from two tables at once (MySql)

Posted: Wed Apr 14, 2004 3:02 pm
by Toneboy
Probably a pretty basic thing, but something I've not done before.

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

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);

?>
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.

Posted: Wed Apr 14, 2004 3:19 pm
by MarK (CZ)
Using array in a string seems a bit wrong to me. you can get array in string like this:

Code: Select all

"asdf $&#123;array&#1111;0]&#125; qwert"
or like this:

Code: Select all

"asdf ".$array&#1111;0]." qwert"
Anyway, I don't understand the "catid" in array's []. It should be variable, number or a text in quotes if I'm right...

Posted: Wed Apr 14, 2004 3:24 pm
by xisle
if I am understanding correctly, there is a many(sites)
to one(cat) relationship here. Wouldn't you want to store the catid as a foreign key in the links table?
then "select * from links where catid=$catid"...

Posted: Wed Apr 14, 2004 3:27 pm
by markl999
$rows = mysql_query("SELECT * FROM links WHERE 'sitecat' = $news[catid]");
Remove the single quotes :
$rows = mysql_query("SELECT * FROM links WHERE sitecat = $news[catid]");

Posted: Wed Apr 14, 2004 5:15 pm
by Toneboy
Thanks for that Mark - its done the trick.

I'm still getting the hang of when you do and don't put quotes into queries. Seems to be a matter of trial and error for me with that at the moment. Is it a case of if it is numeric then avoid quotes and vice-versa?

Posted: Wed Apr 14, 2004 5:18 pm
by markl999
The general rule is, don't quote column/field names and quote values.
I tend not to quote integer values out of habit but it doesn't hurt to do so.

In the example above, a more 'correct' way would be:
$rows = mysql_query("SELECT * FROM links WHERE sitecat = '{$news['catid']}'");

The {}'s around $news['catid'] prevent the 's in there from breaking the query.