mysql query text replace issue

Question

I have a joomla database with 'jos_content' table containing the details about the articles.

In that table, the column 'attribs' is of TEXT datatype, which contains data about the article properties, like:

show_pdf_icon=0
show_print_icon=0
show_email_icon=0

I am trying to modify the property 'show_print_icon' so that it will have 1, like this:

show_pdf_icon=0
show_print_icon=1
show_email_icon=0

For this, i have written a query that replaces the 'show_print_icon=0' to 'show_print_icon=1' in the column.

UPDATE jos_content set attribs=replace("attribs", "%print_icon=0\nshow_email%",
"%print_icon=1\nshow_email%") where attribs like '%print_icon=0\nshow_email%';

For some reason, after executing this query, i got the column values as empty. I could not understand where did i go wrong. What could be the problem with query?

Gaurav · Accepted Answer · 2012-02-02 10:24:26Z

1

One way is :

$sql = "Select attribs from jos_content";
$db = JFactory::getDBO():
$db->setQuery($sql):
$contents = $db->loadObjectList();

foreach($contents as $content){
   $attribs = $content->attribs;
   $attribs = explode("\n", $attribs);
   if(!$attribs['show_print_icon']){
      $attribs['show_print_icon'] = 1;
      $content->attribs = implode("\n", $attribs);
   // run the update query here for each content
   }      
}

I'll let you know other way, using Com Content Model

answered Feb 2, 2012 at 10:24

Gaurav

28.8k9 gold badges54 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Bizdadea · Accepted Answer · 2012-02-02 10:13:31Z

0

Check the newline character. It may not be \n, it could be also \r\n or just \r, so you can't reliably match on this rule.

Also, why do you need the text surroundig? It just makes your login more complex than it has to be. You will have to be sure to keep consistent data and not have the variables there in another order for example.

I don't know what is your database structure there, but try to match on exactly what you need to change. If it's not possible now, than try to change your logic so you make it possible

answered Feb 2, 2012 at 10:13

Dan Bizdadea

1,3028 silver badges15 bronze badges

Collectives™ on Stack Overflow

mysql query text replace issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related