· 9 years ago · Jan 20, 2017, 06:36 AM
1require_once('connect.php');
2$menu = $DB->prepare("SELECT
3 advertisers.*,
4 ad_categories.*
5 FROM ad_categories LEFT JOIN advertisers
6 ON advertisers.categoryID=ad_categories.categoryID
7 WHERE advertisers.categoryID=ad_categories.categoryID
8 ORDER BY categoryName ASC
9 ");
10$menu->execute();
11$menu = $menu->fetchALL(PDO::FETCH_ASSOC);
12
13<li class="dropdown">
14 <a href="" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">DIRECTORY <b class="caret"></b></a>
15 <ul class="dropdown-menu" role="menu">
16 <?php foreach ($menu as $row) { ?>
17 <li>
18 <a href="http://www.platinumweekly.co.za/advertisers.php?categoryID=<?php echo $row['categoryID']; ?>"><?php echo $row['categoryName']; ?></a>
19 </li>
20 <?php } ?>
21 </ul>
22</li>
23
24CREATE TABLE advertisers (
25 id SMALLINT(6) UNSIGNED AUTO INCREMENT PRIMARY KEY NOT NULL,
26 categoryID SMALLINT(6) NOT NULL,
27 firstname VARCHAR(255),
28 lastname VARCHAR(255),
29 companyname VARCHAR(255) NOT NULL,
30 description TEXT NOT NULL,
31 excerpt TEXT NOT NULL,
32 publishdate DATETIME NOT NULL,
33 logoURL VARCHAR(255) NOT NULL,
34 address VARCHAR(255) NOT NULL,
35 telnr VARCHAR(20),
36 cellnr VARCHAR(20),
37 email VARCHAR(100) NOT NULL,
38 website VARCHAR(100),
39 facebook VARCHAR(100),
40 twitter VARCHAR(100),
41 instagram VARCHAR(100),
42 NewspaperAdvert VARCHAR(100)
43);
44
45
46CREATE TABLE ad_categories (
47 categoryID SMALLINT(6) AUTO INCREMENT NOT NULL PRIMARY KEY,
48 categoryName VARCHAR(255) NOT NULL,
49);
50
51
52
53CREATE TABLE IF NOT EXISTS `advertisers` (
54 `id` smallint(6) NOT NULL AUTO_INCREMENT,
55 `categoryID` smallint(6) NOT NULL,
56 `firstname` varchar(255) DEFAULT NULL,
57 `lastname` varchar(255) DEFAULT NULL,
58 `companyname` varchar(255) NOT NULL,
59 `description` text NOT NULL,
60 `excerpt` text NOT NULL,
61 `publishdate` date NOT NULL,
62 `logoURL` varchar(255) NOT NULL,
63 `address` varchar(255) NOT NULL,
64 `telnr` varchar(20) DEFAULT NULL,
65 `cellnr` varchar(20) DEFAULT NULL,
66 `email` varchar(100) NOT NULL,
67 `website` varchar(100) DEFAULT NULL,
68 `facebook` varchar(100) DEFAULT NULL,
69 `twitter` varchar(100) DEFAULT NULL,
70 `instagram` varchar(100) DEFAULT NULL,
71 `NewspaperAdvert` varchar(255) DEFAULT NULL,
72 PRIMARY KEY (`id`),
73 KEY `categoryID` (`categoryID`)
74) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
75
76INSERT INTO `advertisers` (
77 `id`,
78 `categoryID`,
79 `firstname`,
80 `lastname`,
81 `companyname`,
82 `description`,
83 `excerpt`,
84 `publishdate`,
85 `logoURL`,
86 `address`,
87 `telnr`,
88 `cellnr`,
89 `email`,
90 `website`,
91 `facebook`,
92 `twitter`,
93 `instagram`,
94 `NewspaperAdvert`)
95VALUES (
96 13,
97 6,
98 'John',
99 'Doe',
100 'Doe Incorporated',
101 '<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>rn',
102 '<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut facilisis ex nisi, sollicitudin faucibus lorem cursus sed.</p>rn',
103 '2017-01-13',
104 'doe-incorporated-logo.png',
105 '123 Some Street, City, Country',
106 '012 345 6789',
107 '012 345 6789',
108 'name@domain.com',
109 'http://www.domain.com',
110 'https://www.facebook.com/Doe-Incorporated/',
111 '@DoeInc',
112 'doeinc',
113 'doe-incorporated-2017-01-13-issue.png'
114);
115
116CREATE TABLE IF NOT EXISTS `ad_categories` (
117 `categoryID` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
118 `categoryName` varchar(255) NOT NULL,
119 PRIMARY KEY (`categoryID`)
120) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;
121
122
123INSERT INTO `ad_categories` (`categoryID`, `categoryName`) VALUES
124 (1, 'Accommodation/Guesthouses/Hotels'),
125 (2, 'Attorneys/Conveyancers'),
126 (3, 'Auctioneers/Estate Agencies'),
127 (4, 'Barbers/Beauty Salons'),
128 (5, 'Books/Literature'),
129 (6, 'Building Materials/Paint'),
130 (7, 'Butchery/Biltong/Deli'),
131 (8, 'Catering/Restaurants'),
132 (9, 'Charities/NGOs'),
133 (10, 'Churches/Religious Institutions'),
134 (11, 'Clinics/Hospitals/Veternarians'),
135 (12, 'Clothing/Fashion'),
136 (13, 'Colleges/Schools/Training Institutions'),
137 (14, 'Competitions/Events/Events Management'),
138 (15, 'Computers/Electronics/IT'),
139 (16, 'Contractors'),
140 (17, 'Convenience Stores/Grocers/Supermarkets'),
141 (18, 'Courier/Logistics/Transport'),
142 (19, 'Crafts/Gifts/Hobbies/Photography'),
143 (20, 'Dry Cleaning/Locksmiths/Shoe Repairs/Numberplates'),
144 (21, 'Emergency Services'),
145 (22, 'Entertainment/Exercise/Fitness/Recreation/Sports'),
146 (23, 'Equipment/Machinery/Mining'),
147 (24, 'Finance/Insurance/Tax'),
148 (25, 'Furniture/Upholstery'),
149 (26, 'Government'),
150 (27, 'Graphic Design/Printers/Signage'),
151 (28, 'Holidays/Travel'),
152 (29, 'Motor Dealers/Motor Spares & Accessories'),
153 (30, 'Other Services/Specialized Services'),
154 (31, 'Panelbeaters/Towing/Support Services'),
155 (32, 'Plumbing/Irrigation');
156
157... FROM advertisers LEFT JOIN categories ON ...
158
159SELECT categoryName, categoryID
160 FROM Categories
161 ORDER BY categoryName
162
163SELECT c.categoryName, c.categoryID
164 FROM Categories AS c
165 WHERE EXISTS ( SELECT *
166 FROM advertisers
167 WHERE categoryID = c.categoryID )
168 ORDER BY categoryName