Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 03-07-2009
Member
 
Join Date: May 2008
Posts: 255
how to call or execute mySql script in php?

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.
Reply With Quote
  #2  
Old 03-07-2009
Member
 
Join Date: May 2008
Posts: 38
Re: how to call or execute mySql script in php?

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:

Code:
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

Code:
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?;
?>
Reply With Quote
  #3  
Old 03-07-2009
Member
 
Join Date: May 2008
Posts: 43
Re: how to call or execute mySql script in php?

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:
Code:
<?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>?;
}
?>
Reply With Quote
  #4  
Old 03-07-2009
Member
 
Join Date: Apr 2008
Posts: 242
Re: how to call or execute mySql script in php?

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "how to call or execute mySql script in php?"
Thread Thread Starter Forum Replies Last Post
PHP - print string and execute next script Gillian Anderson Software Development 6 14-05-2010 10:14 AM
Can not execute ASP script on IIS7 in Windows 7 Mindstorm Software Development 6 16-01-2010 10:03 AM
IIS - The remote procedure call failed and did not execute Triple-X Networking & Security 3 05-09-2009 07:00 AM
Is it possible to execute Perl script within another script? RasMus Software Development 2 21-07-2009 10:57 PM
Need Perl script to execute from another. beelow Software Development 2 20-06-2009 09:17 AM


All times are GMT +5.5. The time now is 02:29 AM.