|
PHP - Pdo Stored Procedure Output Parameter
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 Similar TutorialsHello 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 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. 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 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?
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 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 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 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 the following code just to insert a username and hashed password into the database but somehow I am getting this error and I couldn't find out where I am doing it wrong...can someone please give me a hand?
I tried it in two ways and both errors...
the first few lines are just connecting database which worked fine and a password.php so I can use password_hash() with my php version
$hash = password_hash('xx', PASSWORD_BCRYPT, array('cost' => 10));
$username = 'xx';
$insertQuery = $db->prepare("
INSERT INTO et_todo (username, password)
VALUES (:username, :hash)
");
$insertQuery->execute(array(
'username' => $username,
'password' => $hash
));
also tried
$hash = password_hash('xx', PASSWORD_BCRYPT, array('cost' => 10));
$insertQuery = $db->prepare("
INSERT INTO et_todo (username, password)
VALUES ('xx', :hash)
");
$insertQuery->execute(array(
'username' => 'xx',
'password' => $hash
));
Hello,
I recently posted here about an issue I was having with my database orientated products page.
I have now run into another problem where say if, /db.php was typed or /db.php?p=IDoNotExist was typed, it returns blank.
I have in my code the desired content to be displayed, but it just doesn't seem to want to make a show.
I was also wondering if it is possible to show different content for whatever the URL is, so for no parameter, the content about the products, and a non existent one, maybe "Product not found"?
Here is my code:
<?php
$db=mysql_connect ("localhost", "webwibco_charlie", "Hello123") or die ('I cannot connect to the database because: ' . mysql_error());
$mydb=mysql_select_db("webwibco_products");
include("header.php");
$status = htmlspecialchars( @$_GET ['p'] );
if ($status == "floorpuzzles") {
echo "<h1>Our Floor Puzzles</h1>";
$sql="SELECT ID, Name, Tags, Description, Category FROM products WHERE Category LIKE '%" . FloorPuzzles . "%'";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
$Name =$row['Name'];
$ID =$row['ID'];
$Description =$row['Description'];
echo "<div class=\"box\">";
echo "<h1>$Name</h1>";
echo "<div class=\"floorbox\"><a href=\"?p=$ID\"><img src=\"images/products/catalogue/big/floorpuzzles/$ID.jpg\" class=\"small\"></a></div>";
echo "<h2>$Description</h2>";
echo "</div>";
}
?>
<?
}else{
if ($status == $_GET["p"]) {
$sql="SELECT ID, Name, Tags, Description, Pieces, Size, Barcode, Category FROM products WHERE ID = '" . $_GET['p'] . "'";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
$Name =$row['Name'];
$ID =$row['ID'];
$Description =$row['Description'];
$Pieces =$row['Pieces'];
$Size =$row['Size'];
$Barcode =$row['Barcode'];
echo "<div class=\"1\">";
echo "<h1>$Name</h1>";
echo "<div class=\"bigbox\">";
echo "<div class=\"floorbox\"><img src=\"images/products/catalogue/big/floorpuzzles/$ID.jpg\" class=\"big\"></div>";
echo "</div>";
echo "</div>";
echo "<div class=\"2\">";
echo "<p>Puzzle Pieces: $Pieces</p>
<p>Puzzle Size: $Size</p>
<p>Barcode: $Barcode</p>";
echo "</div>";
}
}else{
?>
<?
echo"<h1>Our Products</h1>
<p>Our jigsaw puzzles are hand cut by skilled craftsmen and therefore each one is unique with self-correcting pieces. There is a strict quality control process at all stages by our highly experienced staff. The puzzles are durable and provide fun and excitement, enhancing learning and a child’s development.<p>
<p>All of our jigsaws are made using materials from sustainable resources grown in managed forests. Where possible we support companies in the UK and source our components locally, most of our suppliers are in the East Midlands, many in Derbyshire and Nottinghamshire. We keep packaging to a minimum and take our environmental and ethical responsibilities very seriously.</p>
<p>Reducing waste and recycling was a way of life for us before it became fashionable. We are constantly searching for new ideas and consult teachers when developing our jigsaws, which are often used within the national curriculum.</p>
<p>As well as making our own range, we manufacture for leading suppliers to the education market. Check for \"Made in Britain\" and it is probably made by us.</p>
<p>We have a wide variety of products available for viewing, from classic floor puzzles to innovative inset trays. You can take a look at all our products on this page, simply use the navigation buttons to your left.</p>";
}}
include("footer.php");
?>
The final echo is what I wish to be displayed on the URL without or with an invalid parameter.
Here is my site URL: http://www.webwib.co...saws/search.php (note that only the "Floor Puzzles" category has content within it).
Thank you in advance for assistance.
This topic has been moved to PHP Applications. http://www.phpfreaks.com/forums/index.php?topic=327102.0 Well I have a script that executes a scan on a system set to run infinitely, and I need it to echo out a message each time it loops through, but I don't want it to echo out the message with the next loop message below it, and the next one below that etc... I've tried using the flush(); function and been messing around with that with no luck. For security reasons I don't want to release any of the processing code, but here is the basic construction of the script: <?PHP ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** $RepeatIt = -1; for($g=1; $g!=$RepeatIt+1; $g++) { ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** $ScanMessage = ":.:.: SCANNING THE HITLIST FOR MOBSTER: ".$MobName." (SCAN #$g) :.:.:"."<br/><br/>"; echo $ScanMessage; ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** ***PROCESSING AND SCAN CODE*** } ?> At the moment it's returning: :.:.: SCANNING THE HITLIST FOR MOBSTER: DEUS EX DESTROYER (SCAN #1) :.:.: :.:.: SCANNING THE HITLIST FOR MOBSTER: DEUS EX DESTROYER (SCAN #2) :.:.: :.:.: SCANNING THE HITLIST FOR MOBSTER: DEUS EX DESTROYER (SCAN #3) :.:.: :.:.: SCANNING THE HITLIST FOR MOBSTER: DEUS EX DESTROYER (SCAN #4) :.:.: So what I want it to do is just delete the scanning message and replace it with the next scan message so while running this script you would see just the number increment on the same line. Any suggestions? Thanks. 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'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. 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 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; } Hello, Here's my system. Once a user is successfully logged in, a new instance of a User class is created. The constructor of this class grabs the details of the logged-in user from a database and stores them inside properties in the User class. The problem is, obviously I'd want to access these properties from any page I require them to be able to display user data, so I looked into storing the User object in a Session. When I tried implementing this, I ran into a bunch of errors and I couldn't figure it out. Here's an example: After a user has logged in, I had the following code: Code: [Select] $_SESSION['user'] = new User($this->username); I was under the impression that this assigns a user object to a session. But it's not working as I receive this error: Quote Notice: Undefined index: user in ../v2/admin/index.php on line 18 Then on the page I want to display the name of the current user logged-in, I had this code: Code: [Select] $_SESSION['user']->get_Name(); But then I get this error: Quote Fatal error: Call to a member function get_IP() on a non-object in ../v2/admin/index.php on line 18 Can tell me what I have to do, to make this work? Thanks. |
|||||||