Breathing Spring

Stored Procedure and Fetching Result in PHP

ok I assume you know SQL server here now to create a procedure you to to

Stored Procedure-> New Stored Procedure

CREATE procedure dbo.NAME_OF_THE_PROCEDURE
@id int,
@name varchar(250)
As
select * dbo.TABLE_NAME
where
id=@id AND name like @name;
GO

the first two variables refer to the data we will send to the procedure, this will also work on insert.
to fetch the result in PHP first we need to connect PHP to SQL SERVER

$settings;
$db_host = "127.0.0.1"; //THE SQL SERVER IP so you can connect
$ser = "127.0.0.1"; //THE SQL SERVER IP so you can connect
$db_name = "DBNAME"; //DATABASE NAME TO CONNECT
$settings['db_user'] ="username_for_sql";
$settings['db_pass'] = "pass_for_sql";
$settings['dsn'] = "DRIVER={SQL SERVER};" .
"Server=$ser;".
"CommLinks=tcpip(Host=$db_host);" .
"DatabaseName=$db_name;" .
"uid=".$settings['db_user']."; pwd=".$settings['db_pass']."";
$connection=odbc_connect($settings['dsn'], $settings['db_user'] , $settings['db_pass'] ,SQL_CUR_USE_ODBC);


Now finally fetching the Select result (I added how to select Database though its not necessary and only needed if you are switching to another database at the same connection

odbc_exec($connection,"use other_db_name"); //only needed if you are switching database
$query="exec NAME_OF_THE_PROCEDURE 23,'name'"; //23 is the ID, and 'name' is the name
$result=odbc_exec($connection,$query); //result will return the one value fetched
while ($row= odbc_fetch_array($result))
{
$i++;
echo $row['anything1'].$row['someotherfeild'];
}

This entry was posted on Sunday, March 8th, 2009 at 12:37 pm and is filed under SQL SERVER. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.