creating query.

[lucilue2003]

CREATE TABLE category (
category_name varchar(100) NOT NULL default '',
PRIMARY KEY (category_name)
) TYPE=MyISAM;

CREATE TABLE imageinfo (
category_name varchar(100) NOT NULL default '',
imageinfo_name varchar(60) NOT NULL default '',
imageinfo_location varchar(60) NOT NULL default '',
imageinfo_title varchar(60) NOT NULL default '',
linkinfo_url varchar(100) NOT NULL default '',
show int(11) NOT NULL default '1',
PRIMARY KEY (linkinfo_url,category_name)
) TYPE=MyISAM;

CREATE TABLE linkinfo (
linkinfo_url varchar(100) NOT NULL default '',
category_name varchar(100) NOT NULL default '',
linkinfo_description text NOT NULL,
PRIMARY KEY (linkinfo_url,category_name)
) TYPE=MyISAM;

the following query produces nothing, and thats not possible, since the tables are populated.

select * from category, imageinfo, linkinfo
where 'category.category_name' = 'linkinfo.category_name' and
'linkinfo.linkinfo_url' = 'imageinfo.linkinfo_url' and
'category.category_name' = 'air'
;

Please help.

[PHPMagician]

Originally posted by lucilue2003
select * from category, imageinfo, linkinfo
where 'category.category_name' = 'linkinfo_category_name' and
'linkinfo.linkinfo_url' = 'imageinfo.linkinfo_url' and
'category_category_name' = 'air'

