updating row but keeping existing data intact

[Walker33]

I apologize in advance if this is a ridiculously simple question, but I can NOT find the answer anywhere and everywhere.  I'm attempting to use update to add additional data to a row, but I don't want to change the data in there.  Obviously, update eliminates what's there and inserts the new data.

 

<?php
$striplic = substr($isolate,0,9);
echo "$striplic<br><br>" ;

if($no_station=='addon_stn_1'){
if ($stn1_type =='scopist'){
$que = pg_query("SELECT sublicenses FROM sublicenses WHERE license = '$striplic'");
$getarr = pg_fetch_assoc($que);
$licen = $getarr['sublicenses'];

$string="$licen";
$domain3=strrchr($string,"S");

$string2=split(",",$domain3);
print_r ($string2);
$res = $string2['0'];
echo "<br><br>$res";

$number = (int) $res[2];
$new_number = $number+1;
$res2 = substr_replace($res, $new_number, 2);
echo "<br><br>$res2";
//Adding sub for new scopist
$intosubs2 = pg_query ("UPDATE sublicenses SET sublicenses = '$res2' WHERE license = '$striplic' ");
echo "<br><br>should be a new one in subs";
exit;
?>

 

As always, any help is greatly appreciated!

[roopurt18]

So what's the problem?

[Walker33]

Problem is there is already a bunch of data in the row, and I need to keep that in there.  I just need to add the new data from the $res2 variable.  Using UPDATE eliminates existing data and replaces it with $res2 instead of just adding the $res2 data.

[PFMaBiSmAd]

It is extremely inefficient to manipulate a comma separated list in a column. It requires more php support code and the actual queries are more complicated to write and take longer to execute. All of your threads on this subject could have been solved easier if your table had a separate row for each sublicenses.

[roopurt18]

A) What's in the column before you update it?

 

B) What's in the column after you update it?

 

C) What do you expect to be in the column after you update it?

 

Answer those and maybe we can understand better what you're asking, because right now it doesn't make a whole lot of sense.

[Walker33]

Before update, it can contain, for example,

 

R01,R02,R03,S01,S02,S03,A01,A02,A03,A04

 

After update:

 

S04

 

What I want is:

 

R01,R01,R02,R03,S01,S02,S03,A01,A02,A03,A04,S04

[Walker33]

And yes, you're right, PFMaBiSmAd, separate rows would simplify a lot of things.  I didn't build it.  On the other hand, some accounts have as many as 60 subs, so that might get messy, too.  But I agree with you overall.

[roopurt18]

<?php

$pk = 'the_primary_key';

$add_value = db_escape( 'S04' );

// mysql

$u = "update the_table set the_col = concat( the_col, {$add_value} ) where primary_key_col={$pk}";

// postgre

$u = "update the_table set the_col = the_col || {$add_value} where primary_key_col={$pk}";

?>

 

Your next question will then be, "How do I know when to insert the comma?"

 

Depending on the database, you can use some form of a case ... when along with length().

 

$u = "update the_Table set the_col = case length( the_col )=0 then {$add_value} else concat( the_col, ',', {$add_value} ) end where primary_key_col={$pk}";

 

[PFMaBiSmAd]

It's never too late to fix a bad design. A normalized design would not care if there are 1, 60, or 10,000 sublicenses for each license. One of the benefits of a normalized design is that the code does not care how much same type data there is. 60 or 60,000 sublicenses would not get "messy" because the general purpose code can operate on any amount of data.

 

And speaking from experience, the extra time you have spent struggling to make code work with the existing structure is more than what it would have taken you to correct the design and "explode" the existing data into separate rows.

[.josh]

if you insist on sticking to how things are now, an alternative to roopurt's suggestion would be to first select the info, use php to concat it and then update.

[roopurt18]

It's never too late to fix a bad design.

But it may not be cost-effective or even worth it.

[Walker33]

Again, I agree.  Additional problem is that the software we sell looks to that existing column for validity, so changes to the actual app would be required, as well.....but in time, yes, you're most likely right, better to switch it up. 

 

Hey, I really appreciate all your tips and suggestions!  Gotta run, but I'll give these suggestions a shot tomorrow.  As always, this group is great.  Thanks!

[roopurt18]

I didn't want to ninja-edit on you, so this is my would-be edit to my post above:

 

