PHP - Moved: Reversing The Install Procedure

This topic has been moved to Third Party PHP Scripts.

http://www.phpfreaks.com/forums/index.php?topic=333987.0

 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);

 

Okay, Earlier on I asked someone if they could show me a shorter way of making letters one higher, Like A becomes B and B becomes C in a string.

I was wondering if using this code:


for($i=0, $n=strlen($text); $i<$n; ++$i)
{
$val = ord($text[$i]);
if(($val>=65 && $val<=90) || ($val>=97 && $val<=122))
{
$base = ($val<=90) ? 65 : 97;
$text[$i] = chr($base + ($val-$base+1)%26);
}
}


I could reverse it's effect and make A become Z and B become A in a string.

Thanks in advance!

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.

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.

Hi All,

I'm hitting brick walls while trying to run a stored procedure query with PDO (linking to MSSQL)

If I run the query from SSMS (SQL Server Management Studio) I get a result every time (one single row is returned, as expected) - I'm placing the same query into PHP without any dynamic variables etc - its just a straight query... when it runs the query in PDO it fails every time with this error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[IMSSP]: The active result for the query contains no fields.' in index.php:195
Stack trace:
#0 index.php(195): PDOStatement->fetch(2)
#1 {main}
  thrown in index.php on line 195

The code is as follows:

	$SQLSTMTA="exec pEmployeeGetData @EmployeeID='1',@Year='2020'";
	$STHA = $DBH2->prepare($SQLSTMTA);
	//$STH->bindParam(':EMPID', $EmployeeID);
	$STHA->execute();
		while($result = $STHA->fetchAll()) {              //(row 195)
		var_dump($result);
	}

I've also tried a few variations on the above such as ->Fetch (as opposed to FetchAll) without any luck.

Any ideas on how I can get around this?

Thanks in Advance!

 

Hi, I have an adverts table that has records of currently running adverts, i have not used stored procedures or triggers before. I would like to create a stored procedure or a trigger using phpmyadmin for mysql.   Exactly what i want to achieve is, when a record is accessed in adverts table, it causes a table called HITS to create a record that has the adverts_id, IP where the advert was clicked from, current date.   Is that possible?  

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

I'm using a CRUD system which won't automatically update the lft and rght values of my hierarchical tables when I enter a new row. I don't know what the best way to solve this is, all I can think of is adding a stored procedure to the table so that whenever a new row gets added, the procedure will make sure the lft and rght values get shifted. Is this even possible with stored procedures?

Hi Everyone,

This is Tamilmani Mohan , i have few doubts in SQL Stored Procedure.

I am able to run the SQL SP using odbc driver and also get the result set.

For Example: odbc_exec($connection,"exec get_Members");

I don't know how to run the input / output parameters SP's?

Example:-
If i run the below code by using SQL Management Visual Studio, I am able to insert to new record and get the member id and error code values.

declare @MemberId  int,@Error    int
     
EXEC INSERT_MEMBER 1,'Tamilmani','Mohan','Address1', 'Address2', 'Address3','Salem','Tamilnadu','India',9993234234 , @MemberId  OUTPUT ,  @Error    OUTPUT

SELECT @MemberId , @Error

I don't know how to run this type code in PHP ODBC ?

Kindly , please any one give me good suggestion for this problem ?

Thanks
Tamilmani Mohan

Hello All

Can someone help me with this:

I am trying to create some PHP code to pass 2 variables to a stored procedure.

At the moment I have:

<?php

// Connect to MSSQL and select the database

$serverName = "(local)";
$connectionInfo = array( "Database"=>"ashley");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/*Call the stored procedure with a named parameter*/
$tsql = 'EXEC CPS_ADD_NEW_USER @agentF = php,@agentS=test';
$stmt = sqlsrv_query($conn, $tsql);


/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);

sqlsrv_close( $conn);
?>

Could anyone advise me what I need to be doing and where I am going wrong

Thanks in advance

Bicky

Hello - I need a way to execute a stored procedure from a wordpress site with an external database where the stored procedure is housed. I am very limited in php code. All I know is from what little I have used My Custom Functions plugin. Please be very detailed. This stored procedure I run truncates and re-populates a number of tables I use for various reports. I am pulling data from the wp db and creating tables in the external db which make the data easier to read than through the wp meta data. The tables are housed in a separate db so as not to slow down the wp db. The hosting company does not allow 'Events' to run on the mySql db so I am left with figuring out how to create a cron job using a php file which would be the best. Aside from that, I thought a button on an admin page for execution would make it easier if I have to do it be hand. Thanks for your help.

Hi

Please take a look at my code snippet. I don't know how to get the number of rows returned from a MySQL stored procedure so that I can handle it accordingly. Any ideas please?

Thanks

i have been trying to install with no luck come to find otu my server does not support PDO i tried to use mysql_connect instead of new PDO.

however i get  some weird connection error   host '00.00.0 is not allowed to connect" then "call to function exec on a non object"

the lines are below.

  $sql = mysql_connect('mysql:dbname=' . $_POST['mysqldb'] . ';host=' . $_POST['mysqlhost'], $_POST['mysqluser'], $_POST['mysqlpass']);
        foreach($db_sql as $statement) {
            $sql->exec(str_replace('ajaxim_', $_POST['mysqlprefix'], $statement));