[ciapug] More MySQL issues

[Carl Olsen]

I usually do everything I possibly can with stored procedures, so it's just
a habit I've gotten into.  I usually set up a class with all of the
functions that are going to use the stored procedures for that particular
table or in some cases a particular page.  I then call the functions in the
class from the actual page where the data is being input and output.  It's
just a pattern I've gotten into with C#, Java, and PHP (most of the books I
read show it being done this way, which is probably the biggest reason I do
this).  It seems to work very well, and I don't have to think as hard when I
come back to something after a long time (or switch to a different
programming language) if I always do things the same way.  I can't tell you
this is how you should do things, but it works well for me.

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

-----Original Message-----
From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On Behalf
Of Dave J. Hala Jr.
Sent: Friday, July 14, 2006 10:56 AM
To: PHP List
Subject: Re: [ciapug] More MySQL issues

Why would you want to user a stored procedure to return a result set to
a php script?


On Fri, 2006-07-14 at 10:45, Carl Olsen wrote:
> 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
> 
>  
> 
> 
> 
> ______________________________________________________________________
> _______________________________________________
> ciapug mailing list
> ciapug at cialug.org
> http://cialug.org/mailman/listinfo/ciapug
-- 

Open Source Information Systems, Inc. (OSIS)
Dave J. Hala Jr., President <dave at osis.us>
641.485.1606

_______________________________________________
ciapug mailing list
ciapug at cialug.org
http://cialug.org/mailman/listinfo/ciapug