calling stored procedure from php. 1

[Hylsan]

Hi!
I've got a SP (pVisaIntern) that makes two temporary tables and makes a combined result. It has two inputs and all works fine in query browser.

How do I call this on my page?

<?php 
include "../connect.php"; 

		// HTML-tabellens formatering - tabellstart
		echo "<table border='1' bordercolor='#FFAA2A' bgcolor='#FFFFD4' cellspacing='0' cellpadding='3'>";
		echo "<tr bgcolor='#C4E7C9' align=center><td width='45'>Artikel</td><td width='245' align=left>Benämning</td><td width='45'>Vikt</td><td width='45'>Grupp</td></tr>";
	    		  
		#---- Hämtar data skriver ut den ----#
		$artikel = mysql_query("call pVisaIntern('2009-01-01 00:00:00','2009-01-31 23:59:59');") or die(mysql_error('hittar inga artiklar'));
		while($row = mysql_fetch_array( $artikel ))
		{
		#skriver ut innehåller radvis
		echo "<tr align=right><td>"; 
		echo $row['Artikel'];
		echo "</td><td>";
		echo $row['Benämning'];
		echo "</td><td >";
		echo $row['Vikt'];
		echo "</td><td>";
		echo $row['Grupp'];
		echo "</td><td>";
		echo "<b>-</b>";
		echo "</td></tr>";
		}

		echo "</table>";

?>

Hope you can understand the code even if its in swedish

Running Apache, MySql and php5.

All help is appreciated!!
/Hylsan

 

[jpadie]

change mysql_error call

mysql_error('hittar inga artiklar')

to this

mysql_error()

the query looks correct (without knowing what the SP is) except for the semi-colon at the end. there should be no semi-colon.

the revised mysql_error code will produce a meaningful error message if the sql is not correct.

 

[Hylsan]

Thanks!
Managed to clear up some things but now Ive got another problem.

This is my pVisaIntern (Its not ready but you'll get the idea);

DELIMITER $$

DROP PROCEDURE IF EXISTS `vagsql`.`pVisaIntern` $$
CREATE PROCEDURE `vagsql`.`pVisaIntern` (dat1 varchar(20),dat2 varchar(20))
BEGIN
     /* starta överkopiering av artiklar till temp-tabell */
     /* Ska ersättas med CREATE TEMPORARY TABLE */

insert into tmp_artikel select artikel, benamning, grupp, ewc from artikel;

insert into tmp_trans select transnr, artikel, nettovikt, transtid from trans2009 where transtid between dat1 and dat2 and status='2';

select tmp_artikel.artikel,tmp_artikel.benamning, ifnull(sum(tmp_trans.nettovikt),0) as Summa, tmp_artikel.grupp from tmp_artikel left outer join tmp_trans on tmp_trans.artikel = tmp_artikel.artikel
where tmp_artikel.grupp like 'A%i'
group by tmp_artikel.grupp, tmp_artikel.artikel;

      /* Rensar ut i tmp-tabellerna - kommer ersättas med DROP TABLE */
delete from tmp_artikel;

delete from tmp_trans;

END $$

DELIMITER ;

and the result is something like this;

artikel    benamning        summa   grupp
1001       desc 1            2321   a1i
1002       desc 2             112   a2i
1102       desc 9            1221   a7i
....

The error I get states;
"PROCEDURE vagsql.pVisaIntern can't return a result set in the given context"

How do I fix this?
Can mention that Im new to SP's so it might be there the error is.

Thanks in advance!
/Hylsan

 

[jpadie]

there are some php bug reports on this. perhaps you need to upgrade your installation. see eg.

http://bugs.php.net/bug.php?id=42548

 

[Hylsan]

Yepp, helped alot!

Thanks!
/Hylsan