Search mysql with wildcard and put results into php array

Question

Say I have appearances of this is the RED color, this is the BLUE color, this is WHATEVER color as parts of the different sentences they belong to, in different cells of the column column in the table table of my mySQL database, or in the same row if this table.

----------------------------------------------------------------------------------------
| column                                                                               |
----------------------------------------------------------------------------------------
| Look, this is the red color which is is not a nice one.                              |
----------------------------------------------------------------------------------------
| And this is the blue color, I like it more.                                          |
----------------------------------------------------------------------------------------

How could I perform the search in the database for this is the % color for exact match and put the results into PHP array, so that I could output the array exactly like

this is the RED color

this is the BLUE color

this is WATEVER color

skipping all other parts of the respective sentences? I mean I need to put into the array only every appearance of this is the CURRENT_WILDCARD_VALUE color and nothing more.

So far I have the following PHP code

global $wpdb;
$query = $wpdb->get_results( "SELECT * FROM table WHERE row LIKE '% this is the % color %'");
print_r($query);

which returns tons of extra data while I need it to return only my search term keeping in mind the wildcard it uses.

You could use like or regexp. Do you want to only allow one series of alpha characters between the and color? If so I think regexp 'this is the [a-zA-Z]+ color would do it. If color is always caps take out a-z (the mysql regexp might be case insensitive too so maybe it didnt matter) — chris85
– chris85, Commented Feb 11, 2017 at 23:29
Extended latin alphabet with punctuation marks and brackets (square and round) would be quite enough or what did you mean? The result might easily be this it the LIGHT BLUE color. The problem is I also have no idea how to create the query and put the results into php array. — Pimpfru
– Pimpfru, Commented Feb 11, 2017 at 23:34
What do you currently have? Explaining how to query and fetch is a bit broad.. — chris85
– chris85, Commented Feb 11, 2017 at 23:40
Can you update the question with what the db has and what you want the PHP to get? — chris85
– chris85, Commented Feb 11, 2017 at 23:59
That's the query, can you show an actual column's value and what you want out of it? — chris85
– chris85, Commented Feb 12, 2017 at 0:19

Detritus · Accepted Answer · 2017-02-12 01:02:57Z

1

Once you have selected your values from MySQL. Pass each record value through preg_match(). Something roughly like the following:

$myValue = 'Look, this is the red color which is is not a nice one.'; // Value from MySQL
$matches = array();
preg_match ('/this is the (.+) color/', $myValue, $matches);

The value of $matches should be:

array (
  0 => 'this is the red color',
  1 => 'red',
)

If you then want to keep a set of unique values (e.g. red) the easiest is to to store each $matches[1] as an array key. E.g.

$myColors = array(); // Put this before record iterator
$myColors[$matches[1]] = $matches[0];

Which will produce an array of:

array (
  'red' => 'this is the red color',
  'blue' => 'this is the blue color',
)

answered Feb 12, 2017 at 1:02

Detritus

3571 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pimpfru Over a year ago

How could I fill $myValue?

Detritus Over a year ago

I'm not sure what wpdb->get_results returns but I assume it is a list of results. So you could do something like: foreach ($wpdb->get_results() as $myValue) { put stuff in here> }

Pimpfru · Accepted Answer · 2017-02-13 15:35:32Z

Finally I've ended up with the following code that seems to meet my expectations:

global $wpdb;

// setting up the database search term
$srchtrm = "green";

// querying WP database for matches and putting their parent cells into array
$query = $wpdb->get_results("
    SELECT post_content
    FROM wp_posts
    WHERE post_content
    REGEXP 'This is the $srchtrm color'
    ");

// simplifying the query resulting array
foreach ($query as $value) {$simplified_array[] = $value->post_content;}

// avoiding php's Warning: implode(): Invalid arguments passed in
if (empty($simplified_array)) {}
    // converting the simplified array into a single text string
    else {$textstring = implode(" ", $simplified_array);}

// searching the above text string for matches and putting them into array
preg_match_all('/This is the '.$srchtrm.' color/', $textstring, $matches);

// removing repeating items in simplified array
$simplifiedmatches = array_unique($matches[0]);

// sorting the simplified matches array asc
sort($simplifiedmatches);

// outputting values
foreach($simplifiedmatches as $match) {echo $match.'<br />';}

I've strictly set green as the $srchtrm value in this example, but $srchtrm is actually a dynamic variable so everything is ok and I'm not trying to create an array of only one key.

Is everything alright with the code or may be some hidden (for me) errors exist?

Collectives™ on Stack Overflow

Search mysql with wildcard and put results into php array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related