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.
ESCAPE clause right for portability. Backslash escapes are the default in MySQL, but are not part of the ANSI SQL standard.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']);
}
