asked on

Get matchs with regex

Hello all, I want to get the table names from a query, example below:

SELECT t1.`id`, t1.`title`, t2.`title` AS parent, t1.`description`, t1.`published`
FROM `mng_troubleshooter` AS t1
LEFT JOIN `mng_troubleshooter` AS t2 ON t1.parent=t2.id
ORDER BY t2.`title`, t1.`title`

I need regex where I put t1 and will get `mng_troubleshooter`, I know that I would get it by using the "AS t1" but can't get the correct regex for it :(

Thanks in advance!

Terry Woods

To clarify, do you want to swap all occurrences of t1 with `mng_troubleshooter`?

$string = 'SELECT t1.`id`, t1.`title`, t2.`title` AS parent, t1.`description`, t1.`published`
FROM `mng_troubleshooter` AS t1
LEFT JOIN `mng_troubleshooter` AS t2 ON t1.parent=t2.id
ORDER BY t2.`title`, t1.`title`';
 
$pattern = '/t1/';
 
$replacement = '`mng_troubleshooter`';
 
$new_string = preg_replace($pattern, $replacement, $string);
 
print $new_string;
 
############################################################
#Result is:
SELECT `mng_troubleshooter`.`id`, `mng_troubleshooter`.`title`, t2.`title` AS parent, `mng_troubleshooter`.`description`, `mng_troubleshooter`.`published`
FROM `mng_troubleshooter` AS `mng_troubleshooter`
LEFT JOIN `mng_troubleshooter` AS t2 ON `mng_troubleshooter`.parent=t2.id
ORDER BY t2.`title`, `mng_troubleshooter`.`title`

Open in new window

componentslab

ASKER

Nope, what I want is to get from there is mng_troubleshooter.

From the example query I posted, I would put in the regex t1 and the result would be mng_troubleshooter.

SELECT t1.`id`, t1.`title`, t2.`title` AS parent, t1.`description`, t1.`published`
FROM `mng_troubleshooter` AS t1
LEFT JOIN `mng_troubleshooter2` AS t2 ON t1.parent=t2.id
ORDER BY t2.`title`, t1.`title`

Terry Woods

Something like this then?

$string = 'SELECT t1.`id`, t1.`title`, t2.`title` AS parent, t1.`description`, t1.`published`
FROM `mng_troubleshooter` AS t1
LEFT JOIN `mng_troubleshooter2` AS t2 ON t1.parent=t2.id
ORDER BY t2.`title`, t1.`title`';
 
$aliasToFindTableFor = "t1";
$pattern = '/`?(\w+)`?\sas\s`?'.$aliasToFindTableFor.'`?/i';
$count = preg_match($pattern, $string, $matches);
 
$result = $matches[1];
print "Result: $result";

Open in new window

Terry Woods

ps: That's gotta be worth more than 50 points!

ASKER CERTIFIED SOLUTION

Terry Woods

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

componentslab

ASKER

I agree with the points :)

componentslab

ASKER

Worked like a charm, thanks a lot.