Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

PHP and Oracle for an Idiot

I've worked with MySQL and T-SQL, but I'm brand new to Oracle.

While I'm working through some books and tutorials, I wanted to put it out here on Experts Exchange and ask for some "high altitude" perspective.

I'm looking at sites like this: http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm and I'm seeing differences in terms of features, but how do things differ in terms of:

- connection to the database
- queries / code that retrieves info from the database

Not trying to minimize the obvious amount of study and work that will be needed to make this work, but I was hoping for some "bullets" to guide me in my quest.

Thoughts?
0
brucegust
Asked:
brucegust
3 Solutions
 
Ray PaseurCommented:
Probably the most important thing you can know is contained in the prominent red warning box on this page:
http://www.php.net/manual/en/ref.pdo-oci.php

After that, it's just like any other of the PHP database extensions.  There are examples with the function documentation.
http://www.php.net/manual/en/book.oci8.php
0
 
Dave BaldwinFixer of ProblemsCommented:
Using the OCI8 extension http://php.net/manual/en/book.oci8.php , I was able to easily make the connections and queries to the 'free' developers version of Oracle.  However... setting up the users and databases privileges was Quite different and took some time to figure out.  That part is not like MySQL.
0
 
PortletPaulCommented:
Dave's point about security is a really good one.

Regarding "queries / code that retrieves info from the database" here are some things I can think of:

Case sensitivity. By default Oracle is case sensitive.

"FROM DUAL"
  you can't just do "select 1" you MUST use a from clause and there's a special (oddly named) pseudo table "dual"
  "select 1 from dual"

NULL handling
   IFNULL(MySQL,'')
   ISNULL(MSSQL,'')
   NVL(Oracle,'')
   
   or, just use COALESCE(anydb,'')

string functions, these differ
   e.g. CONCAT() only handles 2 parameters,
    and unlike TSQL you can concatenate without cast to varchar first, and you use double pipes for concatenation

     select 'concatenation' || ' differs in more ways than ' || 1 from dual

    LISTAGG() is similar to GROUP_CONCAT()

date/time handling e.g.
   select SYSDATE from dual
   select TRUNC(SYSDATE) from dual -- equivalent to CURDATE()
   to_date('2014-04-21','YYYY-MM-DD')
   to_char(date_field_here,'YYYY-MM-DD')

   &  note that the timestamp data type is more complex to use than the date data type
   
useful function references:
     http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21642/function.htm#TTSQL446
     http://www.techonthenet.com/oracle/functions/index.php

PL/SQL
   The procedural language differs in many ways.

DDL
   There are some syntax differences but they aren't extensive.
   BUT you will have to get used to UPPERCASE table and field names in the "dictionary" e.g.

   select * from user_tab_columns where table_name = 'YOUR_TABLE'
 

It isn't that hard & Oracle is a damn fine product.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now