What's the best database to use with php in this situation???

[bcarl314]

Well,

I've finally (after 2 + years) been able to get php as a web development language at my job, and have only one small problem. They will not let me use mySQL. Too "experimantal" I guess.

Anyway, they gave me the option of using either Oracle (no idea how to use) or MS SQL Server.

I've looked at the php docs on both and it looks like the syntax for MSSQL is almost identical to the syntax for MYSQL. (ie mysql_connect -> mssql_connect, mysql_query->mssql_query, mysql_fetch_assoc ->mssql_fetch_assoc). It's so close in most cases, it would seem like I could just do a global search and replace for mysql with mssql.

However, I do have some problems. I've never used either of those DB servers. So, I've got a few questions.

1) Does MS SQL have a "last_insert_id" function? in the past, I've set up tables with auto_increment fields that reference fields in other tables. Like

table threads(
threadID int(11) auto_increment,
threadTitle varchar(50)
)

table logs(
logID int(11) auto_increment,
threadID int(11),
logText text
)

When a use submits a new thread, it creates the thread first, uses the last insert id to get the threadID and then inserts the log. How would I do this with MS SQL Server?

2) Does anyone know of any tutorials on php with either of these DB servers? (Oracle or MS SQL?)

3) Which would you recommend? I'm leaning towards SQL Server at the moment, just because the syntax seems easier to learn, and I've heard SQL Server's alot like Access and I have very little experience with Oracle (although I have played around with thier personal edition)

Thanks for you help.

[piz]

Hi,
I don't think i'll be able to help you very much, because I haven't use neither Oracle nor MsSQL with php, neither.

There are some Benchmark-Resulsts on the MySQL Site:
http://www.mysql.de/information/benchmarks.html

Oracle seems to be the only DB Server which has a performance similar to MySQL.

...but why not MySQL? Its not bad, powerful and free...
Ok, I think MS SQL Server and Oracle do have a better security management, don't know.

Greetz
Paul

[allida77]

1)Does that function automatically insert into the other table? If so you can use triggers to where if a row is insert into table a it will automatically insert into table b.

2)No

3)I am currently doing a pretty large project with .net and sql server right now. I had never used ms sql before this and it was very easy to learn. I use sprocs, transactions, and other built in functions of the server pretty heavily.


Recommending one over the other depends on how many clients you will have, $$, and simultaneous connections. I have never used oracle so I can not recommend one over the other. You can grab a evaluation version of MS SQL server if you would like (pretty large dl).

http://www.microsoft.com/downloads/details.aspx?FamilyID=D20BA6E1-F44C-4781-A6BB-F60E02DC1335&displaylang=en

[Bawy]

Oracle is always the DB of choice if it is in budget, IMO. The barrier with Oracle is the rather large learning curve. Auto_increment and last_insert_ID is replaced with using sequences and PL/SQL, that is what will need changing in the little SQL code you posted. Oracle works quite differently then the other DBs and if you know how to use it, much faster. The benchmark link is inaccurate because they are attempting to compare apples to apples, but Oracle has oranges that MySQL does not and they are just sitting rotting.

For instance, 100,000 straight SQL inserts will be faster in MySQL; but if you use the bulk loader in Oracle, it will leave MySQL in the dust. ODBC connect is slower to connect to Oracle then to MySQL, but you can connect Oracle natively through OCI from PHP and many other apps which makes it much faster.

If you know how to use Oracle, it can be made significantly more efficient then the alternatives. Some of that applies to MSSQL Server, but not most of it.

[bcarl314]

Hmm, never used triggers before. Maybe I'll have to check them out.

I found a way to recode the program. Basically, I insert into the threads table then do a MAX(threadID) where userName="So and so", then use that result in my 2nd insert.

I figured this should work and not cause any concurrancy issues, as long as the same user isn't logged in at 2 places and doesn't submit multiple logs within 1/10th of a second or so.

As far as money, there is no budget other than time (3 months) so I'm good there, and we have multiple oracle and SQL server licenses.

Expected simultaneous users should be less than 10 at any given moment, possible maxing out around 25 - 30. Unless this is nationally adopted, at which point, it could be 100 - 150 users. Not too heavy though.

Thanks for the input

[Spookster]

I've worked with both MSSQL and Oracle but neither with PHP. MSSQL has an much shorter learning curve obviously and it is almost identical to Access. It comes with an easy to use GUI much like Access. Many of its features work like Access. Performance-wise as Bawy pointed out it is like the tortoise and the hare race between Oracle. Oracle handles lots of datavery quickly when set up correctly. At work we put Oracle to heavy use in our databases. Most of them being a few hundred gigabytes and one being around 2 terabytes. One coworker is in the situation of having to do a join between 17 tables with each table having at least20 columns and in each of those joins a sub select is being peformed. It is really quite insane but Oracle runs it in a relatively fast amount of time.

I run queries all the time selecting a half million records in just under a minute.

So basically it comes down to this....If you need something heavy duty and fast. but with a more difficult learning curve...go with Oracle. If you need to something that can handle most anything you will probably need...has a familiar looking GUI and a shorter learning curve(assuming you have Access experience) then go with MSSQL.

[Bawy]

Another considation is amount of data. MySQL seems to have a performance curve that dips drastically upon accessing a table of over half-a-million records. Maybe it can be resolved via setup, I have never had any luck with it. I beleive it may have to do with the file based storage. Oracle has a bunch of options to handle high record counts, including partitioning a table based on data criteria (like an index, except it partitions instead of just creating an association of data-to-row) and even special de-formatted of a partition on the HD to place a single Oracle datafile for super speedy access.

MSSQL Server is definitely easier, but has it's complications as well, especially when utilizing T-SQL, which is the main point IMO.