Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: Stored procedure query with php

    how do you query mysql stored procedure with php and display the results?... And is it faster to always create a stored procedure for all queries and call it with php?... or a stored procedure should only be created for some queries?... please help ...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wonder what google would say?
    php mysql stored procedure - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    To call a MySQL stored procedure from within PHP you simply run the following code:

    $result = mysql_query("CALL storedProcedure()");

    In respect of efficiency it is more efficient to have stored procedures. The level of efficiency is dependent on your hardware setup. I know some web hosting companies that separate their database server domain and the web server domain. As a result, all PHP processing connecting to a database routes all requests over their internal network. As such every request from PHP needs to be sent across the network and responses sent back. This can add to the overall processing time depending on the network response time and throughput. For example let's imagine you want to either insert a new employee based on a name (which should be unique) or update. In your PHP code you would execute the following:

    SELECT count(*) INTO count FROM emp WHERE name = 'test';
    IF count = 0 THEN
    INSERT INTO ...
    ELSE
    UPDATE emp ...
    END IF

    In this case 2 trips are made to the server the first to determine whether the entry already exists and based on the result either INSERT or UPDATE.

    Using stored procedures in this case will definitely speed up the processing time as only one request is made to the database and all processing happens in memory on the database server.

    If the web server and the database server both exist on the same machine, the lag between sending a request and responding to the request is far less (almost insignificant).
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice explanation, ronan

    unfortunately your example is not the best

    instead of this --
    Quote Originally Posted by it-iss.com View Post
    SELECT count(*) INTO count FROM emp WHERE name = 'test';
    IF count = 0 THEN
    INSERT INTO ...
    ELSE
    UPDATE emp ...
    END IF
    you can use a single INSERT... ON DUPLICATE KEY UPDATE statement

    otherwise, your advice regarding avoiding multiple calls to the database is correct

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Thanks Rudy, I was trying to provide a very simple example and indeed ON DUPLICATE KEY could have satisfied this example.

    Also using stored procedures is even more efficient because it does not have to parse the SQL statements inside as these are usually cached and primed for execution.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •