Refining MySQL Query To Look For First Letter & REGEX

[kjetterman]

Hi!  

 

I'm fairly new to PHP & MySQL (from a programmer's perspective) and have just started seriously building my own applications using PHP.  I need some help bridging the gap with this MySQL query I have created that is to find only the organizations that have names that start with the letter 'A'.  The initial query that I have written does work.  However, I need to refine the query to "skip" over the first word -- example "The" so that I can get the complete correct results.

 

Here is my code:

include_once("dbconnect.php");

$linkA = "SELECT church_org FROM ics_data WHERE church_org LIKE 'A%' ORDER BY church_org ASC";
$queryA = mysql_query($linkA);
$dataA = array();

	while ($row = mysql_fetch_array($queryA) )
		array_push ($dataA, array('church_org' => $row[0]));
	
	echo json_encode(array("dataA" => $dataA));

This fetches every organization that starts with the letter 'A'.  However, some organizations have St. or The in front of their names.  So the query doesn't pick those up because it doesn't match what I have specified.

 

 

I have tried the following:

$linkA = "SELECT church_org FROM ics_data WHERE church_org LIKE 'A%' & TRIM('St. ', 'St ' church_org) ORDER BY church_org ASC";

This doesn't do what I wanted it to do and after further research, I believe it is because TRIM only trims the words specified from the end-result string.    I *think* I need to use REGEX to define a better pattern for the query but am still a little confused after reading the documentation on how to go about building that out.

 

How would I accomplish this?  I appreciate any help or guidance!

 

Hopefully, I have posted this in the right forum.  

 

 

 

 

[requinix]

Keep a separate column in the table for this kind of search (among other potential uses). Remove leading articles, remove punctuation, that kind of thing.

[CroNiX]

or use NOT LIKE to ignore those values, in combination with LIKE

[kjetterman]

Okay so, I had to completely re-work the way that I was thinking about grabbing the information from the database.  I got rid of the JSON for now.

 

Here is my new code:

if(isset($_GET['by'])){
$letter=$_GET['by'];

//My Query

$sql="SELECT contact_id, first_name, last_name, church_org FROM ics_data WHERE last_name REGEXP '^[a-zA-Z]'" . $letter . '^[a-zA-Z]';

What I am trying to do is have the results generated by the first letter of the last name.

 

If I query like this:

$sql="SELECT contact_id, first_name, last_name, church_org FROM ics_data WHERE last_name LIKE '%" . $letter . "%'";

It works, but will pick out the letter I click on and find said letter in any place within the last name.  So for example, I want the query to be able to correctly find the person with the last name Smith, when I click on the letter "S".  I do not want a person with the last name of Karns to be included when I click on the letter "S" simply because "S" is in the name somewhere.  If that makes sense?

 

I've read tons of documentation and have tried to get it to work but sadly cannot figure it out.

Any advice or direction would be much appreciated!!   TIA

Edited September 24, 2014 by kjetterman

[mac_gyver]

you would leave off the leading % wild-card. note: you can put php variables into a double-quoted " ... " string - 

$sql="SELECT contact_id, first_name, last_name, church_org FROM ics_data WHERE last_name LIKE '$letter%'";

[kjetterman]

Oh my goodness!!  Such a simple solution!  So, if I am understanding this correctly, then the leading % was causing the query to search the entire string for that letter, instead of just picking out the first letter..... right?

[CroNiX]

Yes, the % is a wild card.

[kjetterman]

 

you would leave off the leading % wild-card. note: you can put php variables into a double-quoted " ... " string - 

$sql="SELECT contact_id, first_name, last_name, church_org FROM ics_data WHERE last_name LIKE '$letter%'";

 

 

Thank you for your help with this!