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 ...
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 ...
UPDATE emp ...
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).