[ciapug] More MySQL issues

[Carl Olsen]

I'm curious why people on this list question why you would want to use this
or that technology when the question being asked is how that technology
works, not whether someone should use it.  If you want to learn how
something works, is there some way of doing that without trying it?

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