Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Post Unanswered: re-coding to avoid calling stored procedures

    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 php file 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?


    Kind regards

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there's nothing in that stored procedure that requires the use of a stored procedure

    I'd suggest you execute a query from within your PHP script

    $strSQL = "SELECT p.product_id, p.name,
    IF(LENGTH(p.description) <= ".$ShortProductDescriptionLength.",
    p.description,
    CONCAT(LEFT(p.description, ".$ShortProductDescriptionLength."),
    '...')) 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 = ".$CategoryId."
    ORDER BY p.display DESC
    LIMIT ".$StartItem.", ".$ProductsPerPage.";";
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    2

    Thanks

    Hi Healdem

    Thanks for your efforts - unfortunately it didn't work.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    didn't work in what way?
    what error was reported?
    what does the SQL you are sending to the SQL engine look like.. not the PHP code but the actual SQL
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •