Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Sep 2018
    Posts
    119
    Thanks
    25
    Thanked 0 Times in 0 Posts

    how to add limit = 1 with prepare statement?

    Hey guys!

    I am trying to figure out how to add limit = 1 with prepare statement, do you need to add the AND in between the syntax as in the following:

    $sql4 = "SELECT * FROM music_forum_sub_cats WHERE cat_id = ? AND limit ?;";

    I tried that and it didn't work but it works for this code:

    $sql4 = "SELECT * FROM music_forum_sub_cats WHERE cat_id = ? limit ?;";

  2. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,196
    Thanks
    3
    Thanked 538 Times in 525 Posts
    AND is a logical operator - https://dev.mysql.com/doc/refman/8.0...l#operator_and

    You use it between two operands (expressions/values) in a conditional statement when you only want the conditional statement to be TRUE if both operands are TRUE.

    LIMIT ... isn't an expression/value. It is a term in a query that limits the rows that are returned (select query) or operated on (update query) - https://dev.mysql.com/doc/refman/8.0/en/select.html or https://dev.mysql.com/doc/refman/8.0/en/update.html
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  3. Users who have thanked CFMaBiSmAd for this post:

    piano0011 (Sep 14th, 2018)

  4. #3
    Senior Coder deathshadow's Avatar
    Join Date
    Feb 2016
    Location
    Keene, NH
    Posts
    3,085
    Thanks
    4
    Thanked 446 Times in 435 Posts
    @CFMaBiSmAd has it right in that LIMIT is an instruction, not an operator/value. This is why I favor using caps for instructions and lower case for everything else, and using more indentation and whitespace to make the code clearer.

    hence:

    Code:
    $stmt = $db->prepare('
      SELECT *
      FROM music_forum_sub_cats
      WHERE cat_id = ?
      LIMIT ?
    ');
    Where you were trying to add that "AND" you were either saying that the limit was part of the "WHERE" (which is gibberish) or that you had another "WHERE" condition that didn't even exist.

    Also you don't need that trailing semicolon -- generally that's only needed if you are trying to run more than one query per string, something that you should NOT be doing in the first place. Part of why I prefer PDO over mysqli, it flat out doesn't allow multiple queries per query string.

    Likewise, unless you're implementing multi-engine (a PDO thing) stop wasting 'variables for nothing' on your query strings. That's an outdated and outmoded practice that REALLY needs to stop. Just plug your query into the prepare and be done with it, rather than wasting RAM and processing time on making an 'extra copy for nothing'.
    “There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies.” – C.A.R. Hoare, The 1980 ACM Turing Award Lecture
    http://www.cutcodedown.com

  5. Users who have thanked deathshadow for this post:

    piano0011 (Sep 14th, 2018)

  6. #4
    Regular Coder
    Join Date
    Sep 2018
    Posts
    119
    Thanks
    25
    Thanked 0 Times in 0 Posts
    With regard to prepare statement, must I do the following line for each sql statement? $stmt = mysqli_stmt_init($conn);

  7. #5
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,196
    Thanks
    3
    Thanked 538 Times in 525 Posts
    Quote Originally Posted by piano0011 View Post
    With regard to prepare statement, must I do the following line for each sql statement? $stmt = mysqli_stmt_init($conn);
    Perhaps if you do the things that have been written in the replies to your threads -

    Quote Originally Posted by CFMaBiSmAd View Post
    5) Replace the $stmt = mysqli_stmt_init($conn); and mysqli_stmt_prepare($stmt, $sql) pairs with a single - $stmt = mysqli_prepare($conn, $sql); statement.

    6) Switch to use the much simpler and more consistent php PDO extension.
    If you do item #5 in the above, you will only have a single statement to write. And if you do item #6 in the above, the php PDO extension takes fewer lines of code to accomplish every prepared query task, then the php mysqli extension does.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  8. #6
    Senior Coder deathshadow's Avatar
    Join Date
    Feb 2016
    Location
    Keene, NH
    Posts
    3,085
    Thanks
    4
    Thanked 446 Times in 435 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    And if you do item #6 in the above, the php PDO extension takes fewer lines of code to accomplish every prepared query task, then the php mysqli extension does.
    On top of all the other reasons mysqli is almost as trashy as the old mysql_ functions. There's just so many good reasons not to use it when PDO is available as an option.

    ESPECIALLY when it comes to kicking the procedural wrapping nonsense to the curb, and the simple fact that in PDO a query, exec, and prepare all return the same flipping object type whilst mysqli seems to be intentionally crafted to make prepare/execute harder to do -- what with mysqli_result and mysqli_statement being two separate entities with entirely different methods.
    “There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies.” – C.A.R. Hoare, The 1980 ACM Turing Award Lecture
    http://www.cutcodedown.com


 

Tags for this Thread

Posting Permissions

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