470,619 Members | 1,860 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

help with Selecting Post Codes

I have a table containing names and addresses called tblNames and
constructed as below:

CREATE TABLE tblnames (
strNameReference varchar(6) NOT NULL default '',
strSurname varchar(35) default NULL,
strSchool varchar(75) default NULL,
strTitle varchar(25) default NULL,
strPassword varchar(15) default NULL,
strPostCode varchar(15) default NULL,
strEmail varchar(55) default NULL,
strPasswordHint varchar(35) default NULL,
PRIMARY KEY (strNameReference),
KEY strSurname (strSurname,strSchool,strTitle),
KEY strPostCode (strPostCode)
) TYPE=MyISAM COMMENT='Names Table';

The UK Post Code goes on the lines of "CB7 7TS" with a space between the
two parts of the post code.

If the following SELECT statement is used:

SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30

Then MYsql returns no records but no errors either and PHPMYAdmin states
that:

"Your SQL-query has been executed successfully"

Any help will be appreciated.

Thank you.
Stephen Layton


Jul 20 '05 #1
8 1197
st************@btinternet.REMOVESPAMcom wrote:
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason why
you would get zero rows is that you don't have a row in your table where
strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;
Jul 20 '05 #2
st************@btinternet.REMOVESPAMcom wrote:
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason why
you would get zero rows is that you don't have a row in your table where
strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;
Jul 20 '05 #3
Aggro

Many thanks for your reply following which, I have checked my Access
Code which takes the Access table and transfers it to a comma separated
text file. I've just found that it was adding a separate space in the
Post Code ... so it did not find the required field.

Thank you for your help..

In message <q%***************@read3.inet.fi>, Aggro
<sp**********@yahoo.com> writes
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason
why you would get zero rows is that you don't have a row in your table
where strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;


Stephen Layton
Jul 20 '05 #4
Aggro

Many thanks for your reply following which, I have checked my Access
Code which takes the Access table and transfers it to a comma separated
text file. I've just found that it was adding a separate space in the
Post Code ... so it did not find the required field.

Thank you for your help..

In message <q%***************@read3.inet.fi>, Aggro
<sp**********@yahoo.com> writes
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason
why you would get zero rows is that you don't have a row in your table
where strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;


Stephen Layton
Jul 20 '05 #5
You can use TRIM() to process input (or just update ) as well and change
postal code field length to 6.
It will save memory a bit.

Khazret Sapenov
"Stephen Layton" <st*****@stephenlayton.org> wrote in message
news:1w**************@stephenlayton.org...
Aggro

Many thanks for your reply following which, I have checked my Access
Code which takes the Access table and transfers it to a comma separated
text file. I've just found that it was adding a separate space in the
Post Code ... so it did not find the required field.

Thank you for your help..

In message <q%***************@read3.inet.fi>, Aggro
<sp**********@yahoo.com> writes
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason
why you would get zero rows is that you don't have a row in your table
where strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;


Stephen Layton

Jul 20 '05 #6
You can use TRIM() to process input (or just update ) as well and change
postal code field length to 6.
It will save memory a bit.

Khazret Sapenov
"Stephen Layton" <st*****@stephenlayton.org> wrote in message
news:1w**************@stephenlayton.org...
Aggro

Many thanks for your reply following which, I have checked my Access
Code which takes the Access table and transfers it to a comma separated
text file. I've just found that it was adding a separate space in the
Post Code ... so it did not find the required field.

Thank you for your help..

In message <q%***************@read3.inet.fi>, Aggro
<sp**********@yahoo.com> writes
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason
why you would get zero rows is that you don't have a row in your table
where strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;


Stephen Layton

Jul 20 '05 #7
You can use TRIM() to process input (or just update ) as well and change
postal code field length to 6.
It will save memory a bit.

Khazret Sapenov
"Stephen Layton" <st*****@stephenlayton.org> wrote in message
news:1w**************@stephenlayton.org...
Aggro

Many thanks for your reply following which, I have checked my Access
Code which takes the Access table and transfers it to a comma separated
text file. I've just found that it was adding a separate space in the
Post Code ... so it did not find the required field.

Thank you for your help..

In message <q%***************@read3.inet.fi>, Aggro
<sp**********@yahoo.com> writes
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason
why you would get zero rows is that you don't have a row in your table
where strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;


Stephen Layton

Jul 20 '05 #8
You can use TRIM() to process input (or just update ) as well and change
postal code field length to 6.
It will save memory a bit.

Khazret Sapenov
"Stephen Layton" <st*****@stephenlayton.org> wrote in message
news:1w**************@stephenlayton.org...
Aggro

Many thanks for your reply following which, I have checked my Access
Code which takes the Access table and transfers it to a comma separated
text file. I've just found that it was adding a separate space in the
Post Code ... so it did not find the required field.

Thank you for your help..

In message <q%***************@read3.inet.fi>, Aggro
<sp**********@yahoo.com> writes
I have a table containing names and addresses called tblNames and
constructed as below:


There is nothing wrong with the table or with the query. Only reason
why you would get zero rows is that you don't have a row in your table
where strPostCode is 'CB4 3DZ'.

I tested it like this:

# insert one test row to table
insert into tblnames values('x','x','x','x','x','CB4 3DZ','x','x');

# select rows where strPostCode matches
SELECT * FROM `tblnames` WHERE strPostCode like 'CB4 3DZ' LIMIT 0, 30;


Stephen Layton

Jul 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by emmah | last post: by
15 posts views Thread by Simon Gare | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.