By using commas (ie the character ') you are are comparing strings with strings, not fields with strings. What I mean to say is, is you are doing the equivalent of thisL

if ('variable' == 'goodbye') {
    do_action();
}

When you really want to do:

if ($variable == 'goodbye') {
    do_action();
}

I hope you get what I mean.

Your query should be correct like this:

select * from category, imageinfo, linkinfo 
where category.category_name = linkinfo_category_name and 
          linkinfo.linkinfo_url = imageinfo.linkinfo_url and
          category.category_name = 'air'

The only other error I saw within the query was you said: category_category_name instead of category.category_name in you last WHERE clause.

Hope that works out for you, maybe do some more reading and practice on SQL (or refresh yourself, which may be the case).

[lucilue2003]

I have had no luck with that. I actually tried two different things.

Db setup with no surrogate key.

CREATE TABLE category (
category_name varchar(100) NOT NULL default '',
PRIMARY KEY (category_name)
) TYPE=MyISAM;

CREATE TABLE imageinfo (
category_name varchar(100) NOT NULL default '',
imageinfo_name varchar(60) NOT NULL default '',
imageinfo_location varchar(60) NOT NULL default '',
imageinfo_title varchar(60) NOT NULL default '',
linkinfo_url varchar(100) NOT NULL default '',
show int(11) NOT NULL default '1',
PRIMARY KEY (linkinfo_url,category_name)
) TYPE=MyISAM;

CREATE TABLE linkinfo (
linkinfo_url varchar(100) NOT NULL default '',
category_name varchar(100) NOT NULL default '',
linkinfo_description text NOT NULL,
PRIMARY KEY (linkinfo_url,category_name)
) TYPE=MyISAM;

INSERT INTO category VALUES ('Accessories\n');
INSERT INTO imageinfo VALUES ('Accessories\n', 'ebags.gif', 'images/', '', 'http://www.qksrv.net/click-1171617-10293489', 1);
INSERT INTO imageinfo VALUES ('Accessories\n', 'mts.gif', 'images/', '', 'http://www.qksrv.net/click-1171617-39479', 1);
INSERT INTO imageinfo VALUES ('Accessories\n', 'maps.gif', 'images/', '', 'http://www.qksrv.net/click-1171617-42177', 1);

INSERT INTO linkinfo VALUES ('http://www.qksrv.net/click-1171617-10293489', 'Accessories\n', 'eBags is the online leader for luggage, backpacks, handbags, briefcases, and accessories. With over 175 top brands, policies like 110% LOW PRICE GUARANTEES and FREE RETURNS, and various promotions such as Free Shipping - eBags is a consumer favorite. ');
INSERT INTO linkinfo VALUES ('http://www.qksrv.net/click-1171617-39479', 'Accessories\n', 'Travel is the biggest category on the web. Why not get your share of it? ');
INSERT INTO linkinfo VALUES ('http://www.qksrv.net/click-1171617-42177', 'Accessories\n', 'Maps.com is the premier online map store featuring over 3,500 maps, including travel maps, folded maps, wall maps, atlases, and even digital maps for web and desktop publishing. If you\re looking for a map, Maps.com has it. ');

THIS RETURNS NO RESULT.

Db setup WITH surrogate key

CREATE TABLE category (
category_id int(11) NOT NULL auto_increment,
category_name varchar(100) NOT NULL default '',
PRIMARY KEY (category_id)
) TYPE=MyISAM AUTO_INCREMENT=81 ;

CREATE TABLE imageinfo (
imageinfo_id int(11) NOT NULL auto_increment,
imageinfo_name varchar(60) NOT NULL default '',
imageinfo_location varchar(60) NOT NULL default '',
imageinfo_title varchar(60) NOT NULL default '',
linkinfo_url varchar(100) NOT NULL default '',
show int(11) NOT NULL default '1',
PRIMARY KEY (imageinfo_id)
) TYPE=MyISAM AUTO_INCREMENT=688 ;

CREATE TABLE linkinfo (
linkinfo_id int(11) NOT NULL auto_increment,
linkinfo_url varchar(100) NOT NULL default '',
category_name varchar(100) NOT NULL default '0',
linkinfo_description text NOT NULL,
PRIMARY KEY (linkinfo_id)
) TYPE=MyISAM AUTO_INCREMENT=688 ;

INSERT INTO category VALUES (1, 'Accessories\n');
INSERT INTO linkinfo VALUES (1, 'http://www.qksrv.net/click-1171617-10293489', 'Accessories\n', 'eBags is the online leader for luggage, backpacks, handbags, briefcases, and accessories. With over 175 top brands, policies like 110% LOW PRICE GUARANTEES and FREE RETURNS, and various promotions such as Free Shipping - eBags is a consumer favorite. ');
INSERT INTO linkinfo VALUES (2, 'http://www.qksrv.net/click-1171617-39479', 'Accessories\n', 'Travel is the biggest category on the web. Why not get your share of it? ');
INSERT INTO linkinfo VALUES (3, 'http://www.qksrv.net/click-1171617-42177', 'Accessories\n', 'Maps.com is the premier online map store featuring over 3,500 maps, including travel maps, folded maps, wall maps, atlases, and even digital maps for web and desktop publishing. If you\re looking for a map, Maps.com has it. ');

INSERT INTO imageinfo VALUES (1, 'ebags.gif', 'images/', '', 'http://www.qksrv.net/click-1171617-10293489', 1);
INSERT INTO imageinfo VALUES (2, 'mts.gif', 'images/', '', 'http://www.qksrv.net/click-1171617-39479', 1);
INSERT INTO imageinfo VALUES (3, 'maps.gif', 'images/', '', 'http://www.qksrv.net/click-1171617-42177', 1);

tHIS RETURNS RESULTS BUT WITH the row that has ebags.gif repeats twice.

for both of them, i am using the php script below to show the results.

<?php

$query=SELECT * FROM category, linkinfo, imageinfo WHERE category.category_name = linkinfo.category_name AND linkinfo.linkinfo_url = imageinfo.linkinfo_url AND category.category_name = 'Accessories' order by category.category_name, linkinfo.linkinfo_url

$result = db_query($query);
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array( $result );
$i=0;

while ( $i < $numrows) { // start of outer while
if ($row['show'] != "0")
{
?>
<a href="<?php echo $row['linkinfo_url']; ?>" >
<img src="<?php echo $row['imageinfo_location'].$row['imageinfo_name']; ?>" border="0"></a> | <?php echo $row['linkinfo_description']; ?>

<?php
}
$i++;
$row = mysql_fetch_array( $result );
}

?>

I just don't know what wrong with either db implementation.
Please help me.. thanks.

[PHPMagician]

Originally posted by lucilue2003

<?php

$query=SELECT * FROM category, linkinfo, imageinfo WHERE category.category_name = linkinfo.category_name AND linkinfo.linkinfo_url = imageinfo.linkinfo_url AND category.category_name = 'Accessories' order by category.category_name, linkinfo.linkinfo_url 

   $result = db_query($query);
   $numrows = mysql_num_rows($result);
$row = mysql_fetch_array( $result );
$i=0;  

while ( $i < $numrows) {  // start of outer while
	if ($row['show'] != "0")
	{
?>
  <a href="<?php echo $row['linkinfo_url']; ?>" >
                     <img src="<?php echo $row['imageinfo_location'].$row['imageinfo_name']; ?>" border="0"></a>  |    <?php echo $row['linkinfo_description']; ?>   

<?php
	   }
	   	$i++;
}   

?>

[/B]

I didn't go through your SQL for creating and inserting data into the database but I did notice a mistake in your call. Within the while loop, you made a second call to mysql_fetch_array(). That is not needed, and probably why you got results twice. I changed the script, see how it goes now.