SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question sql select statement errors!!

    I have an sql select query which is the following:

    $sql = mysql_query("SELECT max(Price), Make, Model, Price FROM Used_Stock WHERE Make='$manufacturer' AND Feed_Id IN ('$feed_ident') GROUP BY Model ORDER BY Price ASC");

    The $feed_ident is a variable which is set depending on which section of the site the user is in. The above query works fine if the variable is something like the following:
    $feed_ident='11192'

    The problem I have is that this variable will sometimes be more than one number ie:
    $feed_ident='11192', '12345', '978685'

    When this is the case the sql query fails.

    Can anyone tell me how I can write the query above so that regardless of whether the variable is one value or a range of values the query will work please.

    Thanks for any help in advance.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So, the variable is declared like this?*
    PHP Code:
    <?php
    #this
    $feed_ident "11192";

    #or
    $feed_ident "'11192, '12345', '978685'";
    *note the quoting
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It can be either. Problem is with the query set as it is it doesn't work. Any idea how I can get around this?

  4. #4
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I worked it out, thanks for the help with this though.

    I had to declare the variables like this:
    $feed_ident="'11102', '22896'";

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I would probably use a little function to help be build the SQL, it would allow for the input to be a little more forgiving.

    PHP Code:
    <?php
    function get_feed_sql($feeds ''){
      
    $ids null;
      
      
    preg_match_all('~([0-9]+)~'$feeds$matches);
      
      if(
    false === empty($matches[0])){
        
    $ids implode("', '"$matches[0]);
      }
      
      return 
    sprintf("SELECT foo FROM table WHERE id IN ('%s');"$ids);
    }
    PHP Code:
    <?php
    $feeds 
    '';
    echo 
    get_feed_sql($feeds);
    /*
     SELECT foo FROM table WHERE id IN ('');
    */
    PHP Code:
    <?php
    $feeds 
    '12345';
    echo 
    get_feed_sql($feeds);
    /*
     SELECT foo FROM table WHERE id IN ('12345');
    */
    PHP Code:
    <?php
    $feeds 
    12345;
    echo 
    get_feed_sql($feeds);
    /*
     SELECT foo FROM table WHERE id IN ('12345');
    */
    PHP Code:
    <?php
    $feeds 
    '12345,67890';
    echo 
    get_feed_sql($feeds);
    /*
     SELECT foo FROM table WHERE id IN ('12345', '67890');
    */
    PHP Code:
    <?php
    $feeds 
    '  12345,   67890  ';
    echo 
    get_feed_sql($feeds);
    /*
     SELECT foo FROM table WHERE id IN ('12345', '67890');
    */
    Does that make much sense? Hopefully you can see how only sets of numbers are passed to the SQL string and anything else is filtered out.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    377
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for the help, I'll look into using this on the site.


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
  •