Making a two tables mySQL query...

birk · 08-03-2006, 08:38 PM

I'll try to explain this the best I can. Hopefully there are some people out there that can help me with this one since I am completely lost.

First some information. I have two tables with this structure:

Code:

-- 
-- Struktur-dump for tabellen `sl_shares`
-- 

CREATE TABLE `sl_shares` (
  `rel_id` int(10) unsigned NOT NULL auto_increment,
  `req_counter` int(10) unsigned NOT NULL default '0',
  `dl_counter` int(10) unsigned NOT NULL default '0',
  `req_state` int(10) unsigned default NULL,
  `req_userid` int(10) unsigned default NULL,
  `rel_userid` int(10) unsigned default NULL,
  `notmine` tinyint(4) NOT NULL default '0',
  `reqrel_date` int(10) unsigned default '0',
  `cat_id` mediumint(10) unsigned default NULL,
  `description` text,
  `comments` text,
  `website_url` text,
  `network` tinyint(3) unsigned default '1',
  `email_notify` tinyint(3) unsigned NOT NULL default '0',
  `anonymous` tinyint(3) unsigned default '0',
  `str_full` varchar(255) default NULL,
  `str_short` varchar(255) default NULL,
  `str_search` varchar(255) default NULL,
  `releasever` text,
  `releasename` text,
  `publisher` text,
  `users_ip` varchar(23) default NULL,
  `tracked` tinyint(3) unsigned default '1',
  PRIMARY KEY  (`rel_id`),
  KEY `rel_id` (`rel_id`,`req_userid`,`rel_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Full list of releases and requests';

Code:

-- 
-- Struktur-dump for tabellen `sl_tag_data`
-- 

CREATE TABLE `sl_tag_data` (
  `data_id` bigint(20) unsigned NOT NULL auto_increment,
  `rel_id` int(10) unsigned default NULL,
  `tag_id` int(10) unsigned NOT NULL default '0',
  `data` varchar(200) default NULL,
  PRIMARY KEY  (`data_id`),
  KEY `rel_id` (`rel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Associated data for the tags';

What I want to accomplish is get the right data out of the sl_shares table to use in an rss feed I want to create. I'll try to make an example of the problem here based on the entires in both tables for the latest test release I have made.

Code:

INSERT INTO `sl_shares` VALUES (1488, 0, 0, 2, NULL, 53568, 0, 1154347955, 1, 'sagagadfgadfgadfg', 'No comments were given for this item.', '', 1, 0, 0, 'Tester', 'Tester', 'Tester', NULL, NULL, NULL, '0.0.0.0', 1);

Code:

INSERT INTO `sl_tag_data` VALUES (6055, 1488, 10, '1');
INSERT INTO `sl_tag_data` VALUES (6054, 1488, 8, '1');
INSERT INTO `sl_tag_data` VALUES (6053, 1488, 3, '1');
INSERT INTO `sl_tag_data` VALUES (6052, 1488, 1, 'Tester');

Now this is my problem. I want to choose some information from the sl_shares table if tag_id = 10 and data = 1 and ofcause when the rel_id matches the rel_id from the sl_shares table. As I see the problem it is that there are more than one row with the same rel_id in the sl_tag_data table based on the rel_id from the sl_shares table. I have tried with some joins but I just can not seem to get it to work. The code below is what I have tried to come up with:

Code:

$test = mysql_query("SELECT DISTINCT reqrel_date, rel_id, str_full, description FROM " . $dbprefix . "shares INNER JOIN " . $dbprefix . "tag_data ON sl_shares.rel_id=sl_tag_data.rel_id WHERE sl_tag_data.tag_id=10 AND sl_tag_data.data=1 LIMIT 10");

This just returns false. Can someone please help me out?

Kid Charming · 08-04-2006, 04:47 PM

If it's returning false, there's some sort of error in your query (syntax, etc.). Use mysql_error() to get the database's error message. Echo the query to see exactly what's being sent to the server, and try running it directly against the database via a client or command line. Try to get the query to do what you want outside of your script before you try adding script variables and such.

birk · 08-05-2006, 03:41 PM

Quote:

Originally Posted by Kid Charming

If it's returning false, there's some sort of error in your query (syntax, etc.). Use mysql_error() to get the database's error message. Echo the query to see exactly what's being sent to the server, and try running it directly against the database via a client or command line. Try to get the query to do what you want outside of your script before you try adding script variables and such.

Hello and thanks for the help sp far. I did what you said and found what the mysql error is. It is what I expected would be a problem. The error is: Column 'rel_id' in field list is ambiguous
Is there any way around this?

GJay · 08-05-2006, 04:30 PM

prefix it with the name of the table you want to use, e.g.:
select shares.relid, a, bc,d.....

birk · 08-05-2006, 04:38 PM

$test = mysql_query("SELECT sl_shares.reqrel_date, sl_shares.rel_id, sl_shares.str_full, sl_shares.description FROM sl_shares, sl_tag_data WHERE sl_shares.rel_id=sl_tag_data.rel_id AND sl_tag_data.tag_id=10 AND sl_tag_data.data=1 ORDER BY sl_shares.reqrel_date DESC LIMIT 10");

This seems to have solved it.