MySQL and postgreSQL on same box??

I'm relatively new to the database game and have been using AMP on an OS X 10.2 box as my learning platform. The application I'm currently building would benefit from features found in postgreSQL, like foreign key constrains, views, cascading referrences, etc. So, I'm wondering if I can install both MySQL and postgreSQL on the same box?

My concern is how will PHP know what Db to use when passing queries? I've never had to do any configuration for PHP and MySQL to play nicely together -- it's just always worked. Would I need to do something special to the PHP conf file, or is it even possible?

URLs with info on this would be great (already tried a google search without much luck).

Thanks much.

PHP has different APIs for different database systems, so when you use a function from the PostgreSQL API, it will connect to the available PostgreSQL database server.

Now, if you are using one of the databse abstraction libraries for PHP, then the choice of API is hidden inside the library. In this case, you would need to know how to configure your access to that library to choose between database systems.

There is no problem running PostgreSQL and MySQL on the same server; I do it all the time . There is no conflict, because by default, MySQL uses HTTP port 3306 (AFAIK), while PostgreSQL defaults to port 5432. The only thing to remember is not to attempt configuring both MySQL and PostgreSQL to serve on the same port.

There is no magic to running both database systems, but PostgreSQL support is not built in by default. You need to either re-compile PHP for PostgreSQL support (more details in the link above), or load the pgsql extension, in php.ini.

Why would I want to use database abstraction libs?

Sounds like it PHP is configured to use pgSQL and mySQL, them I should be good to go -- at least for the purposes of trying the system out.

Thanks for the info!!

You'd want to use database abstraction if you wanted to make your app available to work with more than 1 database. If you abstract the database connection/query layer you can distribute your application to users of both postgres/mysql/any other database supported in your abstraction layer if you program your app correctly.

If you know that your app will only be used for 1 type of database and will only used internally and not distributed it isn't necessary.

-b

I will have to vehemently disagree with one small part of this comment. While I agree that database abstraction layers can be good things, there is no advantage to coding your application to the "least common denominator" among database systems. If you are going to code your app so it works on all systems, then your app will be terribly hobbled in it's ability to do such things as preserve business logic in the database, and other critical information management concepts. For example, when I code any serious logic in a PHP/MySQL application, I find myself forced to write much more PHP code than I would have to for a PHP/PostgreSQL app, simply because PostgreSQL can handle virtually all of the central logic of the application, and it can do so much more concisely than you can do with application-level business rules and constraints.

Now, if you decide that you will stick to ANSI SQL relational compatibility (which MySQL doesn't have), then you can at least have a significant level of cross-database connectivity with your application, while keeping most of the advantages of relational SQL systems. This would include PostgreSQL, Oracle, Sybase, and DB2, Firebird, and SAP DB, among others. In this case, then an abstraction layer would be a good thing.

Actually, MySQL is the odd duck of the lot. It's more than a simple indexed file manager, such as Berkeley DBM, mSQL, and other lightweight data management kits, but it is not quite on the level of the enterprise DB systems, either, lacking much standardization and logical functionality. This alone makes it unwieldy for including in any true cross-DB application.

So, the question of database portability is central to the architecture of your application. Much more so than portability across operating systems, for example. It can't be pasted on as blank "data management" API, external to the logic of your application.

Yup rycamor is absolutely correct. Actually that's why I pretty much stay away from all purpose db abstraction layers, but for generalized apps like news scripts, discussion boards, etc. the all purpose db abstraction layers seem to do the job well making it easy and accesible for those installing the scripts.

-b

Yeah, there are those simple standardized apps where it really doesn't matter what you use for a database.

The shame of it all, though is that many PHP developers start off with MySQL, and then their apps grow and combine with other apps, and suddenly they are faced with the task of managing a system. This is what makes or breaks a developer, and this is where relying on simple storage mechanisms (treating the DB like a glorified spreadsheet) just isn't going to cut it.

I know, because I've been there . It wasn't pretty...