Jump to content

Refining MySQL Query To Look For First Letter & REGEX


kjetterman
Go to solution Solved by mac_gyver,

Recommended Posts

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.  

 

 

 

 

Link to comment
Share on other sites

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 by kjetterman
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.