Database Basics

MySQL Commands

MySQL is a widely-used open-source relational database management system. Here are some essential MySQL commands for database management:

• Establishing a connection:

$link = mysql_connect("host", "username", "password");
$db = mysql_select_db("database", $link);
        

• Executing queries:

$rs = mysql_query("query", $link); // Retrieve data
$rs = mysql_query("query", $link); // Execute commands
        

• Fetching data:

$row = mysql_fetch_array($rs); // Retrieve data as an index array
$row = mysql_fetch_assoc($rs); // Retrieve data as an associative array
        

• Managing connections:

mysql_close($link); // Close connection
        

• Additional functions:

mysql_insert_id($rs); // Get ID of the last inserted record
mysql_error($link); // Retrieve the last error
$output = mysql_real_escape_string("unescaped_string", $link); // Secure data
        

Creating New Links with MySQL

To create a new link with additional security in MySQL, set the newlink parameter to true:

$link = mysql_connect("host", "username", "password", true);
    

MySQLi (MySQL Improved)

MySQLi is an enhanced extension of MySQL in PHP, providing improved features and security. Key features of MySQLi and PHP Data Objects (PDO) include:

• Connection versatility
• Enhanced security through prepared statements
• Support for atomic transactions with beginTransaction, commit, and rollBack

try {
    $dsn = 'mysql:host=localhost;dbname=library';
    $dbh = new PDO($dsn, 'dbuser', 'dbpass');
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $dbh->beginTransaction();
    $affected = $dbh->exec("sql"); // Execute SQL query
    $dbh->commit(); // Commit transaction
    $dbh->rollBack(); // Roll back transaction

    $sql = "SELECT * FROM table WHERE id = :param1";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':param1', $param1);
    $stmt->execute();
    $results = $stmt->fetchAll();

    foreach ($results as $row) {
        // Process each row
    }
} catch (PDOException $e) {
    echo "Failed: " . $e->getMessage();
}
    

Persistent Connections

Persistent connections in databases maintain their state even after executing a script. Various ways to establish persistent connections based on the database extension used include:

• In MySQL:

mysql_pconnect();
        

• In MySQLi:

mysqli_connect("p:host", "username", "password");
        

• Using PDO:

$dsn = 'mysql:host=localhost;dbname=example_db';
$options = array(PDO::ATTR_PERSISTENT => true);
$dbh = new PDO($dsn, 'dbuser', 'dbpass', $options);