| Author |
Topic |
|
DaveTheDog
Starting Member
4 Posts |
 Posted - 2012-05-16 : 11:44:38
|
| I am building my first on-line store by following an example in a book.Problem is that the book example relies on stored procedures on the database which are called from the php files and my clients server does not allow stored procedures.The simple procedures are easy to replace with code, but I've got stuck on a procedure that prepares and executes a statement.The code in the fill that calls the statement is $sql = 'CALL catalog_get_products_in_category( :category_id, :short_product_description_length, :products_per_page, :start_item)';and the stored procedure is PREPARE statement FROM "SELECT p.product_id, p.name, IF(LENGTH(p.description) <= ?, p.description, CONCAT(LEFT(p.description, ?), '...')) AS description, p.price, p.discounted_price, p.thumbnail FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id WHERE pc.category_id = ? ORDER BY p.display DESC LIMIT ?, ?"; -- Define query parameters SET @p1 = inShortProductDescriptionLength; SET @p2 = inShortProductDescriptionLength; SET @p3 = inCategoryId; SET @p4 = inStartItem; SET @p5 = inProductsPerPage; -- Execute the statement EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;Can anyone help me please?DaveTheDog |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-16 : 20:21:22
|
| whats this code? its not t-sql so you might be better off posting it in relevant forums------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
DaveTheDog
Starting Member
4 Posts |
Posted - 2012-05-17 : 03:21:39
|
| description for this forum indicates it is not exclusively for T-SQL. "This forum is specifically for people that are working with SQL Server Programming for the first time. Anything T-SQL or SQL Server client related is welcome."The code calling the procedure begins "sql =" so I assumed the next bit is sql.The procedure itself is intended to be uploaded to the database using the sql tab in myphpadmin so again I had assumed it to be sqlDaveTheDog |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-17 : 03:39:39
|
Yes. But from the code that you have posted, that does not looks like a Query for Microsoft SQL Server. It looks more like MySQL. If you are using MySQL, try posting your question over at http://forums.mysql.com/ or http://www.dbforums.com/mysql/
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
DaveTheDog
Starting Member
4 Posts |
Posted - 2012-05-17 : 04:09:37
|
| Great, thanks for your helpKind regardsDaveTheDog |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-05-18 : 09:18:35
|
| You are passing values for LIMIT clause dyanmically which needs you to use Dynamic sql. You can just use the same code as inline in your php code instead of having it in a procedureMadhivananFailing to plan is Planning to fail |
|
|
|
DaveTheDog
Starting Member
4 Posts |
Posted - 2012-05-18 : 11:05:34
|
| Hi MadhivananI think real problem is I need to concatenate strings to end up with same end result as the stored procedure but in php not mysql. This involves escaping the single and double quotes and perhaps some other characters.Wish me luck!DaveTheDog |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-05-21 : 06:41:55
|
quote:
Originally posted by DaveTheDog
Hi MadhivananI think real problem is I need to concatenate strings to end up with same end result as the stored procedure but in php not mysql. This involves escaping the single and double quotes and perhaps some other characters.Wish me luck!DaveTheDog
Yes you need to deal with single quotes. You may find this usefulhttp://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
|
alernative to stored procedures