dou760663
2015-11-18 15:11
采纳率: 100%
浏览 76
已采纳

选择DISTINCT并且JOIN不起作用

I've been working on this for days... I'm just learning and I'm reading all the tutorials but this just will not work for me! I know the problem has many answers but I must be typing something wrong...

1KaraokeDJ = 1KaraokeDJcopy because I couldn't figure out the use of AS to create a temp for use in the JOIN with the DISTINCT in 1 SELECT

ID is primary unique key Artist, Title, Disc, Track, Brand are all needed in the output

When using DISTINCT Title, Brand the result only has those 2 columns So I figured I would have to JOIN ON ID so I could have the Distinct records and still have all columns... There is probably a better way to do this but that's all I could find out reading the various tutorials.

<?php ...
$searchterm="call me";
$searchresults=$db->query("select DISTINCT Title, Brand from 1KaraokeDJcopy JOIN 1KaraokeDJ ON 1KaraokeDJcopy.ID=1KaraokeDJ.ID where Title like '%$searchterm%' limit 100");

        if(mysqli_num_rows($searchresults) > 0) {
          while($descri=mysqli_fetch_object($searchresults)) { ?>
            <div class="reslt">
              <h3 id="results">
                <?php
                  echo $descri->Artist); echo " - ";
                  echo $descri->Title);
                ?>
              </h3>
              <p class="Description">
                <?php
                  echo $descri->Brand; echo " - ";
                  echo $descri->Disc); echo " - ";
                  echo $descri->Track;
                ?>
              </p>
              <hr>
            </div>
          <?php } ?>

I get no query with the above code (print_r($searchresults); is null)

$searchresults=$db->query("select * from 1KaraokeDJ where Title like '%$searchterm%' order by Artist, Disc limit 100");

Produces 21 results with 4 duplicates (duplicate Brand because same song on several Discs even though different Disc number ie CB5016-01 and CBEP467-1 but both Brand is Chartbuster)

$searchresults=$db->query("select DISTINCT Title, Brand from 1KaraokeDJ where Title like '%$searchterm%' limit 100");

Produces the correct 17 results but my output no longer contains Artist, Disc, Track

Table structure for table 1KaraokeDJ

CREATE TABLE IF NOT EXISTS `1KaraokeDJ` (
  `Disc` varchar(12) COLLATE latin1_general_ci NOT NULL,
  `Track` enum('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36') COLLATE latin1_general_ci NOT NULL,
  `Artist` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `Title` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `Brand` enum('Chartbuster','DKKaraoke','Karaoke Hits','Sound Choice','Sunfly') COLLATE latin1_general_ci NOT NULL,
  `ID` smallint(5) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),
  KEY `Disc` (`Disc`),
  KEY `Artist` (`Artist`),
  KEY `Title` (`Title`),
  KEY `Brand` (`Brand`),
  FULLTEXT KEY `Artist_2` (`Artist`),
  FULLTEXT KEY `Title_2` (`Title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=28618 ;
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongpanbo4727 2015-11-18 15:34
    已采纳

    Step 0: Turn on error_reporting by adding this line to your PHP files

    error_reporting(E_ALL); ini_set('display_errors', 'On');
    

    Step 0.1: Try queries directly in MySQL if they are not working as desired.

    1. I don't see why you need a join. You are joining the table to itself on ID=ID, which accomplishes nothing.

    1.1 (You don't need to do this but for completeness) To JOIN a table to itself the syntax would be

    SELECT * FROM table1 JOIN table1 as table1copy ON table1.col1 = table1copy.col2
    
    1. Every column you want in the output needs to be in your SELECT clause.

      SELECT Artist, Title, Brand, Disc, Track FROM ...

    2. Your not actually getting duplicates if there is even one column different. That's not a duplicate. I don't totally understand your desired result but i think GROUP BY might help.

    已采纳该答案
    打赏 评论
  • dsfds2353 2015-11-18 15:25

    You forgot to write columns name on the query, if you write

    select DISTINCT Title, Brand from 1KaraokeDJ where Title like '%$searchterm%' limit 100
    

    Query will select only DISTINCT Title and Brand. Only * selects all columns, otherwise you have to specify all of them, so you should write your query like this:

    select DISTINCT Title, Brand,Artist,Disc,Track from 1KaraokeDJ where Title like '%$searchterm%' limit 100
    

    Side note: are you escaping $searchterm value and using mysqli_ or PDO functions? If not, you should, just to avoid SQL Injections :)

    打赏 评论
  • duanjizi9443 2015-11-18 15:28

    ok, first of all in your query you are not providing from which table you are taking the columns and which table column has to match the value. The query should look like:

    $searchresults=$db->query("select DISTINCT 1KaraokeDJcopy.Title, 1KaraokeDJcopy.Brand from 1KaraokeDJcopy JOIN 1KaraokeDJ ON 1KaraokeDJcopy.ID=1KaraokeDJ.ID where 1KaraokeDJcopy.Title like '%$searchterm%' limit 100");
    

    Secondly, its hard to say without having your schema presented and all DB structure, but I would do it with this query (without any joins, distinct value, but having group by):

    $searchresults=$db->query("select * from 1KaraokeDJ where Title like '%$searchterm%' group by Title, Brand limit 100");
    
    打赏 评论

相关推荐 更多相似问题