Test MySQL installation

From Klaus' wiki
Revision as of 12:10, 12 March 2014 by Klaus (Talk | contribs)

Jump to: navigation, search

After installing the necessary development tools and the MySQL database (see Install essential development utilities) it is time for at quick test to see if everything works.

Test Database

In PHPMyAdmin create a new database called test

Create a new table in the test database. Create 3 fields. The fields shall be named:

 id
 name
 place

The id field shall be set to

type int 
10 in length
unsigned attribute
Not Null
auto_increment
Primary

Insert a couple of fields into the table.

Create a test user

Create - in Privileges - a new user with localhost as the host.

Hit the Go button.

Use the edit button at the end of the line with the name to be edited in this view:

User.png

then click the Check ALL in the following

User1.png

and finally the Go button to save the privileges.

Test From C

Using Eclipse there are a couple of settings, that needs to be ensured.

In the project settings ensure that you have the correct paths for the compiler to search for include files (Notice these settings are for a C++ project - they are similar for C):

Includes.png

and the correct paths to the libraries:

Libs.png

Enter the source below in a .c-file or .cpp-file:

/*
 * ifTest.cpp
 *
 *  Created on: 21 Jan 2014
 *      Author: klausk
 */
 
/* Simple C program that connects to MySQL Database server*/
#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
 
int main()
{
	MYSQL *conn; /* Connection */
	MYSQL_RES *res; /* Result set */
	MYSQL_ROW row; /* A row */
	char *server = "localhost";
	char *user = "klausk";
	char *password = "pingeling";
	char *database = "test";
 
	/* Show the client version */
	printf("MySQL client version: %s\n", mysql_get_client_info());
 
	conn = mysql_init(NULL);
	/* Connect to database */
	if ( ! mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
	{
		fprintf(stderr, "%s\n", mysql_error(conn));
		exit(1);
	}
	/* send SQL query */
	if (mysql_query(conn, "show tables"))
	{
		fprintf(stderr, "%s\n", mysql_error(conn));
		exit(1);
	}
	/* Tell database that we're going to use the result */
	res = mysql_use_result(conn);
	/* output table name */
	printf("MySQL Tables in mysql database:\n");
	while ((row = mysql_fetch_row(res)) != NULL)
	{
		printf("%s \n", row[0]);
	}
	/* Tell database that we wont be needing this result anymore */
	mysql_free_result(res);
 
	/* Prepare a new query */
	if (mysql_query(conn, "SELECT * FROM tstTab"))
	{
		fprintf(stderr, "%s\n", mysql_error(conn));
		exit(1);
	} else
	{
		res = mysql_use_result(conn);
		printf("\n\ntstTab contains:\n");
		while ((row = mysql_fetch_row(res)) != NULL)
		{
			printf("id: %s, name %s, place: %s  \n", row[0], row[1], row[2]);
		}
		/* Tell database that we wont be needing this result anymore */
		mysql_free_result(res);
	}
	printf("\n");
	mysql_close(conn);
	return 0;
}

Be sure to change the USERNAME and PASSWORD to a valid user on the database server.

The MySQL C API is documented at C API


Test from PHP

<!DOCTYPE html>
<head>
	<title>MySQL Connection Test</title>
	<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
	<style type="text/css">
		#wrapper {
		    width: 600px;
		    margin: 20px auto 0;
		    font: 1.2em Verdana, Arial, sans-serif;
		}
		input {
		    font-size: 1em;
		}
		#submit {
		    padding: 4px 8px;
		}
	</style>
</head>
 
<body>
 
<div id="wrapper">
 
<?php
    $action = htmlspecialchars($_GET['action'], ENT_QUOTES);
?>
 
<?php if (!$action) { ?>
 
<h1>MySQL connection test</h1>
 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="mail" method="post">
 
    <table cellpadding="2">
        <tr>
            <td>Hostname</td>
            <td><input type="text" name="hostname" id="hostname" value="" size="30" tabindex="1" /></td>
            <td>(usually "localhost")</td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type="text" name="username" id="username" value="" size="30" tabindex="2" /></td>
            <td></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type="text" name="password" id="password" value="" size="30" tabindex="3" /></td>
            <td></td>
        </tr>
        <tr>
            <td>Database</td>
            <td><input type="text" name="database" id="database" value="" size="30" tabindex="4" /></td>
            <td>(optional)</td>
        </tr>
        <tr>
            <td></td>
            <td><input type="submit" id="submit" value="Test Connection" tabindex="5" /></td>
            <td></td>
        </tr>
    </table>
 
</form>
 
<?php } ?>
 
<?php if ($action == "test") {
 
// The variables have not been adequately sanitized to protect against SQL Injection attacks: http://us3.php.net/mysql_real_escape_string
 
    $hostname = trim($_POST['hostname']);
    $username = trim($_POST['username']);
    $password = trim($_POST['password']);
    $database = trim($_POST['database']);
 
    $link = mysql_connect("$hostname", "$username", "$password");
        if (!$link) {
            echo "<p>Could not connect to the server '" . $hostname . "'</p>\n";
            echo mysql_error();
        }else{
            echo "<p>Successfully connected to the server '" . $hostname . "'</p>\n";
//          printf("MySQL client info: %s\n", mysql_get_client_info());
//          printf("MySQL host info: %s\n", mysql_get_host_info());
//          printf("MySQL server version: %s\n", mysql_get_server_info());
//          printf("MySQL protocol version: %s\n", mysql_get_proto_info());
        }
    if ($link && !$database) {
        echo "<p>No database name was given. Available databases:</p>\n";
        $db_list = mysql_list_dbs($link);
        echo "<pre>\n";
        while ($row = mysql_fetch_array($db_list)) {
            echo $row['Database'] . "\n";
        }
        echo "</pre>\n";
    }
    if ($database) {
    $dbcheck = mysql_select_db("$database");
        if (!$dbcheck) {
            echo mysql_error();
        }else{
            echo "<p>Successfully connected to the database '" . $database . "'</p>\n";
            // Check tables
            $sql = "SHOW TABLES FROM `$database`";
            $result = mysql_query($sql);
            if (mysql_num_rows($result) > 0) {
                echo "<p>Available tables:</p>\n";
                echo "<pre>\n";
                while ($row = mysql_fetch_row($result)) {
                    echo "{$row[0]}\n";
                }
                echo "</pre>\n";
            } else {
                echo "<p>The database '" . $database . "' contains no tables.</p>\n";
                echo mysql_error();
            }
        }
    }
}
?>
 
</div><!-- end #wrapper -->
</body>
</html>