Category menu displays duplicates of same category PHP PDO MySQL

Question

I have made a menu that displays all the categories for the relevant categoryID.

MySQL:

require_once('connect.php');
$menu = $DB->prepare("SELECT
 advertisers.*, 
 ad_categories.*
 FROM ad_categories LEFT JOIN advertisers
 ON advertisers.categoryID=ad_categories.categoryID
 WHERE advertisers.categoryID=ad_categories.categoryID
 ORDER BY categoryName ASC
 ");
$menu->execute();
$menu = $menu->fetchALL(PDO::FETCH_ASSOC);

PHP:

<li class="dropdown">
 <a href="" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">DIRECTORY <b class="caret"></b></a>
 <ul class="dropdown-menu" role="menu">
 <?php foreach ($menu as $row) { ?>
 <li>
 <a href="http://www.platinumweekly.co.za/advertisers.php?categoryID=<?php echo $row['categoryID']; ?>"><?php echo $row['categoryName']; ?></a>
 </li>
 <?php } ?>
 </ul>
</li>

However the menu displays multiples of the same categoryName where it should only display one of each categoryName. I have to duplicate this menu for other areas of the website and can't go forward with them until this issue is resolved.

Is the problem with my MySQL query or the PHP doing the foreach loop or both?

I am still relatively new to PHP & MySQL so any help would be greatly appreciated.

EDIT:

CREATE TABLE advertisers (
 id SMALLINT(6) UNSIGNED AUTO INCREMENT PRIMARY KEY NOT NULL,
 categoryID SMALLINT(6) NOT NULL,
 firstname VARCHAR(255),
 lastname VARCHAR(255),
 companyname VARCHAR(255) NOT NULL,
 description TEXT NOT NULL,
 excerpt TEXT NOT NULL,
 publishdate DATETIME NOT NULL,
 logoURL VARCHAR(255) NOT NULL,
 address VARCHAR(255) NOT NULL,
 telnr VARCHAR(20),
 cellnr VARCHAR(20),
 email VARCHAR(100) NOT NULL,
 website VARCHAR(100),
 facebook VARCHAR(100),
 twitter VARCHAR(100),
 instagram VARCHAR(100),
 NewspaperAdvert VARCHAR(100)
);
CREATE TABLE ad_categories (
 categoryID SMALLINT(6) AUTO INCREMENT NOT NULL PRIMARY KEY,
 categoryName VARCHAR(255) NOT NULL,
);
CREATE TABLE IF NOT EXISTS `advertisers` (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,
 `categoryID` smallint(6) NOT NULL,
 `firstname` varchar(255) DEFAULT NULL,
 `lastname` varchar(255) DEFAULT NULL,
 `companyname` varchar(255) NOT NULL,
 `description` text NOT NULL,
 `excerpt` text NOT NULL,
 `publishdate` date NOT NULL,
 `logoURL` varchar(255) NOT NULL,
 `address` varchar(255) NOT NULL,
 `telnr` varchar(20) DEFAULT NULL,
 `cellnr` varchar(20) DEFAULT NULL,
 `email` varchar(100) NOT NULL,
 `website` varchar(100) DEFAULT NULL,
 `facebook` varchar(100) DEFAULT NULL,
 `twitter` varchar(100) DEFAULT NULL,
 `instagram` varchar(100) DEFAULT NULL,
 `NewspaperAdvert` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `categoryID` (`categoryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
INSERT INTO `advertisers` (
 `id`, 
 `categoryID`, 
 `firstname`, 
 `lastname`, 
 `companyname`, 
 `description`,
 `excerpt`,
 `publishdate`,
 `logoURL`,
 `address`,
 `telnr`,
 `cellnr`,
 `email`,
 `website`,
 `facebook`,
 `twitter`,
 `instagram`, 
 `NewspaperAdvert`) 
VALUES (
 13, 
 6,
 'John',
 'Doe',
 'Doe Incorporated',
 '<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut facilisis ex nisi, sollicitudin faucibus lorem cursus sed. Aenean aliquam fermentum augue ut pellentesque. Nullam vestibulum, libero vel cursus imperdiet, libero neque tempor ligula, at eleifend nisl nibh at arcu.<p></p>Suspendisse potenti. Morbi consectetur, odio a tempus dignissim, felis justo sodales sapien, non mollis urna neque non metus. Nulla in leo nec justo pulvinar tempor sed sed sapien.</p>\r\n',
 '<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut facilisis ex nisi, sollicitudin faucibus lorem cursus sed.</p>\r\n', 
 '2017-01-13',
 'doe-incorporated-logo.png',
 '123 Some Street, City, Country',
 '012 345 6789',
 '012 345 6789', 
 '[email protected]', 
 'http://www.domain.com',
 'https://www.facebook.com/Doe-Incorporated/',
 '@DoeInc',
 'doeinc', 
 'doe-incorporated-2017年01月13日-issue.png'
);
CREATE TABLE IF NOT EXISTS `ad_categories` (
 `categoryID` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
 `categoryName` varchar(255) NOT NULL,
 PRIMARY KEY (`categoryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;
INSERT INTO `ad_categories` (`categoryID`, `categoryName`) VALUES
 (1, 'Accommodation/Guesthouses/Hotels'),
 (2, 'Attorneys/Conveyancers'),
 (3, 'Auctioneers/Estate Agencies'),
 (4, 'Barbers/Beauty Salons'),
 (5, 'Books/Literature'),
 (6, 'Building Materials/Paint'),
 (7, 'Butchery/Biltong/Deli'),
 (8, 'Catering/Restaurants'),
 (9, 'Charities/NGOs'),
 (10, 'Churches/Religious Institutions'),
 (11, 'Clinics/Hospitals/Veternarians'),
 (12, 'Clothing/Fashion'),
 (13, 'Colleges/Schools/Training Institutions'),
 (14, 'Competitions/Events/Events Management'),
 (15, 'Computers/Electronics/IT'),
 (16, 'Contractors'),
 (17, 'Convenience Stores/Grocers/Supermarkets'),
 (18, 'Courier/Logistics/Transport'),
 (19, 'Crafts/Gifts/Hobbies/Photography'),
 (20, 'Dry Cleaning/Locksmiths/Shoe Repairs/Numberplates'),
 (21, 'Emergency Services'),
 (22, 'Entertainment/Exercise/Fitness/Recreation/Sports'),
 (23, 'Equipment/Machinery/Mining'),
 (24, 'Finance/Insurance/Tax'),
 (25, 'Furniture/Upholstery'),
 (26, 'Government'),
 (27, 'Graphic Design/Printers/Signage'),
 (28, 'Holidays/Travel'),
 (29, 'Motor Dealers/Motor Spares & Accessories'),
 (30, 'Other Services/Specialized Services'),
 (31, 'Panelbeaters/Towing/Support Services'),
 (32, 'Plumbing/Irrigation');

• Rick James: Please provide SHOW CREATE TABLE for both tables. You have mentioned some columns names without a clue of which table(s) they are in. Also qualify things in the queries, so they are obvious.
• Eugene van der Merwe: Updated my original post

Answer 1

The "left" table is the list of categories. For each of those, you find all the advertisers (possibly none -- LEFT JOIN) for each.

Perhaps you need to swap tables:

... FROM advertisers LEFT JOIN categories ON ...

Separate issue... You have different DATATYPEs for categoryID. Make them consistent, preferably SMALLINT UNSIGNED NOT NULL.

Simpler

Isn't this all that you need?

SELECT categoryName, categoryID
 FROM Categories
 ORDER BY categoryName

It seems that advertisers is irrelevant for building the pulldown.

Checking

You want a list of categories for which a row in advertisers exists?

SELECT c.categoryName, c.categoryID
 FROM Categories AS c
 WHERE EXISTS ( SELECT *
 FROM advertisers
 WHERE categoryID = c.categoryID )
 ORDER BY categoryName

• Eugene van der Merwe: Will check and get back with feedback
• Eugene van der Merwe: Still gives me the same problem. You can have a look at the site here link Under the Directory Menu
• Rick James: INT and SMALLINT -- you are still inconsistent.
• Rick James: Sample data might help us. Let's see the rows relating to "Buiding materials/paint".
• Eugene van der Merwe: Added some sample data hope this helps