Asked  7 Months ago    Answers:  5   Viewed   45 times

I want to be able create stored procedures using phpMyAdmin and later on use it through php.

But I dont know how to?

From what I know, I found out that we cannot manage stored procedures through phpMyAdmin.

What other tool can manage stored procedure?

I am not even sure if it is better option to use stored procedure through PHP. Any suggestion?

 Answers

10

Since a stored procedure is created, altered and dropped using queries you actually CAN manage them using phpMyAdmin.

To create a stored procedure, you can use the following (change as necessary) :

CREATE PROCEDURE sp_test()
BEGIN
  SELECT 'Number of records: ', count(*) from test;
END//

And make sure you set the "Delimiter" field on the SQL tab to //.

Once you created the stored procedure it will appear in the Routines fieldset below your tables (in the Structure tab), and you can easily change/drop it.

To use the stored procedure from PHP you have to execute a CALL query, just like you would do in plain SQL.

Wednesday, March 31, 2021
 
PeterTheLobster
answered 7 Months ago
57

Forget about mysqli, it's much harder to use than PDO and should have been already removed. It is true that it introduced huge improvements over mysql, but to achieve the same effect in mysqli sometimes requires enormous effort over PDO i.e. associative fetchAll.

Instead, take a look at PDO, specifically prepared statements and stored procedures.

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $valuen";
Friday, June 18, 2021
 
Octopus
answered 4 Months ago
76

You can do the following

 Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
PreparedStatement st = session.connection().prepareStatement("{call procedureName(?, ?)}");
                st.setString(1, formatter.format(parameter1));
                st.setString(2, formatter.format(parameter2));
                st.execute();
tx.commit();

Please add the exception handling wherever required.

Monday, August 2, 2021
 
magix
answered 3 Months ago
68

The way stored procedures work with prepared statements is a bit more complicated. PHP manual states that you've got to use session variables (MySQL sessions, not PHP)

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

So you could do it with

$connect=&ConnectDB();
// bind the first parameter to the session variable @uid
$stmt = $connect->prepare('SET @uid := ?');
$stmt->bind_param('s', $uid);
$stmt->execute();

// bind the second parameter to the session variable @userCount
$stmt = $connect->prepare('SET @userCount := ?');
$stmt->bind_param('i', $userCount);
$stmt->execute();

// execute the stored Procedure
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);

Remark:

I recommend to rewrite this procedure as a function with one IN parameter that returns INT.

Thursday, August 5, 2021
 
Shibbir
answered 3 Months ago
78

Maybe you can try the following (taken from https://stackoverflow.com/a/10513319/1236044 )

It uses CreateSQLQuery. You may try replacing the select... with Exec MyStoredProc

The key point is having your select or stored procedure return columns with the same name as the properties of the DTO you are trying to populate.

public class YourDto
{
    public int YourDtoId { get; set; }
    public string YourDtoTitle { get; set; }
}

then

var result = yourNhSession
    .CreateSQLQuery("select yourColumn1 as YourDtoId, yourColumn2 as YourDtoTitle from YOUR_TABLE")
    .SetResultTransformer(Transformers.AliasToBean<YourDto>())
    .List<YourDto>();
Wednesday, October 6, 2021
 
davidb
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :