Jump to content

simple query statement


CanMan2004

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
Link to comment
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
Link to comment
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)
Link to comment
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.
Link to comment
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
Link to comment
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.
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.