Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Nov 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a two tables mySQL query...

    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?

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #3
    New Coder
    Join Date
    Nov 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    prefix it with the name of the table you want to use, e.g.:
    select shares.relid, a, bc,d.....

  • #5
    New Coder
    Join Date
    Nov 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    $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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •