Jump to content


Photo

simple query statement


  • Please log in to reply
13 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 05 October 2006 - 04:01 PM

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

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

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

firstname

Can this be done with ease?

Regards

Dave

#2 thepip3r

thepip3r
  • Members
  • PipPipPip
  • Advanced Member
  • 289 posts

Posted 05 October 2006 - 04:03 PM

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

#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 October 2006 - 04:11 PM

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


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:

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

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#4 thepip3r

thepip3r
  • Members
  • PipPipPip
  • Advanced Member
  • 289 posts

Posted 05 October 2006 - 04:13 PM

nice catch huggie.

#5 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 05 October 2006 - 04:19 PM

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)

#6 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 October 2006 - 04:26 PM

Assume the field name in the form is search.

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

PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#7 thepip3r

thepip3r
  • Members
  • PipPipPip
  • Advanced Member
  • 289 posts

Posted 05 October 2006 - 04:37 PM

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


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.

#8 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 05 October 2006 - 04:48 PM

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


#9 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 05 October 2006 - 04:52 PM

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

#10 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 October 2006 - 04:55 PM

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?
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#11 thepip3r

thepip3r
  • Members
  • PipPipPip
  • Advanced Member
  • 289 posts

Posted 05 October 2006 - 04:57 PM

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.

#12 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 05 October 2006 - 04:59 PM

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

#13 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 October 2006 - 05:07 PM

How are you determining how many characters to search before the space?
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#14 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 05 October 2006 - 05:29 PM

Two assumptions:

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

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

PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users