August 15, 201213 yr Guy's. I currently have a website and I need to streamline the current method I have for updating it. The website is a security directory http://www.uksecurity-directory.co.uk and I have companies listed for services and locations. What I'm doing at the moment is I have a page for each of the UK area's as well as inside that another page for each security service within that area then I am just adding the companies links to each of the relevant pages. In a nutshell the same company link/advert is shown on multiple pages which is a pain in the backside to update and edit as I am having to update multiple pages for every listing. Basically what happens is a company fills out the form on my site telling me what services they offer and what locations they are based. So the info I get will have for example, "security guarding, door supervisors, key holding" and "North East, North West, Midlands, Scotland". What I then do, after putting that info into an Excel spreadsheet is update the individual pages with that companies link, which in this example would be 12 pages (North East security guarding, North East door supervisors, then North West security guarding etc, etc). Is there a way in which I can set up a database or use the database that I already have within Excel to update the listings in my html pages (I use Dreamweaver also). Ideally what I'd like to do is input the company name and relevant links/information then fill in some check boxes on a spreadsheet and then let that automatically update the relevant pages? Hope that makes sense. I'm kind of expecting this to not even be possible but you never know until you ask. Thanks for your time.
August 15, 201213 yr I'd look at moving your whole excel database into a sql database, and then moving away from static HTML pages to something more dynamic using PHP and the MYSQL database created from you spreadsheet. In the long run it's going to make your site more expandable and should reduce the time you have to edit the pages.
August 18, 201213 yr Author I'd look at moving your whole excel database into a sql database, and then moving away from static HTML pages to something more dynamic using PHP and the MYSQL database created from you spreadsheet. In the long run it's going to make your site more expandable and should reduce the time you have to edit the pages. Thanks for the input. I had looked at the SQL option just before posting but only very very briefly and I as I don't know PHP very well it kind of went over my head. You've confirmed what I suspected though so will now look into that. Thanks.
August 20, 201213 yr Yup, PHP/MySQL is the way to go. In a nutshell, you put your database together however you want it. Then you write web pages that do something like this (pseudocode) which will automatically put together a table: include 'header.htm'; //The common header file for everything, basically all the HTML up to and including "<body>" connect_to_database(); $query = "SELECT name, link, location, rating FROM companies WHERE region='London'"; $result = mysql_query(query); echo "<table> <tr><th>NAME</th><th>WEBSITE</th><th>LOCATION</th><th>RATING</th></tr>"; while ($row = mysql_fetch_object($result)){ echo "<tr><th>{$row->name}</th><th>{$row->link}</th><th>{$row->location}</th><th>{$row->rating}</th></tr>"; } echo "</table>"; It's obviously more complicated than that - the first big pitfall to watch out for is "SQL injection", it would be a tad embarrassing to expose a database full of security companies (A good first step is not including any non-public data on the database!) Edited August 20, 201213 yr by T-G
August 20, 201213 yr Author T-G many thanks for your addition. I'm just starting to get to grips with MYSQL and PHPMyAdmin stuff now. The database for security companies is literally a directory for resources. The only information I will hold is information that the companies have given permission to be displayed so there's not too much of a problem with security, but your recommendation is definitely something I will keep in mind. I do have another query though that I was going to start a new thread for but will see if you guys can help in here. Basically the information I will be holding for each company will be the company name, email, services offered and locations covered plus a description of that company. Currently all my information is held on a spread sheet. I have the first column set up for the company name then 1 column for each service and location then I have just simply placed an x in each column alongside the company name (like a check box) to denote what services and locations are covered (see diagram below). I can't seem to add a picture but I'm sure you get what I mean. Anyway the question is. Can I create fields in my SQL database that will contain multiple bits of information or do I need to have one piece of info per field. So for instance can I label a field services, input the relevant services each one separated by a character? So for example the services field would contain the information security guarding, door supervisors, close protection. Is that possible for the php to then distinguish between the different sets of information contained in that one field, or do I need an individual field for each individual service? The end result I want is to have a drop down search feature so that my users can select a security service, followed by the required UK location (i.e door supervisors , London) then when they hit search all the companies that match the search selections are displayed. Hopefully that all makes sense?! Edit: T-G I've just looked closer at your code you provided and it looks like you've based it on one having a field labelled 'region'. Then selected all the companies showing London in that field to be selected. Could also have Scotland, and other regions in that same field under the header 'region' or would it all be treated as the same information? Some of the larger companies that use my site cover most of the UK and multiple services. I'd need about 23 fields just for the services and locations if it's not possible? Edited August 20, 201213 yr by MWP
August 20, 201213 yr What I had in my head was a field called "region" (eg London, Midlands, Northern England etc) and "location" (High Street, Somesuch Village). Basically, there's lots of different ways to organise the database and PHP and you need to pick the easiest/fastest/most convenient way. To take your example of the services offered, there's two obvious ways as you suggest: 1: make a separate field for each service, if there's a 1 in the field the company offers it, if there's a 0 they don't (or TRUE/FALSE if you prefer). This is a useful approach if you only have a certain number of services to list. Don't worry about having 20-something fields, that's hardly unusual. 2: Have a single "services" field which contains a comma-separated list, eg "nightman,keyholding,dogs,alarms", then when your PHP retrieves the field contents you can simply do '$services = explode("," , $row->services);' to get a nice array of the services offered. This is a little trickier to search however, you need to do a fulltext search which means you have to use a certain type of database (MyISAM off the top of my head, but I'm not sure) There's other ways to do it of course, like having an entirely separate table of services and insert the id of the companies which offer it, eg: table companies id - name 1 - Autosecure 2 - Betatek 3 - G4S table services name - companies dogs - 1,3 alarms - 2 nightman - 1,2,3 If I were you I'd get a test database set up, import your spreadsheet (mySQL can import excel spreadsheets) and have a play around. Don't underestimate the value of a blackboard and some chalk in planning these things!
August 20, 201213 yr Author Perfect, thanks. I haven't found anywhere yet that actually say's yes or no to my question in Google so it is good to see the options I have. The True or False method seems like the most obvious to fit in with what I've already been doing, but I shall have a play around. Cheers.
August 20, 201213 yr Database design isn't my strongest field (pun intended ), don't be surprised if you find a much more obvious and easy way! (And tell me if you do!)
September 8, 201213 yr Author OK. I'm getting there with this (I think) but have hit a wall. I've created my database and have written the php code to show it on a webpage. It works as required however I can't seem to style it. In the code below you'll notice that I have some paragraph tags in. That was just my experimenting as I can't seem to find a tutorial to explain what I need explaining although I'm sure this is more to do with me not using the right keywords! Anyway, my code is as follows; <?php $username="xxxxxx"; $password="xxxxxx"; $database="xxxxxxx"; $hostname="xxxxxx"; $link = mysql_connect($hostname, $username, $password); $db_selected = mysql_select_db($database, $link); $SQL = "SELECT * FROM security_companies WHERE london='TRUE' AND listing='SILVER' OR ukwide='TRUE' AND listing='SILVER'"; $result = mysql_query($SQL); while ($db_field = mysql_fetch_assoc($result)) { print "<p><a href=".$db_field['website'] . ">". $db_field['company'] . "</a></p><br>"; print "<p>" .$db_field['description'] . "</p><BR>"; } mysql_close($link); ?> Now, I want to add into the results a div class to style it but can't figure out where to put it. The look I want the result to have is shown here (You'll see the top listings have a blue background with a top border.) : http://www.uksecurit....uk/london.html When I put the div class around the whole php element it styles all the results with one seamless background, where as I need each individual result styled. I've tried adding a class to the paragraph tags for the second part of my code (not shown as that just displays a link) and it shows as an invalid syntax. If anyone has any ideas or ideally if anyone has a link to somewhere where I can learn this as I just can't seem to find the right place, it'd be much appreciated. Cheers.
September 8, 201213 yr Author OK with some trial and error I found what I was doing wrong. I've now added the highlighted styling and it works perfectly. <?php $username="xxxxx"; $password="xxxxx"; $database="xxxxx"; $hostname="xxxxx"; $link = mysql_connect($hostname, $username, $password); $db_selected = mysql_select_db($database, $link); $SQL = "SELECT * FROM security_companies WHERE london='TRUE' AND listing='SILVER' OR ukwide='TRUE' AND listing='SILVER'"; $result = mysql_query($SQL); while ($db_field = mysql_fetch_assoc($result)) { print "<div class=\"silver\"><h5><a href=".$db_field['website'] . ">". $db_field['company'] . "</a></h5>"; print "<p>" .$db_field['description'] . "</p></div><BR>"; } mysql_close($link); ?> OK, can't highlight it but it's the div tags and h5 tags's that I couldn't get in the right place. Such a good feeling to figure something out!!
September 8, 201213 yr OK with some trial and error I found what I was doing wrong. I've now added the highlighted styling and it works perfectly. <?php $username="xxxxx"; $password="xxxxx"; $database="xxxxx"; $hostname="xxxxx"; $link = mysql_connect($hostname, $username, $password); $db_selected = mysql_select_db($database, $link); $SQL = "SELECT * FROM security_companies WHERE london='TRUE' AND listing='SILVER' OR ukwide='TRUE' AND listing='SILVER'"; $result = mysql_query($SQL); while ($db_field = mysql_fetch_assoc($result)) { print "<div class=\"silver\"><h5><a href=".$db_field['website'] . ">". $db_field['company'] . "</a></h5>"; print "<p>" .$db_field['description'] . "</p></div><BR>"; } mysql_close($link); ?> OK, can't highlight it but it's the div tags and h5 tags's that I couldn't get in the right place. Such a good feeling to figure something out!! When i'm normally mixing bits of php code with html, I tend to keep my html 'outside' of the php tags. It makes things much cleaner to read imo. So your code could look something like this: <?php $username="xxxxx"; $password="xxxxx"; $database="xxxxx"; $hostname="xxxxx"; $link = mysql_connect($hostname, $username, $password); $db_selected = mysql_select_db($database, $link); $SQL = "SELECT * FROM security_companies WHERE london='TRUE' AND listing='SILVER' OR ukwide='TRUE' AND listing='SILVER'"; $result = mysql_query($SQL); while ($db_field = mysql_fetch_assoc($result)) { ?> <div class="silver"><h5><a href="<?=$db_field['website']; ?>"><?= $db_field['company']; ?></a></h5> <p><?= $db_field['description']; ?></p></div><BR> <?php } mysql_close($link); ?> and work the same. So I don't confuse you '<?=' is a shorthand method of writing '<?php echo' or '<?php print' Edited September 8, 201213 yr by Samus
September 9, 201213 yr The field you;d actually have is "region_id", that links to your lookup table, "regions". That's just embarassingly awful advice. Nobody in their right mind should be storing data like that. It's crap database design and makes adding extra services a pain in the ass. A little better, but you're failing to see the context of the OPs site. It's a directory. Searching will be common place. By storing the data like that you;re making things much harder than they need be when searching. I think you've confused yourself there as that's not how that type of relationship is structured. You're looking for something like (* = a PK): Company * company_id company_name slug [other fields ...] Service * service_id service_name slug [other fields ...] Company_service * company_id * service_id So in the company_service table you'd just be storing numbers and you can easily associate a single company with multiple services. +1
November 6, 201213 yr Author Ignore this, I've found the problem. It was me being a moron!! Edited November 6, 201213 yr by MWP
Create an account or sign in to comment