Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0

    MySQL, Dynamic Query String, Single Quote ERROR, HELP!!


    Hello,
    First off I would like to thank everyone in advance for your help.

    I am dynamically generating this sql query from several variable inputs and concatenating them together before the query is run. All seems well, then I hit a roadblock.

    The issue:
    When the query is run in the browser I get zero results when in fact that same query returns results when run via navicat, phpmyadmin, etc... The only difference is that I have to replace the single quotes that encapsulate the variable integers because for what ever reason they dont work the way they are rendered in the browser. If i don't use single quotes around the integers the query returns no result either way. e.g.

    Browser Rendering: (not working)
    SELECT s.id, s.image, s.order_number, s.equivalent_price, s.custom_link_url, s.sbc_onboard_wireless, s.sbc_onboard_storage, s.sbc_onboard_graphics, c.companies_long_company_name, c.companies_logo_thumb_name, p.family_family, p.family_title, f.factor, a.application, os.name FROM ed_sbc AS s LEFT JOIN ed_companies AS c ON(s.mfg_id = c.id) LEFT JOIN ed_processor_family AS p ON(s.proc_family_id = p.id) LEFT JOIN ed_sbc_form_factors AS f ON(s.form_factor = f.id) LEFT JOIN ed_sbc_applications as a ON(s.application = a.id) LEFT JOIN ed_sbc_os as os ON(s.sbc_os = os.id) WHERE
    Code:
    ((c.companies_intel_alliance = ’1′ AND c.companies_active = ’1′) AND (c.id = ’689′) AND (s.form_factor = ’1′) AND (p.family_family LIKE ‘%atom%’))


    Direct Query (Working):
    SELECT s.id, s.image, s.order_number, s.equivalent_price, s.custom_link_url, s.sbc_onboard_wireless, s.sbc_onboard_storage, s.sbc_onboard_graphics, c.companies_long_company_name, c.companies_logo_thumb_name, p.family_family, p.family_title, f.factor, a.application, os.name FROM ed_sbc AS s LEFT JOIN ed_companies AS c ON(s.mfg_id = c.id) LEFT JOIN ed_processor_family AS p ON(s.proc_family_id = p.id) LEFT JOIN ed_sbc_form_factors AS f ON(s.form_factor = f.id) LEFT JOIN ed_sbc_applications as a ON(s.application = a.id) LEFT JOIN ed_sbc_os as os ON(s.sbc_os = os.id) WHERE
    Code:
    ((c.companies_intel_alliance = '1' AND c.companies_active = '1') AND (c.id = '689') AND (s.form_factor = '1') AND (p.family_family LIKE '%atom%'))


    Notice, the only difference between the two (Highlighted) is the way the single quotes are rendered. The first one has 3 or 4 different renderings of the single quote where the second query is a consistant straight single quote. my files are utf8_unicode_ci encoded. No matter what I have tried I cannot get the browser to render the quotes any differently. HELP!!!

    Any and all advise / help is much appreciated.
    J
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    definitely this is a quotes issue

    what language is generating the browser code? php?

    because that's the forum you'll have to have this question moved to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    definitely this is a quotes issue

    what language is generating the browser code? php?

    because that's the forum you'll have to have this question moved to
    Yup its PHP, I am very new to this forum. Should I copy and paste to the PHP section or how would I go about moving the thread?

    Thanks Again.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by JustinRoy
    Yup its PHP
    i moved it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,493
    Rep Power
    594
    Please post your PHP code and be sure to use [ PHP ] tags. See the sticky at the top of this forum.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    we need to see the PHP code.

    But I'm pretty sure the problem is that you're using an editor not suitable for programming. A straight single quote is a specific character (ASCII/UTF-8 code 0x27), either it is in your source code or it's not in your source code. If it's not in the code and instead you have other quotes, then something must have put them there -- and that's most likely your editor. Word processors like Word or OpenOffice.org do character subsitutions like that.
    The 6 worst sins of security • How to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,

    we need to see the PHP code.

    But I'm pretty sure the problem is that you're using an editor not suitable for programming. A straight single quote is a specific character (ASCII/UTF-8 code 0x27), either it is in your source code or it's not in your source code. If it's not in the code and instead you have other quotes, then something must have put them there -- and that's most likely your editor. Word processors like Word or OpenOffice.org do character subsitutions like that.
    Hello,
    Thanks for the reply, I am using Coda2 and TextMate with the same results. here is a sample of the code.

    PHP Code:
    $query_Product "SELECT s.id, s.image, s.order_number, s.equivalent_price, s.custom_link_url, s.sbc_onboard_wireless, s.sbc_onboard_storage, s.sbc_onboard_graphics, c.companies_long_company_name, c.companies_logo_thumb_name, p.family_family, p.family_title, f.factor, a.application, os.name  FROM ed_sbc AS s LEFT JOIN ed_companies AS c ON(s.mfg_id = c.id) LEFT JOIN ed_processor_family AS p ON(s.proc_family_id = p.id) LEFT JOIN ed_sbc_form_factors AS f ON(s.form_factor = f.id) LEFT JOIN ed_sbc_applications as a ON(s.application = a.id) LEFT JOIN ed_sbc_os as os ON(s.sbc_os = os.id)";

    if(
    $_POST['all_alliance'] == 'all'){
        
    $alliance_string "";
    }elseif(isset(
    $_POST['alliance'])){
        unset(
    $_POST['all_alliance']);     
        
    $alliance_string " AND (";
        
    $alliance_array $_POST['alliance'];
        
    $alliance_values explode(", "$alliance_array);
        foreach(
    $alliance_array as $aa){
            if(
    $aa == $alliance_array[0] && $aa == end($alliance_array)){
                
    $alliance_string .= "c.id = '".$aa."')";
            }
            elseif(
    $aa == $alliance_array[0] && $aa !== end($alliance_array)){
                
    $alliance_string .= "c.id = ".$aa."";
            }elseif(
    $aa !== $alliance_array[0] && $aa !== end($alliance_array)){
                
    $alliance_string .= " OR c.id = ".$aa."";
            }elseif(
    $aa !== $alliance_array[0] && $aa == end($alliance_array)){
                
    $alliance_string .= " OR c.id = ".$aa.")";
        
            }}
        
    }
    if(
    $_POST['all_processors'] == 'all'){
        
    $processor_string " AND (p.family_title LIKE '%Intel%')";
    }elseif(isset(
    $_POST['processor'])){
        unset(
    $_POST['all_processors']);     
        
    $processor_string " AND (";
        
    $processor_array $_POST['processor'];
        
    $processor_values explode(", "$processor_array);
        foreach(
    $processor_array as $pa){
            if(
    $pa == $processor_array[0] && $pa == end($processor_array)){
                
    $processor_string .= "p.family_family LIKE '%".$pa."%')";
            }
            elseif(
    $pa == $processor_array[0] && $pa !== end($processor_array)){
                
    $processor_string .= "p.family_family LIKE '%".$pa."%'";
            }elseif(
    $pa !== $processor_array[0] && $pa !== end($processor_array)){
                
    $processor_string .= " OR p.family_family LIKE '%".$pa."%'";
            }elseif(
    $pa !== $processor_array[0] && $pa == end($processor_array)){
                
    $processor_string .= " OR p.family_family LIKE '%".$pa."%')";
        
            }}
        
        

    }
    if(
    $_POST['all_factors'] == 'all'){
        
    $factor_string "";
    }elseif(isset(
    $_POST['form_factor'])){
        unset(
    $_POST['all_factors']);     
        
    $factor_string " AND (";
        
    $factor_array $_POST['form_factor'];
        
    $factor_values explode(", "$factor_array);
        foreach(
    $factor_array as $fa){
            if(
    $fa == $factor_array[0] && $fa == end($factor_array)){
                
    $factor_string .= "s.form_factor = '{$fa}')";
            }
            elseif(
    $fa == $factor_array[0] && $fa !== end($factor_array)){
                
    $factor_string .= "s.form_factor = '".$fa."'";
            }elseif(
    $fa !== $factor_array[0] && $fa !== end($factor_array)){
                
    $factor_string .= " OR s.form_factor = '".$fa."'";
            }elseif(
    $fa !== $factor_array[0] && $fa == end($factor_array)){
                
    $factor_string .= " OR s.form_factor = '".$fa."')";
        
            }}
        
        }

    $property_sort_string $alliance_string $factor_string $processor_string $application_string $os_string;
    $where_string " WHERE ((c.companies_intel_alliance = '1' AND c.companies_active = '1')";

    $query_Product .= $where_string $quick_sort_string $property_sort_string ")"
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,493
    Rep Power
    594
    As I said please use [ PHP ] tags like the sticky shows. You code is too hard to read. Edit your post, highlight the properly formatted code then click the php icon.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by gw1500se
    As I said please use [ PHP ] tags like the sticky shows. You code is too hard to read. Edit your post, highlight the properly formatted code then click the php icon.
    Done, FYI the php icon adds [ PHPNET="" ] [ /PHPNET ] and thats why it looked funny, sorry.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,493
    Rep Power
    594
    Did you echo $query_Product and does it contain what you expect? What do you get that is wrong?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by gw1500se
    Did you echo $query_Product and does it contain what you expect? What do you get that is wrong?
    yes the variable contains the proper string but the quotes are not rendering properly. if you notice at the top of the page I have the two examples the first has italicized quotes around the integers and the second, "working" has straight single quotes. Everything else is perfect I just cant figure out why when php renders the string it adds those funny quotes.

    When I echo the string, copy, and paste it into NaviCat, phpMyAdmin, I have to replace those quotes to get the query to work.

    Hope that helps...
  22. #12
  23. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,549
    Rep Power
    2337
    php renders the string it adds those funny quotes
    If I'm understanding this correctly...

    It's not PHP that's render the quotes that way...it's how your browser's font is rendering them! It's got nothing to do with PHP.

    If you are looking to print this query in a way that is readily copy/pasteable from a web page I might suggest either wrapping the output in "pre" tags or using CSS to render the containing element with white-space:pre with a monospace font.
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by ptr2void
    If I'm understanding this correctly...

    It's not PHP that's render the quotes that way...it's how your browser's font is rendering them! It's got nothing to do with PHP.

    If you are looking to print this query in a way that is readily copy/pasteable from a web page I might suggest either wrapping the output in "pre" tags or using CSS to render the containing element with white-spacere with a monospace font.
    Thanks for the reply, but i'm not trying to print it necessarily I just want the query to work and it is not currently. I echo the string so I can see what is going on.

    If the query acted the same via the browser as it does when submitted directly I would understand. But there is something else happening here that I cannot debug. One returns results (directly) One does not (the browser).

    I can however return results using different combinations of variable input via the browser. So it's not that I am unable to return any data, just that this specific query is not working via the browser, and leads me to believe that I may not be getting 100% accurate results with other queries as well.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,493
    Rep Power
    594
    If the browser is not rendering your echo properly then you can't really tell what the query ultimately looks like. Use the <pre> tags, as ptr2void suggested, to see what it looks like then.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  28. #15
  29. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,549
    Rep Power
    2337
    I guess I'm just confused. You were going on about quotes, but that appears to be strictly a rendering issue and is not germane to the query not working.

    So where is your actual SQL call in PHP? Is it failing? If so, what error is reported?
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo