Tag: mysql

Pubblicato il

leaving xampp

For many years I used xampp (for Linux: Lampp), because of its simplicity, unlike the “native” Linux apache/php/mysql apps, wich seemed to me very difficult to configure.

The pro of xampp is its simplicity (with few mouse clicks you can do all to install and configure your local php/mysql sever). But the con is that your xampp apps don’t are updated regularly, and there can raise conflicts with Linux “native” mysql.

Therefore I decided to learn how to configure “native” (system-rooted, so to say) apache/php/mysql apps. I chose to install mariadb and not mysql.

And in these last weeks I managed to configure the server, with the following steps:

  • sudo apt install -y apache2
  • sudo apt install -y php
  • sudo apt install mariadb*
  • sudo mysql_secure_installation
  • sudo mysql -u root -p (and within >mysql shell
    • use mysql
    • GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION;
    • SHOW GRANTS FOR 'root'@localhost; //to check if it is all right
    • FLUSH PRIVILEGES;
    • exit;
  • sudo systemctl enable mariadb.service

Pubblicato il

mysql add some string to several rows

If a column has several rows, i.g.:

'red'  
'NULL'  
'red, yellow'  
'green'  
'NULL'  
'yellow, green'

And you want to add a new string (‘blue’) to every row, so that the result be:

'red, blue'  
'blue'  
'red, yellow, blue'  
'green, blue'  
'blue'  
'yellow, green, blue'

you can use this code

UPDATE my_table 
SET mycolumn = CONCAT('same new string ',mycolumn);

But the above code works only for rows with some content, not for the empty (NULL) ones. Therefore you can complete your task with the following:

UPDATE my_table 
SET mycolumn = 'same new string' 
WHERE mycolumn IS NULL;
Pubblicato il

mysql/php output in utf8

To avoid output problems in non-latin characters you should add this line in a msyqli query (i.e. after $db = mysqli_connect($db_host, $db_user, $db_password, $db_name);):

mysqli_set_charset($db, 'utf8');

Pubblicato il

import an external table to a mysql table

  • You can use a graphical interface to prepare the rows to insert in mysql table: be careful that a) the number of columns be matching with the mysql table rows number and b) the order of columns be the same.
  • Save your date as csv: in this way you will be able to make further modifications to your data: i.g. you could add new ,NULL,columns (because in librecalc if a column is empty at the end of a file that column is not added).
  • Be very careful with comma (,) within the columns, if you import the table in mysql with comma as column separator. You should avoid commas within a column, if you use commas as separator.
  • In phpmyadmin import your csv file as a new table. As and of line you can set \n if yout line ends with something like “NULL”. Otherwise, if you rows ends with “NULL”, you will get an undesired column at the end of teh impoorted table.
  • Don’t worry if the columns type doesn’t match with the destination mysql table: this is negligible.
  • In sql do this command:

INSERT IGNORE
INTO your_destination_table
SELECT *
FROM the_new_data_table-from-csv;

  • Using insert IGNORE you can avoid to delete the ID beginning column, if it is simply an autoincrement one.
  • Your new rows should have been added to your destination table: done!
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>";}