DEV Community: Vinod Devasia

Changing MySQL Default Port number

Vinod Devasia — Fri, 18 Feb 2022 12:42:05 +0000

Sometimes, when you install a new application on your server, they may compete/clash with port numbers already been used by other applications. This may cause your server to slow down, some applications to crash or even servers to crash occasionally.

One such instance is MySQL crashing periodically after I installed a certain application. Unfortunately, this application uses port 3306, and it has no method to specify a different port. MySQL default port is 3306. And it has good flexibility to change its port.

So let us see how.

Step 1 Edit the MySQL my.ini

Go to [path-to-xampp-folder]/mysql/bin/ and open the file my.ini. Or from Xampp, click on Config at the MySQL Module.

Locate the line(s) containing '3306' no. and change it to '3390' or something else and save. Note to change both the server and client parts as shown in the figure below.

Please make sure the new port number is not used by any other service. You can use 'Netstat' to check all used ports on your system.

Step 2 Edit the phpMyAdmin file

We need to change the way phpMyAdmin connects to MySQL so that it knows that it has to use the new port number instead of the default one. Failing to do so, will cause the following error whenever you try to connect to MySQL.

From xampp control panel, click on the config for the apache server, and select the phpMyAdmin.ini and make the following changes as shown below

Step 3 Edit all website connections to use the new port

Edit all configuration files in your webserver that connect to this MySQL instance with the new port number.

Failing to do this will cause the web apps to fail.

Based on how your app connects to the MySQL server, either in the config file or a function, ensure that it knows that we are not connecting via the default port anymore.

In my case, my App had a function connecting to the MySQL

And everything is back working!!!

Conclusion:
If you ever have to change the port of your MySQL server, ensure that you configure it both on servers, client connections, phpMyAdmin, and the connecting app(s).

FREE Website TOOLS for Website Performance Tracking

Vinod Devasia — Fri, 07 Jan 2022 01:51:41 +0000

Introduction

While developing a new web site, it is very necessary to ensure that the website is SEO friendly, and we may need to track the quality of our pages, what keywords our competitors are using, what is the website rank, what improvement we can do to make the site stand out above the competitions.

I have compiled quite a few sites that I have been using, and sharing them here. Best of all, all of these are FREE service.

1. Website Components

Find out what websites are Built With the following links. These websites provides information about the website technology profile such as what analytics and tracking is being used, widgets used, ecommerce platform, JavaScript used, ssl certificate details;

Built With Link
W3Techs Component review

2. Nu Html Checker

The Nu Html Checker (v.Nu) is an ongoing experiment in better HTML checking, and its behavior remains subject to change.

Nu Html Checker

3. Site information

Some general site information, this may overlap some of the links provided already. Some may be in German or Russian, and you may need to use google translate to understand the information provided;

info site show
talk reviews
stats crop
pr.cy
bel.ru

4. IPS / DNS Lookup

These includes details such as IP number, DNS Reverse lookup information, WHOIS Records, WOT information, any Virus threats, Any Realtime block details, Geo location etc.

robtex whois
ip-address info
phurix whois
whois
who.is
whois.de
whois lookup
informer.com
View who is
DNS Who is
Is your website in any DNS Block list

5. Website Worth and Traffic Statistics/Ranking information

These website provides details such as Website worth and traffic estimates, backlinks details, search engine and internet presence details. There are a bunch of places one can find these for free, and I am including a few here.

Stat Show
Worth of Web
Site worth traffic
HqIndex
Value Analyze
Site Price
Site Value fox
url Rate
Woo rank
web search ranking
crawler ranking
my wot

6. Keyword Analyze

Through this keyword tool, you can perform advanced keyword research and keyword tracking to study what your competitors have been advertising in AdWords & Other PPC campaigns to improve your own PPC campaign. Finally with this keyword software, you can now get AdWords tips and a complete in-depth analysis, stats, budget, affiliates & ad copies of your competitors.

