Example Scripts for Accessing a MySQL Database

The following scripts are examples of MySQL database functions which can be used to work with MySQL databases.

Creating a table

<?php
$server= "dbXX.db.1and1.com"; /* Address of the 1&1 IONOS Database Server */
$user= "xxxxxx"; /* Database User Name */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table */

/* Connect to the MySQL Server and create the table */
MYSQL_CONNECT($server, $user, $password) or die ("<H3>Database server not reachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database not available</H3>");

$result=MYSQL_QUERY( "CREATE TABLE $table (name varchar(25),email varchar(25),id int(11))");

MYSQL_CLOSE();
?>

Updating a table

You can change entries of an existing table by updating the table.
With the update query, all occurrences of mail@1and1help.com in the email field are changed to mail@1and1help.co.uk .

<?php
$server= "dbXX.db.1and1.com"; /* Address of the 1&1 IONOS database server */
$user= "xxxxxx"; /* Database User Name */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table */

/* Connect to the MySQL Server and update the entries */
MYSQL_CONNECT($server, $user, $password) or die ("<H3>Database server not reachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database not available</H3>");

MYSQL_QUERY("UPDATE $table SET email = 'mail@1and1help.co.uk' WHERE email = 'mail@1and1help.com'");

MYSQL_CLOSE();
?>

Deleting a table

The following script deletes a table from the database.

 

Please note: Please do not delete the database under any circumstances, but only individual tables, once deleted, the database cannot be created again.

<?php
$server= "dbXX.db.1and1.com"; /* Address of the 1&1 IONOS database server */
$user= "xxxxxx"; /* Database User Name */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table */

/* Access the MySQL Server and delete the table. */
MYSQL_CONNECT($server, $user, $password) or die ("<H3>Database server not reachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database not available</H3>");

$result=MYSQL_QUERY( "DROP TABLE $table");

echo "<H1>The table was successfully deleted.</H1>";

MYSQL_CLOSE();
?>

Adding Entries to a Table

<?php
$server= "dbXX.db.1and1.com"; /* Address of the 1&1 IONOS database server */
$user= "xxxxxx"; /* Database User Name */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table */

/* Connect to the MySQL Server */
MYSQL_CONNECT($server, $user, $password) or die ("<H3>Database server not reachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database not available</H3>");

/* Add the data into the table */
MYSQL_QUERY( "INSERT INTO $table VALUES('John Doe','john@doe.co.uk','1')");
MYSQL_QUERY( "INSERT INTO $table VALUES('Jane Doe','jane@doe.co.uk','2')");
MYSQL_QUERY( "INSERT INTO $table VALUES('AN Other','an@other.co.uk','3')");

/* Displays the number of entered values */
$number=MYSQL_NUMROWS(MYSQL_QUERY( "SELECT * FROM $table"));
if ($number==0):
echo "No data available";
elseif ($number > 0):
echo "$number records exist";
endif;

MYSQL_CLOSE();
?>

Reading Data / Viewing the Table

<?php
$server= "dbXX.db.1and1.com"; /* Address of the 1&1 IONOS database server */
$user= "xxxxxx"; /* Database User Name */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table */

/* Connect to the MySQL Server and query the table */
MYSQL_CONNECT($server, $user, $password) or die ("<H3>Database server not reachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database not available</H3>");

$result=MYSQL_QUERY( "SELECT * FROM $table order by name");

/* Output of the table in a HTML table  */
echo "<table><tr>";

while ($field=mysql_fetch_field($result)) {
echo "<th>$field->name</th>";
}
echo "</tr>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
for($i=0; $i < mysql_num_fields($result); $i++) {
echo "<td>$row[$i]</td>";
}
echo "</tr>\n";
}
echo "</table>";

MYSQL_CLOSE();
?>

Reading specific entries in a table

With this script only specific entries are read from a table. This considerably improves the speed of the script, which in turn shortens the loading time of the website.
 
The first 3 entries in the database which contain either united in the email field or contain 1&1 IONOS in the name field are retrieved.
 
The email field is not case sensitive when using LCASE().
 
This query is efficient because only what is actually needed is transferred. The fields name and email are transferred, the last field id is ignored.

$server= "dbXX.db.1and1.com"; /* Address of the 1&1 IONOS database server */
$user= "xxxxxx"; /* Database User Name */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table */
/* Connect to the MySQL Server and query the table */
MYSQL_CONNECT($server, $user, $password) or die ("<H3>Database server not reachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database not available</H3>");

$result=MYSQL_QUERY("SELECT name, email
FROM $table
WHERE (name = '1&1 IONOS' OR INSTR(LCASE(email), 'united'))
ORDER BY NAME DESC LIMIT 3
");

/* Output of the table in an HTML table */
echo "<table border=\"1\" align=center width=50%";
echo "<tr>";
echo "<div color=\"#ffff00\">";
while ($field=mysql_fetch_field($result)) {
echo "<th>$field->name</A></th>";
}
echo "</font></tr>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
for($i=0; $i < mysql_num_fields($result); $i++) {
echo "<td align=center>$row[$i]</td>";
}
echo "</tr>\n";
}
echo "</table>";

MYSQL_CLOSE();
?>

Deleting individual entries from a table

<?php
$server= "dbXX.db.1and1.com"; /* Address of the 1&1 IONOS database server */
$user= "xxxxxx"; /* Database User Name */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table */

/* Connect to the MySQL Server and and delete the entry */
MYSQL_CONNECT($server, $user, $password) or die ("<H3>Database server not reachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database not available</H3>");

/* The entry where the ID = 3 is deleted from the table.*/
MYSQL_QUERY("DELETE FROM $table WHERE id = '3'");

MYSQL_CLOSE();
?>