0

I'm trying to figure out how to write an HTML form that will run an stored SQL routine. I've never done anything like this before and can't seem to find anything related to my specific situation.

The guy who is working on the Database told me this was the routine I need to execute:

call prc_cat (name_id);

I basically need a drop down menu to select one of the Names (name_id) and execute the routine on that name.

The guy who works on the database is unavailable so I don't have much else to go off of.

Drew
  • 17
  • 1
  • 6
  • You can't execute a stored proc from HTML, however if you use PHP (which you tagged) it's possible. Write it like this: `$q = $db->query("CALL prc_cat($name_id)");` – Daan Apr 20 '15 at 13:07
  • Execute the stored procedure and use the resulting data in the html you render – cwurtz Apr 20 '15 at 13:09

3 Answers3

0

You need to use a form which have 2 attributes :

  • method : POST (url parameters are hidden) or GET (url parameters are shown)
  • action : path to your .php file which will execute the procedure

the form looks like this :

<form action="myFile.php" method="POST">
  <select name="name_id">
    <option>10</option>
    <option>11</option>
    <option>12</option>
    </select>
  <input type="submit" />
  </form>

In your myFile.php, you will take back the select value by writting :

$name_id = $_POST["name_id"];

And the sql code needed to execute the procedure comes here.

Anwar
  • 4,162
  • 4
  • 41
  • 62
  • This looks like exactly what I needed. Is it possible to have the drop down automatically populated with the ID's from mySQL? The names are in a table with "name_id" and "name". – Drew Apr 20 '15 at 13:19
  • I found a solution for the dropdown. I'll try to test this asap. Thanks again. – Drew Apr 20 '15 at 13:23
  • For reference: http://stackoverflow.com/questions/8022353/how-to-populate-html-dropdown-list-with-values-from-database – Drew Apr 20 '15 at 13:28
0

You can call stored procedures like any other sql statement.

Make your html form with relevant dropdown:

<form action="subToDB.php" method="post">
<select name="uName">
<option value="1">Allan</option>
<option value="2">Bill</option>
</select>
</form>

then create a php script to pass that name's id (from the options value attribute) into the stored procedure in the database:

<?php
$nID = $_POST['uName'];
$con = new PDO("mysql:host=<database address>;dbname=<name of schema with stored proc>", "<dbUserName>", "<dbPassword>");
$stmt = $con->prepare("CALL prc_cat (:name_id)");
$stmt->bindParam(":name_id", $nID, PDO::PARAM_INT);
$stmt->execute();
$result=$stmt->fetchAll(PDO::FETCH_ASSOC);
...
<Do whatever you like with the results of your query>
?>

Be sure to change the PDO connection info as well.

MuppetGrinder
  • 234
  • 1
  • 8
  • I was finally able to test and I created the php file using the exact code you gave me and I keep getting an error. `Parse error: syntax error, unexpected '$con' (T_VARIABLE) in home/***/***.php on line 3` any idea what could be going on? – Drew Apr 23 '15 at 02:13
  • I missed out a semi-colon on the $nID = $_POST['uName'] line at the top. I have edited it and add that in. Most code is untested and will require adaptation and / debugging. – MuppetGrinder Apr 23 '15 at 07:33
-1

You need to submit your form to an php file. In this you can run a sql query with the string "SELECT prc_cat('name_id')"

FHanisch
  • 34
  • 4