[ciapug] More MySQL issues

[Dave J. Hala Jr.]

I was just curious, as to why you did it that way... It was just a  way
of doing that was differant than the way I do things and like I said, I
was just curious...

I try new stuff all time, thats why I asked... it might be something
that I'd try sometime...



On Fri, 2006-07-14 at 11:07, Carl Olsen wrote:
> 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