echo mysql stored procedure

mbesuma

Hi all,

Im a newbie. I have a database that has stored procedures outputing statements.

Procedure Printer()
begin
...
if ... then select 'there is a problem with A';
elseif ... then select 'A is much bigger than B';
...
end if;
if ... then select 'C is clear';
...
end if;
end;

I can see the outputs in MySql.
mysql> call printer()
+---------------------------------+
| there is a problem with A |
+---------------------------------+
| there is a problem with A |
+---------------------------------+
1 row in set (0,05 sec)

+---------------------------------+
| A is much bigger than B |
+---------------------------------+
| A is much bigger than B |
+---------------------------------+
1 row in set (0,05 sec)

+-------------+
| C is clear |
+-------------+
| C is clear |
+-------------+
1 row in set (0,05 sec)

Query OK, 0 rows affected (0,06 sec)

My question is, how can I get php to put all these rows on my page?
Please help. I've been stuck on this for a while now and am begining to think I cant do this. I've been looking almost everywhere!
Mbe.

mbesuma

Hi all ... as you can see Im still new at this. I made a serious mistake when typing out my first post. Below are the differences in the mysql procedure

procedure my_proc(in id int)
begin
...
if ... then select 'this is both a sedan and suv';
elseif ... then select 'blah';
else select 'blah blah';
end if;
if ... then select 'this is a sedan';
...
end if;
if ... then select 'this has 4wd';
...
end if;
if ... then select 'and so on...'
end;

So now the procedure would output 'this is both a sedan and suv', 'this is a sedan', 'this has 4wd', etc all at once when the procedure is called. How can I get this output on my webpage when I call this mysql stored procedure?

Now about my installation, I was looking at this site http://mysql.gilfster.com/php/calling_procedures.php
Its the only site I could find the clearly explained the calling of mysql stored procedures in php. I made the exact database and php code but still came out with problems. Here's my code.

MYSQL

create table emps(emp_id int NOT NULL,
emp_name varchar(30),
dept_id int,
salary decimal(5,2),
primary key(emp_id));

insert into emps (emp_id,emp_name,dept_id,salary)
values (1,'Roger',1,2000.00),(2,'John',2,2500.00),(3,'Alan',1,2100.00);

delimiter $
create procedure select_emps()
begin

select emp_id, emp_name from emps;

end;$

PHP

<?php

$link = mysql_connect("localhost","root","alittletoolate");

//if (mysql_connect_errno()) {
//echo "connection error";
//exit();
//}
echo "we dont need no more ";
mysql_select_db ($link,"pers");

if ($result = mysql_query($link,"call select_emps()")) {

while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
echo "Emp ID : ".$row[0]." Name : ".$row[1]."<br> ";
}

mysql_free_result($result);

} else { echo "trouble 🙁 "; }

OUTPUT

we dont need no more trouble 🙁

If I remove the comments, I end up with a blank screen. What am I doing wrong? Is it the mysqli feature? I say this because on the link above, they use mysqli_statements. I had to change all the mysqli_statements into mysql_statements in order to get any sort of output on my web browser. What is the difference between the two anyway?

If it is the problem, how can I get it to work on my system (Mandriva 2005 LE, php5.0.5, apache 2.0.55, mysql 5.0.15)? Can someone please help me?

Thanks.

mbesuma

was mysqli

any ideas about putting all those sentences into my web page? Please, anybody?

slweb

if (mysql_connect_errno()) {

should be

if (mysql_connect_error()) {

also dont leave your namr and password in the connection when posting.

slweb

try

$connection = mysql_connect(localhost,$username,$password); 
@mysql_select_db($database) or die( "Unable to select database"); 

$result = "SELECT * FROM emps "; 
mysql_query($result) { 

echo 'Emp ID:';
echo $result['emp_id';
echo '<br>Emp Name:';
echo $result['emp_name';
echo '<br>Dept:';
echo $result['dept_id';
echo '<br>Salary:';
echo $result['salary';

} 
mysql_close();