Spy Fu
Serp Analytics

7. Is Website Up and running ?

These websites will check and report if your website is up and running

Website down ?

8. What is your websites reputation ranking?

These websites will check and report if your website reputation score.

scamanalyze

9. Free backlink for your website.

Just attach your website name to these URLs and you have a few free backlink to your websites ;)

deviant art
proza.ru
ibm.com

10. Time capsule

Archive.today is a time capsule for web pages, It takes a 'snapshot' of a webpage that will always be online even if the original page disappears. It saves a text and a graphical copy of the page for better accuracy and provides a short and reliable link to an unalterable record of any web page

archive.is
archive.org

11. SEO Optimization Reports

This is one of my favorite free SEO Optimization tools.

w3SEO
SEO Optimizer

12. Social Media Presence

Add your website URL link to many social media sites from here;

Add to Many

Conclusion

While developing a website, it is essential to keep track of the quality, and the SEO worth of the website and to do that we have a bunch of website tools that provides these information absolutely free to use

Google Search Console - Text too small to read - ERROR

Vinod Devasia — Sat, 01 Jan 2022 18:38:01 +0000

Introduction

Recently after submitting a website sitemap to google search console, I encounter two errors, of which I will cover the 1st type, which is Text too small to read.

This is how the error is displayed in the google search console, regarding a particular page.

So in this article I will explain how to fix this problem.

What is the "Text too small to read" Error?

Since google wants to offer a good user experience, and expects majority of the users to view your website using a mobile or similar device, it want to ensure that the users will have to "pinch to zoom" on your pages to read the contents, if the font sizes are too small.

So this error indicates that google Smartphone crawler has identified that your page has a mobile usability error, and somewhere on your page, some text has the font size too small, and will cause the "pinch to zoom" behavior.

Lets see how we can fix this.

STEP 1 Find out which pages are causing this problem

Below the error report, google search console will also display the pages that caused the problem.

STEP 2 Ensure that you have the Meta Tag viewport in the page heading.

The viewport is the users visible area of the web page, and it will vary with the device used to view your website. A computer screen will have the largest view area, while a mobile the lowest view area. To instruct the browser how to control the page dimension and scaling we use the meta tag viewport as shown below;

<meta name="viewport" content="width=device-width, initial-scale=1">

the width=device-width tells to set the width of the viewport to the device width, and initial-scale=1 tells it to use zoom level 1 as default zoom.

right click and select view page source, and inspect the head section to see, if the page contains the meta tag viewport. If that is missing add the above line of code to your webpage head section.

STEP 3 Check the font size in the page

In general, the rule of thumb is that font size needs to be 16 pixels for mobile websites. Anything smaller than that could compromise readability for visually impaired readers. Anything too much larger could also make reading more difficult.

Clicking Ctrl + Shift + J to bring the console screen, inspect your text font size. if you find that the text size is smaller than 16 font size, fix it to at least 16, or keep increasing it by one px till google stops complaining.

After you have completed all the fixes stated above, you can now click on the Validate Fix button to signal google that the fix is in place and let it retest your page again.

Google will start the validation process to see if the “text too small to read” issue has been fixed on your site, and if it finds no errors are detected, the validation will be declared as passed.

it will send you an email saying the process has started as well..

And the result!!!

Here are two links that will check your webpage for mobile friendliness so that you can fix any errors before submitting it to google search.

Google Mobile friendliness check page

Bing Mobile friendliness check page

Conclusion

It is very essential that all webpages we design are mobile friendly and before submitting to google search it is advisable to do a quick check for mobile friendliness and fix all the issues.

CSS Shopping cart Icon with number of items.

Vinod Devasia — Tue, 21 Dec 2021 13:05:49 +0000

Introduction

Every eCommerce website, need to display the number of items the customer has put into the shopping cart. Let me show you how easy it is to display this using a bit of html and CSS.

STEP 1 Include the font awesome stylesheet CDN

We need this to get the awesome font image for the shopping cart. Include this in the <HEAD> section of your html page.



<link href="https://use.fontawesome.com/releases/v5.0.1/css/all.css" rel="stylesheet">

STEP 2 lets write the style for the badge using CSS

A badge is the round circle with the number of items the customer have added to the shopping cart so far.



    .badge:after{
        content:attr(value);
        font-size:12px;
        color: #fff;
        background: red;
        border-radius:50%;
        padding: 0 5px;
        position:relative;
        left:-8px;
        top:-10px;
        opacity:0.9;
    }

Explanation :

We will display the value passed to the badge, the value here is the number of items in the cart. we are using white font color, with a background of red. We are using :after to indicate we want the badge to be displayed after displaying the cart icon.

STEP 3 Writing the html portion



<i class="fa badge fa-lg" value=5>&#xf290;</i>
<i class="fa badge fa-lg" value=8>&#xf07a;</i>

Explanation :

We using html list item i to display the cart. we are displaying the cart icon with a font size of 24px.

 is for the shopping bag
 is for the shopping cart

You can find more free font awesome codes by clicking this link

I have included the entire code here



<html>

<head>

    <link href="https://use.fontawesome.com/releases/v5.0.1/css/all.css" rel="stylesheet">

</head>

<body>

<style>

    .badge:after{

        content:attr(value);

        font-size:12px;

        color: #fff;

        background: red;

        border-radius:50%;

        padding: 0 5px;

        position:relative;

        left:-8px;

        top:-10px;

        opacity:0.9;

    }

</style>

<body>

<i class="fa badge fa-lg" value=5>&#xf290;</i>

<i class="fa badge fa-lg" value=8>&#xf07a;</i>

</body>

</html>

Conclusion

With simple HTML and CSS, we can build an awesome display for the shopping cart icon with items in it. Note, you will have to use either PHP or .JS in real scenario to provide the number of items as they cannot be static as shown in the above example.

Creating a table from an existing MySQL table using SQL Query

Vinod Devasia — Sat, 11 Dec 2021 12:30:39 +0000

INTRODUCTION

Sometimes when you start with a new project, you may want to create a new table which may be very similar to some table in the same database, or other. It may be combination of several tables, or a self-join, along with the data as well. Instead of creating the table, and then filling the data, or using export and import features of MySQL, we can use SQL Queries to do the job for us.
In this article I will be talking about this with a couple of examples

The syntax of creating a table with SQL Query is as follows

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

Example 1 Creating a subset of a larger table

Lets assume we have a very big table called Cities which has about 147110 rows. It contains almost all the cities information of the world.

For some new application, we want a table called citys with only county code 233 which is for the United States of America. The following SQL statement will do the trick

CREATE TABLE citys AS SELECT * FROM cities WHERE country_id = 233;
If we peek into the newly created citys table, we will find the cities of USA only in this newly created table.

Example 2 – Creating table with data from several tables

We will recreate the citys table again but this time we will add more information using two more tables states and countries to fill in the details rather than just the codes. We will restrict our table columns to id, name, state_name, country_name
Here we are joining the three tables’ cities, states and country, and creating a new table called citys, with new names for the columns as well.

CREATE TABLE citys AS SELECT c.id, c.name as city_name, s.name as state_name, ct.name as country_name FROM cities c LEFT JOIN states s ON c.state_id = s.id LEFT JOIN countries ct ON c.country_id = ct.id WHERE c.country_id = 233;

Let’s see how our new table looks like now

Note : When creating a table with CREATE TABLE ... SELECT, make sure to alias any function calls or expressions in the query. If you do not, the CREATE statement might fail or result in undesirable column names. In the above example since we used three tables and there were many columns (especially the id and names) common in these tables, we made sure we aliased the column names to specify very clearly which columns we are referring to.

Example 3 – Creating a table which exists in another database

If we want to create a new table in a new database, but the tables are existing in another database in the same server, then all we need to do is to include the database name as well in front of the table names.

CREATE TABLE new_tbl AS SELECT * FROM database.orig_tbl;

To create our citys table in another database (assume the three tables are in a database with name newdb)

CREATE TABLE Citys AS SELECT c.id, c.name as city_name, s.iso2 as state_code, s.name as state_name, ct.iso2 as country_code, ct.iso3 as country_iso3, ct.name as country_name, ct.phonecode as phonecode, ct.currency as currency, ct.region as region, ct.subregion as subregion FROM newdb.cities c LEFT JOIN newdb.states s ON c.state_id = s.id LEFT JOIN newdb.countries ct ON c.country_id = ct.id WHERE c.country_id = 233;

And we should find our newly created citys table in the current database

INDEXES

The city table we just created has about 19,818 rows. If we inspect the structure of the table, you will notice that there is no index on the newly created table!

CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement:

So let recreate the table, but this time we will specify to create the indexes as well. Let us see how

CREATE TABLE Citys (PRIMARY KEY (id)) AS SELECT c.id, c.name as city_name, s.iso2 as state_code, s.name as state_name, ct.iso2 as country_code, ct.iso3 as country_iso3, ct.name as country_name, ct.phonecode as phonecode, ct.currency as currency, ct.region as region, ct.subregion as subregion FROM newdb.cities c LEFT JOIN newdb.states s ON c.state_id = s.id LEFT JOIN newdb.countries ct ON c.country_id = ct.id WHERE c.country_id = 233;

Now you will find the new table with a PRIMARY KEY index as well

Hope you enjoyed this article on how to create a new table from existing ones, with the help of SQL queries.

Managing different PHP Versions while testing Laravel Modules.

Vinod Devasia — Thu, 09 Dec 2021 12:35:47 +0000

Introduction

Though I like to work with the latest version of PHP, which is 8+, many times while testing some Open-source modules of Laravel framework, I encountered problems such as finding some of the module’s dependencies require a different PHP version.

Example while trying to install this module with PHP 7.4 installed on my laptop;

composer require postcode-nl/api-magento2-module

However, the installation failed this error:

`Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

Problem 1
- feedbackcompany/module-reviews 1.0.0 requires php ~7.1.3||~7.2.0||~7.3.0 -> your PHP version (7.4.18) does not satisfy that requirement.
- feedbackcompany/module-reviews 1.0.0 requires php ~7.1.3||~7.2.0||~7.3.0 -> your PHP version (7.4.18) does not satisfy that requirement.
- feedbackcompany/module-reviews 1.0.0 requires php ~7.1.3||~7.2.0||~7.3.0 -> your PHP version (7.4.18) does not satisfy that requirement.
- Installation request for feedbackcompany/module-reviews ^1.0 -> satisfiable by feedbackcompany/module-reviews[1.0.0].
`

If you ever faced a problem like this, there are two ways to handle this;

1. Force composer installation to ignore PHP version requirements

A new feature in Composer v2 allows you to selectively ignore platform requirements.

composer install --ignore-platform-req=php

Composer already had the --ignore-platform-reqs option, but it ignored all platform requirements, including PHP version, extensions (ext-*), and composer-plugin-api. However now we can just use it to set specific requirements that Composer can ignore.

2. Temporarily change the PHP version to the version required

Now this can be a hassle to uninstall the entire XAMPP installation and reinstalling especially since XAMPP don’t even allow installing on a folder if it exists already.

Now assuming you already have some XAMPP version running, you can download multiple portable version of PHP that you wish to work from the link below

https://sourceforge.net/p/xampp/activity/?page=0&limit=100#619f851aaa0ec74743cc7a56

(Remember to click on show all activity, to see all the different versions available)

Download the version of PHP’s you want, ensuring you are selecting the portable version, and not the executable version. (Either .zip or .7z)

Here are some versions I downloaded

XAMPP released /XAMPP Windows/8.0.13/xampp-portable-windows-x64-8.0.13-0-VS16.zip
XAMPP released /XAMPP Windows/7.4.26/xampp-portable-windows-x64-7.4.26-0-VC15.zip
XAMPP released /XAMPP Windows/7.3.33/xampp-windows-x64-7.3.33-0-VC15.zip

And so on.

Unzip them one by one in their own folders and then delete everything inside them except the Apache and PHP folders. I have arranged them as follows in my laptop

As mentioned, each version only contains the Apache and PHP folders only.

Copy the current PHP version’s php.ini (c:\xampp\php\php.ini) to the correct versions PHP folder here, so that you don’t lose any configuration you had made and want it back when you put your original PHP version back.

Next, you need to stop all services in the XAMPP control panel.

Next delete the Apache and PHP folder from the c:\xampp folder (or wherever your XAMPP installation files are).

Now copy the Apache and PHP file from the version you want here. Restart the services from XAMPP, and that’s it. You have got the PHP version you want installed.

When you have done testing, you can revert back to the original version by doing the same steps as described above.

How To insert Non-English Text into MySQL Tables

Vinod Devasia — Sat, 04 Dec 2021 17:13:02 +0000

Introduction

When we work with web application that may be accessed globally, we may need to display messages in non-English characters and may need to store these non-English characters in our database tables. In this article, I will explain how to store non-English characters in MySQL.

Lets first see why it is necessary with an example.

Let create a simple table, which has three columns.

Create table Langs ( LangId char(2), KeyName varchar(20), KeyValue varchar(50) );
Lets Insert a few values in English, Catalan, German, Hindi and Russian into this newly created table as follows;

Insert into Langs(LangId, KeyName, KeyValue) values('EN','userid_required','User ID required!'); Insert into Langs(LangId, KeyName, KeyValue) values('CA','userid_required','Es requereix identificador d\'usuari!'); Insert into Langs(LangId, KeyName, KeyValue) values('GE','userid_required','Benutzerkennung erforderlich!'); Insert into Langs(LangId, KeyName, KeyValue) values('HD','userid_required','यूजर आईडी जरूरी!'); Insert into Langs(LangId, KeyName, KeyValue) values('RS','userid_required','Требуется идентификатор пользователя!');
Notice the \' in the d\'usuari!. We have used the backward slash in front of the apostrophe to include the special apostrophe character in our data.

Let see how the data will show up in our Langs MySQL table.

As we can see the non-English data got stored in ??? which is not what we want.

We can configure MySQL to accept non-English characters in two ways

Configuring at the Table Level.

While creating the table, we can specify that the tables charset is utf8mb4, which will allow us to store non-English characters in the table. Note this effect the entire table column and not just a specific column.

Lets drop that table and recreate it this time, and specifying that the tables charset set is utf8mb4

CREATE TABLELangs(LangIdchar(2) NOT NULL ,KeyNamevarchar(30) NOT NULL,KeyValuevarchar(100) NOT NULL ) DEFAULT CHARSET=utf8mb4;

Lets try re-inserting the values in English, Catalan, German, Hindi and Russian as before;

Now we got the non-English characters stored properly into the tables.

Configuring at the Database Level.

If we have many tables that need to store non-English characters, then we can configure the database while creating it as follows;

You can do this at the database creation:

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4

Now we don't have to specify the CHARSET=utf8mb4 for individual tables.

We can also alter a existing database with the alter database command

ALTER DATABASE mydb DEFAULT CHARSET utf8mb4;

Conclusion

To store the non-English Characters or Multi language in MySQL database table, we can configure it in two ways. Either at the table level, or the database itself. Character set utf8mb4 helps us to store non-English characters into the MySQL tables.

I hope you all found this article useful. Please share your feedback in the comment section.

AUTO INCREMENT On A MySQL Secondary Column

Vinod Devasia — Fri, 03 Dec 2021 20:54:33 +0000

MySQL AUTO_INCREMENT

MySQL provides us with AUTO_INCREMENT attribute that allows us to create a column that contains a sequence of numbers (1, 2, 3, and so on). AUTO_INCREMENT attribute is generally used to generate a unique number that act as a primary key in a table.

Let see a simple example. Here we have a table Mydevices that stores different devices we have such as laptop, computer, cellphone, tablet and so on.

The MySQL statement to create the table Device is given below

CREATE TABLEMyDevices(DeviceIdint PRIMARY KEY NOT NULL AUTO_INCREMENT,DeviceNamevarchar(10) NOT NULL,CreatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,UpdatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP );

Executing this gives us the table MyDevices

Lets Add a few rows, to see that the sequencing is working as we want.

Insert into mydevices(DeviceName) VALUES ('Laptop'); Insert into mydevices(DeviceName) VALUES ('Computer'); Insert into mydevices(DeviceName) VALUES ('Phone'); Insert into mydevices(DeviceName) VALUES ('Tablet');
If we do a query on our table we should find that MySQL have added a nice Sequences number to our DeviceId column

Now comes the twist. Lets add one more column, say CustomerId in the above table. Lets drop the Devices table and recreate it as follows;

DROP TABLE MyDevices; CREATE TABLEMyDevices(CustomerIdint NOT NULL,DeviceIdint NOT NULL PRIMARY KEY AUTO_INCREMENT,DeviceNamevarchar(10) NOT NULL,CreatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,UpdatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP );

Lets Add a few rows, to see that the sequences are working as we want.

Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Laptop'); Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Computer'); Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Phone'); Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Tablet'); Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Laptop'); Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Printer');
Lets see how the data looks like now;

As you will see, the auto increment feature keeps on adding the sequences number for each row as we insert. (1,2,3,4,5,6)

But what if this is not the behavior we want.. What if we want the sequence number to increase per customer. That is each customer has its own sequence numbers for the devices.

So Customer 1, has sequences 1,2,3... and so on
while Customer 2 devices gets a sequence number again from 1,2,3.. and so forth.

There are many ways to do this such as using stored procedures. But let us see today a very easy method to achieve this, by defining the table in a certain way.

ENGINE= MyISAM

The default engine storage for MySQL relational database management system prior to versions prior to 5.5 was MyISAM. It is based on the older ISAM code, but it has many useful extensions.

If we define tables either as MyISAM or BDB, it gives the ability to specify AUTO_INCREMENT on a secondary column in a multiple-column index.

For such tables, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.

This is useful when you want to put data into ordered groups. Lets adapt this to the above example of MyDevices table.

Lets create the table now as a MyISAM table

CREATE TABLEMyDevices(CustomerIdint NOT NULL,DeviceIdint NOT NULL AUTO_INCREMENT,DeviceNamevarchar(10) NOT NULL,CreatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,UpdatedAtdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (CustomerId,DeviceId) ) ENGINE= MyISAM;

Lets now insert data to this table (we will add a few more inserts that before);

Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Laptop'); Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Computer'); Insert into mydevices(CustomerId, DeviceName) VALUES (1,'Phone'); Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Tablet'); Insert into mydevices(CustomerId, DeviceName) VALUES (2,'Laptop'); Insert into mydevices(CustomerId, DeviceName) VALUES (3,'Printer'); Insert into mydevices(CustomerId, DeviceName) VALUES (4,'Printer'); Insert into mydevices(CustomerId, DeviceName) VALUES (4,'Laptop');

Lets see what we got into our tables;

There you go, we got now separate sequences for each of the customers.

QUIZ : In the above table what sequence do you think will customer 3 have for DeviceId if we inserted this SQL statemwent;

Insert into mydevices(CustomerId, DeviceName) VALUES (3,'Fax');

Answer : 2 ( as the last sequence generated for customer 3 was '1', the next number it assigned will be 1+1 = 2

Hope you enjoyed this article..