Adding Text to Existing DB Field

how can I append / prepend text to an existing field

[old_expat]

I have a small database with a few records, each with a 'reviews' field. Is there a realistic way to add something like:

<div class=someclass>
A hundred words or so of text ..
</div>

.. to the 'reviews' field without disturbing what is already there?

[Habtom]

I think the way to go is to select the previous review, take it out, add the new one, and update the field.

Something like the following:

$prev_rev = mysql_query("SELECT review FROM customers WHERE id = $id");
...
$new_review = $prev_rev['review']." ".$new_review;

$rev = mysql_query("UPDATE customers SET review = $new_review");
...

Habtom

[dreamcatcher]

No need for two queries Habtom. Try CONCAT [dev.mysql.com]

mysql_query("UPDATE table SET field = CONCAT(field,'New Data')")

dc

[Habtom]

Thanks dreamcatcher, I knew there could be easier way out, but didn't know how. It makes the code a bit more elegant. tnx

[old_expat]

Dreamcatcher .. thanks. That sounds easy enough that a dummy like me can get it done! ;)

[old_expat]

Um .. forgot something. I assume that the "new review" goes to the bottom. Right now the reviews are most recent first.

If I want the most recent reviews displayed first, is there a way to add the new review to the top of the field?

[Habtom]

It will be logical to put:

mysql_query("UPDATE table SET field = CONCAT(field,'New Data')")

This way:

mysql_query("UPDATE table SET field = CONCAT('New Data',field)")

One thing I noticed though is, to leave a gap in between the old and new one. ('New Data',field) gives an output like this: New DataOld. I hope you see what I mean.

Hab

[ergophobe]

Hmm... I'm just sort of guessing, but it sounds like you might want to think about your data architecture and parse this out into tables before you get multiple reviews in a single record.

It sounds like you have something like this:

TABLE BOOKS
- bookId
- author
- price
- title
- ISBN
- reviews

What you want is

TABLE BOOK
- bookId
- authorId
- price
- title
- ISBN

TABLE AUTHORS
- author_id
- lastname
- firstname

TABLE REVIEWS
- reviewId
- bookId
- reviewText

[old_expat]

Hi ergophobe,

This projects database is so small it really doesn't justify this .. I don't think.

Also, I wouldn't know how to write the queries.

That being said, I am re-starting a project that 2 coders have walked away from (the first walked away with my 50% deposit as well).

It potentially involves 'partners' and 'properties' and lots of info on each. I'll probably open a new thread just to get an idea of how many tables I will need and will also need to learn how to structure the queries.

Everyone, thanks for the help!:)

[ergophobe]

Are you sure that it will *stay* small? If not, one of the best things you can do for long-term flexibility and scalability is get the data definitions right.

Believe me, the DB that I mentioned in the other post I made recently, started out as a text file in 1988. When that got to be a hassle, it started to run on some free dB software because, you know, it was just a personal thing and not worth putting too much effort into.

Then I wanted to have some actions programmed in, so I put it into Access where I could use some Visual Basic for Applications. That was about 1997, and I knew better by then, but, hey, it would be so much work to split the data into tables and set up relations with a couple of thousand records (which would have been split into perhaps 20,000 records if I had done it properly then). There was no real grant money to do it, and I wasn't going to do it in my free time.

Then I started sharing the data and using a desktop app wouldn't really work, so it went online, but the data just got translated.

Now there's a couple of research projects that use it and depend on it, it runs at a university supercomputing center and oh, oh, oh, I wish that at some point I had rethought the data architecture, because now I think it would probably take someone a year of full-time work to set it up right. As it is now, there are serious limitations, because essentially, the data is organized exactly how it was when this was all just one smallish ASCII text file.

Sad story and perhaps someday I will see if I can get funding to fix it up, except that doing so would be incredible drudgery.