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 ;