0

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', 
    'name@domain.com', 
    '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');
  • 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. – Rick James Jan 16 '17 at 17:30
  • Updated my original post – Eugene van der Merwe Jan 17 '17 at 6:49
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
| improve this answer | |
  • Will check and get back with feedback – Eugene van der Merwe Jan 17 '17 at 6:58
  • Still gives me the same problem. You can have a look at the site here link Under the Directory Menu – Eugene van der Merwe Jan 17 '17 at 7:50
  • INT and SMALLINT -- you are still inconsistent. – Rick James Jan 17 '17 at 18:53
  • Sample data might help us. Let's see the rows relating to "Buiding materials/paint". – Rick James Jan 17 '17 at 18:56
  • Added some sample data hope this helps – Eugene van der Merwe Jan 18 '17 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.