Getting details from two tables at once (MySql)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Toneboy
Forum Contributor
Posts: 102
Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:

Getting details from two tables at once (MySql)

Post 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.
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post 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...
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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"...
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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]");
User avatar
Toneboy
Forum Contributor
Posts: 102
Joined: Wed Jul 31, 2002 5:59 am
Location: Law, Scotland.
Contact:

Post 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?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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.
Post Reply