[ciapug] More MySQL issues

[Carl Olsen]

Apparently, MySQL Stored Procedures don't support the LIMIT function very
well.  In order to get a Stored Procedure to return a set or rows using the
LIMIT function, you have to do some procedural gymnastics, such as:

 

DELIMITER $$;

 

DROP PROCEDURE IF EXISTS `news`.`sp_News_ReturnApprovedArticles`$$

 

CREATE PROCEDURE `sp_News_ReturnApprovedArticles`(IN sp_var1 INT, IN sp_var2
CHAR(10), IN sp_var_skip INT, IN sp_var_count INT)

BEGIN

SET @String1 = concat(concat(concat("SELECT * FROM news_article WHERE cid =
",sp_var1," AND sdatedisplay >= '",sp_var2,"' AND sdatedisplay <
DATE_ADD('",sp_var2,"',INTERVAL 1 MONTH) AND iapproved = 1 ORDER BY iorder
ASC, sdatedisplay DESC LIMIT "),sp_var_skip,","),sp_var_count);

PREPARE Stmt FROM @String1; 

EXECUTE Stmt; 

SET @String1 = "";

END$$

 

DELIMITER ;$$

 

When you run this as a query, it returns a set of records as you would
expect.  However, when I call it from a PHP prepared statement, I get
extremely unusual results.  I get the correct number of rows each time, but
the data is scrambled beyond recognition.  I get some characters that don't
display correctly.  The data is all numbers, except for the characters that
it doesn't recognize (it just displays them as an empty square).  I'm
thinking it returning binary data or something bizarre like that.  I finally
gave up on this and skipped the stored procedure and ran the query inside a
prepared statement.  Prepared Statements seems to work for anything I can
come up with, but Stored Procedures seem to have limited functionality.

 

--
Carl Olsen, MCSE

Web Developer, CMS Implementation

Marketing and Communications
Drake University
2507 University Avenue
316 Old Main
Des Moines, Iowa 50311-4505
Phone: 515-271-2986
Fax: 515-271-3798
Carl.Olsen at drake.edu
www.drake.edu

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cialug.org/pipermail/ciapug/attachments/20060714/dab18c48/attachment.html