Help with a query

Updating a number in a string..

[nfs2]

Ok, so i have a blog, and the entry information is kept in a table called "journal_entries". In that table, i have a column called "entry_replies" which basically just counts the number of replies left for that entry.

By default, it is blank. Not zero, just blank. On update, i want to enter "1 reply". And when another comment is added, change it to "2 replies" and so on. The text "reply" and "replies" is important. I need it and can't leave it out.

Here's the non working code i've been trying

$result = mysql_query("SELECT entry_replies FROM journal_entries WHERE entry_id = '$entry_id' && journal_id = '$owner_id'");

if (!$result){

$reply_text = 'reply';
$value = '1 '.$reply_text.'';

$sql3 = "UPDATE $table2 SET entry_replies = '$value' WHERE entry_id = '$entry_id'";
mysql_query($sql3) or die(mysql_error());

} else {

$total = explode(',', $result);
$num_replies = $total[0];
$reply_text = $total[1];

if ($num_replies < 1){
$insert = 1;
} else if ($num_replies == 1) {
$insert = 2;
} else if ($num_replies > 1) {
$insert = ($num_replies +1);
}

$array = array($insert, $reply_text);
$value = implode(',', $array);

$sql3 = "UPDATE $table2 SET entry_replies = '$value' WHERE entry_id = '$entry_id'";
mysql_query($sql3) or die(mysql_error());

}

It doesn't work. If there is 1 reply, the column will have a value of 1, (no "replies" text, just the number one and a comma). Any update after that wont change the value of the column, it'll just stay at 1,

Any ideas?

[RedAndy]

Hi nsf2,

The problem you're having is really that your design is a little broken. It would make it much easier on you if you did one of two things ( I prefer the second )

1/ Made the reply counter field an int as you can then add to it as you want with a pretty simple query eg

UPDATE $table2 SET entry_replies = entry_replies+1;

then, when you want to write your data to screen you can append "reply" or "replies"

if ($entry_replies==1) {
$out="is $entry_replies reply";
} else {
$out="are $entry_replies replies";
}

echo "There $out";

2/ abandon this field until you know you need it. If you're storing the replies in the database as well then count them when you need to know how many there are. That way you save an update query with each new reply.

eg "SELECT COUNT(whatever_field) FROM reply_table WHERE journal_id=$journal_id";

Both of the above have the advantage of allowing you to easily see what your most popular topics have been as you can have your database order the data for you,

hth

Andy

[nfs2]

Thanks for your relply.

As i said in my post, i can't leave out the "reply" or "replies" text part. It seems i need to explan a bit.. lol

Well im calling the info from that column when making the link to the replies (so "1 reply" etc will be the link). By leaving out the replies text, and inserting it seperatly, a stray link "replies" will appear for every entry in the loop. There's no wat around it, and i dont want anything there.

Like i said, this column need the replies text, and needs to be blank (not zero, but just blank) if there are no replies.

Your second option wont work with my site either because i need to pull this value for the same loop, so i can get each "1 reply" "2 replies" etc link for each entry in the loop.

ooor, you can disregard everything i just said, and take my word for it that i need to do it this way.. lol

[RedAndy]

There's no wat around it, and i dont want anything there.

Post the code here, I just don't believe there's no way around it :o)

Your second option wont work

Sure it will, sounds like you want some links along the lines of
"Article One 12 replies"
"Article Two 3 replies"
"Article Three 0 replies"

Is that right? I see no reason why you can't get that data from your DB - it would probably help if we could see your DB schema,

Andy

[nfs2]

Ok here it is. On the journal page that lists the entries, the folowing code is excecuted

$result=mysql_query("SELECT * FROM journal_entries WHERE journal_id = '$entry' ORDER by entry_id DESC LIMIT $from, $max_results ");

while ($i = mysql_fetch_array($result))
{
$entries[] = $i;
}

include_once('template');
$tpl = new template();
$tpl->set('entries', $entries);

That sends the entry loop to the template. The template loop is as below

<div id="entries">
<loop:entries>
<table class="row1" >
<tr>
<td width="100%"><span class="entry_subject"><tag:entries[].entry_subject /></span></td><td nowrap="nowrap"><span class="entry_time"><tag:entries[].entry_time /></span></td>
</tr>
<tr>
<td class="entry_text" colspan="2"><span><tag:entries[].entry_text /></span></td>
</tr>
<tr>
<td width="100%"><span class="adminlinks"><tag:menu /></span></td><td nowrap="nowrap"><a href="entry?entry_id=<tag:entries[].entry_id />#replies"><tag:entries[].entry_replies /></a></td>
</tr>
</table>
<table>
<tr>
<td class="spacer">&nbsp;</td>
</tr>
</table>
</loop:entries>
</div>

So <tag:entries[].entry_replies /> will get the value of the entry_replies colum for the entry it is displaying.

[RedAndy]

So the problem is really the template language rather than php?

Never let a templating system define your data for you - get a better templating system instead :o) A major part of the point of templating systems is that they seperate data from presentation, this one seems to be failing you. I don't know the system you're using - the only templating system I've used is Smarty, which would let you do what I described pretty easily.

Beyond that I don't really have much to say that can help. Is there a forum for the templating system you're using? They might be able to help with the specifics of using it.

Is your database normalized or have you hacked other bits to try and fit the templating system as well?

[nfs2]

I dont know what the templating system is called. Its just a single file that is very fast and lightweight..

The following works though

$result=mysql_query("SELECT entry_id, journal_id, entry_subject, entry_text, entry_time, entry_replies FROM journal_entries WHERE journal_id = '$entry' ORDER by entry_id DESC LIMIT $from, $max_results ");

while ($i = mysql_fetch_array($result)) {

if ($i['entry_replies'] > 0){
$replies = $i['entry_replies'];
}
$entries[] = array('entry_id' => $i['entry_id'], 'journal_id' => $i['journal_id'], 'entry_subject' => $i['entry_subject'], 'entry_text' => $i['entry_text'], 'entry_time' => $i['entry_time'], 'entry_replies' => $replies );
}