The following scripts show examples of some applications that you can use to access functions of MySQL databases.

Creating a table

<?php
$server= "dbXX.ionos.com"; /* Address of the IONOS Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "CREATE TABLE " . $table . "(name varchar(25),email varchar(25),id int(11))")) 
  die(printf("<H3>Table cannot be created: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error()));

mysqli_close($link);
?>

Updating a Table

You can change entries of an existing table by updating the table.

With the update query, all entries in the field email, which contain the part ionos.com, are set to mail@example.com.

<?php
$server= "dbXX.ionos.com"; /* Address of the IONOS Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access to SQL server and creation of the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "UPDATE " . $tabelle 
      . " SET email = 'mail@example.com' WHERE INSTR(LCASE(email), 'ionos.com')")) 
  die(printf("<H3>Record cannot be updated: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$number = mysqli_affected_rows($link); 
printf("There were " . $number . " records updated.<BR />");

mysqli_close($link);
?>

Deleting a table

The following script deletes a table from the database.

Warning

Under no circumstances should you delete the database, only individual tables, as the database cannot be recreated.

<?php
$server= "dbXX.ionos.com"; /* Address of the IONOS Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access the SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "DROP TABLE " . $table)) 
  die(printf("<H3>Table cannot be deleted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error()));

mysqli_close($link);
?>

Entering Data in a Table

<?php
$server= "dbXX.ionos.com"; /* Address of the IONOS Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$number = 0; 
/* Einfügen der Daten */ 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('John Smith','john@smith.com', 1)")) 
 die(printf("<H3>Data record 1 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('Jane Doe','jane@doe.com', 2)")) 
 die(printf("<H3>Data record 2 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('Thomas Schmitt','thomas@schmitt.com', 3)")) 
 die(printf("<H3>Data record 3 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('Example Information','info@example.com', 4)")) 
 die(printf("<H3>Data record 4 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('IONOS','support@ionos.com', 5)")) 
  die(printf("<H3>Data record 5 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 

/* Total number of records inserted */ 
printf("There were " . $number . " records inserted.<BR />");

mysqli_close($link);
?>

Reading Data/Viewing the Table

<?php
$server= "dbXX.ionos.com"; /* Address of the IONOS Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access the SQL server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$result=mysqli_query($link, "SELECT * FROM " . $table . " ORDER BY name"); 
$row_cnt = mysqli_num_rows($result); 
printf("There were " . $row_cnt . " records found.<BR />"); 

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

while ($field = mysqli_fetch_field($result)) { 
  echo "<th>$field->name</th>"; 
} 
$field_cnt = mysqli_field_count($link); 
echo "</tr>"; 
while($row = mysqli_fetch_row($result)) { 
  echo "<tr>"; 
  for($i = 0; $i < $field_cnt; $i++) { 
    echo "<td>$row[$i]</td>"; 
  } 
  echo "</tr>\n"; 
} 
echo "</table>"; 

/* Close results */ 
mysqli_free_result($result);
mysqli_close($link);
?>

Reading Specific Entries of a Table

In this script, only certain entries are read from a table. This improves the speed of the script considerably, which in turn reduces the loading time of the website.

 

Here, the first 3 entries are retrieved from the database, which contain united in the email field, or which contain only IONOS.

 

The selection of the email field is not case sensitive by using LCASE().

 

This query is very efficient because only what is really needed is transferred. Only the fields name and email are transmitted, the last field id is ignored.

<?php
$server= "dbXX.ionos.com"; /* Address of the IONOS Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$result = mysqli_query($link, "SELECT name, email FROM " . $table 
 . " WHERE (name = 'IONOS' OR INSTR(LCASE(email), 'united')) 
 ORDER BY NAME DESC LIMIT 3"); 

$row_cnt = mysqli_num_rows($result); 
printf("Es wurden " . $row_cnt . " records found.<BR />");
/* Output of the table in HTML format */ 
echo "<table border=\"1\" align=center width=50%"; 
echo "<tr>"; 
echo "<div color=\"#ffff00\">"; 
$field_cnt = mysqli_field_count($link); 
while ($field = mysqli_fetch_field($result)) { 
  echo "<th>$field->name</A></th>"; 
} 
echo "</font></tr>"; 
while($row = mysqli_fetch_row($result)) { 
  echo "<tr>"; 
  for($i = 0; $i < $field_cnt; $i++) { 
    echo "<td align=center>$row[$i]</td>"; 
  } 
  echo "</tr>\n"; 
} 
echo "</table>";
/* close result set */ 
mysqli_free_result($result);
mysqli_close($link);
?>

Deleting Individual Entries from a Table

<?php
$server= "dbXX.ionos.com"; /* Address of the IONOS Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access the SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "DELETE FROM " . $table. " WHERE id = 3")) 
       die(printf("<H3>Record cannot be deleted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$number = mysqli_affected_rows($link); 
printf("There were " . $number . " records deleted<BR />");

mysqli_close($link);
?>