PHP - Stored Procedure For Updating Nested Hierarchy

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!

 

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 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 - 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.

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

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

having a problem importing a file having multiple hierarchy levels!! its a csv file the contents look something like this! just need a jump start! thanks
the file looks likes this

row 1     row2   row3
name1   subj1   abcd
                          efgh

            subje2   test1
                          test2

the file has to first import name1 then subj1 then abcd,then efgh then move back to subj2 and so on!

Thanks

Hi guys,

I'm trying to build an array to replicate the hierarchy in a menu:

<ul>
<li><a href="file1.html">text1</a></li>

<ul>
<li><a href="file2.html">text2</a></li>
<li><a href="file3.html">text3</a></li>
<li><a href="file4.html">text4</a></li>
<li><a href="file5.html">text5</a></li>
</ul>

</ul>

An i would like the output to be:

"text1"
"text1", "text2"
"text1", "text3"
"text1", "text4"
"text1", "text5"
 

Here is my loop to go through the html hierarchy:

        foreach ($html2->find('ul') as $ul) {
            foreach ($ul->find('li') as $li) {
                foreach($li->find('a') as $a) { 
                    // need to filter out empty and index.html, tried if(!$->href = 'index.html) {do stuff} but didn't work
                    $links2[] = $a->href ;
                    $taxo2[] = $a->plaintext ;                   
                }
            }
        }

This finds all the links but not the hierarchy, any ideas how to approach this? And also how to filter out blanks and references to index.html?

Im new to PHP and have had success with much help from the community and am greatly thankful for all input.

I have a pretty good understanding of what needs to be done, but actually implementing and accomplishing is difficult for me.

Im trying to break down hierarchy from an existing HTML list.
First Id like to point out that I am working with an HTML file that I must use, and cannot change, as the source.

I am rewriting onto another file, removing HTML, and so forth.  I have already accomplished this.  But what I would really like to keep the hierarchy structure by breaking it down first, so I can also use that later.

Example HTML list
Code: [Select]
<ul><li>modem, UP</li>
<ul><li>wifiRouter, UP</li>
<ul><li>PC1, DOWN</li>
<ul></ul> <!-- Open-close of UL identifies end -->
</ul> <!-- Up one level -->
<ul><li>wiredRouter, UP</li>
<ul><li>server, UP</li>
<ul><li>dnsServer, UP</li>
<ul></ul> <!-- Open-close of UL identifies end -->
<ul><li>webServer, UP</li>
<ul></ul> <!-- Open-close of UL identifies end -->
</ul> <!-- Up one level -->
<ul><li>PC2, UP</li>
<ul></ul> <!-- Open-close of UL identifies end -->
</ul> <!-- Up one level -->
</ul> <!-- Up one level -->
<ul><li>modemBackup, DOWN</li>
<ul></ul> <!-- Open-close of UL identifies end -->
</ul>

Desired Output:
Code: [Select]
1, 0, 0, 0, modem, UP
1, 1, 0, 0, wifiRouter, UP
1, 1, 1, 0, PC1, DOWN
1, 2, 0, 0, wiredModem, UP
1, 2, 1, 0, server, UP
1, 2, 1, 1, dnsServer, UP
1, 2, 1, 2, webServer, UP
1, 2, 2, 0, PC2, UP
2, 0, 0, 0, modemBackup, DOWN

If im logically looking at this right
(1) Count <ul> entry and continue counting for each group
(2) Stop group count at end argument <ul></ul>
(3) The immediate next end argument </ul>, following (2), says to continue counting the previous group +1
(4) Repeat steps 1-3 until end

Any advise?

Hello,

I'm not able to figure how to fix these warnings / errors I'm getting. The file that I'm creating comes out correctly so it's as if I need some type of conditional to end the function calls or something? I'm really stumped with this one...

Code: [Select]
Undefined index:  1
Invalid argument supplied for foreach()
Undefined index:  4
Invalid argument supplied for foreach()
Undefined index:  7
Invalid argument supplied for foreach()
Undefined index:  21
Invalid argument supplied for foreach()
Undefined index:  2
Invalid argument supplied for foreach()
Undefined index:  11
Invalid argument supplied for foreach()
Undefined index:  6
Invalid argument supplied for foreach()
Undefined index:  22
Invalid argument supplied for foreach()
Undefined index:  24
Invalid argument supplied for foreach()