And speaking from experience, the extra time you have spent struggling to make code work with the existing structure is more than what it would have taken you to correct the design and "explode" the existing data into separate rows.

Again, not always the case.  If the application is in the early stages then yes, it is better to fix it now.  However, this could be a legacy application with all sorts of hooks from outside sources that he is not aware of.  In that case it is better to follow the "if it ain't broke don't fix it" as fixing it may break all sorts of things you can't adequately prepare for.  Preparing for changes like that take time (which equates to money) that someone may not be willing to pay for.

 

Also, his difficulty in supporting it is not necessarily from the existing bad design (though that doesn't help), but his failure to read the manual for his corresponding database, or his inexperience with databases in general.

 

The next breaking point for his application, however, will be when the field is not wide enough to accommodate the values within.  At that point he will be forced to normalize the data at best and at worst change the field definition to something like a text field.  A middle ground solution might be something with views and triggers that makes the changes transparent to any other processes or programs that go at this data.

 

Just my thoughts. 

[Walker33]

Ha!  Believe me, the main difficulty is my inexperience with the database, php, you name it!  I'm trying to read and study as much as I can, but yes, I'm extremely green to all of this.  Yes, you are right about the ap, all sorts of hooks and such.  A universal update would be required, and with our customers, that is ALWAYS 

[Walker33]

(whoops) a nightmare.  And yes, we already ran into the field length issue in the past.  Fixed by our actual developers.  Anyway, thanks again.

[.josh]

Ha!  Believe me, the main difficulty is my inexperience with the database, php, you name it!  I'm trying to read and study as much as I can, but yes, I'm extremely green to all of this.  Yes, you are right about the ap, all sorts of hooks and such.  A universal update would be required, and with our customers, that is ALWAYS[...] 

 

...like pulling your own teeth with a pair of pliers and no drugs?

...like squeezing blood from a turnip?

 

 

[PFMaBiSmAd]

And yes, we already ran into the field length issue in the past.

 

Kind of reminds me of the Y2K problem or the Unix Timestamp problem where the designers weren't thinking outside their cubical on how the system would be used.

[.josh]

well to be fair, back in the day, even 2 digits was a valuable commodity, memory-wise.  And 2000 was a long time down the road, stuff you read about in sci-fi novels.  It's not entirely unreasonable that they just assumed that far down the road, some entirely new system would be invented and implemented.

 

And indeed, better systems were invented.  Just that nobody got around to updating until the last minute.  More a matter of procrastination than lack of foresight. 

[roopurt18]

designers weren't thinking outside their cubical on how the system would be used.

 

It may also be the case where the designers are unsure exactly how a system will be used.  I can't tell you how many times I've began work on a project with the most vague of requirements.  In that case you end up going through many iterations of the project until the business end of things says, "It's no longer cost-effective to work on this and we need to move on to other things."  Other things might be a different project, or it might be an extension of the existing one, such as adding all sorts of reports or synchronization routines.

 

Eventually you come back to the project as you left it, since the only real constant in software is change, and now you have to support this rubber band ball of a solution.  It's rare, although fortunate, when things are perfectly spec'ed out and sufficient budget and time is available to complete a project in full before leaving it.

[.josh]

No help at all that the non-tech people who dream up the projects think anything under the sun is possible/worth it, can be implemented in like an hour, and cost $1. 

[Walker33]

Quick thought:  Actually, I would say that it's quite a bit of help that non-tech people think anything is possible (not in an hour, and not without $$$$).  I started my company with an idea and no tech knowledge (which I still lack, quite obviously) and thousands of people currently benefit from the idea and the product.  I hired brilliant developers to implement it, but I had some pretty fantastical expectations to start.  But I think that's what keeps the big minds (like all of you) rockin' and rollin'.  Great practical minds need great dreamer ideas.

 

How many superstar guitarists play fairly simple songs for a living but don't write any of their own?  First, Asimov or Heinlen (or whoever) has to propose the theory in a fictional sense, and then a whole team of people brighter than them, like NASA, make the theory a reality.  I think the world needs both, the dreamers and the do-ers.  Where would the Beatles have been without Brian Epstein?  Where would Brian Epstein have been without the Beatles?  Anyway, that's just my two cents.  I think it's symbiotic. 

 

Again, all of you are a great help to me in the stuff I'm trying to accomplish on my own (trying to cut some costs in these tough times).  You really have no idea how much I appreciate your patience and good advice.  Thanks again!