Need to convert custom thread template to normal post

[singabaloo]

I have a (paid) Mod which I'm struggling to get any support for. It's causing conflicts with other Mods so I need to replace it.

The Mod adds 7 Columns in the thread table. When a thread in a particular forum is opened, the first post includes the data in the 7 custom thread table columns plus pagetext from the post table.

When I turn off the Mod, the data from the 7 thread columns disappears.

What I need to do, but don't know how, is to export the posts created by the mod (they are all in one forum) and convert them to a normal post so all the data displays with the Mod turned off. There will be another step to massage the formatting so it matches the new custom template I'll be using, but I reckon I can handle that myself (famous last words).

Has anyone done this sort of things before and has a script I can look at and hack at to do what I want ? Or is it a relatively simple script that someone could help me with ?

[singabaloo]

If this isn't the forum to post this request for help, which one should I post in ? I really don't want to lose all the threads created by the mod (ironically it's the first and only mod I actually paid for and now I find myself in a bind over it).

[kh99]

If I had to do that, I'd write a php script: For each thread, read the 7 columns from the thread table and the pagetext field for the first post (from the post table), and then update the post pagetext field with a string that includes the thread table data. It may be possible to do it totally with sql so you wouldn't need a script, but it seems easier to deal with in a php script (maybe just because I'm not an sql expert).

Your request is kind of in a "grey area" - we'll help you, but I think it's unlikely that anyone's going to take the time to write it for you (but maybe someone will prove me wrong).

[singabaloo]

Thanks for the advice. My php skills match my SQL skills which means this is going to be a long slog. But I'll give it a shot as I've got nothing to lose and plenty to gain.

I'm assuming there is a field in the thread table that correlates the first post in the thread to posted ? Once I've matched the two and pulled the 7 thread fields I should be able to do the rest without to much trouble, or at least trial and error.

[kh99]

Well, OK, I could probably get you started (if you haven't already). Are all these threads in a certain forum, or is there some way you can check to see which threads have the fields you're insterested in?

[singabaloo]

Thanks.

They are in one forum ( Forum ID 101 in this case) but if I'm going to create this script then I'll try and make it generic enough so that others who may need it can use it.

The mod adds the field "enable_recipe" in the forum table. "0" if it's not active, "1" if it is. If searching for this flag is just as easy as hardcoding the forum ID then I'll try the latter.

The Mod adds the following fields into the thread table:
recipe_time
recipe_description
recipe_prep
recipe_difficult
recipe_ingrediants
recipe_size
recipe_steps

recipe_description is the only mandatory field so if that field is blank then the thread isn't one created by the mod.

When it comes to coding, I'm good at modifying, hacking at someone elses work. Starting from scratch has always been beyond me (or my level of confidence at least)

[singabaloo]

I've managed to start a script that seems to read in the correct rows from the table I want. I didn't screw anything up which is a bonus.

Now some guidance (or if I should ask on another site because this is more general scripting advice I reckon more than VB specific)

I'm assuming everything I have selected is in memory. Should I write it to a file (if so how ?)

The next step is for me to add some formatting around the fields and combine them into one field.

So the end result would look like:

Ingredients

Fish
Chips
Vinegar

Cooking Time

15 mins

etc

How should I do this ? Massage the formatting of each field and then combine them or do it at the same time ?

Once that is completed, it I will need to insert the new field into the post table where firstpostid = postid but I need to Add my new formatted and combined field at the start of pagetext making sure I don't overwrite what is in there.

Here's the script so far. If there are any serious issues with it please let me know. I really don't want to blow up my database, even if it a test copy.

PHP Code:

<?php
/**
 * Here goes nothing
 *
 * Get the database connection details
*/
$user="";
$password="";
$database="";
$host="";
/*
 * Connect to the database or die
*/
mysql_connect($host,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
/*
 * Select all required fields rows in the 'thread' table that are not null
 * That didn't work as all rows where selected even if the field was empty.
 * Added AND recipe_ingrediants<>'' to the WHERE criteria
 * Result but Remember to mistype 'ingrediants'
*/
$query="SELECT title, firstpostid, recipe_time, recipe_description, recipe_prep,
recipe_difficult, recipe_ingrediants, recipe_size, recipe_steps FROM thread
WHERE recipe_ingrediants IS NOT NULL AND recipe_ingrediants<>''";
$result = mysql_query($query);
/*
 * Count and echo the number of rows just to make
 * sure the amount of data returned looks about right.
 */
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows\n </br>";
 /*
  * Print out the firstpostid and thread title just to prove that I got this right so far
  */
while($row = mysql_fetch_array($result))
  {
  echo $row['firstpostid'] . " " . $row['title'];
  echo "<br />";
  }
/*
 * close the database connection because I need to work out what to do next.
 */
mysql_close();
?>

[kh99]

First, sorry I didn't get to this sooner. I've got a lot of things going on and I guess I'm not very organized. Anyway, the first thing you can do is add to the query to get the pagetext from the first post:

$query="SELECT thread.title, firstpostid, recipe_time, recipe_description, recipe_prep,
recipe_difficult, recipe_ingrediants, recipe_size, recipe_steps, pagetext FROM thread
LEFT JOIN post ON (thread.firstpostid = post.postid)
WHERE recipe_ingrediants IS NOT NULL AND recipe_ingrediants<>''";

Next, you just need to build a string that formats the fields using bbcode, so just as an example:

$formatted = "[b]Ingredients[/b]
[INDENT]{$row['recipe_ingrediants']}[/INDENT]
[b]Cooking Time[/b]
[indent]{$row['recipe_time']}[/indent]
etc.

{$row['pagetext']}"; // This add the existing post at the end

Obviously you'll want to play with that. What I'd probably do is something like add "AND threadid IN(1, 2, 3)" to the query so you're only looking at one or a few to start with (change 1, 2, 3 to whatever threadids you want, of course). And maybe just echo it out to start with.


When you think it looks OK, you can write it to the db by adding a query:

"UPDATE post SET pagetext='" . mysql_real_escape_string($formatted) . "' WHERE postid = {$row['firstpostid']}";

(That goes inside the loop when you're done formatting, of course).


And also, at the same time you might want to remove any cached version of the post from the postparsed table so you'll be sure to see your changes right away:

"DELETE FROM postparsed WHERE postid = {$row['firstpostid']}"

Again, you might want to have the "AND threadid IN(X, Y, Z)" on the outer query when you try this, then you're only risking one thread until you make sure it's working.

[singabaloo]

Thanks and no need to apologise. I'm very thankful for the help.

Here's what I have now

PHP Code:

$query="SELECT thread.title, firstpostid, recipe_time, recipe_description, recipe_prep,
recipe_difficult, recipe_ingrediants, recipe_size, recipe_steps, pagetext FROM thread
LEFT JOIN post ON (thread.firstpostid = post.postid)
WHERE recipe_ingrediants IS NOT NULL AND recipe_ingrediants<>''";
$result = mysql_query($query);
/*
 * Count and echo the number of rows just to make
 * sure the amount of data returned looks about right.
 */
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows\n </br>";

while($row = mysql_fetch_array($result))
{
    $postid = $row['firstpostid'];
    $recipe = "[center][size=5][color=black]{$row['title']}[/color][/size][/center][size=5][color=black][/color][/size][color=black][/color]\r\n
        [b]Recipe Description[/b][indent]{$row['recipe_description']}[/indent]        [b]Preparation Steps:[/b][indent]{$row['recipe_prep']}[/indent]        [b]Level of Difficulty:[/b][indent]{$row['recipe_difficult']}[/indent]        [b]Time Needed:[/b][indent]{$row['recipe_time']}[/indent]        [b]Ingredients:[/b][indent]{$row['recipe_ingrediants']}[/indent]        [b]Serves:[/b][indent]{$row['recipe_size']}[/indent]";

    echo "<br /><b>Post ID:</b>" . $postid . "<br />". $recipe;
} 


The echo is there so I can see what it looks like. I had this laid out already using HTML tags but didn't even think to use BBCode instead. Formatting is I have is sufficient...

Except

Under Ingredients the contents of the field come out on a single line separated by a space. They should be a list. Is there anyway easy way to do that ?

I've attached a screenshot of phpmyadmin showing the format of the ingredients (and directions) filed that has the items separated by a cr (i think)

Once I can sort out this little bit of the formatting then I think I am good to try updated the posts table.

Again, thanks for your help.

[kh99]

I think it's just that when you echo the string it's used as html, and newline characters don't actually make a newline in html, they just get translated to a space. So it will probably be OK when it's displayed in a post (since a newline in bbcode becomes a <br />). But to see it in your test program maybe you could do something like this:

echo "<br /><b>Post ID:</b>" . $postid . "<br /><pre>". $recipe."</pre>";

Edit:...well, I guess if you put the entire recipe in <pre> tags you won't see any of the other formatting you added.