A database is a structured collection of data. It can be anything from a simple web shop list to a photo gallery or all of the information on a company’s network. In order to add data and edit the stored data, you will need a database management system such as MySQL.

One MySQL database comes standard with the hosting package. An additional MySQL database costs an additional € 12 excl. VAT per year. You can add an additional database to your package yourself at MyVevida.

MySQL is a relational database management system. A relational database stores data in separate tables instead of in one big long list. Speed and flexibility are two major advantages of this database type. MySQL is one of the most popular open source databases thanks to its fast performance, high reliability and ease of use. MySQL is used in over 6 million systems worldwide, ranging from big corporations to specialized embedded applications. SQL stands for Structured Query Language. SQL is the standard language for accessing databases.
SQL statements are used to execute certain tasks, such as updating data in a database, or retrieving data from a database. Standard SQL commands like “Select”, “Insert”, “Update”, “Delete”, “Create” and “Drop” can be used to do accomplish whatever one might want to do with a database.

Example query:

SELECT * FROM table;

This retrieves (SELECTs) all (*) data FROM table.

MySQL Database Access from PHP

In order to make contact with a MySQL server, you will need the following four items:

  1. host name, usually mysql.yourdomain.com
  2. database name
  3. user name
  4. password

The function for establishing the connection is mysql-connect:

