<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns0="urn:schemas-microsoft-com:office:smarttags">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PostalCode"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="State"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="Street"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceType"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceName"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="address"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>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:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>DELIMITER $$;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>DROP PROCEDURE IF EXISTS
`news`.`sp_News_ReturnApprovedArticles`$$<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>CREATE PROCEDURE `sp_News_ReturnApprovedArticles`(IN sp_var1
<st1:City w:st="on">INT</st1:City>, <st1:State w:st="on">IN</st1:State> sp_var2
CHAR(10), IN sp_var_skip <st1:place w:st="on"><st1:City w:st="on">INT</st1:City>,
<st1:State w:st="on">IN</st1:State></st1:place> sp_var_count INT)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>BEGIN<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>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);<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>PREPARE Stmt FROM @String1; <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>EXECUTE Stmt; <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>SET @String1 = "";<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>END$$<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>DELIMITER ;$$<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>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.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>--<br>
Carl Olsen, MCSE</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Web Developer, CMS Implementation<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Marketing and Communications<br>
<ns0:place w:insAuthor="Carl Olsen" w:insDate="2006-07-14T10:39:00Z"
w:endInsAuthor="Carl Olsen" w:endInsDate="2006-07-14T10:39:00Z"><ns0:PlaceName
w:insAuthor="Carl Olsen" w:insDate="2006-07-14T10:39:00Z"
w:endInsAuthor="Carl Olsen" w:endInsDate="2006-07-14T10:39:00Z"><st1:place
w:st="on"><st1:PlaceName w:st="on">Drake</st1:PlaceName> <st1:PlaceType
w:st="on">University</st1:PlaceType></st1:place><br>
<st1:Street w:st="on"><st1:address w:st="on">2507 University Avenue</st1:address></st1:Street><br>
316 Old <st1:place w:st="on">Main</st1:place><br>
<st1:place w:st="on"><st1:City w:st="on">Des Moines</st1:City>, <st1:State
w:st="on">Iowa</st1:State> <st1:PostalCode w:st="on">50311-4505</st1:PostalCode></st1:place><br>
Phone: 515-271-2986<br>
Fax: 515-271-3798<br>
<a href="mailto:Carl.Olsen@drake.edu">Carl.Olsen@drake.edu</a><br>
<a href="http://www.drake.edu">www.drake.edu</a></ns0:PlaceName></ns0:place><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>