simple query statement

[CanMan2004]

Hi all

I have a sql database which stores peoples name in, for example

David Lawson
Harry Brickton

I want to do a query on this table, but just with the first 2 characters. For example, if I do a search for

Da

then it would return

David Lawson

and every other row which starts with Da.

There can also be numbers mixed in, for example

3D

The query I use is

[code]$sql = "SELECT * FROM db WHERE `firstname` = '".$firstname."' ORDER BY RAND()";[/code]

But I want to check just the first 2 characters of the field

firstname

Can this be done with ease?

Regards

Dave

[thepip3r]

$sql = "SELECT * FROM DB WHERE firstname LIKE '%Da%' DESC";

Change DA in the code to whatever variable you have that holds the first two letters of each queried name.

[HuggieBear]

[quote author=thepip3r link=topic=110591.msg447250#msg447250 date=1160064207]
$sql = "SELECT * FROM DB WHERE firstname LIKE '%Da%' DESC";
[/quote]

This isn't quite accurate.  This will correctly identify Someone with a name of narinda, or adam or anyone with 'da' in their name.

If you only want ones that start with da, then you want it like this:

[code]
$sql = "SELECT * FROM DB WHERE firstname LIKE 'Da%' DESC";
[/code]

Regards
Huggie

[thepip3r]

nice catch huggie.

[CanMan2004]

That would work but im inputting a name into a html form and then submitting it, it then takes the value ive entered, and uses that. So it could be

Da

or it could be

Ed

depending on what is entered, is there know way to do it by entering it the amount of characters into brackets, such as (0,2)

[roopurt18]

Assume the field name in the form is [b]search[/b].

[code]
<?php
  $Clean['search'] = ValidateSearch($_POST['search']);
  $sql = "SELECT * FROM <table> WHERE <field> LIKE '{$Clean['search']}%'";
?>
[/code]

[thepip3r]

[quote]Change DA in the code to whatever variable you have that holds the first two letters of each queried name. [/quote]

tha'ts why you need to change "DA" in the query statement to the variable name you assign the $_POST or $_GET value passed from your HTML search form.

[CanMan2004]

The problem is that in one of my queries searches for codes, such as

E2 3E
R5 2D
V2 B7

if I do a search for

E2 6Y

then it returns

E2 6Y
E22 4RB
E23 9U

and so on, I need to limit it, so that it just looks for the first 2 characters, as I wouldnt want

E22 4RB
or
E23 9U

I would only want

E2 6Y

returned as it only contains 2 characters before the space..

Does this explain it a little better?

Any help would be great

[CanMan2004]

Or would I need a whole new query to do that one?

[roopurt18]

Are you saying you only want it to accept the first two characters of what the user entered and only return entries that have those two characters followed by a space and then followed by other text?

[thepip3r]

you MIGHT be able to do this in SQL, I'm not sure because i'm more versed in PHP than I am in SQL but how I would accomplish what you were looking for is leaving the query statement as is.  Write all values that you get from the SQL query to an array then run each value through a loop and a conditional to check for the specific cases you're looking for.  Unfortunately it doesn't look like your database information is standardized and becaues of it, you could get varying results.... hopefully a SQL guru will look at this and give you a much simpler solution.

[CanMan2004]

yes, so that it only finds rows with 2 characters before the space and then anything following that.

It might want to chance it from 2 to 3 characters, maybe 4 too

Thanks

[roopurt18]

How are you determining how many characters to search before the space?

[roopurt18]

Two assumptions:

$limit holds the number of chars to search before the space
The form field with the search text is named 'search'.

[code]
<?php
$Clean['search'] = ValidateSearch($_POST['search']);
$Clean['search'] = substr($Clean['search'], 0, $limit) // Get first $limit chars
$sql = "SELECT ... WHERE <field> LIKE '{$Clean['search']} %'";
?>
[/code]