Snippets Databases
Databases
MSSQL SERVER stored procedure from php
by: Cesar Correa
|
July 16, 2004
Version: 4.3.7
Type: Sample Code (HOWTO)
Category: Databases
License: Other
Description: hi, just a want to contribute with a little code for to call stored procedure from ms sql sever in php, using parameters and exporting the result query to Excel file. in spanish: hola solo queria contribuir con un poco de codigo que llama a un procedimiento almacenado de sql server desde php y exportando el resultado de la consulta a un archivo en Ms Excel. espero sea de mucha ayuda.
este es mi p.a en mi base de
datos.
CREATE PROCEDURE Ver_cliente
@Id nvarchar(30) output -- IMPORTANTE!!!
AS
select
[clientes].cCedula as Cedula,
[clientes].cNombres as Nombres,
[clientes].cApellidos as Apellidos,
[clientes].cDireccion as Direccion,
[clientes].desResidencia as "Ciudad Residencia",
--[clientes].cCiudadResidencia as "Codigo Ciudad",
[clientes].cTelefono as Telefono,
[clientes].cPase as Pase,
[clientes].siCategoria as Categoria,
[clientes].sdVencimiento as Vencimiento,
[clientes].cMovil as Movil,
[clientes].promedioCalificacion as Calificacion,
oficinas.cCiudad as Oficina,
[clientes].iCodigoFotografico as CodigoFotografico,
[clientes].bActivo as Activo,
--bAprobado as Aprobado,
[clientes].bRevisado as Revisado,
[clientes].deuda as Debe_$,
[clientes].CreadoPor as CreadoPor,
(usuarios.cNombres + ' ' + usuarios.cApellidos) as
"NombreCreadoPor"
from [clientes] inner join
oficinas on [clientes].ioficina = oficinas.iCodigo
inner join usuarios on usuarios.ccedula = clientes.CreadoPor
where [clientes].cCedula = @Id
GO
**********************************************
this my code in php , taked from others examples ;-)
<?php
$file_type = "vnd.ms-excel";
$file_ending = "xls";
//}
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment;
filename=file_name.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
//get contents
//puede ser la fecha actual
$now_date = date('d-m-Y H:i');
$title = "Listado Personas $now_date";
$sql = "Select * from clientes";
$valor = 8400038; //id from client
/*
Database Connection (Alternative- for mysql_fetch_array) */
$conex =
mssql_connect("yourserver","sa","123");
mssql_select_db("yourdatabase");
$proc = mssql_init("Ver_Cliente", $conex) or die("Hay
problemas para conectar a Stored Procedure");
mssql_bind($proc, "@Id", &$valor, SQLVARCHAR, true) or
die("Hay problemas para crear bind");/*parametro &$valor
pasado por referencia. true = is_output, like the declaration in the
procedure code above */
$result=mssql_execute($proc) or die ("Error sacando el
procedimiento almacenado");
//end of connection code
//define separator (defines columns in excel)
$sep = "\t";
//print excel header with timestamp:
echo("$title\n");
//start of printing column names
for ($i = 0; $i < mssql_num_fields($result); $i++) {
echo mssql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
//start while loop to get data
/*
note: the following while-loop was taken from phpMyAdmin 2.1.0.
--from the file "lib.inc.php".
*/
$i = 0;
while($row = mssql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mssql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$",
"", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
$i++;
}
return (true);
?>
it's all folks,
*****************************
Cesar Correa from Bello, Medellin, Colombia
PHPBuilder
Policies
PHP Developer
Topics
PHP Developer
Resources
PHP Developer
References
