Regex to select records from a db matching a predefined text prefix

I need to grab all records from a database table where the values in the option_name column begins with the prefix 'my_'

And store the results of that query in an array consisting of option_name, option_value pairs?

The database columns I need are called "option_name" and "option_value".

I'll use this array to execute some further processing in a for loop.

I'm guessing this would require 开发者_高级运维a regex in the SQL statement for the records where the option_name values match the target prefix.

For the SQL part, just use like with the % wildcard:

select * from YourTable where option_name like 'my|_%' escape '|'

Since _ is also a wildcard you have to escape it.

There's plenty of resources for reading the results of a SQL query in PHP: here's one.

If using a simple pattern such as "begins with", you can use the LIKE operator :

select *
from your_table
where option_name like 'my\_%';

Notes :

• % means "any character, any number of times".
• _ means "any character, one time" -- so, if you want to search for a literal '_', you have to escape it with a \.

Just for information, if you ever need to test for matches with a regex, you'll want to take a look at the Regular Expressions section of the manual.
But this is not necessary, here : like works fine for begins with.

$query = mysql_query("SELECT option_name, option_value FROM table WHERE option_name like 'my_%'");

while($result = mysql_fetch_array($query)){
$array = array("name" => $result['name'],"value" => $result['value']);
}