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!

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>";}

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.

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.

convert an InnoDB database to MyIsam

The problem of Innodb is that you can’t copy the database tables from a PC to another or from localhost to an online website, because of the file “ibdata1”, which is out of the tables folder.

So I think that the best solution, if you need to sync your database between different locations, is to convert Innodb tables into MyIsam tables, which can be copied without problems.

Unfortunately I didn’t find a way to convert a whole database with a single mysql command, but I had to convert each table, with this code:

ALTER TABLE table_name ENGINE=MyISAM.

But at the end the result was any way good.