return mysql string using regex

Question

Here is my code:

$sql = "SELECT `description` FROM `auctions` WHERE `description` REGEXP '[0-9]{10}'";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
  echo $row["description"];
}

This returns the whole field description, I need to just get the part that matches the REGEXP, how do I do this using php?

You have to do that in plain PHP because, if you move the regex part into the select, it will only return `0` or `1`. [See the documentation](http://dev.mysql.com/doc/refman/5.0/en/regexp.html?iframe=true&width=100%&height=100%). (and you can see an example [here](http://sqlfiddle.com/#!2/7fa2b/2)) — j0k, Jan 22 '13 at 08:35
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://stackoverflow.com/a/14110189/1723893). — NullPoiиteя, Jan 22 '13 at 08:40

Barmar · Accepted Answer · 2013-01-22T21:32:07.247

1

$sql = "SELECT `description` FROM `auctions` WHERE `description` REGEXP '[0-9]{10}'";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    preg_match('/\d{10}/', $row["description"], $match);
    echo $match[0];
}

edited Jan 22 '13 at 21:32

answered Jan 22 '13 at 08:31

Barmar

741,623
53
500
612

MC Emperor · Answer 2 · 2013-01-22T08:48:52.333

Try this:

$results = array();
$sql = "SELECT `description` FROM `auctions` WHERE `description` REGEXP '[0-9]{10}'";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    // Checks for a match in the description and capture the match to $match
    preg_match("/[0-9]{10}/", $row['description'], $match);

    // Save the complete match (the ten digits) to the results array.
    $results[] = $match[0];
    // Or just echo them
    echo $match[0];
}

By the way, you also should take a look at prepared statements, inter alia to prevent SQL injections.

return mysql string using regex

2 Answers2