executing stored procedure in php

codrgii

im calling just one stored procedure from my server onto my php script, the procedure just calls out a select statement and a count() statement, if possible i'd like for them to be separate, how would i do that? any examples?

djjjozsi

why don't you build a view table for this?

CREATE  ALGORITHM = UNDEFINED VIEW test(
id,
email,
field2
) AS SELECT `id` , `email` , `field2`
FROM `users`

codrgii

because the procedure makes a temp table just for viewing and selecting the count, i just need to return the select and count results from the procedure into my php, how would i do that?

djjjozsi

lets use the prepare and execute to prepare statements, and use them.

PDO or pear classes suggested.
http://hu.php.net/pdo

http://pear.php.net/manual/en/package.database.db.intro-execute.php

And this link is useful to start:
here

codrgii

im not asking to improve anything sql side. i want to execute my SQL stored procedure to return its values in php when i execute it through my script. i can execute it fine through php but it dosen't return its results, i need it to return its select and count results, ty for trying but it didn't help me, thats all

codrgii

still need help on this :quiet:

djjjozsi

if i see the examples on execute commands, i can see how they call stored procedures.

if i've got a stored procure in mysql: Insert_Users():
these are examples from the internet.

$result= mysql_query("CALL Insert_Users('username','useradd1','useradd2','userage')")
or die(mysql_error());
print_r($result);

if this is a select you can get the result with a fetch:

<?php
$mysqli = new mysqli('localhost', 'user', 'password', 'test');

if ($stmt = $mysqli->prepare("CALL select_details()")) {
   $stmt->execute();

   $stmt->bind_result($col1, $col2);

   while ($stmt->fetch()) {
       printf("%s %s <br />", $col1, $col2);
   }
   $stmt->close();
}
$mysqli->close();
?>

codrgii

the way you've put your examples, they are connecting to a mysql server, i haven't set up a mysql server as i don't need it. The procedure is in SQL, from SQL to PHP

This is close to what my procedure is all about

if @modeis = 1
   BEGIN

while @i < 7011
   BEGIN
                 insert into table (account) VALUES (@ACC)
                 set @i = @i + 27

   END
   END
select * from table
select count (*) from table

Again i just want to see the select and count results from this procedure onto my php script

codrgii

How do i make the sp show all the results? select from table result and the count () result? i've tried this but at best can only make it come back showing just 1 result from the select (last result selected)...

I'll try myself and read more about how sp works in php, pretty sure this would have to be an array of results...in the meantime will appreciate any guidance on this