how to call or execute mySql script in php?

[Quattro]

Hi,

I need some help to know how to call or execute mySql script in php?
I know we can use the stored procedures but I dont have any practice with the same.

Thanks in advance.

[amadeo]

Actually its very simple:
just execute "call proc_name"

Using Stored procedure with mySQL and PHP
A stored procedure is simply a procedure that is stored on the database server. After the storing of the commands is done, the tasks can be performed or executed continuously, without being repeatedly sent to the server. his also helps in decreasing the traffic in the networks and also reduces the CPU load.
A stored procedure is simply some SQL statements
The syntax for using Stored Procedures is as follows:

CREATE

[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement

A simple example for MySQL Stored Procedure
To calculate the area of a circle with given radius R, the following commands can be given

delimiter //
create function Area (R double) returns double
deterministic
begin
declare A double;
set A = R * R * pi();
return A;
end
//
delimiter ;

And to call it from php code to display the area of a circle with radius 22cm,
<?
$rs_area = mysql_query(“select Area(22)”);
$area = mysql_result($rs_area,0,0);
echo “The area of the circle with radius 22cm is ”.$area.” sq.cm”;
?>

[GianCarlo]

Here is a quick tutorial for executing the PHP MySQL 5.0 stored procedure
http://www.joeyrivera.com/2009/using...ysqlmysqlipdo/

• A simple select statement
• Calling stored procedure passing IN variable and retrieve OUT variables - get_user
• Calling stored procedure with no parameters and returns a recordset - get_users

Below is the code to run all three tests with each of the database extensions:

<?php
// MYSQL
$mysql = mysql_connect('localhost', 'example', 'example');
mysql_select_db('test', $mysql);

print ‘<h3>MYSQL: simple select</h3>’;
$rs = mysql_query( ‘SELECT * FROM users;’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}

print ‘<h3>MYSQL: calling sp with out variables</h3>’;
$rs = mysql_query( ‘CALL get_user(1, @first, @last)’ );
$rs = mysql_query( ‘SELECT @first, @last’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}

print ‘<h3>MYSQL: calling sp returning a recordset - doesn\’t work</h3>’;
$rs = mysql_query( ‘CALL get_users()’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}

// MYSQLI
$mysqli = new mysqli(’localhost’, ‘example’, ‘example’, ‘test’);

print ‘<h3>MYSQLI: simple select</h3>’;
$rs = $mysqli->query( ‘SELECT * FROM users;’ );
while($row = $rs->fetch_object())
{
debug($row);
}

print ‘<h3>MYSQLI: calling sp with out variables</h3>’;
$rs = $mysqli->query( ‘CALL get_user(1, @first, @last)’ );
$rs = $mysqli->query( ‘SELECT @first, @last’ );
while($row = $rs->fetch_object())
{
debug($row);
}

print ‘<h3>MYSQLI: calling sp returning a recordset</h3>’;
$rs = $mysqli->query( ‘CALL get_users()’ );
while($row = $rs->fetch_object())
{
debug($row);
}

// PDO
$pdo = new PDO(’mysql:dbname=test;host=127.0.0.1′, ‘example’, ‘example’);

print ‘<h3>PDO: simple select</h3>’;
foreach($pdo->query( ‘SELECT * FROM users;’ ) as $row)
{
debug($row);
}

print ‘<h3>PDO: calling sp with out variables</h3>’;
$pdo->query( ‘CALL get_user(1, @first, @last)’ );
foreach($pdo->query( ‘SELECT @first, @last’ ) as $row)
{
debug($row);
}

print ‘<h3>PDO: calling sp returning a recordset</h3>’;
foreach($pdo->query( ‘CALL get_users()’ ) as $row)
{
debug($row);
}

function debug($o)
{
print ‘<pre>’;
print_r($o);
print ‘</pre>’;
}
?>

[afidelino]

The reason we use stored procedure:
1. They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used - the stored procedure remains consistent.
2. They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results.