Here is a var_dump of $menu

Code: [Select]
array(2) { ["items"]=> array(12) { [1]=> object(stdClass)#17 (10) { ["id"]=> string(1) "1" ["name"]=> string(4) "HOME" ["parent"]=> string(1) "0" ["type"]=> string(9) "slideshow" ["order"]=> string(1) "1" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [5]=> object(stdClass)#18 (10) { ["id"]=> string(1) "5" ["name"]=> string(4) "INFO" ["parent"]=> string(1) "0" ["type"]=> string(6) "parent" ["order"]=> string(1) "2" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [3]=> object(stdClass)#19 (10) { ["id"]=> string(1) "3" ["name"]=> string(9) "GALLERIES" ["parent"]=> string(1) "0" ["type"]=> string(6) "parent" ["order"]=> string(1) "3" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [18]=> object(stdClass)#20 (10) { ["id"]=> string(2) "18" ["name"]=> string(16) "GALLERY SECTIONS" ["parent"]=> string(1) "0" ["type"]=> string(14) "gallery_portal" ["order"]=> string(1) "4" ["visible"]=> string(4) "true" ["image"]=> string(11) "DSC0250.jpg" ["color"]=> string(6) "848a5a" ["link"]=> NULL ["target"]=> NULL } [22]=> object(stdClass)#21 (10) { ["id"]=> string(2) "22" ["name"]=> string(7) "CONTACT" ["parent"]=> string(1) "0" ["type"]=> string(5) "forms" ["order"]=> string(1) "5" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [24]=> object(stdClass)#22 (10) { ["id"]=> string(2) "24" ["name"]=> string(12) "MAILING LIST" ["parent"]=> string(1) "0" ["type"]=> string(5) "forms" ["order"]=> string(1) "6" ["visible"]=> string(5) "false" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [2]=> object(stdClass)#23 (10) { ["id"]=> string(1) "2" ["name"]=> string(7) "WEDDING" ["parent"]=> string(1) "3" ["type"]=> string(7) "gallery" ["order"]=> string(1) "1" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [4]=> object(stdClass)#24 (10) { ["id"]=> string(1) "4" ["name"]=> string(8) "ABOUT US" ["parent"]=> string(1) "5" ["type"]=> string(9) "text_page" ["order"]=> string(1) "1" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [7]=> object(stdClass)#25 (10) { ["id"]=> string(1) "7" ["name"]=> string(8) "CALENDAR" ["parent"]=> string(1) "5" ["type"]=> string(8) "calendar" ["order"]=> string(1) "2" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [21]=> object(stdClass)#26 (10) { ["id"]=> string(2) "21" ["name"]=> string(14) "PORTFOLIOSITEZ" ["parent"]=> string(1) "5" ["type"]=> string(8) "external" ["order"]=> string(1) "3" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> string(25) "http://portfoliositez.com" ["target"]=> string(6) "_blank" } [11]=> object(stdClass)#27 (10) { ["id"]=> string(2) "11" ["name"]=> string(9) "LANDSCAPE" ["parent"]=> string(2) "18" ["type"]=> string(7) "gallery" ["order"]=> string(1) "1" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } [6]=> object(stdClass)#28 (10) { ["id"]=> string(1) "6" ["name"]=> string(9) "PORTRAITS" ["parent"]=> string(2) "18" ["type"]=> string(7) "gallery" ["order"]=> string(1) "2" ["visible"]=> string(4) "true" ["image"]=> NULL ["color"]=> NULL ["link"]=> NULL ["target"]=> NULL } } ["parents"]=> array(4) { [0]=> array(6) { [0]=> string(1) "1" [1]=> string(1) "5" [2]=> string(1) "3" [3]=> string(2) "18" [4]=> string(2) "22" [5]=> string(2) "24" } [3]=> array(1) { [0]=> string(1) "2" } [5]=> array(3) { [0]=> string(1) "4" [1]=> string(1) "7" [2]=> string(2) "21" } [18]=> array(2) { [0]=> string(2) "11" [1]=> string(1) "6" } } }

And here is the code

Code: [Select]
public function sitemap()
{
$query = $this->db->order_by('parent', 'asc')->order_by('order', 'asc')->get('menu');

foreach($query->result() as $row)
{
$menu['items'][$row->id] = $row;
$menu['parents'][$row->parent][] = $row->id;
}

function build_map($parent, $menu, &$data)
{
foreach($menu['parents'][$parent] as $itemId)
{
if(!isset($menu['parents'][$itemId]))
{
if(($menu['items'][$itemId]->parent == 0) && ($menu['items'][$itemId]->visible != 'false'))
{
$name = str_replace(' ','-', $menu['items'][$itemId]->name);
$data .= "\t<url><loc>" . base_url() . "#!/". $name ."</loc></url>\n";
}
elseif($menu['items'][$itemId]->visible != 'false' && $menu['items'][$itemId]->type !='external' && $menu['items'][$itemId]->type != 'parent')
{
$name   = str_replace(' ','-', $menu['items'][$itemId]->name);
$name2 = str_replace(' ','-', $menu['items'][$menu['items'][$itemId]->parent]->name);
$data .= "\t<url><loc>" . base_url() . "#!/". $name2 ."/" . $name ."</loc></url>\n";
}
}

if(isset($menu['parents'][$itemId]) && $menu['items'][$itemId]->visible != 'false' && $menu['items'][$itemId]->type != 'parent' && $menu['items'][$itemId]->type !='external')
{
$name = str_replace(' ','-', $menu['items'][$itemId]->name);
$data .= "\t<url><loc>" . base_url() . "#!/". $name ."</loc></url>\n";
}

build_map($itemId, $menu, $data);
}
}

$data = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">\n\t<url><loc>".base_url()."</loc></url>\n";

build_map(0, $menu, $data);

$data .= "</urlset>";

write_file('sitemap.xml', $data);
}

This topic has been moved to PHP Applications.

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

Often times I need to convert between PHP Arrays, MySQL, XML, JSON sqlite etc. Lately I've been just building PHP arrays with all the data. Its a big hassle though when theres a lot of data. I'm thinking I should have one central location that I store data in, then export to other formats from there, but I dont know what that central location should be. I like MySQL because I'm used to it. Sometimes its not an option though, like if I need to make a really light script, I dont want the hassle of connecting to MySQL. Would XML be the best place to store data, then from the XML file I can convert it to whatever format I want?

I'm working on a project and cannot figure out why one piece of code works in one project but not in this one.

Code: [Select]
$username = "voltageme5";
$result = $mysqli->query("call usernameCheck($username)");

returns
string(43) "Unknown column 'voltageme5' in 'field list'"

Here is the stored routine:

Code: [Select]
CREATE PROCEDURE `database`.`table` (IN memUsername varchar(45))
BEGIN
select count(*) as total from members where username = memUsername;
END

Whats weird is that in the PHP code if i replace the variable with a string, it works. It's only with the variable in the call that it errors out like that. I'm new to this stored procedure thing so I'm sure I'm missing something stupid.

The path to a image is stored in a variable and I need to display this image in the email. Can someone help me.

This is my current attempt and nothing is happening. But the variable passes the URL correctly.

Code: [Select]
<TABLE BORDER='0'>
<TR>
<TD align='left' width='370'><img src='".$dis_logoimg."' width='370' height='86' /></TD>
</TR>
</TABLE>

I am writting a php function that uses mysql to get user data - pretty common, right

Well, my issue is that I need to run a check in my file system.  Users profile pictures are stored in my image directory as .png's.  I need to have my function check that directory and if an image matches their id, then return their information.  I only want the user data if they have an image uploaded.

Here is my current function:

Code: [Select]
function fetch_users_login($limit)
{
$limit = $limit(int);

$sql = "SELECT
                `users`.`id`,
                `users`.`firstname`,
                `users`.`lastname`,
                `users`.`username`,
`user_privacy`.`avatar`
            FROM `users`
LEFT JOIN `user_privacy`
            ON `users`.`id` = `user_privacy`.`uid`
WHERE `users`.`status` > 2
AND `user_privacy`.`avatar` = 1
ORDER BY `users`.`id` DESC
LIMIT 0, {$limit}";

$result = mysql_query($sql) or die(mysql_error());

$users = array();

$i = 0;

while(($row = mysql_fetch_assoc($result)) !== false)
{
$users[$i] = array(
'id' => $row['id'],
'firstname' => $row['firstname'],
'lastname' => $row['lastname'],
);

$users[$i]['avatar'] = getUserAvatar($row['username']);

$i++;
}

return $users;
}