How to connect to my MySQL Database server using command line and php

Task: Use command mysql command line client

The syntax is:
mysql -u DBUSER -h DBSERVERNAME_OR_IP -p
Or
mysql -u user_name -h mysql_server_ip_address_here -p db_name_here
Make sure you replace username vivek and hostname localhost as per your setup:
$ mysql -u vivek -h localhost -p
Supply the password when prompted for password. You should get the mysql> or MariaDB prompt as follows:

How to connect to MySQL from the command line

How to list all databases

You need to type SQL command. For example, to list database, run:
mysql> show databases;
Sample outputs:

+--------------------+
| Database           |
+--------------------+
| nixnewsletter      |
| nixwikibashportal  |
| nixscbzcms         |
| nixscbzfaq         |
| nixsnixtipsclean   |
| information_schema |
| mysql              |
| performance_schema |
| nibashportal       |
+--------------------+
9 rows in set (0.00 sec)

How to access specific database named nibashportal

The syntax is:
mysql> use nibashportal;

How to list all tables in nibashportal database

The syntax is:
mysql> use nibashportal;
mysql> show tables;

Sample outputs:

+------------------------+
| Tables_in_nibashportal |
+------------------------+
| wp_commentmeta         |
| wp_comments            |
| wp_links               |
| wp_options             |
| wp_postmeta            |
| wp_posts               |
| wp_term_relationships  |
| wp_term_taxonomy       |
| wp_termmeta            |
| wp_terms               |
| wp_thesis_backups      |
| wp_thesis_terms        |
| wp_usermeta            |
| wp_users               |
| wp_wdpv_post_votes     |
| wp_wp_rp_tags          |
+------------------------+
16 rows in set (0.00 sec)

To describe table wp_users, run:
mysql> desc wp_users;
Sample outputs:

+---------------------+---------------------+------+-----+---------------------+----------------+
| Field               | Type                | Null | Key | Default             | Extra          |
+---------------------+---------------------+------+-----+---------------------+----------------+
| ID                  | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| user_login          | varchar(60)         | NO   | MUL |                     |                |
| user_pass           | varchar(255)        | NO   |     |                     |                |
| user_nicename       | varchar(50)         | NO   | MUL |                     |                |
| user_email          | varchar(100)        | NO   | MUL |                     |                |
| user_url            | varchar(100)        | NO   |     |                     |                |
| user_registered     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| user_activation_key | varchar(255)        | NO   |     |                     |                |
| user_status         | int(11)             | NO   |     | 0                   |                |
| display_name        | varchar(250)        | NO   |     |                     |                |
+---------------------+---------------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)

To list all stored data in wp_users table, run:
mysql> select * from wp_users;
OR
mysql> select ID,user_login,user_status,display_name from wp_users;
Sample outputs:

+----+------------+-------------+--------------+
| ID | user_login | user_status | display_name |
+----+------------+-------------+--------------+
|  1 | vivek      |           0 | Vivek Gite   |
+----+------------+-------------+--------------+
1 row in set (0.00 sec)

To exit simply type the:
mysql> exit;
Sample outputs:

Bye

For more info read the mysql command line man page:
$ man mysql

Task: Use PHP to connect to MySQL

Type the following PHP code in a file named test.php:

<?php
   //php7 tested 
   //replace db, user and password
   $link = mysqli_connect("localhost", "my_user", "my_password", "my_db");
 
  // did we connected?
  if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
   }
 
  // run some query
   $query = "SELECT * FROM table";
   $result = mysql_query($query);
 
   while ($line = mysql_fetch_array($result))
   {
      foreach ($line as $value)
       {
         print "$value\n";
      }
   }
 
    mysqli_close($link);
?>

Make sure you replace USERNAME and PASSWORD with your database user name and password. Also, replace TABLE and DATABASE with the valid table and database names from your database.

Upload the PHP file and type url https://your-domain-name-here/myscript.php