...

View Full Version : Please help with substring query



treetops
06-19-2011, 12:01 AM
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,

Old Pedant
06-19-2011, 01:25 AM
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:


$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:


$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 )

treetops
06-20-2011, 02:44 PM
Thank you again. I used the LEFT function and it works a treat. Thanks for the help,



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum