MySQL

import excel data table into database with only phpMyAdmin access

In a specific table in a MySQL database a column needed to be populated with thousands of different values depending on a row-ID. The row-ID and value pairs could be found in an excel file / table. No direct access to MySQL was possible, only the import function of phpMyAdmin could be used.

Joins are more expensive than a pure concatenation. In your case maybe UNION or UNION ALL might be a good solution because it parses the first SELECT statement and moves on to the next SELECT statement and adds the results to the end of an output table. The finding of the different connections of the joined tables is a huge cost factor.

Using joins (mySQL) in a query

Actually in a web application performance has a huge influence on usability. So I often use joins in my queries to provide compact sql queries. But queries with many joins have a bad performance and the user have to wait for a long time. Is there a better opportunity to provide a better performance?

Optimizing SQL Queries

In a typical university course, performing an SQL query was always a fast thing. However, when you are working on a project that grows over time, you at first might not give the performance of a query much thought. As the project grew, I had complex queries and lots of records in the database, distributed over many tables. What I didn't expect, was that from one day to another, the performance of the page went from zero complaints to constant user complaints (the page loads too slowly). As I found out way later, the bottleneck comes suddenly. It does not build up. And at that point I was stressed and had to do an overnight shift, dealing with temporarily shutting down the users and optimizing the backend queries (take a look at the JOINS, and narrow it down as much as you can). For future reference I know that I will give SQL query performance way more attention, and as the query complexity and database size grows, you should in the same step perform a check how the performance scales.

First, the Site Address and the WordPress address need to be updated. Go to Settings->General in the WordPress administration area and change the value of these two settings to the new URL. New contents are now created with HTTPS.

Unfortunately, changing the site address does not update URLs in existing contents, so a SQL-Statement to replace the old URLs with new one is required (replace example.org with the URL of the site to move):

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://www.example.org', 'https:// www.example.org)

Mapping taxonomy terms from Drupal 5 to Drupal 7

In Drupal 7 tagging terms are stored as fields of the entity. In previous Drupal versions tags are assigned to nodes stored in a table taxonomy_index(nid, tid, created). In D7 we have some table as for example field_data_field_taggings were each term refrerence is stored as a row. If more then one term is assigned to a content node (e.g. in the answers module), the value in the column delta incremented to have a unique key. To generate this table we need an sql statement that increments this delta if more than one term exist for the node.

I have copied the table representing the relation between question and answer wrongly.

Taggings:

Opening phpmyadmin in an incognito window

If phpmyadmin is open in a new incognito window, the user has to login again. Then he can kill the running processes in Status tab under Processes (see http://prntscr.com/df4ydl). Unless the user has the proper rights, he can only kill his own processes. After killing the process, the user can use the incognito window or the window he was using before to run another query.

Taggings:

Squash TM with MySQL

Prerequisite: Administrator rights

The exception indicated that there were some issues with the size of text that was inserted into one of the tables. Since it was a SQL error there was the possibility of fixing the error by changing the data type of the affected column:

- Open the script with a text editor

- Find the part of the script where the tables are created

- Change the data type of the column in the script from VARCHAR(32000) to Text


CREATE TABLE `CORE_CONFIG` (`STR_KEY` VARCHAR(255) NOT NULL, `VALUE` VARCHAR(32000) NULL, CONSTRAINT `PK_CORE_CONFIG` PRIMARY KEY (`STR_KEY`));

To


CREATE TABLE `CORE_CONFIG` (`STR_KEY` VARCHAR(255) NOT NULL, `VALUE` TEXT NULL, CONSTRAINT `PK_CORE_CONFIG` PRIMARY KEY (`STR_KEY`));

PDF from Database using FPDF

This solutions came to my mind when i was asked to allow registrar to create student certificates online when they were using the school's local website - similar to TISS certificate generator - i used the free FPDF library which is a PHP class which allows to generate PDF files with pure PHP - the aim is to allow the registrar of school to be able to take the student’s id and course id and generate the student's certificate for a course - the main aim is to show how the class is used in a simple and easy way for use in a small scale - There are two files. The first file, the user inputs the student id and course id and hits create certificate. The file points to the fpdf file that goes and creates a pdf file and displays the certificate

Pages

Subscribe to MySQL