Slashdot Mirror
Build a Database Driven Site -- Quick
Yank starts with the basics of MySQL and PHP installation on Windows, Linux and Mac OS X systems (he notes PHP 4.3 differences as well), and walks the reader through his first PHP script (no, not "Hello World!"). This first chapter is well written, with step-by-step instructions and shell script examples. It will help even a newbie feel comfortable with the process, and encourage him or her to move on to the rest of the book.
Chapter 2 focuses on relational databases and SQL queries. This chapter is not an in-depth study of RDBMs, but rather an extremely brief overview of the concepts involved in order to introduce the reader to command line interaction with MySQL. A simple database is begun that will be used in later chapters.
Basic syntax and commands of PHP are covered in Chapter 3 (statements, variables, operators). There are a lot of simple examples here that clearly demonstrate the elemental concepts of PHP. Yank uses forms, user interaction and control structures (if-else, while loop, for loop) to illustrate some easy methods of data access and user interaction with PHP.
Chapter 4 combines the two previous chapters' concepts into the beginnings of a working data-driven Web site. Yank shows the reader how to use PHP to connect to a sample MySQL joke database ("A man walks into a bar....Ouch."). He introduces sending SQL queries with PHP (mysql_query, delete, insert, update), handling SELECT result sets and inserting data into the sample ijdb (Internet Joke) database.
Chapter 5 is devoted to relational database design, and expands the one-to-one relationship to many-to-one, one-to-many and many-to-many relationships, this chapter teaches the reader how to join data spread between tables into one resultant set. This chapter is not meant to deal comprehensively with the complexities of relational database design. Indeed, the author gives an extremely brief nod to the inherent informality of his approach and references other resources for deeper study. Yank's intention here, as with the entire book, is to use relevant real-world examples to illustrate the simpler types of relationships a beginner will experiment with and how to deal with complex data and table issues with good design practice.
The next chapter presents content management and restricted-access database administration without relying on the command line (a few hints on protecting pages with appropriate access restrictions are in the introduction to this chapter but aren't dealt with in any depth until Chapter 12). Chapter 4's mention of forms is revisited here, and forms are used to manage, add, search for, edit and delete data.
At this point, the reader will have designed a database, organized the data into categories, created Web pages to display the data to site visitors, and prepared pages for administration of the data. The HTML is separate from the data, thereby relieving the Webmaster from the onerous and constant task of having to refresh pages with content. Here, in Chapter 7, the reader learns to format and submit content without resorting to hand-written HTML by using PHP functions (Yank covers the more standardized POSIX regular expressions, not PCRE). Code examples for string replacement, boldface and italic text, paragraphs, hyperlinks and splitting text into pages are included. The last bit of this chapter is dedicated to automatic content submission and has a nice design note about creating a visible column to the joke table where newly submitted jokes are handled as a No value, which allows review by a content manager before being posted.
This leads well into Chapter 8, "MySQL Administration (backing up, access control, checking and repairing data files)." Yank explains mysqldump and the use of update logs to create a practical backup-management scheme. He also covers using the myisamchk utility to check and repair MySQL data files. Basic MySQL access control using GRANT (creates new users, assigns passwords and adds user privileges) and REVOKE (the reverse of those functions) is included in this chapter as well, along with some tips and tricks to prevent access control problems.
Chapter 9 "gets back to the fun stuff" with Advanced SQL Queries (sorting and GROUPing SELECT results, setting LIMITs, LOCKing TABLES, aliases, LEFT JOINs and Limiting results with HAVING) giving the reader a well rounded sense of the versatility and scope of SQL in general and the SELECT command in particular.
Yank veers from textual data in Chapter 10, "Binary Data" (image files, encryption keys, programs for download) and shows the reader how to deal with working with files in PHP, handling uploaded files in PHP, storing and retrieving binary data in MySQL and learning when to use semi-dynamic pages to lighten the load on server performance in the process.
Chapter 11 deals with creating persistent variables, and offers an excellent description of cookies and sessions in PHP. I like Yank's figure "the life cycle of a cookie," which shows a graphical representation of a PHP-generated cookie. Yank rounds out the chapter with a simple shopping-cart example that consists of PHP scripts handling a product catalog and a checkout page (very real world).
The final chapter of the book is titled "Structured PHP Programming," and focuses on techniques for organizing code in order to simplify management (using include files, writing your own functions and streamlining code within Web pages). Yank gives a lot of sensible advice here, and his approach is not preachy. He brings up many important pitfalls that developers fall into: too much code, difficulty of finding what you need, understanding how it works. As this is a beginner's book, I would say that good design, good technique and good sense go a long way and should be stressed at the start of anyone's career in coding.
Build Your Own Database Driven Website Using PHP & MySQL, 3rd Edition runs only about 350 pages with a clean, easy-to-read page design, comfortable typography, lots of script boxes and screen shots. The appendices cover MySQL syntax, functions and column types and PHP functions for working with MySQL. Errata can be found at sitepoint's Web site, and I can't stress enough the value of checking these out before delving into any technical or instructional book: the frustration level goes way down if you know in advance that there's a typo, or a step missing!
This is a beginner's book with the essential tools and techniques that will get anyone started with serving up their first dynamic Web site. The tutorial approach of this book makes it easy for any reader to follow the step by step instructions. Yank manages to cover pretty much every topic necessary to provide the reader with a clean overview of the topic. It's a quick read and gives the reader encouragement and enough knowledge to move on to more complex volumes on the subject. This book provides a great first step for the beginner."
You can purchase Build Your Own Database Driven Website Using PHP & MySQL from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
It's like boiling water these days.
Database driven S?
Apparently a much needed book!
ROR! -- "Using PHP & MySQL to Build a Database Driven S"
;^)
quickly changed to: "Build a Database Driven Site -- Quick"
MMmmmmmh @ Perl + Mysql.
--Robert
A summary is NOT a review.
Slashdot meets Sesame Street?
We apologize for the inconvenience.
I was just using flat files for my S, and it was no good! Thanks to this book, my S is now running 150% faster and I can now start working on T-Z!
"Quoting famous computer scientists out of context is the root of all evil (or at least most of it) in programming." - K
They were built extremely quickly. If they weren't built quickly, I can only surmise that these sites were all a product of those who ride the short bus.
If you really want to set up a quick database site then you may as well use SQLlite that comes bundled in PHP5. No need to worry about connecting to a separate mySQL server with all those niggling connections
And really, if you don't already have an understanding of basic DB design (tables, fields, records, data types, etc.) are you really going to be designing such a site? If you didn't, there are plenty of free resources on the web to help you do that.
Programming is primarily a self-starter job. You learn by doing, and by using free resources out there on the web. Why pay money for a book that regurgitates already free information for two pieces of free software.
As the title suggests. Nothing about PHP templating technology (like Smarty) which can lead to some pretty gnarly PHP code. I'd recommend following this book up with Advanced PHP Programming by George Schlossnagle as it focuses more on PHP.
Shouldn't that be "Quickly", or are we allowed to modify verbs with adjectives on Slashdot?
"Today's Database was Driven by the Letter S and the Number 5"
I wish these books would include a section or two on properly coding web apps and sites. Often people who begin coding with weakly typed languages such as PHP (not a knock against it) do not become familiar with proper design and memory management.
(Even though what you are building probably doesn't even need javascript, let alone a database)
I'm always amazed when books claim 'quick' web development with MySQL and PHP. I think the planning of data structures alone makes this a not quick process. With like a gazillion pre-made CMS's available for demo at OpenSourceCMS (http://www.opensourcecms.com/) wouldn't that be the 'quick' way to go?
T.J. Schmitz - the man, the myth, the legend - o
I guess they ran into the 46 character bug, wh
You have a constitutionally protected right to be wrong, and I the right to ignore you.
I learned PHP using Kevin Yanks tutorials and articles 4 years ago. His books and tutorials are very easy to understand and use. His tutorials and articles can be read on http://sitepoint.com/
Free Web based FTP
I hope SitePoint is paying for all this advertising. That's like 3 book reviews in the past little while?
They should make this with firebird sql and php. would be much more powerful.
Remember the good old days when the /. mods would at least check the headlines before posting stories?
No
I recently threw together a rather Slashdot'ish site (http://www.omninerd.com/) and I used XML text files with PHP (and XSLT) over the mySQL alternative. Now, I'm no DB expert, but is there really any need for most sites to be DB driven? For example, on my site, there are articles, and the news posts that introduce these articles that readers can comment on. Perhaps I'm missing the big picture, but why would I need a DB solution when XML files get the job done, are easily portable, and can be accessed without the use of a DB program. I understand for extremely large data sets a DB is probably what you want, but what about small timers like me? Is a DB solution a waste of my time, or am I missing something big?
Mark A. McBride -- OmniNerd.com
In the early 90's many companies were working hard on data-centric products that took the grunt-work out of typical "CRUD" screens ("Create, Read, Update, Delete"). The leaders were probably PowerBuilder and Clarion, with VB and MS-Access barrowing some of the concepts to a more limited extent.
The Web seemed to ruin this trend. CRUD screens via web forms is a pain in the glutious. Web standards were optimized for e-brochures, not business forms. Frameworks exist, but I have not found any that scale well in customization: if you need something outside of the framework, you are hosed.
I wish the OSS community would work on producing more CRUD tools. If we have to toss HTML+DOM+JavaScript to get it, so be it. I think a remote-GUI protocol workable over HTTP is possible.
Business development is so much smoother if you have good CRUD tools. Otherwise you spend all day reinventing the wheel and dealing with low-level annoyances. I know many slashdotters don't like dealing with CRUD issues, finding it boring or feel it lacks geek status or whatever, but there is a big business need for it. I am kind of a connoisseur of CRUD technologies (for good or bad), and the current wine is bitter.
Table-ized A.I.
The review didn't touch on security. I think that when you're trying to teach beginners and/or non-programmers how to build web applications, a good foundation on computer security principles is a necessity.
Basic things like input validation and protecting against XSS are a MUST when dealing with PHP (or any language for that matter). Since beginners are the ones most likely to make these mistakes, it is important that they be educated now.
1. Buy a book about how to make database-driven websites.
2. Find the sections that tell you how to get it working.
3. Don't read any more about it.
Two words: "SQL Injection."
No flames, please. I never really studied MySQL (other than installing, configuring, fiddling with Wordpress DBs, etc.), since my scholarship's teacher is a fan of PostGreSQL and I learned it first. Now I'm curious about why MySQL is so popular. Everytime someone is talking about a database-driven website it's Perl+MySQL, PHP+MySQL, Ruby+MySQL. What distinctive characteristics does it have over PostGres? Is it faster? Why do you like it so much?
Someone said to me that it's simpler, but from the little that I tried they seemed to have pretty much the same complexity.
Prescriptive grammar:linguistics
Well I run a MYSQL and PHP movie site. It takes time to debug and build a list of films in the database, checking to make sure it is not of the same name. We interviewed some retro actors too. http://insomniacmania.com/ I have also written a crazy movie script that may interest some. http://insomniacmania.com/forum/viewtopic.php?t=20 05
1. Which language you use - be it Perl, php,
whatever - is not important. Know the language you
program in BEFORE you start the project! Almost
all scripting languages have the database interfaces
you need.
2. Encapsulate recurring themes like database
selects, inserts and so on. Knowing your language
helps. Balance abstractness against usability.
3. Use a (at least moderate flexible) template
engine.
Then youre (almost) done.
In the last few years I used PHP and Perl. Both
have their advantages and horrors. PHP is getting
(even) better fast. Perl is quite nice if you know
it good, which could take a little time.
I only used MySQL and SQLite. MySQL with InnoDB is
very reliable under heavy loads and huge datasets,
but gets rather clumsy to back up and replicate.
SQLite is blazingly fast, but I cannot say anything
about reliability. I wont bet my crown-jewels on
it (yet).
Anyway. Good luck.
Meme of the day: I browse "Disable Sigs: Checked". So should you.
I've seen _dozens_ of live database queries to fill a 'State' dropdown on a website... ...when was the last time we ratified a new state?
I can't help but feel a lot of 'live instant all the time' sites would be a lot more efficient if it was 4 database calls a day, rather than Every Single Time Slashdot Hits Their Site.
"Draco dormiens nunquam titillandus."
Many Web designers don't have deep knowledge and experience in data coding but want to get started serving up dynamic Web pages.
When I read this kind of article I can't help but feel shudders. I wouldn't touch one of this sites even with a disconnected PC.
"I think this line is mostly filler"
"Build a Database Driven Site - With a Skilled Contractor Who Actually Knows What He's Doing"
:)
Just a thought...
"It was hell!" recalls former child.
Connection pools are your friend... and rather hard to use without an app server, which kinda spoils all the fun of writing PHP, the point of which is generally being able to avoid having to use one in the first place.
Unless you have a limitless supply of CPUs+RAM, you're going to need connection pooling very, very quickly. Frankly, they're so easy to use, I don't understand why anyone would bother coding a database app without them.
But, for a beginner, this seems to cover some of the more important structural aspects of RDBMS in relation to webapps, not just "look, ma, it's dynamic!" Most of the books out there I've seen seem to just assume you know what you're doing on the SQL side of things and just focus on the PHP/JSP/Whatever side of things, which is just a death sentence for a beginner who has never touched a SQL server...
And teach people how to abstract connection information in a separate page.
A whole bunch of items are about to break beacuse people need to use mysqli. It would have been nicer if all these hacks used some db abstraction layer.
And anyone who has had to update some pages a newbie built, will say please learn to abstract the connection information into a single page, not one connection per page.
Ayeee.
Is something analogous to Web Forms. It'd be a great way to encourage people to work towards XHTML compliance if they could write some really slick UI with PHP and "PHP Web Forms" that could be manipulated directly from PHP rather than processed as a regular HTML form.
Then again, I suppose working namespace support is probably a more pressing concern at this point.
Click here or a puppy gets stomped!
Maybe we should focus on building a site well, not quick? How many PHP/MySQL driven sites have you been to only to be greeted by error pages? Note that I'm not blaming the technology, only those who put them together "quick" and "easy"...
And contrarily to popular opinion, Microsoft is not McDonalds ;-)
:)
Well they DO fit the "Supersize me" theme
I'll bite. Mysql installs & runs on Windows without any third party software. (Yes, the Web really runs on Linux. I know, I know.) PostGreSQL seems to run on Windows only in emulation (via Cygwin). Also, there used to be very slight performance differences (in terms of maximum numbers of hits to the DBMS per second, on simple benchmark datasets), and people seem to enjoy the idea that someone the same box could take 20,000+ hits in an hour using Mysql rather than 19,000 on the same hardware using PostGreSQL. This was a couple years ago so could be completely wrong now. I think there might also be a perception that MySQL is easier to use than PostGreSQL (based, I think, on pronunciation of the name). Plus, Slashdot uses MySQL. Those are the reasons I can think of. YMMV.
We must drive a sword through any hypothesis that is not strictly necessary.
MySQL installation is pretty brainless. Unpack the archive, tweak the config file, start it up, and create your users.
It's pretty fast, very stable, and has an excellent front-end available. Basically, it does the job, and it does it well. There are certain advanced DB tasks that you might need a more robust SQL implementation for, but for general purpose DB work, there's really not much reason to use anything else.
PostgreSQL is awesome, but last time I set it up, it was definately more work than MySQL.
Yet Another PHP And MYSQL Book. Good-Fucking-God. Mysql sucks ass.
I don't think it has to do so much with complexity or simplicity or anything like that, but actually more to do with the appearance of simplicity. MySQL has a ton of really good documentation in many places on the web, whereas Postgres has a crappy online manual that even folks with years of experience with other dbs find confusing (like, um, me).
Also, for select intensive applications MySQL is faster. And you don't need to worry about optimizations like you need to with postgresql. Vacuum? I don't even do that to my house.
MySQL started out a lot simpler - actually quite severely limited compared to PostgreSQL - but its simplicity is part of what attracted a large community. The community created network effects which have a lot to do with MySQL's continued dominance - more people are familiar with it, more packages already use it, so it makes sense to pick it for new projects, especially now that many of its original limitations have been overcome.
Silas
mySQL is popular now because every hosting firm offers it as an option, but Postgres is far less common.
Further, most web service add-ons (CMS, forums, etc.) are mySQL based out-of-the-box so it has become the platform on which to build.
You'll notice there are no technical reasons there - as RDBMS go, mySQL is pretty horrible. It's the Windows of free databases, as it were.
Let's get things straight. This book is for newbies or for those who want a quicksearch reference. After all, what non-newbies title doesn't include "build your own"? (For more advanced users, I recommend the PHP Anthology which deals with more complicated stuff, like FTP, thumbnail generation, search engine friendly urls, etc.)
:-P ). But the part that has helped me the most is the reference (Appendices).
:)
I began programming in PHP+MySQL around 2 years ago, and this book practically taught me everything.
The book had a nifty section on administrating your MySQL database (specially useful when you forget your password
Appendix A: MySQL syntax (with all the optional parameters)
Appendix B: MySQL functions. For example, what command do we use to search a substring in mysql? Quick search Appendix B... there! LOCATE.
Appendix C: MySQL column types. I don't use MySQL commands often, except when I add a module to my PHP framework (programmed by myself). so when I want to know how to specify a certain type, it's all there.
And finally, Appendix D: PHP functions for working with MySQL.
When you have read this book and have it in your office drawer, flipping thru some paper pages is definitely much faster (at least for me) than typing the search terms on the keyboard. So I recommend it to anyone wanting to learn PHP and MySQL. And to anyone who wants a reference handy
(off-topic Grammar Nazi hint: It's "waste", not "waist". Waist is what you get when you waste too much money on junk food)
Coming from some work in PHP, I've been burying my head in Ruby lately, to much joy, and have also discovered Ruby on Rails, which was also featured in a recent Slashdot article. What I've seen is amazing so far (not to mention that Ruby code is so much more readable than PHP that it's not even funny). Just an FYI...
Now I'm curious about why MySQL is so popular
Two words: blazing fast.
I first compared mysql and postgres in the 1999 era, and found mysql to be like a stripped down hot rod, fast and without frills, though fun to use. Postgres was like a classic cadillac in comparison. Large, ponderous, full of features and amenities. But when it came to performance, mysql just blew postgres out of the water. I ran a number of benchmark tests, and in some cases postgres and mysql were fairly close, while in other tests, postgres took 10 times as long as mysql. In a number of the tests, postgres couldn't finish in a reasonable time, so the results were interpolated.
Postgres fans pointed out that what with all the features and sophistication of postgres, it wasn't fair to benchmark it against mysql.
Fast forward to January 2005. Postgres has gotten faster, while mysql has gained features such as subselects, row-level locking, transactions and more. I ran the sql-bench test suite against mysql 4.0.22, and postgres 7.4, both completely stock, with default configurations as shipped with suse 9.2.
As it turns out, mysql was still an order of magnitude faster on some tests, while mysql and postgres were close on only a few of the tests.
I find mysql extremely easy to work with, as well as being lightweight. There's a reason that Mysql is the M in LAMP, after all.
Mysql installs & runs on Windows without any third party software. (Yes, the Web really runs on Linux. I know, I know.) PostGreSQL seems to run on Windows only in emulation (via Cygwin).
This is no longer true! PostgreSQL 8.0 was recently released and one of the main feature enhancements is a Win32 Native Server
"As it turns out, mysql was still an order of magnitude faster on some tests, while mysql and postgres were close on only a few of the tests."
Which table type was used in the the default MySql setup?
Perl has built in database support. Why use SQL at all?
I'll save my money, thank you!
I'd be more inclined to using Ruby on Rails.
I was impressed with what I saw when... a lot of bang for very little code.
Wanted: witty unique signature. Must be willing to relocate.
So you mean something like an embedded DB, that every time an event was fired would note the event and also the generating UI element in such a way that something else could ue it to work backward to the UI element?
I also agree that it would be nice to expose the event handlers more in GUI development tools, instead of requiring you to go through layers... it really seems like a lot more could be done to visualize GUI layout along with behind the scene components together than has been done up until now. What we have is WYSIYG, but not WTHWHH (What The Hell Will Happen Here).
"There is more worth loving than we have strength to love." - Brian Jay Stanley
I'm gonna go out on a limb here and say that the person who modded this down completely missed the point -- namely, that when this topic was born, it had a different title that was truncated.
I thought it was funny, myself. *shrug*
I'm sorry, was that title "Build a Database Driven Site" or "Hack into someone else's Database Driven Site"?
Find a web host online that has PHP, MySQL and will autointall scripts for you. One such good one is http://secure.lunarpages.com/tracking/cgi-bin/clic kthru.cgi?id=mnewbe2
Purchase Webspace
Login and click the Mambo installer button.
Done - You Now have a PHP/ MYSQL Web Site
Or you can just install Mambo yourself http://mamboserver.com/
That is my suggestion for a QUICK way to do it.
mnewberg.com
I suppose they're archaic now.
The publisher is offering 4-sample chapters in PDF Format for this book on their Website: SitePoint.com/books/phpmysql1/ - It's definitely more useful in helping me make a decision than reading through a Table of Contents or Index, at least for me.
i think it's mainly tradition. mysql was fast, stable, and usable for basic sites long before postgresql. mysql gained a lot of mindshare early on, when they were the only free game in town (as far as most people knew anyway). while postgresql was focused on correctness first, and speed and ease of use only later, mysql was fast and simple to get working almost from the start, and most people didn't know or care why they wanted ACID in a database. now, some six years later, postgresql is mostly a match for mysql in speed, and mysql has added a lot of the 'real' database features that they were criticized for not having early on (although some of us will still not forget their attitude towards implementing them). there's not nearly as much reason to choose one over the other anymore as there used to be, but mysql had the advantage of early mindshare, so all of the websites talk about LAMP, and all of the books talk about "how to do X with mysql".
personally i would never use mysql for data that i didn't want to risk losing, although i have no doubt that it has improved substantially since the last time i had the pleasure of using it. but that's just me.
If I don't put anything here, will anyone recognize me anymore?
You'll notice there are no technical reasons there - as RDBMS go, mySQL is pretty horrible.
What a broad fucking statement to make without any facts. Grandparent asked a good question for once about comparing the two, and you zealots *still* have to bitch. Come on man.
Which table type was used in the the default MySql setup?
The default MyISAM tables were used.
I'll test again using the more capable innodb tables when I get a chance...
personally i would never use mysql for data that i didn't want to risk losing
I hear that a lot without much to say beyond it. But it seems like everytime MySQL and pgSQL come up it's hard to wade through all the zealotry and bs to find any real answers. What exactly do you (and presumably others) base your lack of trust in MySQL's data storage on? I've seen a few personal accounts about how MySQL has f'ed up and lost people's data, but do you know what kinds of things lead up to that? Or, why it would be more likely under MySQL than any other database?
It's more fun to tear your hair out trying to figure out what went wrong! :-P
Galen
In your face, and always right!
This was a couple years ago so could be completely wrong now.
.org domain relies on the PgSQL platform for example.
No completely wrong, but mostly:
* The current release of PgSQL runs natively on NT/2K/XP/2K3 Server as a service. The Cygwin emulation and related kludges are not an issue with either database now.
* PgSQL has been quite optimised in recent years, while at the same time MySQL has become rather less lightweight than it used to be. The only way to get any measurable performance benefit from MySQL over PgSQL now is to forego the use of InnoDB tables in MySQL (and the transactional ACID-compliancy/rollback capability that comes with them). Even then, it is only fast at SELECTs--speed of INSERTs, UPDATEs and DELETEs was never MySQLs real strom point in any case.
* As far as volume of hits and concurrent users go, PostgreSQL is far superior because it has a mature, stable MVCC (multi-version concurrency control) solution that almost completely eliminates table and record locking. If you have a site that does frequent and random insertion, deletion and modification transactions PgSQL wins.
* MySQL was perhaps simpler in the past, but that was because it's capabilities were much more limited. It isn't hard to use today, but it isn't exeptionally easy anymore. Furthermore, PgSQL has a lot more tools to ease administration tasks than it used to. I am puzzled by comments that PgSQL us hard to use--I actually find it is easier to use than MS SQL Server 2000 now. The documentation has come a very long way and you can point-and-click your way around PgSQL with PgAdmin, WebMin, and various PHP web-based tools.
* There are a lot of large-scale PgSQL implementations that rival or exceed Slashdot in scale. The entire
Anyways, I hope I haven't offended MySQL fans--it is a fine product and has enjoyed a great deal of success and advancement with its association with SAP for example. For a typical blogger/slashdot-style site MySQL fits the bill nicely as it has the largest installed base, doesn't handle mission-critical data, and the vast majority of activity is read-only.
If the data in the application is *important* and is write-heavy then you'll find that the case is different than above. For mission-critical web-based systems PostgreSQL tends to be be chosen over MySQL. For example, the SQL-Ledger accounting system uses PgSQL and NOT MySQL. However, MySQL has grown up some and has become a viable option here too--it's just that PgSQL has a more established image as being not the fastest but themost reliable with your data.
Just remember that if you decide to pass on InnoDB to max out performance of your MySQL database you better make damn sure you have a reliable UPS and don't trip on the power cord or bump the emergency power disconnect switch or you'll have a crisis on your hands...
so, now that PHP actually has a complete, functional, and, most importantly, built in database abstraction layer, why are they still teaching people to use mysql_connect/query/etc?
shouldn't everyone be using PEAR::DB by now?
bad news when you decide you want to change your database because mysql can't handle the load without munging your data anymore....
(ok, so the jab at mysql was flamebait, but the rest is a serious question....)
If I don't put anything here, will anyone recognize me anymore?
I tried this WAMP setup. PHP, MySQL and Apache all in one. It was really easy.
In no time, I had some PHP software up and running on it.
Don't know if it's the best or not, but it worked for me on my Windows box right away. Just install and it has a nice control-panel based thing to use. I use it for an intranet site at work.
http://www.wampserver.com/en/index.php
(and I didn't need a book to set it up...)
MySQL beats the others in speed only if you use the default table type. When you change to an ACID compliant table you loose your speed. When working with simple sites with little data and not many concurrent users I've found flat file databases like Sqlite to be faster than MySQL.
Cheers,
Adolfo
Lately I have been tasked with helping our communications department get on track with creation of a data driven website. At the moment, we're talking about helping two people. One is a graphic designer who manages a fairly large website. She has done a little bit of asp/access, but I don't think she understands it particularly well. She says she did a little php a long time ago. The other individual has been maintaining a filemaker database which presently contains data that is not in an optimal format for programming. This second individual has, using filemaker, managed to generate static pages off of the data (using some rather scary techniques). While they both have probably seen or written a few sql statements, I doubt they understood what they were doing.
// declaration // gets the student with pri key=2 // print's student's name. // commits name change
I am on loan to this department, so I can't just finish the project in a weekend and then hand it to them. Rather it's going to be a fairly long drawn out educational process (~2 months @ 1/2 time). They need to be able to understand how it works, how to maintain it, how to enhance it. Essentially they need to be an integral/invested part of the development process.
Anyways, my initial idea was to have them use PHP alongside Pear's DB_DataObject and eventually Html_QuickForm libraries. For those not in the know, DB_DataObject is an object oriented data access layer generator framework thingy. Basically, instead of establishing connections, writing sql statements, and iterating over recordsets, they can write fairly simple code like the following.
require_once('some-config-file.php');
$student = new DB_Student();
$student->get(2);
print $student->name;
$student->name = "Bob Bobertson"; set students name.
$student->update();
Now when I see a newbie book teaching people to pound out their own sql and use old school mysql_connect style functions, I question my judgement. Is it a good idea or a bad idea to try to introduce these kinds of rapid development tools to novices? On one hand, these tools make my life easier on a daily basis. On the other hand, sometimes it's better to know the basics before going off to advanced topics like this. What do you guys think?
It should be noted that whatever happens we are not sticking with filemaker (not even my decision). We will either be using Access which doesn't appear to be supported by DB_DataObject or potentially Access/ADP/MSDE or Access/Linked/MySQL which both do work with DB_DataObject. I am desperately trying to set up something that lets them create/edit/drop tables from within Access and lets them easily design queries in access which are then usable with DB_DataObject.
Thus far, the closest I've come is using MSDE (light weight MS SQL Server) as the backend for Access. This is done using the Active Data Project (ADP) format not with linked tables. They can create/edit/drop tables and create views in Access. The views and tables are all reachable via DB_DataObject. However, there is no expression builder in the Access interface when working in this fashion.
This is problematic because these folks are more accustomed to using wizards to dump all of their messed up logic right into their database software. I can see them wanting to create numerous complicated views but not knowing how unless they learn a sizable chunk of TSQL. If they have to do that, the value of a library like DB_DataObject, which prevents them from having to write sql, is significantly reduced.
Personally, I think it all comes down to which they want to be easier: creating access forms/queries/etc or creating data driven web pages. Any thoughts?
Most PHP developers that I know don't take those things into consideration, and when they do, they move on to something like Java, Python and .NET
That is one of the main reasons that made me settle for ASP.NET on the app servers and linux on the database and NSF servers. I get built in caching capabilities so I can decide to keep the State query on memory or on disk for, lets say, a day.
Yes, I know that the same thing can be achieved with PHP and third party products, but my point is that PHP is a friendly, quick, and easy point of entry to many people. Therefore, many mistakes can be made and the outcome is not allways the best.For that reason I like to call PHP the Visual Basic of the web.
Cheers,
Adolfo
i used mysql at work for some time from 1998-2001. at one job we had about 12-16 mysql servers, and every week, at least one of them would hang until we truncated that data files and restarted the server process. not really a big deal to us, as we used the mysql servers for pure speed- all of the data in the mysql databases was tracking data that was aggreagated into two oracle databases on sun e5000's. (the mysql db's were also on suns, but smaller)
i worked at another job where we were using mysql to back an in house cms system, and the data corruption there was less frequent but more problematic. all of the data that went through the system was also archived in rcs, so we never lost any data, but it was still a pain restoring it.
i haven't used mysql since then, so i don't know what it is like currently, but the attitude of the mysql developers was never very reassuring to me either.(*) it's been a long time since i've worked on a project where database speed was my primary concern, so when i can choose my database, i usually choose postgresql. when i can't, it usually seems to be oracle or ms sql server. if i had to work on another project like i did in '99 where speed was my primary concern, i would take another look at mysql- that was why that company used it after all. for all the problems they had with it, nothing else was nearly fast enough.
(*) given the rather famous rant about why foreign keys are a bad thing and you should never use them, as well as some of the other statements they've made over the years about transactions, atomicity, etc. i have a hard time trusting my data to an application written by somebody with their attitude. that is the main reason i say that while i am sure that the mysql of today is nothing like the mysql i used years ago, i still have no intention of trying it again.
If I don't put anything here, will anyone recognize me anymore?
There's also such a thing as programming idioms. Translating a set of non-ACID MySQL queries verbatim to PostgreSQL isn't likely to give good performance, in much the same way that writing C in Python is going to be dog slow. It doesn't seem to like getting bogged down in the hundreds of tiny individual queries that people used to fire off to MySQL back when they used to think that MyISAM was pretty cool.
On the other hand, idiomatic PostgreSQL is likely to be nearly as fast (or much faster) than its MySQL counterpart, much as idiomatic Python is comparable to C. In both cases, given the backend a high-level task and letting it handle the low-level optimization usually results in a huge performance boost.
In other words, you can't take a series of queries written for 3.x-era MySQL and expect them to run well on pretty much any other database. If that's not what you're doing, then I apologize. If it was, then you might consider re-thinking your testing methodology before drawing conclusions.
Dewey, what part of this looks like authorities should be involved?
First of all, this book seems like a nice rewrite of on-line documentation. It is even a good idea in principle, because building a database driven site with PHP and MySQL is indeed very quick, almost as quick as using Perl and SQLite, but as with every RDBMS there are gotchas. It is true for MySQL, true for PostgreSQL, true for SQLite and even for Oracle, because just like no system is secure, no database is perfect. You always have to know the gotchas to work around them, which is especially important when you want to write a portable database-independent application, which is always a good idea. Unfortunately, this book lacks many important informations about those issues, as it also lacks essential introduction to relational algebra, set theory and predicate calculus, which are important to understand the relational model and to know what the relational database is all about. Without such background, people tend to confuse the relational model with a SQL interface to the filesystem, or an object store, so the lack of such an introduction is the most important flaw of that book. Other than that, it is quite a nice rewrite of many HOWTOs available on-line, and it is always easier and quicker to read one book than to hunt countless websites. All in all, a nice book.
Sincerely,
Pan Tarhei Hosé, PhD.
"Homo sum et cogito ergo odi profanum vulgus et libido."
I guess it might be worth mentioning that when I built my first MySQL/PHP site, I used ADOdb. ADOdb is pretty slick! This is comming from a person who:
.. examples can really clarify things as well!
1. Never programed in PHP! (Hell, never programed in ANYTHING before)
2. Never did ANYTHING database related before!
An all-general newbie to this kind of stuff. In one day, I learned how to create tables, insert data, display data on web pages, and all of the other basic stuff! At least a must-check-out for beginers! Ohh yeah, and "use the force, read the source"
PS - Must Explain why I still have a girlfriend!
Fully dynamic websites will crush your server.
Dynamic websites may be easy for beginners with this book, but introduce (a) a large amount of data or (b) a large amount of traffic (e.g. slashdot effect), and your server will fall over faster than a debutante in her first set of heels.
I was on the team that helped set up cnn.com, back in the "early days" of the Web. And more recently, during the U.S. presidential debates, I convinced FactCheck.org that their server would stop falling over, if they just exported their article database as static HTML files, rather than being 100% dynamic. (that indeed fixed the problem)
Dynamic content has its place, but too many newbies make the assumption that a fully dynamic website is a good idea. For content that does not change frequently, it is often more wise to use triggers to export the data as static HTML than to continually query and generate the same dynamic content over and over again. Database query caches help, but not a whole lot. Static HTML pages, and dynamic pages that provide the HTTP cache/expire/etag info are much more friendly to the web caching infrastructure in your browser and at your ISP.
If anyone is interested in some extra resources for learning PHP, check out my powerpoint slides:
http://www.cs.trinity.edu/~rzinchak/php/
You have one friend in app design: Mr. Benchmark.
If connection pools make your app go faster, use em. If they don't, then leave them out and keep your app simple.
If your site is fast enough, don't do anything. Keep your app simple.
If your app is slow, find what makes it slow and correct it. Otherwise keep your app simple.
What do I mean by simple? I mean, use one class instead of a hierarchy of three if there's no need for the abstraction. Don't say "I might need it someday". Not using classes? No problem, you might not need them. When you do, you can add them. Don't use temporary variables to save compution. Don't pile everything into a small number of functions to avoid "function calling overhead". Don't indent more than 2-3 levels.
Concentrate on simple, readable, understandable, well-factored code. Then when it comes time to optimize (after you get a good set of benchmarks in hand of course), you will have a much easier time of it.
Advice from the trenches..........
> Two words: blazing fast.
Assuming you're using myISAM. No transactions, and a 2G limit. InnoDB and BDB are a great deal slower.
Yeah, ISAM is fast. They figured that out in the 60's
Its as simple as ./configure && make && make install, then you can tweak the config file if you want, or just init the database and start it up. Its been this easy for at least 4 years, which is when I started using it.
For select intensive applications that don't use transactional table types its slightly faster. And postgresql lets you run an autovaccuum to do it in the background whenever it needs, or you can still schedule it manually if you have an off-hours time you want to do it in.
Assuming that performed the benchmark for your own dataset, which interface did you use ? How about native SQL statements.
As a result, you will become a happier, more spiritually enlightened person. No, really!
If you can't manage to find hosting with postgresql support, you are blind or retarded. I found far more than I needed to when I looked, and ended up making my hosting choice based on other factors since postgresql was so widely supported.
Maybe that only begs the question: why is mysql webspace cheaper?
I don't know why, but if you are looking for way cheap webspace. Like free to $5/month. You'll probably find more offer mysql than postgre.
Seems to be my experience anyway.
I have to wonder why anyone wouldn't be using cached dynamic pages...
If your pages are cached with sensible conditions: that is, they depend on the content used to generate them, then you get marginally better performance than triggers which regenerate static HTML - and no briefly out of date pages.
If you're using a database to back the site's content, then you'll need simple triggers to flag those conditions. Otherwise, if you're using files to back it (e.g. a file per article), you don't even need that and you get up to date dynamic pages from templates with the performance of a fast optimised static server.
-- Jamie (who is building a server which works exactly like the latter...)
I've found that Mambo is a quick way to put up a very nice and robust database driven site that also uses PHP... Do we really need to reinvent the wheel?
When reporting benchmarks, you should report in excruciating detail. If it's too long for a post, give us a link to a website about the benchmark.
PostgreSQL and MySQL are designed to perform in different environments. There are fundamental design choices that determine the areas that each will excel. The most major one is PostgreSQL's MVCC vs. MySQL's locking. Each strategy is used to maintain ACID compliance (I'm talking about InnoDB now, since that has a more comparable feature set), but the performance implications are profound. In particular, PostgreSQL avoids many performance issues with locking. However, for a web app, maybe you don't care so much about locking. It all depends.
Each benchmark only matters to the specific set of database operations your applications are performing. Being more clear about the benchmarks enables the readers to either:
(a) relate to your results because their applications perform similar operations; or
(b) decide that a bunch of "select 1" queries from a single app aren't really representative of their usage patterns.
I am not saying that you ran a bunch of "select 1" queries. But without the tuning parameters of the benchmark software and the detailed sequence of events, your comment is no more useful.
Social scientists are inspired by theories; scientists are humbled by facts.
I have a similar attitude toward the developers of MySQL.
However, you should realize that with either, the probability of hardware failure is much greater than software failure (you might have a different story if you buy good hardware).
What worries me more than crashes of MySQL is the fear that it will do something I don't expect. Perhaps MySQL makes a lot of sense to some people, but PostgreSQL fits my brain much better.
Social scientists are inspired by theories; scientists are humbled by facts.
Zope plus Plone - if you are looking for a content management system, or Zwiki if you are looking for a wiki solution, or learn Python and roll your own inside of Zope's Management Interface (ZMI).
Before you know it you will have dynamic content coming out of your ears - and you won't have to muck about with a relational database at all (and the ZODB scales better anyway from my experience).
Lodragan Draoidh
The more you explain it, the more I don't understand it. - Mark Twain
What worries me more than crashes of MySQL is the fear that it will do something I don't expect.
That's another thing I seem to see on occasion with little elboration. What exactly do you mean by "something you don't expect?" And why do you think it is more likely to do that than any other database?
It would seem that personal experience is the most common reasoning behind people's dislike of MySQL. But it also appears that a lot of people's experiences come from a while back (e.g. grandparent), and have prompted them to not try it again as it has developed.
a lot of people's experiences come from a while back (e.g. grandparent), and have prompted them to not try it again as it has developed.
well, as i said in my last post, my past experiences with mysql are only a small part of the reason that i don't use mysql today. the biggest reason i don't currently use mysql is that postgresql provides more features that i use, and it's been a long time since i worked on a project where database speed was the primary concern. if i were to work on such a project again, i would be happy to look at mysql again, but lacking that there's not really any compelling reason to consider mysql again and give up stored procedures, true sequences, etc.
the other primary reason that i don't use mysql, which i mentioned, and which i think the poster you responded to meant by 'doing something he doesn't expect' is that the mysql developers have said and done a lot of things over the years which cause me and many others to wonder how much they really know about good database principles. and i would hesitate to trust a database written by people to don't really seem to understand the concepts behind data integrity.
and that's why you see comments like this on a regular basis. it's often based on the impressions that have been made by the developers rather than any specific behavior of mysql itself.
but anyway, an example of mysql doing something you wouldn't expect:
the lest time i used mysql, when you would insert into a column whose primary key is an auto-increment integer, the insert returns the next integer in sequence, whether the insert succeeded or failed. so rather than checking the return value of the insert to see if the query succeeded (and using the value in your application if it did) you have to do a select with that value to see whether there is actually a row present with that id. that is an example of mysql doing something that you wouldn't expect. and that behavior existed for at least two years before it was changed (if it ever was- i have no idea how long that behavior existed before i started using mysql, and whether it was ever fixed after i stopped)
If I don't put anything here, will anyone recognize me anymore?
I bought a previous edition of this book based on a "cautiously positive" review on Slashdot, and returned it - save yourself the trouble and don't bother. I don't know about this edition but based on the previous it's all fluff and no substance. Written like a student solely for the sake of ego. Not a single complete example in the whole book, and the main "pull-it-together" example required the user to type data in manually (rather than pull from database)! Too sparse for experts, and too irrelevant for beginners. Thumbs-down.
ooooohhhh man.
Come back here in a few months, after a few hundred (or maybe a few dozen) more postings to your site. Bogged down yet?
After all, I bet Slashdot would have been fine using just TEXT FILES... at least in the first few weeks or so.
Indexes are why databases exist. And indexes take a huge heap of data, and prevent you from having to examine every little blade of hay every time you're looking for a needle.
When reporting benchmarks, you should report in excruciating detail
meh. not intended to be a report, just a data point. But I did run a number of sql benchmarks, including the wisconsin benchmark. On the wisconsin benchmark, for instance, postgresql took about 5 times as long as mysql to finish.
It's not really practical for me to answer every heckler, but to the guy who mentioned a 2 GB file size limit, oracle had that limitation for years, but somehow managed to handle bigger size databases than 2 GB. ask yourself how oracle did it. apply the answer to mysql.
In short, I would love to see a benchmark that would show postgresql in a positve light! If any of you postgres fans knows of such a benchmark, bring it on, I'd love to run it on both pg and mysql to see for myself.
Well, I've been bitten by a few date handling issues in the past.
Perhaps some are corrected. Is Feb. 31st still a valid date in MySQL? It seems to me that in order to maintain backwards compatibility, MySQL would have to continue to do some of the strage things it did before. And "strange" is certainly in the eye of the beholder, but some things just really didn't fit my brain well at all.
Social scientists are inspired by theories; scientists are humbled by facts.
You posted a question about how to force Apache into running seperate named sites, but it's been archived so I can't answer it. Hopefully you'll see it here. (This reply posted anon 'cause it's OT) http://apache.slashdot.org/comments.pl?sid=133526& cid=11183303
If you really want to run separate different with essentially jailed configurations of Apache, you need to run different seperate instances of Apache. Improvements in Apache may reduce your desire to do this, but they'll never make it not true for some fringe cases.
However, I think I can give you a suggestion that provides a possible solution your question. 1) Run site 1 Apache on port 8001. Run site 2 Apache on 8002. Run different instances listening on those ports.
Run a main instance of Apache on port 80. Use mod_proxy and the ProxyPass directive inside each virtual host. So www.host1.com is proxied to 127.0.0.1:8001/ etc. This gives you named resolution to arbitrary ports OR hosts, so this result scales to multiple servers on multiple OSes - you can have www.mainhost.com on linux and www.winhost.com on a Windows box, both on the same routeable IP, using named differentiation on a gateway apache server (which can be either of them)
You can also use this technique to have www.host.com/win/ be a different machine.
The only problem is certain forms of server side tracking don't work properly because at least by default the requesting IP is the proxy (it's not a transparent proxy by default, at least)
- Arete
areteslashdot AT xig net