Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

simple query statement

Recommended Posts

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

Share this post


Link to post
Share on other sites
$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.

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
How are you determining how many characters to search before the space?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.