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 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2010
    Posts
    31
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Please help with substring query

    Hello, the following code works fine, comparing a CSV field 'city_preferred' with data from field 'Name' in my ' Town' database table.

    // Get TownID and CountyID from table 'Town' - Works fine
    $tt = @mysql_row("SELECT ID,CountyID FROM Town WHERE Name = '".$data['city_preferred']."' OR Postcode = '".$hotel['Postcode']." LIMIT 1'");

    if ($tt['ID'])
    {
    $hotel['TownID'] = $tt['ID'];
    $hotel['CountyID'] = $tt['CountyID'];
    }

    The problem is, if we have two towns with the same Name in my database, e.g Bradford in Yorkshire, and Bradford in Cornwall.

    To eliminate this problem I was hoping to do something like the following, so that it not only compares town names, but also compares the first 2 characters in my Town tables 'Postcode' field, with the 'zip' field in the CSV (which also contains postcodes):

    // Get TownID and CountyID from table 'Town', plus substring to compare part of postcodes - Not working.

    $tt = @mysql_row("SELECT ID,CountyID FROM Town WHERE Name = '".$data['city_preferred']."' AND SUBSTRING( Postcode, 1, 2 ) = SUBSTRING( '{".$data['zip']."}', 1, 2 ) LIMIT 1'");

    As I am not a programmer, please can anyone tell me if something is wrong with the code, as it really looks to me like it should be working

    Thanks in advance for any help,

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    No, the { and } in there are bogus. And you have an extra apostrophe after the LIMIT 1

    But in any case, it's helpful to do a little debugging:
    Code:
    $sql = "SELECT ID,CountyID FROM Town " .
         . " WHERE Name = '".$data['city_preferred']."' "
         . " AND SUBSTRING( Postcode, 1, 2 ) = SUBSTRING( '".$data['zip']."', 1, 2 ) " 
         . " LIMIT 1";
    echo "DEBUG SQL: " . $sql . "<hr>\n";
    $tt = @mysql_row( $sql )
    Then, if it doesn't work, you look at the debug output and copy/paste the query into a MySQL tool and execute it there and hopefully get a better error message.

    Incidentally, MySQL has a LEFT( ) function that is more convenient that SUBSTRING:
    Code:
    $sql = "SELECT ID,CountyID FROM Town " .
         . " WHERE Name = '".$data['city_preferred']."' "
         . " AND LEFT( Postcode, 2 ) = LEFT( '" . $data['zip'] . "', 2 ) " 
         . " LIMIT 1";
    echo "DEBUG SQL: " . $sql . "<hr>\n";
    $tt = @mysql_row( $sql )

  • Users who have thanked Old Pedant for this post:

    treetops (06-20-2011)

  • #3
    New Coder
    Join Date
    Aug 2010
    Posts
    31
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thank you again. I used the LEFT function and it works a treat. Thanks for the help,


  •  

    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
    •