PHP - Call Db Stored Procedure When Button Is Clicked
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. Similar Tutorialswrote 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?
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 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 Hello, I had a web page which had sharing (share issue on facebook, twitter....) and I want to record what each user had shared. So, they will click on alink to share it. I want to call a php function when the user click on a link, whats the best method to do this? Thanks in advance i am developing online test ,after succesful completion of the test i want to display the correct answe,in databse i have stored corect answer as radio button values(ex 1,2,3,4,) i want to display the coreect answer with a right mark,how can i achive this?could anybody help?
here is my code
<?php $testid=$_GET['testid']; include_once("header.php"); ?> <html> <head> <body> <?php include_once('connect.php'); $sqltest="select testname from test where testid='$testid'"; $sqltestresult=mysql_query($sqltest) or die(mysql_error()); while($result=mysql_fetch_array($sqltestresult)) { $testname=$result['testname']; } ?> <!--pass test name and time left --> <table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td width="50%" valign="middle"> <h1 style="margin-left:0;"><?php if(isset($testname)) { echo $testname ; } ?></h1> </td> <td width="50%" align="right" valign="middle"> <ul class="tabs" id="tabs"> <li id="showtime" style="font-weight:bold; color:#FF0000; bottom:5px; font-size:16px; float:right"></li> </ul> </td> </tr></table> <div class="div-tabs-container" class="div-tabs-container" style="width:79%;margin-left:3%" > <table cellpadding="0" cellspacing="0" border="0" id="questiontable" > <tr> <?php include_once('connect.php'); $sql="select * from testquestions where testid='$testid'"; $result=mysql_query($sql) or die(mysql_error()); if (mysql_num_rows($result)>0) { $data = array(); // create a variable to hold the information while (($row = mysql_fetch_array($result, MYSQL_ASSOC)) !== false) { $data[] = $row; // add the row in to the results (data) array } //shift off the first value array_shift($data[0]); $merge = call_user_func_array('array_merge', $data); $size=sizeof($merge); $newdata=array(); $num=1; $correctanswerarray=array(); $selctedanswers=array(); /* echo "<form action='fetchquestion.php' method='post' id='formsubmit' onsubmit='return out()' name='test'>"; */ foreach ($merge as $key => $value ) { $sql2="select qid, question,option1,option2,option3,option4,correct_answer from question where qid='$value' "; $result2=mysql_query($sql2) or die(mysql_error()); while($row2=mysql_fetch_assoc($result2)) { $questionid=$row2['qid']; $question=$row2['question']; $option1=$row2['option1']; $option2=$row2['option2']; $option3=$row2['option3']; $option4=$row2['option4']; $correctanswer=$row2['correct_answer']; #array to store the correct answer. $correctanswerarray[] = $questionid.$correctanswer; #$newdata[]=$row2; echo " <table class='bix-tbl-container' cellspacing='0' cellpadding='0' border='0' width='100%' id='questiontable'><tr> <td class='bix-td-qno jq-qno-2413' rowspan='2' valign='top' align='left'>$num</td> <td class='bix-td-qtxt' valign='top'><p>$question</p></td> </tr> <tr> <td class='bix-td-miscell' valign='top'><table class='bix-tbl-options' id='tblOption_2413' border='0' cellpadding='0' cellspacing='0' width='100%'><tr><td nowrap='nowrap' class='bix-td-option bix-td-radio' width='1%' id='tdOptionNo_A_2413'> <input type='radio' class='result-option cls_2413' name='option_$questionid' value='1' id='disable'/> </td><td class='bix-td-option' width='1%'>A.</td> <td class='bix-td-option' width='48%' id='tdOptionDt_A_2413'><table border='0' cellpadding='0' cellspacing='0'> <tr> <td class='bix-inner-td-option'>$option1</td> <td id='tdAnswerIMG_A_2413' class='jq-img-answer' valign='middle'style='display:none;padding-left:10px;'> <img src='/_files/images/website/accept.png' alt='' /> </td> </tr> </table></td></tr><tr><td nowrap='nowrap' class='bix-td-option bix-td-radio' width='1%' id='tdOptionNo_B_2413'> <input type='radio' class='result-option cls_2413' name='option_$questionid' value='2' id='disabble'/> </td><td class='bix-td-option' width='1%'>B.</td> <td class='bix-td-option' width='48%'id='tdOptionDt_B_2413'><table border='0' cellpadding='0' cellspacing='0'> <tr> <td class='bix-inner-td-option'>$option2</td> <td id='tdAnswerIMG_B_2413' class='jq-img-answer' valign='middle' style='display:none;padding-left:10px;'> <img src='/_files/images/website/wrong.gif' alt=''/> </td> </tr> </table></td></tr><tr><td nowrap='nowrap' class='bix-td-option bix-td-radio' width='1%' id='tdOptionNo_C_2413'> <input type='radio' class='result-option cls_2413' name='option_$questionid' value='3' id='disable'/> </td><td class='bix-td-option' width='1%'>C.</td> <td class='bix-td-option' width='48%' id='tdOptionDt_C_2413'><table border='0' cellpadding='0' cellspacing='0'> <tr> <td class='bix-inner-td-option'>$option3</td> <td id='tdAnswerIMG_C_2413' class='jq-img-answer' valign='middle' style='display:none;padding-left:10px;'> <img src='/_files/images/website/wrong.gif' alt='' /> </td> </tr> </table></td></tr><tr><td nowrap='nowrap' class='bix-td-option bix-td-radio' width='1%' id='tdOptionNo_D_2413'> <input type='radio' class='result-option cls_2413' name='option_$questionid' value='4' id='disable'/> </td><td class='bix-td-option' width='1%'>D.</td> <td class='bix-td-option' width='48%' id='tdOptionDt_D_2413'><table border='0' cellpadding='0' cellspacing='0'> <tr> <td class='bix-inner-td-option'>$option4</td> <td id='tdAnswerIMG_D_2413' class='jq-img-answer' valign='middle' style='display:none;padding-left:10px;'> <img src='/_files/images/website/wrong.gif' alt='' /> </td> </tr> </table></td></tr></table> \n <input type='hidden' name='count' value='$num' id='count'> <input type='hidden' name='queId[]' value='$questionid' id='queId'> </td> </tr> </table> "; $num++; } } echo " </tr> </table> "; #array of correct answrer #echo "array of correct answer<br/>"; #print_r($correctanswerarray); #echo "size of the coreect answer array"; $size1=sizeof($correctanswerarray); echo "<br/>"; } else { echo " no questions available for selected test"; } ?> </div> <!-- add div right side --> <div style="height:500px;width:150px;float:right;margin-top:-500px;border:1px solid #99CCFF"> </div> <div style="height:30px"> </div> <!-- add div bottom --> <div style="height:125px;border:1px solid #99CCFF"> </div> <?php include('footer.html'); ?> </body> </html> Edited by mac_gyver, 25 October 2014 - 10:05 AM. code tags around code please I have a comment section. It is comprised of 3 major files. One file creates the form where you fill in your name and comment. Another file sends your comment to the DB. The third file displays the already existing comments on the page. You have the option to reply to the existing comments on the page. When you click the reply button I would like the comment text box to light up so the user knows its working but I have hit a brick wall.
This is the comment form the user actually types into:
<div id="comment-container" > <form id="comment_form" action="/comm_1/post_comment.php" method='post' onsubmit=" return validateForm()"> <table> <tr> <td id="error"></td> </tr> <tr> <td><textarea name="comment_body" id='comment_body' placeholder="Comment"></textarea></td> </tr> <?php if(!$username && !$userid): ?> <tr> <td><input type="text" name="name" class="input_style" placeholder="Name"/> <input type="submit" id="loginbtn" value="Or Login" onclick="window.location='/login_scripts/login.php'" /></td> </tr> <tr> <td><input type="email" name="email" class="input_style" placeholder="Email"/></td> </tr> <?php endif; ?> <?php if($username && $userid): ?> <input type="hidden" name="name" value="<?php echo htmlspecialchars($username) ?>"/> <input type="hidden" name="email" value="<?php echo htmlspecialchars($email) ?>"/> <?php endif; ?> <tr> <input type='hidden' name='parent_id' id='parent_id' value='0'/> <td><input type="submit" name="submitbtn" id="submitbtn" value="Add comment"/></td> </tr> </table> </form>This how the existing comments are displayed on the page: You can see on line 7 is the button to the reply to a comment. When this is clicked I need the "comment_body" from the above code to highlight. <?php function getComments($row) { echo "<li class='comment'>"; echo "<div class='aut'>".$row['author']."</div>"; echo "<div class='timestamp'>".$row['created_at']."</div>"; echo "<div class='comment-body'>".$row['comment']."</div>"; echo "<a href='#comment_form' name='replybtn' class='reply' id='".$row['id']."'>Reply</a></script>"; $q = "SELECT * FROM threaded_comments WHERE parent_id = ".$row['id'].""; $r = mysql_query($q); echo "</li>"; if(mysql_num_rows($r)>0) { echo "<ul>"; while($row = mysql_fetch_assoc($r)) { getComments($row); } echo "</ul>"; } } ?>I tried this with JS, but it does not work: $(function(){ $("a.reply").click(function() { var id = $(this).attr("id"); $("#parent_id").attr("value", id); $("#comment_body").focus(); }); }); Edited by ryanmetzler3, 26 January 2015 - 09:48 PM. Hi, I am new to PHP world!! I am just trying to find a way to disable a button after its been clicked for lets say 5 minutes. I have searched google and haven't found what I am looking for. I just dont want the users to be able to refresh the page and the button becomes enabled again. any code examples with explanation or a simple tutorial will be much appreciated. Thanks This topic has been moved to JavaScript Help. http://www.phpfreaks.com/forums/index.php?topic=331164.0 I feel like a moron asking this question as it seems to be one of the most common things done with php but I cannot find a tutorial (probably because I don't know the correct wording to search under) on this specific thing.. Anyhow. I'm spitting out a list of the Titles of my test table like so (and its working as expected): <?php $posting_sql = "SELECT * FROM postings"; $posting_results = (mysqli_query($cxn, $posting_sql)) or die("Was not able to grab the Postings!"); while($posting_row = mysqli_fetch_array($posting_results)) { echo "<li><a href='posting_details.php'>$posting_row[title]</a></li>"; } ?> Now as you can see: <a href='posting_details.php'> I am calling a new page, and that page contains this: <h2><?php echo "$posting_row[title]"; ?></h2> <p><?php echo "$posting_row[description]"; ?></p> It's no surprise it's not working (with many variations etc), but I am only familiar with using <form> GET or POST, and these of course are not form elements, so I cant seem to call them the same way. My suspicion (and from the research data I could find) is that I need to pass values in my link: <a href='posting_details.php'> ... I could not get it to work, but I was trying variations like: <a href='posting_details.php?get[title&get[description]]'> but it seems like I am screwing things up even more by doing this.. Anyhow. If anyone could show me a tutorial that covers this specifically or some suggestions on the best approach to this would be much appreciated.. I have two pages one is db.php and another is form.php. In form.php i have created a form which contains different fields and a submit button. But i want to write the queries in db.php. And when i click on the submit button the insert query in db.php should be executed and insert data in database but the focus remains on form.php. How can i do this??? Any Idea? This topic has been moved to PHP Applications. http://www.phpfreaks.com/forums/index.php?topic=327102.0 This topic has been moved to Other Libraries and Frameworks. http://www.phpfreaks.com/forums/index.php?topic=319682.0 |