PHP - mySQL

Einleitung

Seit PHP 5 kann man (und sollte inzwischen auch) die Klasse mysqli nutzen um Queries auf mySQL zu programmieren.

Mysqli unterstützt wie früher den prozeduralen Programmierstil, so kann man alten Code relativ leicht anpassen. Oft reicht es bei den Befehlen einfach ein i anzuhängen (mysqli statt mysql).

Zusätzlich kann man jetzt auch Objekt orientiert arbeiten. Das erzeugt übersichtlicheren Code.

Die Performance soll sich im Vergleich zu mysql verbessert haben. Außerdem gibt es einige neue Features.

PHP - mySQL - Snippets

Beispiele

Aus php.net

Objektorientierter Stil

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* Create table doesn't return a resultset */
if ($mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {
    printf("Table myCity successfully created.\n");
}

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
}

/* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */
if ($result = $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT)) {

    /* Note, that we can't execute any functions which interact with the
       server until result set was closed. All calls will return an
       'out of sync' error */
    if (!$mysqli->query("SET @a:='this will not work'")) {
        printf("Error: %s\n", $mysqli->error);
    }
    $result->close();
}

$mysqli->close();
?>

Prozeduraler Stil

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* Create table doesn't return a resultset */
if (mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {
    printf("Table myCity successfully created.\n");
}

/* Select queries return a resultset */
if ($result = mysqli_query($link, "SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", mysqli_num_rows($result));

    /* free result set */
    mysqli_free_result($result);
}

/* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */
if ($result = mysqli_query($link, "SELECT * FROM City", MYSQLI_USE_RESULT)) {

    /* Note, that we can't execute any functions which interact with the
       server until result set was closed. All calls will return an
       'out of sync' error */
    if (!mysqli_query($link, "SET @a:='this will not work'")) {
        printf("Error: %s\n", mysqli_error($link));
    }
    mysqli_free_result($result);
}

mysqli_close($link);
?>

Weitere Beispiele

Standard Select

// Connection
$db = new mysqli('localhost','user','pass','database');

// Check Error
if(mysqli_connect_errno()){
  echo mysqli_connect_error();
  exit();
}

// Query
$result = $db->query("call getUsers()");
if($result){
  // Cycle through results
  while ($row = $result->fetch_object()){
    $user_arr[] = $row;
  }
  // Free result set
  $result->close();
} else echo($db->error);

// Close connection
$db->close();

Daten fetchen

while ($row = $myquery->fetch_assoc()) {
  $markup.= '
    <tr>
      <td>'.$row["firstname"].'</td>
      <td>'.$row["lastname"].'</td>
      <td>'.$row["city"].'</td>
   </tr>
';
}