SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Make WHERE value dynamic??

    Here is a snippet from an SQL statement in my PHP...
    Code:
    WHERE ap.sd_section_slug = 'finance'
    AND ap.ds_dimension_slug = 'featured_finance'

    Is there some way that I can make the 2nd value in my WHERE clause "partially-dynamic" like this...
    Code:
    WHERE ap.sd_section_slug = $section
    AND ap.ds_dimension_slug = 'featured_<Insert value from $section variable here>'

    The goal is that if a user is clicking on different "Section" tabs, I want my query to change like this...
    Code:
    WHERE ap.sd_section_slug = 'finance'
    AND ap.ds_dimension_slug = 'featured_finance'
    
    
    WHERE ap.sd_section_slug = 'legal'
    AND ap.ds_dimension_slug = 'featured_legal'
    (I'm using Prepared Statements if that matters...)

    Sincerely,


    Debbie

  2. #2
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    "WHERE ap.sd_section_slug = '{$section}'
    AND ap.ds_dimension_slug = 'featured_
    {$section}'" 
    Should do it.
    Last edited by captainccs; May 19, 2013 at 18:37. Reason: Added missing single quotes | I really should be sleeping
    Denny Schlesinger
    web services

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Once user input is accepted in queries you should be using variable binding. Especially, since you're using PDO. I know your next question is going to be how that is done.

    http://php.net/manual/en/pdostatement.bindparam.php

    You could also use escaping as one might with the traditionally php database layer mysql_* functions but that kind of defeats the purpose of using the PDO adpater in the first place.
    The only code I hate more than my own is everyone else's.

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by captainccs View Post
    PHP Code:
    "WHERE ap.sd_section_slug = '{$section}'
    AND ap.ds_dimension_slug = 'featured_
    {$section}'" 
    Should do it.
    What do the curly braces do??

    So, this seems to work, but upon reflection, it is probably a bad idea since I am using Prepared Statements, and security is a must for my queries!!!

    Is there a way to do what I want, but make it work with the ? symbol in my Prepared Statement?

    Sincerely,


    Debbie

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Once user input is accepted in queries you should be using variable binding. Especially, since you're using PDO. I know your next question is going to be how that is done.

    http://php.net/manual/en/pdostatement.bindparam.php

    You could also use escaping as one might with the traditionally php database layer mysql_* functions but that kind of defeats the purpose of using the PDO adpater in the first place.
    Who said I am using PDO???????

    I said I am using Prepared Statements...


    Debbie

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Here is what I came up with to preserve how my Prepared Statements are supposed to work...

    PHP Code:
        // Set Variables.
        
    $sectionSlug $_GET['section'];
        
    $dimensionSlug 'featured-' $sectionSlug;        //APPEND THINGS BEFOREHAND

        // Build query.
        
    $q1 "SELECT ss.name
                FROM subsection AS ss
                INNER JOIN dimension_subsection AS ds
                ON ss.slug = ds.subsection_slug
                INNER JOIN section_dimension AS sd
                ON ds.dimension_slug = sd.dimension_slug
                WHERE sd.section_slug = ?
                AND sd.dimension_slug = ?
                ORDER BY sd.section_slug, ss.sort"
    ;        //USE QUESTION MARK IN BOTH PLACES VARIABLES ARE NEEDED

        // Prepare statement.
        
    $stmt1 mysqli_prepare($dbc$q1);

        
    // Bind variable to query.
        
    mysqli_stmt_bind_param($stmt1'ss'$sectionSlug$dimensionSlug);        //BINDING AS NORMAL 
    This seems to be working, and should be much more *secure* than the original solution.

    Agree or not?!

    Sincerely,


    Debbie

  7. #7
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    What do the curly braces do??

    Debbie
    You don't really need them in this case but I like them as they highlight the variables inside the string.


    Variable parsing

    When a string is specified in double quotes or with heredoc, variables are parsed within it.

    There are two types of syntax: a simple one and a complex one. The simple syntax is the most common and convenient. It provides a way to embed a variable, an array value, or an object property in a string with a minimum of effort.

    The complex syntax can be recognised by the curly braces surrounding the expression.

    Complex (curly) syntax

    This isn't called complex because the syntax is complex, but because it allows for the use of complex expressions.

    Any scalar variable, array element or object property with a string representation can be included via this syntax. Simply write the expression the same way as it would appear outside the string, and then wrap it in { and }. Since { can not be escaped, this syntax will only be recognised when the $ immediately follows the {. Use {\$ to get a literal {$. Some examples to make it clear:
    Lots of example worth looking at...

    http://php.net/manual/en/language.types.string.php
    Denny Schlesinger
    web services

  8. #8
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Another example:

    PHP Code:
    include "{$module_x}/myfile.php"
    instead of

    PHP Code:
    include $module_x "/myfile.php"
    I find it more legible.
    Denny Schlesinger
    web services

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by captainccs View Post
    Another example:

    PHP Code:
    include "{$module_x}/myfile.php"
    instead of

    PHP Code:
    include $module_x "/myfile.php"
    I find it more legible.
    Okay, thanks for the tip!


    Debbie

  10. #10
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,314
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    PHP Code:
        $dimensionSlug 'featured-' $sectionSlug;        //APPEND THINGS BEFOREHAND

        // ...

                
    AND sd.dimension_slug = ?

        
    // ...

        
    mysqli_stmt_bind_param($stmt1'ss'$sectionSlug$dimensionSlug);        //BINDING AS NORMAL 
    Agree or not?!
    Agree.
    "First make it work. Then make it better."

  11. #11
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    Agree.
    Cool, I have been "blessed" by the Master Coder!!!

    Thanks,


    Debbie


Bookmarks

Posting Permissions

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