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 5 of 5
  1. #1
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts

    difficulty forming an array for a mysql query.

    Hi,

    In one of my queries, the resultset is being stored into an array. I thne process that array from
    Hostelries Dining Out Attractions

    into
    'Hostelries','Dining Out','Attractions'

    If I hard code that last line into my query, it works as expected. However, I need it to be dynamically created and so I ask for you help in building the correct array for the query (below).

    here is my attempt.
    Code:
    push(@business_types,$business_type);
      $_ = qq('$_') for @business_types;
      $business_types_to_exclude = join(',',@business_types);
    when I print that out, it does it like this:
    Code:
    'Hostelries','Dining Out'
    which I think is how it should be built (???)

    If I hard code that line into the query it works but it doesn't if I push it through a placeholder.

    so, this works
    Code:
    and bt.business_type not in ('Hostelries', 'Dining Out')
    but this doesn't

    Code:
     push (@business_types, $business_type);
    
     $_ = qq('$_') for @business_types;
     $business_types_to_exclude = join(',',@business_types);
    
    and bt.business_type not in (?) 
    
    $sth2->execute( $business_types_to_exclude
                    , $base_latitude_miles
                    , $base_longitude
                    , $base_radius
                    , $base_radius
                    , $base_radius
                    , $base_radius
                    , $base_radius
                    );
    It seems that the array is being compiled differently from how I expect so please, what am I missing?

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,840
    Thanks
    2
    Thanked 160 Times in 155 Posts
    This line is unnecessary.
    Code:
    $_ = qq('$_') for @business_types;
    What happens if you remove that line?

  • #3
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,840
    Thanks
    2
    Thanked 160 Times in 155 Posts
    This line should generate an error, if it's used as it is in your code snippet.
    Code:
    and bt.business_type not in (?)

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Hi Fishmonger

    if I remove the line
    Code:
    $_ = qq('$_') for @business_types;
    I get the error message

    Code:
    execute statement failed: Unknown column 'Accommodation' in 'on clause'
    But then, if having removed it, if I use the following execute statement, it seems to work.

    Code:
    $sth2->execute( @business_types_to_exclude
                    , $base_latitude_miles
                    , $base_longitude
                    , $base_radius
                    , $base_radius
                    , $base_radius
                    , $base_radius
                    , $base_radius
                    )or die "execute statement failed: $DBI::errstr\n";
    is that the correct way to do it?

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,840
    Thanks
    2
    Thanked 160 Times in 155 Posts
    Yes, that is the correct method.


  •  

    Posting Permissions

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