How to replace "[[5]]" with MySQL row 5 data

I have a MySQL table of image URLs/titles/subtitles with IDs 1-150, and I want to be able to dynamically insert them into the text of my pages with shortcodes like this:

Blabla bla bla bla bla. [[5]] Also, bla bla bla bla bla [[27]] Hey, and bla bla bla! [[129]]

So, I just want to grab the ID as $id, and then feed it to a MySQL query like mysql_query(“SELECT title,subtitle,url FROM images WHERE id = $id”) and then replace the “[[id]]” with the img/title/subtitle. There can be multiple shortcodes per page.

I know this has to involve regex and functions like preg_match, preg_replace, strstr, strpos, substr, maybe preg_replace_callback… but I don’t know which functions I should be using to do which things. How should I do this?

The regex, I believe, would be:

/[[[1-9]{1,3}]]/g

(for a 1-to-3 digit number inside double brackets.)

Edit: Regexp will match any digit, regardless of the length.



$str = <<<EOF
This image [[1]] is going to be shown together with this image [[124]]
EOF;

preg_match_all("#\\[\\[([\\d]+)\\]\\]#", $str, $matches);

var_dump($matches);

That should give you what to pull out of the database.
Now to replace it, let’s assume you have an array constructed so that the id points to the actual image location, something like:



$images[1] = 'http://localhost/image_1.jpg';
$images[124] = 'http://localhost/image_124.jpg';

$str = preg_replace("#[\\[([\\d]+)\\]\\]#e", '$images[\\\\1]', $str);


It might not be what you’re looking for exactly, but it should put you on the right track. If you got any questions, feel free to ask :slight_smile:
And happy new year!

$text = "Blabla bla bla bla bla. [[5]] Also, bla bla bla bla bla [[27]] Hey, and bla bla bla! [[129]]";

preg_match_all('#\\[\\[(\\d{1,3})\\]\\]#', $text, $matches );

var_dump( $matches[1] );

From that array of matches you can build an sql query:


echo "select from stuff where id in (" . implode($matches[1], ",") .")" ;

That gives you a single select which will get all the image data you need for this page:

eg


SELECT URL
, title
, subtitle 
FROM images where id in (5,27,129)

But you still then have to replace all your tags [[xxx]] with urls, I presume.

Edit:

Ahh, bit slow replying there, and a slightly different tack to furicane, but between the two it should get you on track.

Thanks, furicane and Cups! This is really helpful.

For replacing the [[xxx]], will this line from furicane:

$str = preg_replace(“#[\[([\d]+)\]\]#e”, ‘$images[\\1]’, $str);

just replace all of the []s with the URL for image 1? How do I make it replace the right [] with the right $image?

(One other thing: What are <<<EOF and EOF;?)

Your sql result should come back as a multi array of elements.

Here is simulated array.


$results = array(
array(

'id' => 5, 
'url'  => 'url1',
'title' => 'title1' ),

array(
'id' => 27, 
'url'  => 'url2',
'title' => 'title2' ),

);

I suppose you’ve then got to get them into a format that you can use as in furicanes example:


$images[1] = 'http://localhost/image_1.jpg';
$images[124] = 'http://localhost/image_124.jpg';

I’m not much on templating myself, so will shut up now.

When all said and done, you may well want to look at caching the html output of this operation for short periods, because this script could be doing a lot of work.

This is so that your CMS users can use pseudo BB tags like [[55]] when writing an article is it?

Then what are you doing, showing the image inline or providing a link to an image?

If the latter then you might want to consider doing this final text replacement in jQuery or similar, all depends on your situation of course.

It’s a way of working with strings known as heredoc

Since Cups and SpacePhoenix covered majority of the questions, I’ll just explain what the regexp you quoted means.

Regexp will attempt to match digits using the pattern, and then it will attempt to replace the pattern with php array that you construct.
The array should have keys that correspond to the id of the image you’re trying to pull out of the database.
Example is always the best - if you have $image[1] and you have string [[1]] that you want to replace with the image - regexp will match number 1 and then it’ll use the $image array with key 1.
That’s the ‘e’ modifier in the expression.

I hope that covers your question, I won’t get into templating and methods of such :slight_smile:

For the replacement text, I want to include something flexible/involving multiple columns of the row x data. Something like this:

Original string:
Let me tell you about my fifth picture.
[[5]]
Isn’t that interesting?

Replacement string:
Let me tell you about my fifth picture.
<b>$title[5]</b>: $subtitle[5]
<img src=“$url[5]”>
<a href=“$link[5]”>Link</a>
Isn’t that interesting?

Is this still doable with these methods? Or—perhaps I should be using preg_replace_callback?

If so, all that I have left to figure out is

a) how to refer to the items (url, title, subtitle, link) from the $results array (e.g., is it $url[5] or $results[$url][$id] or something else); and

b) how to include all of that in the replacement string. I can’t seem to include anything complicated in the second parameter of the preg_replace(“#\[\[(\d{1,3})\]\]#e”, ‘$images[\\1]’, $text) function, which is what lead me to think maybe a callback would be necessary.

I am using this to show charts and their associated links/downloads.

I think I’m almost there… Here is what I have so far, which succeeds in finding the [[id]]s and replacing the [[id]]s with the same chart, but I am missing whatever I need to make it replace each [[id]] with its own unique chart.


function getchart($zzid) {
if ($zzid !='') {
	$result = mysql_query("SELECT download_image_file_path,download_file_file_path,title,subtitle FROM charts WHERE id = '$zzid'");
	if (!$result) {
    	echo 'Could not run query: ' . mysql_error();
    	exit;
	}
$row = mysql_fetch_row($result);

$replacement = '<!--[[' . $zzid . ']] chartbegin--><div class="chart"><a href="/beta/charts/view/' . $zzid . '"><img src="/beta/files/images/med/' . $row[0] . '"></a><div class="excel"><a href="/beta/files/files/' . $row[1] . '">Download Excel data</a></div></div> <!-- chartend -->';

return $replacement;
}
else {
	return "No chart ID was specified.";
}
}


$text = "Blabla bla bla bla bla. [[5]] Also, [[111]] bla bla bla bla bla [[27]] Hey, and bla bla bla! [[129]]";

$zpreg = preg_match_all('#\\[\\[(\\d{1,3})\\]\\]#', $text, $matches );

var_dump( $matches[1] );  


   $newtext = preg_replace('#\\[\\[(\\d{1,3})\\]\\]#', getchart(??????), $text);


What do I have to do to make it replace [[5]] with getchart(5), [[127]] with getchart(127), etc?

(This is probably all hideous code… I am new at this.)

You need the e modifier in preg_replace to make it evaluate (using eval()) the new code instead of just outputting it:


$newtext = preg_replace('#\\[\\[(\\d{1,3})\\]\\]#e', 'getchart(\\\\1)', $text);

Also see example #4 here http://nl3.php.net/manual/en/function.preg-replace.php