<?php
$link = mysql_connect('mysql.uwdomein.com', 'mysql_gebruikersnaam', 'mysql_wachtwoord');
if (!$link) {
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Once a connection has been established to the database, you should select the database you want to use. Use the mysql-select-db function for this. Also check whether the database was selected correctly:

<?php
$link = mysql_connect('mysql.uwdomein.com', 'mysql_gebruikersnaam', 'mysql_wachtwoord');
if (!$link) {
  die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
  die ('Can't use foo : ' . mysql_error());
}
?>

A second option is to use PEAR class MDB2. Example of a connect and disconnect:

<?php
require_once 'MDB2.php';

$dsn = 'mysql://gebruikersnaam:wachtwoord@hostnaam/databasenaam';
$options = array(
  'debug' => 2,
  'result_buffering' => false,
);

$mdb2 =& MDB2::factory($dsn, $options);
if (PEAR::isError($mdb2)) {
  die($mdb2->getMessage());
}

// ...

$mdb2->disconnect();
?>

For detailed documentation, please visit the PEAR::MDB2 documentation website.

MySQL Database Access from ASP

In order to make contact with a MySQL server via ASP, you will need the following four items:

  1. host name, usually mysql.yourdomain.com
  2. database name
  3. user name
  4. password

It is best to use MySQL ODBC 5.1 Driver. The string for establishing the connection is (explanation of options):

<%@ %>
<% 
Option Explicit
Dim strConnection
Set strConnection = Server.CreateObject("ADODB.Connection")
strConnection.Open         "Provider=MSDASQL;" & _
"Driver={MySQL ODBC 5.1 Driver};" & _
"Server=mysql.uwdomein.com;" & _
"Database=databasenaam;" & _
"UID=gebruikersnaam;" & _
"PWD=wachtwoord;"

If strConnection.errors.count = 0 Then
Response.write "Connected OK"
End If

strConnection.Close
Set strConnection = nothing
%>

The Provider=MSDASQL addresses the Microsoft OLE DB for ODBC:

The Microsoft OLE DB Provider for ODBC (MSDASQL) is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. MSDASQL is an OLEDB provider that connects to ODBC, instead of a database.

MySQL Database Access from ASP.NET

Connector/NET

Since 11 February 2010, it has been possible to use the native MySQL Connector/NET to establish a connection to a MySQL database. A modified and limited System.Net.SocketPermission configuration has been set for this.

For information on this connector, please visit:
http://dev.mysql.com/doc/refman/5.0/en/connector-net.html.

A VB.NET example:

<%@ Page debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>

<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim myConnection  As MySqlConnection
Dim myDataAdapter As MySqlDataAdapter
Dim myDataSet     As DataSet
Dim strSQL        As String
Dim iRecordCount  As Integer

' pooling=true of false is hieronder mogelijk
myConnection = New MySqlConnection("server=mysql_hostnaam;" & _
"user id=mysql_gebruikersnaam;" & _
"password=mysql_wachtwoord;" & _
"database=mysql_databasenaam;")

strSQL = "SHOW TABLES;"

myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
myDataSet = New Dataset()
myDataAdapter.Fill(myDataSet, "mytable")

MySQLDataGrid.DataSource = myDataSet
MySQLDataGrid.DataBind()
End Sub
</script>

<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>

<form runat="server">
<asp:DataGrid id="MySQLDataGrid" runat="server" />
</form>

</body>
</html>

The MySQL Connector/NET is not installed server-wide on our web servers, unlike MySQL Connector/ODBC. The MySql.Data.dll assembly can be placed in the ~/bin directory of the relevant application.

Entity Framework on top of MySQL Connector/NET

(for somewhat advanced developers)
If you make an Entity Framework on top of MySQL Connector/NET, please note the following –additional- information:

The correct connection string in your web.config or app.config should have the part after provider connection string between single quotes (‘), instead of the double quotes (“) generated by VS2008 & MySQL Connector/Net. These will in fact work on your development environment, but not on a web server at VEVIDA.

Good connection string:

<add connectionString="metadata=res://*/Model.csdl|
res://*/Model.ssdl|res://*/Model.msl;provider=MySql.Data.MySqlClient;
provider connection string='server=mysql.server.ext;User;
Persist Security Info=True;database=mysqldb;password=mysqlpwd'" 
providerName="System.Data.EntityClient" />

Automatically generated (incorrect) connection string:

<add connectionString="metadata=res://*/Model.csdl|
res://*/Model.ssdl|res://*/Model.msl;provider=MySql.Data.MySqlClient;
provider connection string="server=mysql.server.ext;User;
Persist Security Info=True;database=mysqldb;password=mysqlpwd"" 
providerName="System.Data.EntityClient" />

It is also a good idea to put a DBFactory in your web.config so that you can be certain that the application is addressing the MySQL.Data.dll in your ~/bin directory.

<system.data>
<DbProviderFactories>
<clear />
<add invariant="MySql.Data.MySqlClient" 
description=".Net Framework Data Provider for MySQL" 
type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, 
Version=6.2.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>

Connector/ODBC

You can use the MySQL Connector/ODBC to establish a connection with a MySQL database from .NET. For information on this connector, please visit:
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc.html.

The C# example for .NET on the above pages has been translated into ASP.NET below. For the original example, please click on the URL below.
http://dev.mysql.com/doc/refman/5.0/en/odbc-net-op-c-sharp-cp.html.

<%@ Page Trace="False" Debug="False" EnableViewState="False" %>
<%@ import Namespace="System.Data.Odbc" %>

<script runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
  try {
    // Connection string for MyODBC 5.1
    string MyConString = "DRIVER={MySQL ODBC 5.1 Driver};" +
    "Provider=MSDASQL;" +
    "SERVER=mysql.hostnaam.nl;" +
    "DATABASE=databasenaam;" +
    "UID=inlognaam;" +
    "PASSWORD=wachtwoord;" +
    "OPTION=3";

    // Connect to MySQL using MyODBC
    OdbcConnection MyConnection = new OdbcConnection(MyConString);
    MyConnection.Open();

    Response.Write("<br /> !!! success, connected successfully !!!<br />");

    // Display connection information
    Response.Write("Connection Information:<br />");
    Response.Write("Connection String:" + MyConnection.ConnectionString + "<br />");
    Response.Write("Connection Timeout:" + MyConnection.ConnectionTimeout + "<br />");
    Response.Write("Database:" + MyConnection.Database + "<br />");
    Response.Write("DataSource:" + MyConnection.DataSource + "<br />");
    Response.Write("Driver:" + MyConnection.Driver + "<br />");
    Response.Write("ServerVersion:" + MyConnection.ServerVersion + "<br />");

    // Close all resources
    MyConnection.Close();
  }
  catch (OdbcException MyOdbcException) //Catch any ODBC exception ..
  {
    for (int i=0; i < MyOdbcException.Errors.Count; i++)
    {
      Response.Write("ERROR #" + i + "<br />" +
      "Message: " + MyOdbcException.Errors[i].Message + "<br />" +
      "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "<br />" +
      "Source: " + MyOdbcException.Errors[i].Source + "<br />" +
      "SQL: " + MyOdbcException.Errors[i].SQLState + "<br />");
    }
  }
}
</script>

MySQL Database Access from Perl

In order to make contact with a MySQL server via Perl, you will need the following four items:

  1. host name, usually mysql.yourdomain.com
  2. database name
  3. user name
  4. password

You can use DBD:mysql to make the connection.

The string for establishing the connection is:

#!C:/Perl/bin/perl
use strict;
use CGI qw/:standard/;
use DBI;

my ($dbh, $sth, $count);
$dbh = DBI->connect ("DBI:mysql:host=mysql.uwdomein.com;database=databasenaam", 
"gebruikersnaam", "wachtwoord", {PrintError => 0, RaiseError => 1});

my $query = "SHOW TABLES";
$sth = $dbh->prepare ("$query");
$sth->execute ();

print header;

$count = 0;
while (my @val = $sth->fetchrow_array ())
{
  ++$count;
}
print p ("$count rows total");

$sth->finish ();
$dbh->disconnect ();

MySQL Database Maintenance

You can maintain the database via phpMyAdmin. After downloading phpMyAdmin, you can upload it to your website (for instance, to http://www.yourdomain.com/phpmyadmin/), at which point you will need to change the appropriate data in file config.inc.php.

It is recommended that you secure the directory you are using for phpMyAdmin by deleting the permissions for the IUSR. You can then use your FTP login details to log into the phpMyAdmin folder and create tables.
You can also use mySQL-Front (if this download does not work, you can always download from http://www.mysqlfront.de/). This is a program that you can install on your local PC. After installation, you can enter your login details and connect to the server.
MySQL CC and MySQL Administrator are client programs developed by the makers of MySQL.

You can use mySQLdump or mySQL-Front (the import/export function) to make a backup of your database or convert databases.
mySQLdump is a utility for making a dump of a database for backup purposes, or for sending data to another SQL server (this does not necessarily have to be a MySQL server). The dump contains SQL statements to create the tables and, if necessary, fill them. If you have the MySQL client tools (Windows or Linux), then you can use the following command to make a dump (backup) of your MySQL database (put the command on a single line):

mysqldump --opt -h mysql.uwdomein.com -u database_usernaam -p
  --databases databasenaam >> databasenaam.sql

The -p makes it so that a password will not need to be entered until [ENTER] is pressed.

This way you will always have a backup of your database on your local computer.

MySQL Database Optimization

Optimization is a complex facet of database use and management because it requires knowledge of the entire system. One example here is that you can greatly accelerate your database by using table indexes.

You can read more on this topic at http://dev.mysql.com/doc/refman/5.0/en/optimization.html, in particular sections:

And

It is important to execute an OPTIMIZE TABLE query regularly on all tables in a database. This deletes items such as the “overhead” space: space that becomes available after deletion of records, but which is still in use by the database. The data in the tables get fragmented (spread out).
The following PHP script can be used to optimize all tables in a MySQL database. You can put the PHP script on your website (name it db_optimize.php, for instance) and call it online via your website.

<?php
/**
* (c)2005 - 2009 VEVIDA Services BV
*
* 20090701.1100 - Jan Reilink <info@vevida.com>
* Script om alle tabellen in een MySQL database te optimaliseren.
*/
 
// MySQL configuratie
$dbhostnaam = 'mysql.example.com';
$dbusername = 'mysql_gebruikersnaam';
$dbpassword = 'mysql_wachtwoord';
$db = 'mysql_databasenaam';

// Maak verbinding met de MySQL server  
$link = mysql_connect($dbhostnaam, $dbusername, $dbpassword);
if (!$link) {
  die('Could not connect: ' . mysql_error());
}

// Selecteer de database, of geef een foutmelding
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
  die ('Cannot use '.$db. ' : ' .mysql_error());
}

// Maak een query
$listquery = "SHOW TABLES FROM `".$db."`";
$dbtables = mysql_query($listquery);

/**
* Vraag alle tabellen uit de database op en plaats deze in een array.
* Een tellertje op het aantal geoptimaliseerde tabellen is ook 
* altijd leuk :-)
*/
$t = 1;
while ($row = mysql_fetch_array($dbtables, MYSQL_NUM)) {
  $optimizequery = "OPTIMIZE TABLE `".$row[0]."`";
  $result = mysql_query($optimizequery);

  /**
   * Geef een melding dat de betreffende tabel geoptimaliseerd is,
   * of "exit" met een foutmelding, als er iets fout gegaan is.
   */
  if (!$result) {
    exit(mysql_error());
  } else {
    print 'OK, table '.$row[0].' geoptimaliseerd.<br />';
    $t = $t + 1;
  }
}
echo '<br />Klaar! In totaal '.$t.' tabellen geoptimaliseerd.';
?>

MySQL Database Size

The following PHP script can be used to query how big a MySQL database is. You can put the PHP script on your website (name it db_size.php, for instance) and call it online via your website.

<?php
/**
*  Script om op te vragen hoe groot een MySQL database is
*  Invoer: $dbhostnaam, $db, $dbusername, $dbpassword
*
*  v20090330.1630
*  Copyright 2004 - 2009 VEVIDA Services B.V. - Jan Reilink <info@vevida.com>
*
*/
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>MySQL database grootte (PHP)</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta content="VEVIDA Services BV" />
</head>
<body>
<p style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px;">

<?php
$dbhostnaam = "mysql.uwdomein.com";
$db = "mysql_databasenaam";
$dbusername = "mysql_gebruikersnaam";
$dbpassword = "mysql_wachtwoord";

mysql_connect($dbhostnaam,$dbusername,$dbpassword);
mysql_select_db($db) or die ("Fout, kan de database niet openen");

function get_dbsize($db) {
  $query = "SHOW TABLE STATUS FROM `".$db."`";
  if ($result = mysql_query($query)) {
    $tables = 0;
    $rows = 0;
    $size = 0;
    while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
      $rows += $row["Rows"];
      $size += $row["Data_length"];
      $size += $row["Index_length"];
      $tables++;
    }
  }

  $data[0] = $size;
  $data[1] = $tables;
  $data[2] = $rows;
  return $data;
}

$result = get_dbsize( $db );

print "Database <b>" .$db . "</b> heeft de volgende gegevens:<br /><br />";

$megabytes = $result[0] / 1024 / 1024;
$megabytes = round($megabytes, 3);

print "<span style="font-variant: normal;">";
print_r( $result[0] . "t" . " bytes, " . "in MegaBytes maakt dit, " . $megabytes . " MB\r\n");
print "<br />";
print_r( $result[1] . "tt" ." tables\r\n");
print "<br />";
print_r( $result[2] . "tt" ." rows\r\n");
print "</span>";
?>
</p>

<p style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px;">
Controle gedaan op:
<?php
/**
* geeft de datum weer in het formaat: Fri, 03 Feb 2006 11:02:09 +0100
*/
echo date("r");
?>

<br /><br />
<span style="font-size: 10px; padding-left: 3px;">
© 2004 - 2009 <a href="http://www.vevida.com" target="_blank">Vevida Services BV</a>.
</span>
</p>

</body>
</html>

LOAD DATA LOCAL INFILE

The MySQL function LOAD DATA LOCAL INFILE can be used add a local file with data, on the web server’s file system, to the database with a single statement. You can use this function to add bulk data (such as from a .csv file) to the database in a single operation.

Example script:

<?php
/**
* (c)2005 - 2009 VEVIDA Services BV
* 20090720.1000 - Jan Reilink <info@vevida.com>
*
* LOAD DATA LOCAL INFILE voorbeeld.
*/
$link = mysql_connect('mysql.[domein].org', '[gebruikersnaam]', '[wachtwoord]');
if (!$link) {
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully <br/>';

// maak '[dbnaam]' de geselecteerde db
$db_selected = mysql_select_db('[dbnaam]', $link);
if (!$db_selected) {
  die('Can't use foo : ' . mysql_error() .'<br/>');
}

/**
* let op het aantal backslashes ('').
* de specifieke query is afhankellijk van het .csv bestand.
*/
$datafile = 'd:www[FTP-inlognaam].orgwwwdata_bestand.csv';
$query = "LOAD DATA LOCAL INFILE '{$datafile}' INTO TABLE `tabelnaam` 
FIELDS TERMINATED BY ';' LINES TERMINATED BY 'rn' (rij1, rij2, rij3, rij4, rij5)";
$result = mysql_query($query, $link);

if (!$result) {
  die('Query is mislukt: ' . mysql_error() .'<br/>' .$query);
}
else {
  echo 'query succeeded succesfully.';
}

mysql_close($link);
?>

Please note that you cannot use this function via the phpMyAdmin application.

phpMyAdmin

http://www.phpmyadmin.net:

phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the Web.

In MyVevida, a preinstalled phpMyAdmin is available for customers who have a MySQL database. You can find this phpMyAdmin by logging into MyVevida. If you have a MySQL database, then you will see the header “Your mySQL server” here.
This means that you no longer need to put a phpMyAdmin on your website yourself, although you are of course free to do so if you like.
If you go to the phpMyAdmin page, then you must enter your MySQL username and password and select the relevant MySQL server from the drop-down menu. Your database will be selected automatically. The connection uses a secure SSL (HTTPS) connection. The login is handled using a cookie. Your browser must support this.

Outside Help

If you should need any help with PHP or mySQL, there is an excellent manual available at
http://nl.linux.org/doc/HOWTO/PHP-MySQL-NL.html
MySQL 5.0 Reference Manual

« Back

Customer service

Cannot find what you are looking for? Please contact our customer service:

We are glad to be of service.