So, in my gradual build-up of computer skills for my personal benefit and enjoyment, i’ve decided that my next project is to learn about databases and how to integrate them with web pages.
This post will be somewhat rambling and meandering, as i attempt to tell you what i already (think i) know, what i want to be able to do, and why i’m thinking about some things and not about others. I want to stress that i’m not looking for anyone to teach me how to do SQL, or how to write PHP. I realize that learning those things will take a while, and i’m going to devote a bunch of time to it. This post is more about getting myself set up, and learning what problems i might run into along the way.
First of all, the primary reason i want to know how to do this stuff is that i want to be able to make my own website with my pictures and other stuff, and i want the site to be searchable. More specifically, i want things like pictures and text to be searchable using categories, keywords, etc., etc. From some of the reading i’ve done, it seems like a database-driven site is the way to go. I also think it would be cool to learn databases anyway.
I’ve spent some time online just learning some basic SQL and PHP. While it will take a lot more reading and even more practice before i’m very confident with either of them, i do think that i can get the hang of them. They seem to be very logical in their construction, and i have the sort of mind that can generally follow that sort of stuff pretty well.
I realise that there are multiple database applications out there, as well as multiple coding languages for integrating databases with webpages, and also different types of servers. From what i’ve read, though, it seems that PHP is a pretty good way to go for this sort of stuff, especially for someone who isn’t an experienced programmer. Also, PHP seems to be rather easily transportable. Is all this true? Should i be looking more closely at other things?
It seems that lots of people, especially those who are just starting out like me, tend to go for a combination of mySQL and PHP. I’ve seen some other databases recommended, but my first thought is to download mySQL. Is there any reason why a different database would be better?
Also (and this is where i start to get a bit confused), i believe that, once i have my database, and i’ve contructed a website using html and php, if i want to be able to see the results of my PHP on my computer, i also need to install a server on my computer. Do i understand this correctly? Plenty of sites that i’ve been to on the net seem to recommend Apache as the way to go for a mySQL/PHP combination. Do you agree with this?
Is it hard to install a server on my computer? Will it fuck up any of my other applications or general computing stuff? Also, it seems that there are “kits” you can download that combine a database, PHP, and Apache server (plus other stuff) all in one file, like the ones on this page. Is there any advantage to doing this rather than, say, downloading mySQL, PHP, and Apache separately?
I’m also wondering if my whole move to databases can be made any easier by other software. I have Dreamweaver MX, but i’ve never really ventured much beyond basic html. I’ve done a very little bit of Javascript, and i’ve never really dealt with forms or anything like that. Will Dreamweaver make my life easier or harder in all this? Also, i’ve seen a couple of websites recommend ColdFusion for integrating websites and databases. I don’t have ColdFusion, but i can get access to it on campus. Will a program like this make things easier, or is it better to do it by hand?
I guess those constitute my main questions and concerns. I really want to have a plan before embarking on this, rather than starting something and then finding out down the road that i should have done it differently. Time is not really much of a concern, because i have plenty of non-computer-related work to do, and because i don’t have any computer stuff that needs to be done right away. This is all for fun and for my own interest as much as anything else, but i’d still like to do it right.
Web sites exist on web servers. Those servers may have several applications installed to take advantage of certain things. Apache, MySQL, and PHP are just some examples here.
You can use several types of web tools to build web sites and have the site(s) uploaded to the web servers. Some web tools only work if there is a specialized piece of software existing on the web server. HomeSite, Dreamweaver and FrontPage are examples here. (In the case of FP, FP-created sites will only work at optimum levels if installed on M$ servers with FP-extensions and being viewed only by web users using IE.)
Understand the difference between the above two concepts. From the gist of your post, you do not appear to be clear here.
PHP is open sourceware. So is MySQL and Apache. You do not need a paid license to install it and operate it. Forget Cold Fusion. IIRC you can install the CF personal server on your own computer for testing, but to use it on a web site requires a formal server installation and the software isn’t cheap.
Check out the O’Reilly book series. There are other publishers out there as well that cater to PHP, MySQL, etc. Plenty of quality web sites as well.
While bundled install packages may save you time, you are at the mercy of the person who created it. Sure it gets you up and running with a web server real fast, but is that all you want to know about it? Quick and dirty but how do you tweak it to learn more? Do you know enough about the package and its developer? (Wear Tinfoil hat: How do you know there is no pre-configured spyware in the package?)
All in all, keep one thing in mind. Do not be afraid to make mistakes, providing you are willing to try and understand what you might do wrong, and learn from them. Experimentation is lots of fun, but it can be time consuming (Be prepared to rebuild your computer time and time again if you are really into tinkering. That’s why many suggest having a completely separate computer to be your web server.)
My development environment is a WAMP environment with Windows, Apache, mySQL and PHP.
My production environment is a LAMP environment with Linux, Apache, mySQL and PHP.
There are a couple of small differences in the way that the PHP variables exist (especially with regards to the temp directory) on a windows vs. linux machine. The rest of the time I get everything executing just fine.
I downloaded the application software individually and installed Apache, PHP and mySQL in that order. I chose that way so I could learn more about what was being installed and from there into the config files (ini files) to see what I could tweak with regards to additional packages such as ImageMagik, etc. If you install from a bundled package you may never gain knowledge.
As far as development goes I use FP and wordpad to create my PHP code. FP to maintain the IDE and wordpad to edit the actual code. I have used PHPEdit which is a better IDE for this purpose but the bits I needed help with were all in French so I gave it up. Not sure if there’s an English version of PHPEdit.
I recommend an IDE (Intergrated Development Environment). This allows for syntax highlighting and debugging to occur (as well a plethora of other useful stuff) all in the same environment so it becomes easier to spot errors. There’s nothing worse that missing a } ‘somewhere’ and you just can’t find it.
Start small and grow bigger. Don’t try and redesign your entire site at once. You might take months and lose interest as you can’t see anything happening. Take a small part of your site (say one page with one form) and develop that. Decide what the form needs to do, see if that information should be written to a database and design your form accordingly. Then create the PHP to take the submitted form, maniuplate / validate the data and write it to a database. You then get an immediate feeling of accomplishment that things are going well (positive thoughts here).
Then move on to other parts of your site. As long as you keep the look and feel of the final rendered HTML there is no reason that you can’t redevelop your site in bits and pieces without your viewers noticing anything. Then when you’ve got the site set up, add the search capability. This is when you visitors will notice change.
One more thing to keep in mind is that generally your php files will end with php. eg. index.php rather than index.html
The .php extension on your files tells the webserver to run the page through the PHP interpreter first.
This can be problematical if some of your visitors have bookmarked the .html page. If you change it to .php their bookmark won’t work and you will 404 on them.
This can be changed by accessing the .htaccess file or the .ini file (can’t remember which one, sorry) to set .html files to be parsed by the PHP interpreter. This means that your visitors won’t notice any difference at all, even as far an naming your pages goes.
As has been mentioned PHP, mySQL and Apache are all open source, so no payment from you. Cheap!
If you are using a commercial host, most of them will have a package that combines mySQL and PHP. Finding a commercial host that supports other databases may be more difficult, but have a look around.
Don’t be afriad to try out new things. I started with PHP and mySQL about 18 months ago and haven’t looked back. There is still heaps to learn, but as I develop my site in bits and pieces, I am always looking for new things to do that can be done in PHP. Also when it all comes together it is very satisfying indeed.
I probably should have made it clearer, but i am actually aware of the distinction that you draw between (a) web server applications, and (b) web tools that i use to make my website.
My OP really relates, in the first instance, to the latter, i.e., to the stuff i will use for putting my website together on my computer. I figure that, as long as i use a set of tools and languages that are compatible with a large number of servers, i can worry about whose server i’m going to use later.
Basically, right now i’m looking for advice about tools are best to have on my own computer for the following tasks:
a) constructing (a) database(s) with all my pictures, essays etc.
b) writing a website that can talk to and display the contents of that database
c) actually view that website working on my home computer, without uploading it to someone else’s server
Thanks for the advice about the bundled install packages. It did occur to me that downloading each piece of software individually would probably be the best way to go, especially for learning stuff.
Unfortunately, i can’t afford a separate computer to do this stuff on, so i was a bit concerned about your assertion that i might need to be prepared to rebuild my computer over and over. Do you mean hardware, or reinstalls etc. of software? Because i am a grad student working on my dissertation, i have to be careful about keeping my research work and my writing backed up, and i’d prefer not to have to reinstall everything on my computer on a regular basis.
And thanks for the advice about not being afraid to make mistakes. I figured that this would be the case, and that it will be something of a long process, with plenty of swearing along the way.
Caught@Work:
Thanks for the advice about software installation. It’s this sort of stuff i’m least sure about. You see, i assumed that i could download and install mySQL, spend a bunch of time putting my database together (pictures, descriptions, keywords, dates, etc.), then download and install PHP and Apache and put the website together. I figured that, when setting up a website like this, you take a completed database and then write a website and incorporate PHP in order to get access to and display the data as webpages.
But it appears, from your later paragraphs, that you actually write PHP to add data to the database in the first place, rather than just add a database to a website using PHP. I’m going to have to go off and do a bit more reading about this, i think, as it seems that i’ve been under a few misapprehensions.
Some of your wording suggests that you think that i already have a website, and that i’m going to add a database to it. But i don’t. Basically all the website design i’ve done in the past has been for other people (e.g., for the profs at my university, who don’t know any html). I don’t have my own website at all, not even written on my own computer, so this enterprise will literally be starting from scratch. I don’t even know what my home page is going to look like yet.
As for choosing a commerical host, one of the reasons that i thought to go with mySQL and PHP from the beginning is that, according to what i’ve read, a large proportion of web hosts support those packages.
Anyway, not sure if all this has made things any clearer, or has just muddied the water even further. Thanks for the advice so far, folks.
You don’t have to rebuild your computer over and over. And there are many, many free tools out there that you can use.
For example, I’m working on an application right now that uses Tomcat as a web applications server, mySQL as the database engine, and JSP pages, tag libraries, and Java Beans for the business layer. You can set an environment up like this for free by downloading Tomcat, the Java SDK from Sun, and you can get a great free editing environment by downloading Eclipse. This is an enterprise-level tool set that can be used to build large commercial web sites.
I also build applications on the same computer using IIS 5.0 for the web server, SQL Server for the database, and ASP pages. Again, all this stuff is free (substituting MSDE for SQL Server, anyway), and can coexist with other development environments.
When dealing with databases, I actually prefer the Microsoft solution. I use ADO to connect to the database, and it’s as easy as can be.
PHP and mySQL are perfectly fine methods of creating a web page, and indeed you will probably be able to find a hosting service that supports this combination. But you will definitely want to verify first that they specifically do support webpages utilizing these programs.
Just a warning, but this will probably be a pain in the ass. Not to say that it wouldn’t be a fun project, but I suspect that you will find this more difficult than you are thinking it will be. I believe that there are ways to embed google in your webpage so that it only searches your own site and this really might be recommended.
Specifically, to get your pictures and essays in the database, you will need a webpage on your server that can upload a file off of your harddrive and put it in the database, and then return some sort of unique identifier which you can then write into your various links and image sources which allows those webpages to know how to get data out of the database. This all means a lot of hand-work on your part and making sure you get everything right. And of course, the order by which you add things to your development database and public website could change the unique id, so you would have to individually fix all those pages and such. Essentially, anywhere anything relies on manual intervention, you’ve got a big mess waiting to happen.
Now there are other ways to handle this issue, like by scanning the harddrive with a webpage, parsing files, and adding that data to the database. But this will be a difficult bit of code–not to mention that some hosting services would rather you not manually effect the file system (for security purposes) though if you only read data, this might still be possible. …You would want to have some level of security on this page, so someone randomly accessing your site wouldn’t be able to keep hitting the update button on your database.
Yes, you do need to have a server to run a preprocessed webpage. If going with PHP and mySQL I would recommend Apache. There is a PHP module for IIS (Microsoft’s server stuff–which incidentally, you can install off your Windows CD if you are Windows) but it is a pain to find on the interrnet, and most reference material you will find for PHP and mySQL will be assuming that you have Apache. Also, I would assume that your eventual hosting company will probably be running Apache as well, and generally I think it’s best to develop in as close an environment as your target one.
You might want to call about first to see what versions of these packages are supported though. For instance, PHP 5.0 was just released and supports a greater deal of object orientedness–which is lovely, but if you wrote all your pages with objects, just to find out that no ones supports 5.0 yet because it is too new to be viewed as secure, then you’ve got a problem.
But anyways, so long as you follow the instructions, downloading and getting Apache, PHP, and mySQL is not an issue. Might be a couple times you scratch your head for a moment, but really it was pretty easy for setting up a working development environment on your personal PC.
I would recommend downloading and installing them separately. No they shouldn’t mess up anything else you have.
In general, I view impressive work environments which “make life easy” as only making life easy if you already understand what all the hard parts are. So until you can do everything that these applications (like Dreamweaver) can do by hand, I would recommend not going for them. Otherwise they’ll just be adding another layer of stuff to figure out when you’ve already got quite more than enough on your plate.
After you’ve done a nice sizeable project it might be worth it. But for most home pages, dumping a $1000 into Dreamweaver or VS .NET probably isn’t worth it. And if you go to work for a corporation doing webpages, then you’ll be issued a copy of whatever IDE they are using.
NOOOOOOO.
A database is an application that runs like a server. It just sits there and you send requests to it as SQL queries. It will then send that data back to you.
So when you download and install your database, it will be in an entirely different place and completely unrelated to your webpage. And once it is installed, you need to start the application running (…well, actually the installer will probably start it automatically on Windows and set it up to be launched every time the computer is started.)
Now, one odd bit is that a database has 1) The database application, and 2) Databases (<- plural) within it that can be accessed by different username and passwords. So the eventual hosting company will only have one copy of mySQL installed on their server, but will have created a database just for you within it (with a unique username and password.) Since you won’t know anyone elses username or password, it will look to you like you are the only database on the server, but there may be thousands more in there that you can’t see.
Also note that SQL itself is fairly limited–though semi-guaranteed to work across all databases. But in real life, there is a pretty good bet that you will have to or want to use special stuff that mySQL has added to the SQL language. These will probably not work for other databases and the query would just look like junk. Dependent on your level of comfort with just looking at the reference manual for mySQL, you may or may not want to by a mySQL book. As others have mentioned, the O’Reilly series tends to be better for most topics.
Since you have to manually enter all the information into the database anyway, you might consider just building an XML file with your picture information in them. You can use the XML file as a database, or you can put an XSL stylesheet on it and render it as a set of links, or whatever you want.
An XML entry for a photo might look like this:
<photos>
<photo name="Scene By The Lake" url="http://mysite.com/lakepicture.gif" date="04/23/2004">
<Description>
I took this photo last year, by a lake!
</Description>
</photo>
<photo name="Scene By The House" url="http://mysite.com/housepicture.gif" date="04/23/2004" >
<Description>
I took this photo last year, by a house!
</Description>
</photo>
</photos>
Once all your photo data is in XML, you can use the XML file to import into a database if you decide that’s the technology you want to use. Or, you can just transform the XML into HTML. You can even transform it multiple ways to give you multiple views into the data.
I think these guys have covered everything, but I just wanted to add that I set out to do much the same as you - write more interesting websites with a database and so on.
I used “PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide” by Larry Ullman to good effect, and managed to get done what I wanted to. I’ve not gone to the limits of either PHP or MySQL, not by far, as it’s just a hobby for me, and I’m no expert.
In terms of having your PC be a server, if you want to be able to check out your website on your PC before you post it to a hosting provider, you will need to have your PC running as a server. I think Windows 2000 and XP have “IIS” which will do this for you, although I don’t know how easy it is to configure.
I installed Apache, PHP and MySQL on my Win98 machine, and configured Apache to work with the other two. The documentation online for all the applications was very good, although I will say I didn’t get it up and running immediately - there was a bit of frustrated “WHY won’t you work!?!” before I got there.
I’ve really enjoyed working with PHP, and setting the server up myself was an interesting experience, and in my view, well worth doing for the understanding you’ll gain. It saved me having to ftp a new file to some server somewhere every time I tweaked something, as well.
Oh, and in terms of writing the actuall script / HTML, I use notepad. It makes me feel like a real geek, not using a more complicated editor.
I can’t believe I just said that.
All good advice here. I do have a couple of things to add:
there’s already stuff out there that does what you describe. For example, Moveable Type allows you to have text entries and pictures, and search them. It’s database driven. You can feel geeky by changing the templates and such. If you’re looking for a solution - rather than a learning experience - you might want to look into using something already written rather than rolling your own.
I’d go with a commercial web service rather than setting up your own server. They’re cheap (the one I use is $5/month and comes with MySQL and php) and you won’t have to worry about a bunch of issues that come along with running your own host.
If you do run your own host, or even if you just set up a server for testing, remember to put a Firewall on it. I learned the hard way that a Web host is susceptible to a LOT more crap than a Web client machine. How did I learn it? By installing Apache so I could test some Web stuff. Four hours later I noticed my computer was downloading and installing crap by itself. It took me 3 days to clean it off.
php & MySQL are fairly easy to learn and use, but that’s not saying that it’s not going to be difficult to figure them out, especially if you don’t have a lot of programming experience. If you’re doing this just to learn, then great! No problem! But if you have goals in mind - “Get a Web page up and running within 4 months” - then I’d really really think about using a prepackaged utility to do it.
Sorry for the protracted absence, folks; the last couple of days have been pretty busy.
Thanks for the new replies. I certainly have plenty to think about before embarking on this project.
I think, given this paragraph and your other commments, that i must be under something of a misapprehension as to exactly how a database-driven website works.
I was under the impression that i could have a database with some tables in it and that the PHP would help get the data out. Let me try to explain how i understood it, and see if this makes sense:
So, say i have a very simple database table for my photos, for example, with columns titled:
Country
Year
People
Subjects
or whatever.
Then, say i want to call up all pictures taken in the United States, in 2001, containing John and Mary, and taken at the beach.
I was under the impression that PHP allows me to set up a search function that allows the entering of keywords, and properly-written PHP will use those keywords to create an SQL query, send it to the database, bring back the appropriate results, and display them as a webpage.
If it doesn’t work like this, then i have been laboring under a misapprehension, and i’ve probably started asking questions too early and need to go do a lot more reading.
Sam: another thing on my “to do” list is working out what XML is all about. From your post, it looks like that might be worth investigating now.
bras: thanks for the advice; i might check that book out. My university library actually has quite a collection of “how to” computer books, and i know there are a bunch on SQL and PHP.
Athena: thanks for the suggestion about Moveable Type. I might have a look and see what it can do. But this really is a learning experience for me, and there is literally no time contraint at all, It’s all for fun, so i don’t need to get anything up and running in a hurry.
Thanks everyone. I’ll get some books and start doing some serious investigation.
PHP itself provides a function which allows you to issue a SQL query and then retrieve the result of the query. This is no different from any other language and nothing about PHP specifically helps you do database work (except, of course, for the existence of those functions.) Creating the SQL query is entirely up to you.
SELECT pictureID FROM photoTable WHERE
country="United States" AND
year=2001 AND
pictureID IN (
SELECT pictureID FROM photoPeople WHERE
person="John"
) AND
pictureID IN (
SELECT pictureID FROM photoPeople WHERE
person="Mary"
)
However. I wasn’t really talking about there being any problem with getting data out of the database, but rather in putting the data into it.
Essentially, once you have a third party hosting company, the amount of access you will have to your database to insert information into it becomes limited.
If I might ask–since I am not sure how much you know–how exactly do you think that all of your pictures and essays and other information gets into the database, and then how were you planning on making this be both searchable while still allowing you to easily show the items statically in a webpage?
I think that knowing this would allow us to better answer your questions.
But don’t be scared though! Not trying to frighten you off–rather, just to farm out any misunderstandings so that you can get everything right the first time, without having to go back several times and rewrite stuff or do a lot more work than is needed.
XML is like a database but where you get to create and edit the contents yourself instead of having to do everything through the tailpipe. Access times are a bit slower, and it wouldn’t be recommendable for a corporate website with millions of hits a day. But for your purpose, this might be an easier and more flexible solution.
Not sure how much PHP reference there is, but if it looks like you might be able to find a couple of good books, that might be the way to go. The PHP 5.0 documentation certainly looks like it is supported, and that via the XSL and XML functions you will be able to do webpages and searching of those webpages in a centralized manner.
My biggest caveate is that database work leads to better paying jobs than webpage work–so it might be preferable to go the hard route if you are planning on getting into the programming world. Similarly, working with a database will be harder and force you to think more creatively and put more thought into design and flexibility–such practice is just good period.
First, i am completely aware that constructing the database will require inserting all the pictures, essays, keywords, titles, people, etc., etc. into the database myself. I realise that this will be something of a lengthy task (depending on how much stuff i actually want to put in), but the task itself is actually part of the appeal for me.
The second part of your question i’m not so sure about, and this probably reflects my inadequate understanding of the way databases can be integrated with webpages.
You showed the SQL query for the example that i gave earlier, and even with my very rudimentary knowledge i understand the language in that query, and i can see how it works to draw a particular record set out of a database.
My assumption was that i could set up a form (i’m not sure if this actually requires PHP or something similar), where there are a bunch of categories. For example (working on my previous categories), there would be a place to input the country, a place to input the year, a place for the people and a place for the subject/s. You could input search terms into one or more of these, and the appropriate SQL code could be generated and then sent to the database to retrieve the appropriate records.
Then, it was my understanding that the PHP itself allows for the on-the-fly creation of html pages that present the data in a particular way. The sort of model i had in mind, i guess, is something like a library catalog where you can input keywords for categories like author, title, subject, etc., and retrieve a set of records presented as a web page.
Again, if i’m an idiot and completely misunderstanding the way this works, i apologize.
Ah, see this is a little confusing too.
I assumed that if your website was properly created, and if the code was properly written, then it would be a relatively easy task to add things to the database and the new entries would then be incorporated in the sort of searches i was talking about above.
Well, as someone in the process of getting a PhD in US history, i guess i should always keep an eye out for alternative job opportunities, given the current academic job market.
But this stuff, at the moment at least, really is merely for my own edification and amusement.
One final question. This one is specifically about databases on my computer, and has nothing to do with web pages.
Before i started thinking about doing all this more systematically, i played around with Access a little bit, just trying to make a simple database incorporating a few photos. I made a few tables and set up some categories and stuff, and was sort of learning how the whole thing works.
The problem is, if i’m going to have my own database of my photos on my computer, i would like to make it so that when i search the database and get a set of results, it will not only show me the details about each photo, but will actually display the jpeg image itself.
I couldn’t find a way to make Access show a jpeg image as part of each individual record. I’m not sure if this is just because i suck at Access, or if this is something that the program itself isn’t designed to do.
Can this be done in Access, or in any other database program?
This will work, and it will work even once you are dealing with a hosting company. So if you do things this way, you at least don’t have to worry about that.
The issue is–and this is speaking from a good bit of programming experience–that anything that requires human interaction is going to be problematic.
Firstly, you will want to have a working copy of your webpage that runs just on your home computer and another that is your public webpage. This means you will have to individually insert every single picture, document, scrap of text, and description plus where those items are meant to go (which page and in what order) twice. Now short of building your own wiki system, going back and fixing either of these if you did the insertion wrong is going to mean either deleting the contents of the DB and re-entering, or creating a webpage that just runs a single SQL query. If you have the single query page, and the bugged page was on the public server, this means you will have to write the query (in the page), upload it, access the page, verify that the fix was correct, and then repeat that for every single error.
And this is assuming your basic design wasn’t flawed to begin with. In reality (particularly as a beginner), you will probably have to redesign your database and PHP templating several times to a level that will require your having to re-enter all the data and fix it all over again.
Also, it sounds like you are planning on just having the entire set of webpages be in the database (again, like a wiki.) If this is so, then the biggest issue is simply creating a sufficient set of tables in the database to allow you to specify what item goes on what page, where it goes on the page, color, link, style, etc. And of course, when you enter this in, in your form, you’re going to have to have a very clear minds-eye view of the resulting page–because as said, re-entering it will be very laborious.
If you don’t have all of this extra data in the database, but instead want to have a non-templated webpage that grabs text out of the database and then applies the various color, linking, and such that you want in the webpage code itself–then your webpage is going to have to know how to tell the database which item it wants specifically. (A search gets you a list of possibilities, but when you’re just making a normal page, you know which item you want already and should have some set way to specify it.) This means that when you enter your text into your form, you’re going to want to have some sort of unique identifier for it. In the webpage, you would then request the text that corresponds to that identifier and write it into the page.
If you decide your unique identifier and insert it into the database yourself through your form, then you need to have a bunch of notes where you keep track of them, so that your webpages and the database both synch up correctly. Of course, a typo will break this as will spilling coffee on your notes or losing them after development, messing yourself up two years later when you want to change your page.
However, if you have the database create a unique identifier yourself–then this means that you have to (probably) enter the data in a specific order for each item to end up with the same identifier each time you have to reenter data. So you will have to keep notes on the order in which you have to insert everything, and then you have to keep a record of what the IDs were, so that you can write them into your webpages. Again, typos, coffee, or lost notes will mess you up eventually.
Etc. etc.
So it’s not so much a matter that you are misunderstanding how databases work–but rather you suffering from a belief that you won’t mess up. Humans make errors, and programming is probably the profession which most particularly proves this point. Old grey-hair genius programmers working on a project will be just as likely as not to write very inefficient code that does error check after error check, because they know from their years of experience that their experience doesn’t make a squirt of difference. The program will break–the important thing is making sure that no matter what goes wrong you can find out where and be able to fix it without having to rewrite large chunks of code.
You will mess up a lot, and you should assume this for everything you make. So where possible, if there is a way to write a small bit of automation code–it will only require making sure that that works, and then after that you are again protected from yourself for the rest of development and it will only require small tune-ups to your automation engine or your input files to get everything fixed again.
If you simply wanted to show a table of “This person ate how many calories for each day from 1999 to present.” then this isn’t so much of a problem. But home pages generally are a lot more artistically created and consist of a jumble of text, images, tables, buttons, and links. Allowing such a random jumble to happen based on a very structured format like a relational database, will require a lot of tables and linking them to and from each other. …Either that or writing an entire webpage and copy and pasting into your form.
PHP is just a language. Creating a proper website engine that has the underlying infrastructure to be able to dynamically add and remove stuff is entirely dependent on your creating it yourself. Similarly as all the machining tools and an endless supply of metal will allow you to more easily build the worlds best ever car. But it just means you have a better starting position–actually designing and building the worlds best ever car is entirely up to you, your skill with the machining tools, and knowledge to design a fully functioning and kick-ass ride.
Literature major, here.
Firstly, to a database application, the data entered into it is just random 1s and 0s. Microsoft could, of course, give the option to allow you to choose what you want it to interpret your data as and show it–but this really isn’t the kind of thing databases are for. Databases just take your data and store it how you say to store it, and give it back when you tell it what you are looking for. Really it is best to keep it this way–software which tries to second guess programmers tends to be a lot more of a hastle than that which just does what you say and “the heck whicha” if what you said was inherently flawed.
Secondly, when you are dealing with a hosting company, the nice happy Access application probably won’t be there. In fact you may have no way to interact with your database beyond issuing SQL commands in a PHP page which you keep modifying and reloading. You are probably best to assume that everything you do with your database will all be text commands without any sort of windowed application. In this context, it will be impossible to show those images.
If you want to have your PHP page show an image from your database, you will have to create a PHP page that pretends to be a JPG image, and when you do a:
<img src=“imageGetter.php?image=myimage.jpg” />
and then imageGetter.php will find the image in the database, and return it with a header saying, “I am an image.”
Again, PHP and mySQL just provide the tools. Actually making anything requires you making it happen–and not messing up when you do it!
Thanks, Sage Rat. That’s all very ineteresting food for thought.
I’m going onto campus today, so i’m going to grab a few books and start reading. Maybe once i’ve got the hang of things a bit more, i’ll try writing a very small website and get some Dopers to have a look at it.
You lost me here. Why can’t you just build your database at home and upload it to your server?
I don’t understand this either. You develop on your development machine. When the code tests out fine, you upload it to the hosting server.
[quote]
And this is assuming your basic design wasn’t flawed to begin with. In reality (particularly as a beginner), you will probably have to redesign your database and PHP templating several times to a level that will require your having to re-enter all the data and fix it all over again.
[quote]
No. You don’t enter all your data and THEN code. You build your database structure. You write your code. You add five or ten sample records. You test your code against it. When you’re happy that everything’s working, you insert your ‘production’ data.
That would be a crazy way to build this.
Huh? You’re not going to hard-code unique identifiers into your web page, so typos are irrelevant.
Lost me again. I build databases all the time, and I have yet to have a ‘list of identifiers’ that I’ve written down to keep track of everything.
Not at all. I don’t really understand what you’re getting at. I have never, ever seen a database used to store the entire presentation code for a web page. Typically, if you want to serve up dynamic pages you would store the dynamic data in tables, then retrieve it, stuff it into various places in the web page using a server-side language, and display the output. At most, you might store a document in a database using something like XML, then run it through a transform to render it. Storing the actual physical representation of the page inside the database is nuts.
First, you’re not going to store the images themselves in the database. That would be silly. It would use up huge gobs of space, it would be slow, and there’s no need for it.
Second, if you did it would not be impossible to show those images. On the server side, you could simply retrieve the blob from the database, stuff it in the output buffer, set the mime type to image/jpeg or whatever the image is, and fire it at the browser.
Okay, this is what I was talking about above, but this really is an overly complex way to do things. While it might be useful for some commercial applications and huge web sites, trying to do this for a personal image site would be serious overkill.
Let me take a quick crack at steering you down a path. Let’s say you want to build a web site that allows people to search through all your pictures and display the ones that look interesting.
First, upload your pictures to a directory on your hosting server. Now you’ve got maybe 100 pictures in a folder, and we want to search them. Next step, build a database.
That’s the table definition. What that means is there will be three fields - a unique ID for each picture, a text string describing its location, and a description. A sample of two records might look like this:
PictureID Location Description
---------------------------------------------------------
001 images/puppies.jog I like puppies!
002 images/cats.jpg A dead cat I found in the road
Next, if you want to search for keywords, you want to have a ‘keyword’ table. This is really a mapping table between the images and the keywords. It looks like this:
Table - Keywords
----------------------
KeywordID - Integer
Keyword - String
[/ccode]
Now, from here we could add another field called 'PictureID', which would be the ID of the picture in the pciture table that matches the keyword. But here's the problem with that - say you have one keyword that applies to three pictures. Then you wind up with data like this:
KeywordID PictureID Keyword
001 2 CAT
002 1 DOG
003 1 PET
004 2 PET
Notice that if I want to have a keyword point to two different pictures, I have to have two entries in the database, and the keyword text is identical. This will work - if I want to find all the pictures that are associated with 'pet' I can do this:
rs = "SELECT PictureID from KeywordTable WHERE Keyword = 'PET' "
That would get me a recordset containing all the PictureIDs that match that keyword. Now that I have that, I could write code like this:
webpage.asp (actually pseudo-code)
Here's a selection of images you asked for:
<%
keyword = receivedForm.field(“Keyword”)
rs = “SELECT PictureID from KeywordTable WHERE Keyword = '” + keyword + “’”
For each record in rs
location = record.location
output("")
next
%>
Hope you liked them!
What this is, is a server-side page that gets rendered on the server and sent to the browser. The section between the <% and %> is actually executed on the server, and the output written inline with the rest of the page. The page that gets sent to the browser actually would look like this:
Here's a selection of images you asked for:
Hope you liked them!
So basically, you would write a web page that has a form that asks for a keyword. The user types it in, and submits it. The asp page receives the form, strips out the keyword, does a database query, then formats the result as images and sends it back.
Now let's say you wanted to add Categories. Now you can create a new table like this:
Table - Category
---------------------
CategoryID - int
Name - String
Description - String
The difference this time, just for purposes of illustration, is that I'm going to create another table that maps the categories to the pictures, so that we don't have the same problem we had with the keyword table of having to have duplicate categories for each picture. A mapping table looks like this:
Table - CategoryMap
--------------------------
CategoryID - int
PictureID - int
That's it. Nice and small. This is a 'normalized' database structure, where nothing is duplicated. So now let's say I have a category called 'Pets'. The category table looks like this:
CategoryID Name Description
----------------------------------------------------------------------
001 Pets These are all my pets!
002 Hated Things I hate
The mapping table looks like this:
CategoryID PictureID
------------------------------
001 001
001 002
002 002
Now, here's the nice thing about this - Now that I have a normalized category table, on my input page I can create a drop-down list of categories like this:
Now, when the person loads the page for selecting a picture category, he'll see a drop-down list with all your categories in it. When he selects one and submits the page, the CategoryID gets sent to the results page, which can then use that to map the category to the pictures that match and output them.
If your database is designed properly, you will never have to remember things like the IDs of categories when building the pages themselves. Any input that uses a list of selections should be generated by the same database that will be used to query the results.
Of course, this is just a rough sketch of how it might work, but at least it's a starting point you can use to ask questions. I didn't cover joining tables, or how you get the data into a normalized database in the first place.
Before you dive in to making actual web pages, I would play around with the database part of it first. If you download MySQL, download the MySQL Control Center. It's also free, and it's a nice tool for entering data, building tables, etc. There's even an SQL query editor in there you can use to write queries and see what comes back.
Get comfortable with databases and queries. Then start on a scripting language, either PSP, JSP, or ASP (they all work roughly the same way). Then put it all together with HTML pages.
I’m assuming a worst case situation where there might not be a way to import a database into the one on the remote server. Until having signed up with a hosting company and checked out what level of access you have, I always think it is best to assume that you will get dirt squat.
And hey, you’re ruining the “human dependency is bad” horror story! :mad: *
And now you’re ruining my, make sure to plan ahead horror story! :mad: *
That’s the point.
A file name is a unique identifier… I highly doubt that anyone has found a way to specify what should up on a web page without having some way (like a file path, or other ID) to uniquely pinpoint what item should go there.
Course, but you knew what you were doing. And, I am also talking from the vantage point of where there would be almost nothing in the file system–but rather where all text, images, and styling info was held in a database for searching. And while this would make the data infinitely searchable, once you want to grab all the paragraphs and images and styling data from your database to display what looks like a static webpage, I envision a lot of difficulty–as described.
This isn’t how your or I would build it of course, but it is a method I could see someone who was creating their first big project trying–just to have to rebuild it all again on realising it was a poor way to proceed.
Though, XML on the other hand, is essentially doing just this. But with XML that isn’t a problem because the XML can easily be translated to a webpage, and it can also be searched like a database. (Well, the images would be in the file system, just the paths in the XML files.)
Of course. But for someone who wanted to make everything on every page searchable, this would be a way to go and as such something a beginner might do. Not a good or recommended way, and I don’t know that mhendo would have ended up trying it, but it seemed best to be safe and warn against as many poor designs as I can think of.
Endo was specifically talking about keeping images in Access. I warned against.
Thanks, Sam, that makes thing much clearer in the practical sense of knowing the sort of things i can expect to have to master, and the sort of results that i can hope for.
Absolutely. I’m definitely going to try and just get the database thing down first. For one thing, the database itself is probably the thing that really interests me the most. For another, once the database is set up and running on my computer, at least i will have a useful tool, even if no-one else can see it on a webpage yet.
So i think i’ll spend some time trying to put together an efficient and comprehensive database of my stuff. I figure that once i’ve done that, i will also have a better idea of exactly what i want to be able to do in terms of making it accessible through web pages. I’ll come back and start a new thread once i’m happy with the database (although you may see the occasional thread asking for database help in the interim).
Anyway, thanks again to everyone who’s pitched in to help me on my way in this project. If/when it all finally comes together, months or years down the track, you’ll be the first people to see it.