PHP - Stored Procedure Or Trigger Between Two Tables

 wrote a stored procedure this morning and i don’t know how to get the values out of it through a class function in php or phpmyadmin.

here is what i wrote :

public function totalProcedures($friend_name,$session_id) 
{ /* *query to fetch stored procedure */ 
try { 
//executing the stored procedure 
$sql_sp="CALL timeline (:friend, :session,@updates, @group_posts)";
$stmt_sp= $this->_db->prepare($sql_sp); 
$stmt_sp->bindValue(":friend",$friend_name);
$stmt_sp->bindValue(":session",$session_id); 
$stmt_sp->execute();
$rows=$stmt_sp->fetch(PDO::FETCH_ASSOC); 
$stmt_sp->closeCursor(); // closing the stored procedure 
//trying to get values from OUT parameters.
$stmt_sp_2=$this->_db->prepare("select @updates,@group_posts");
$stmt_sp_2->execute();
return $stmt_sp_2->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $ei)
{
echo $ei->getMessage(); } 
}

can someone helpme how to get results.

here is the storedprocedu

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `timeline`(IN `friend` VARCHAR(255), IN `session_id` VARCHAR(255), OUT `updates` VARCHAR(62555), OUT `group_posts` VARCHAR(62555))
BEGIN


select * FROM updates where author in (friend,session_id)  order by time desc limit 5;



select * FROM group_posts where author_gp in (friend,session_id) order by pdate desc limit 5;
END$$
DELIMITER ;

i get the result in php myadmin as follows:

 how do i do this inside a php class function.

CALL timeline('shan2batman','aboutthecreator', @updates, @group_posts);

 

Hi Everyone,

I have this php page that runs couple of MS SQL stored procedures to fetch me values from SQL Server, however I have run into a problem where not more then 2 stored procedures are executed at same time. For instance below mentioned code only fetches me values for 1st and 2nd SP's. If I need values from the 3rd stored procedure then I would have to comment either one of the first two stored procedure.   Can someone please tell me what is that I am doing wrong.   I am relatively new to PHP, so please be gentle. Any help would be highly appreciated.

Thanks.

<?php


if($conn = mssql_connect('host', 'user', 'pass')) echo 'Connected to SQLEXPRESS<BR>';
if(mssql_select_db("database",$conn)) echo 'Selected DB: SomeDatabase<BR>';

$variable1=something;

$sql_statement =  mssql_init("stored_procedure1 '$variable1'", $conn);
$result=mssql_execute($sql_statement);

$x=0;

while ($row = mssql_fetch_assoc($result))
    {
    $column2[$x]= $row['column2'];
    $column1= $row['column1'];
    
$x++; }

    
echo "Value 1: $column1      </br>";
echo "Value 2: $column2[0]  </br>";
echo "Value 3: $column2[1]  </br>";
echo "Value 4: $column2[2]  </br>";
echo "Value 5: $column2[3]  </br>";
echo "Value 6: $column2[4]  </br>";
echo "Value 7: $column2[5]  </br>";


mssql_free_statement($sql_statement);



$sql_statement =  mssql_init("stored_procedure2 '$variable1'", $conn);
$result=mssql_execute($sql_statement);


$x=0;

while ($row = mssql_fetch_assoc($result))
    {
    $someval1[$x]= $row['somecolumn1'];
    $someval2[$x]= $row['somecolumn2'];
    $someval3= $row['somecolumn3'];
    $someval4= $row['somecolumn4'];
    $someval5= $row['somecolumn5'];
    $someval6= $row['somecolumn6'];

$x++; }

    
echo "Something1: $someval1[0]</br>";
echo "Something2: $someval3 </br>";
echo "Something3: $someval2[0] </br>";
echo "Something4: $someval4 </br>";
echo "Something5: $someval6 </br>";
echo "Something6: $someval5 </br>";


mssql_free_statement($sql_statement);


$sql_statement =  mssql_init("stored_procedure3 '$variable1'", $conn);
$result=mssql_execute($sql_statement);


$x=0;

while ($row = mssql_fetch_assoc($result))
    {
    $somevaluecheck= $row['somecolumncheck'];

$x++; }
    
echo "SomethingCheck: $somevaluecheck </br>";


mssql_free_statement($sql_statement);


?>

