Categoria: database

Pubblicato il

regex multiple replacements

You could have to replace several “whatever”, f.e. in a sql file I had to replace many columns with dates, such as ‘2005-08-15’ splitting them in ‘2005’, ’08’, ’15’.

I manage to do it with this code: 

find NULL, '(.*?)-(.*?)-(.*?)', NULL, NULL, NULL
replace NULL, '\1', '\2', '\3', NULL

In this way you can replace '2005-08-15', NULL, NULL, NULL with '2005', '08', '15', NULL, and likewise all the like, such as '2012-06-23', NULL, NULL, NULL with '2012', '06', '23', NULL and so on.

But, be careful: make a backup of your file before.

another case: if you want replace several digits with the same digits formatted, you can try something like this code:

search: ^    (\d{2})$
replace: <h2>cap. \1</h2>\n
Pubblicato il

import an xml into phpmyadmin

Given that WordPress doesn’t allow to import a local file (and this is quite unfair, in my opinion), you can transform a wp site into a xml file and then import it in your local database via phpmyadmin.

But you have to format carefully the xml: look how phpmyadmin export an xml file and format your xml according to that model.

Pubblicato il

problems with phpmyadim

Sometimes it happens that phpmyadmin (/mysql) don’t allow you to do what it should allow, such as change the encoding of a column (or of a table or of a database), or change the engine of tables.

Then, after many failed attempts via sql query, I found that the easiest solution is

  • export the database
  • do the changes you want through a text editor, such as Kate, i.g., replacing the old enconding with the new one
  • import the (modifyed) database (after deleting/renaming the old one)
  • done!

Pubblicato il

php/msql keywords as hashtags

php separate all items in a mysql field

You can use the explode syntax, as in the following exapmple:

$keywords = $row['keywords'];
foreach (explode(',', $keywords) as $key) {
    echo "<span><a href="\hashtag.php?tag=$key\">{$key}</a></span>";
}

In the example we have a mysql field (keywords) with many items comma separated (such as : “truth, soul, body, mind” and so on).

And we obtain to have as many links from each item toward a specific target, as they are (that is: 3 links if you have 3 items, 7 links fi you have 7 ones).

In this way you can get a system of hashtags for your database keywords.

other steps

You need another file, let we call them hashtag.php.

The content of hashtag.php could be something like:

<?php  
 //hashtag.php  
 if(isset($_GET["tag"]))  
 {  
      $tag = preg_replace('/(?<!\S)#([0-9a-zA-Z]+)/', '', $_GET["tag"]);

//to beautify and stylize, but not necessary BEGIN
      $title=$tag;
      include "$root/intell/header-intell.inc";      
//to beautify and stylize, but not necessary END      

      $connect = mysqli_connect("localhost", "[mysql user]", "[mysql password]", "[mysql database]");  
      
      mysqli_set_charset($connect, 'utf8mb4');  // procedural style
      
      $query = "SELECT * FROM [your table] WHERE [your fields with tags] LIKE '%".$tag."%';  
      $result = mysqli_query($connect, $query);  
      if(mysqli_num_rows($result) > 0)  
      {  
           while($row = mysqli_fetch_array($result))  
           {  
                echo "<h2>$row[title]</h2>
                <blockquote><p>$row[text]</p></blockquote>
                <p><i>$row[author]</i><br />";
                $keywords = $row['keywords'];
                foreach (explode(',', $keywords) as $key) {
                if(trim($row["keywords"])==''){echo "";} else{echo "<span><a href=\"hashtag.php?tag=$key\">{$key}</a></span>";}
                }
                echo "</p>";
           }  
      }  
      else  
      {  
           echo '<p>No Data Found</p>';  
      }  
 }  
 ?>

Afterwards obviously you could adjust the css according to your needs.

Of course you can have as many other php files as you want, where you usually store you database content, in which you can add the kewwords as hashtags, with a code like the following:

echo "</p><p class=\"keywords\">";
$keywords = $row['keywords'];
foreach (explode(',', $keywords) as $key) {
    if(trim($row["keywords"])==''){echo "";} else{echo "<span><a href=\"hashtag.php?tag=$key\">{$key}</a></span>";}
}
echo "</p>";}

Pubblicato il

LibreBase problems with native mysql connector

1. mysql connector not working

From LO 6.4 native mysql connector didn’t work any more. But a possible solution is to comment the line skip-networking in my.conf.

I mean now the line is #skip-networking, before it was skip-networking.

2. subforms not working

In new releases, to avoid this issue, you have to cut a small portion of code in content.xml, as in the following instructions:

have a look at the content.xml in the *.odb-file (could be opened by a zip-program)

Not working code:
<db:driver-settings db:system-driver-settings=”” db:base-dn=”” db:parameter-name-substitution=”false”/>

Working code
<db:driver-settings db:system-driver-settings=”” db:base-dn=””/> »

It works!

3. tables or queries not working

You should check the table/query structure and avoid fields (which should have a length) with no length.

Pubblicato il

mysql convert a column to utf-8

You can use this mysql query code:

ALTER TABLE yourtable MODIFY
    yourcol VARCHAR(255)
      CHARACTER SET utf8
      COLLATE utf8_general_ci;

There are more problems with ENUM and SET type fields.

For ENUM you have to specify all the field values, no matter how many they are

ALTER TABLE yourtable MODIFY
    yourcol ENUM('val1','val2',etc)
      CHARACTER SET utf8
      COLLATE utf8_general_ci;

For SET the problems rise from the name SET, so you have to do this command

ALTER TABLE yourtable MODIFY
    yourcol CHAR(255)
      CHARACTER SET utf8
      COLLATE utf8_general_ci;

You will be able afterwards to modify CHAR to SET (with your previous values) without losing your data.