Page 1 of 1

Calling MySql stored procedure

Posted: Wed Nov 16, 2005 4:32 pm
by 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.

Posted: Thu Nov 17, 2005 1:25 am
by RobertGonzalez
Have a look at the following mysqli functions for PHP5. These are not PHP4 compatible and they require version of PHP to support mysqli functions:

http://us2.php.net/manual/en/function.m ... repare.php
http://us2.php.net/manual/en/function.m ... -param.php
http://us2.php.net/manual/en/function.m ... xecute.php

Hope it helps.

msqli vs mysql

Posted: Thu Nov 17, 2005 3:30 am
by mbesuma
I am running on Mandriva 2005 LE, php 5.0.5, apache 2.0.55, mysql 5.0.15 - all of which I am new to. I followed certain instructions made for installing php4-mysql4-apache2 (replacing php4 with php5 and mysql4 with mysql5). I am not getting any outputs when using mysqli. As soon as I change the mysqli statements with mysql statements, I atleast get some output, although not the ones I want. Does anyone know how I can change or activate my system to use mysqli?
Also, what is the difference between the two types of statements? The only difference I found was that mysqli was the future of php-mysql development and musql statements would soon be useless.
Thanks for the quick response.

REVISED ... PLEASE READ THIS ONE

Posted: Thu Nov 17, 2005 12:47 pm
by mbesuma
Hi all ... Thanks everah for your help. I believe my problem is with mysqli. 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","********");

//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.

Posted: Thu Nov 17, 2005 2:56 pm
by yum-jelly
you need to configure your install with mysqli support. then setup your PHP.INI to include the extension and set any values you want the default connection to use! If you go to PHP.NET and look up mysqli, in the first user post there is a link that points to a forum post that has the info on how to install PHP 5 with both mysql and mysqli support!

On a side note, there is nothing wrong with your example you just need mysqli to do it!

yj

Posted: Thu Nov 17, 2005 4:54 pm
by mbesuma
thanks ... decided to re-install everything ... its taking time ... will post when Im done.
Any other ideas on how to input all those mysql select statements into $result ? (in the mean time :lol: )

Thanks again.

Posted: Thu Nov 17, 2005 5:12 pm
by mbesuma
I've hit a rock ... the installation manual Im using can only install either mysql_ or mysqli_ . If I chose mysqli_, am I going to face other problems? Or does mysqli_ have all mysql_ operations.
Waiting for a response. Thanks again.

Posted: Thu Nov 17, 2005 8:15 pm
by mbesuma
Done and working ... Thanks.

Posted: Thu Nov 17, 2005 8:27 pm
by yum-jelly
You can install / configure both on Mandriva 2005, I did it the other day for someone. You just have to add both to your * ./configure --with-mysql=/usr/local/mysql --with-mysqli=/usr/local/mysql/bin/mysql_config * add what ever else you want to configure

Then fix the the PHP Makefile... (open it in your favorite editor)

find this line...

EXTRA_LIBS =

remove any duplicates that you find, such as * -lmysqlclient * (all duplicates, leave just one)

example my Makefile with duplicates

EXTRA_LIBS = -lcrypt -lcrypt -lmysqlclient -lz -lm -lxml2 -lz -liconv -lm -lxml2 -lz -liconv -lm -lmysqlclient

after removing dupz

EXTRA_LIBS = -lcrypt -lmysqlclient -lz -lm -lxml2 -liconv

save changes, then....

make
make install

yj

Posted: Fri Nov 18, 2005 2:37 am
by mbesuma
hey man ... thanks for that post. Helped filled in a lot of holes. Yes I have both running now. Thanks again.

Posted: Fri Nov 18, 2005 2:45 pm
by mbesuma
Hey yj,

got a small question of big proportions ... I have a small network at home. As I said earlier, Im running on Mandriva 2005 LE and it runs all my php files from /apache2/htdocs/*.php . Problem is, when I save the file on my text editor(kwrite), say input.php, there's another file input.php~ in the same folder that outputs my code (including my password which all you know) if addressed by a browser. How can I stop this from happening?

Thanks in advance.

Posted: Sat Nov 19, 2005 9:50 am
by yum-jelly
Hi (Kwrite Menu)

settings >> configure editor >> open / save >> backup when saving, (option at the bottom of the right tab)


I hope that helps...

yj!

Posted: Sat Nov 19, 2005 11:47 am
by mbesuma
Thanks Yj ... didnt think of that ,,, thanks.