I am trying to make a  setup file for a website I'm developing, and am having the database tables and stored procedures created automatically.  However, I'm having issue getting the stored procedures to be created.  Here is my code:
Code: [Select]
$db->Q("
DELIMITER $$
CREATE PROCEDURE `Database`.`Procedure_Name`(in sp_uid mediumint(20) unsigned, in sp_user varchar(15), in sp_pass varchar(20), in sp_email varchar(30))
BEGIN
Insert Into Table Values (sp_uid, sp_user, md5(sp_pass), sp_email, '1', '1', '0');
Select true;
END$$
");
Assume that the "$db->Q()" works just fine, as I'm having issues no where else with it.  This automatically connects to the database and runs a query with whatever is inside the "()".  The tables are being created just fine, but no stored procedures are being created.  I've tried everything I can think of, and googled my question many different ways without finding an answer or work-through.  Does anyone know what I am doing wrong?

Thanks in advance.

i have made a logon script but he must give a output parameter "relatieid" but if i do print relatieid i don't get the output parameter. what can i do to get the output parameter. i must give -1 if your logon data don't be good and your relatie id if you are succesfull logon. i saw that you can use fatchall but how

my script is:
php
Code: [Select]
<?php 
session_start(); 
$sessieid = session_id(); 
error_reporting(-1); 
ini_set('display_errors', 1); 

if($_SERVER['REQUEST_METHOD'] == 'POST') 
{ 
     $username = $_POST['username']; 
     $wachtwoord = $_POST['wachtwoord']; 
     $ip = $_SERVER["REMOTE_ADDR"]; 
     $computernaam = php_uname('n'); 

     if(trim($username) == '') 
     { 
        echo "<font color='red'>Vul geldige gebruikersnaam in!</font><br>"; 
        header("refresh:5;url=/login/"); 
    exit() ; 
    }     
     
     if(trim($wachtwoord) == '') 
     { 
        echo "<font color='red'>Vul geldige wachtwoord in!</font><br>"; 
        header("refresh:5;url=/login/"); 
    exit() ; 
    } 
      
      
    $db = new PDO('mssql:host=localhost\snelstart;dbname=SluisWWW','test','********'); 
     
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
     
    $stmt = $db->prepare("EXECUTE spMagInvoeren ?,?,?,?,?,?,?"); 
      
    $stmt->bindValue(1 ,$username, PDO::PARAM_STR); 
    $stmt->bindValue(2 ,$wachtwoord); 
    $stmt->bindValue(3 ,$ip); 
    $stmt->bindValue(4 ,$computernaam); 
    $stmt->bindValue(5 ,$sessieid); 
    $stmt->bindParam(6 ,$poging); 
    $stmt->bindParam(7 ,$relatieid); 

    $stmt->execute(); 

print "<br/>Returned: $relatieid<br/><br/>\r\n"; 





    { 
        setcookie("TestCookie", $username); 
        // redirecten 
        exit(); 
    } 
     
     
} 
stored procedure ms sql server
Code: [Select]
USE [SluisWWW]
GO
/****** Object:  StoredProcedure [dbo].[spMagInvoeren]    Script Date: 04/04/2012 09:54:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Henk Boessenkool
-- Create date: 23 Maart 2012
-- Description: test login
-- =============================================
ALTER PROCEDURE [dbo].[spMagInvoeren]
-- Add the parameters for the stored procedure here
@Usernaam nVarchar(20) ,
@Wachtwoord nvarchar(20),
@IPAdres nvarchar(20),
@Computer nvarchar (20),
@SessieID nvarchar(50),
@PogingenOver integer  OUTPUT,
@RelatieNummer integer OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @Success INT
SET NOCOUNT ON;
    SET @Success = (SELECT COUNT(*)
        FROM contactpersonen
        WHERE (username = @Usernaam AND wachtwoord = @Wachtwoord))
    IF @Success = 1
    BEGIN
       SET @RelatieNummer = (SELECT TOP 1 relatie_id FROM [SluisWWW].[dbo].[contactpersonen]
                             WHERE (username = @Usernaam AND wachtwoord = @Wachtwoord))
       
    END
    ELSE SET @RelatieNummer = -1
    INSERT INTO [SluisWWW].[dbo].[Logins] (Login,Wachtwoord,RelatieID,IP,Computer,SessieID)
                VALUES (@Usernaam,@Wachtwoord,@RelatieNummer,@IPAdres,@Computer,@SessieID)
               
                set @PogingenOver = 24
 
   
   
   

 
    -- Insert statements for procedure here
END

Hi all,     This is just an experiment to make sure that I can get data from a Stored Proc 'IF' and when I need to, so it's basically a THOUGHT EXERCISE.   MSSQL 2014     I'm a noobie and need a hand, I've managed to get my Instance connected to the internet and I can query it using PHP and SQL, I can also look at views with no problem.     I have it working as an "ADODB.Connection" and like I said it connects and I can query data and display results.     Now I have coded a Stored Proc "GetMonthDays" in Sql Server:   Which returns days 1 through xxx in a given month and also returns the Day name  eg... Sat for each date     2014-01-01 Thurs   2014-01-02 Fri   etc...     It works perfectly and very fast so All cool with that side BUT...     I want to be able to query the Database through a Stored Proc, I've spent all day trying to find a way to get this to work and I've hit a wall   Can anyone please tell me IF it's A. possible and B. maybe if they are really really nice and share with me a code example that I can change to fit my needs ?     This T-SQL returns what it needs to     Begin
  EXEC dbo.qselGetMonthDays '2015-01-01'
  End
      Cheers     JD
Edited by juddadredd, 02 January 2015 - 04:55 PM.