Ninedays Blog

Making Updates in MySQL. How Many are Affected?

When you need to update a record in your database you'll use a MySQL UPDATE query. If you want to know how many rows were affected you could you use this next addition to our Query class in the functions-db.php file.

PHP

function Affected() {
    return mysql_affected_rows($this->connect);
}

I'll create a new sample script to use this function, along with some of the ones we've already covered.

PHP

<?php
    # sample2.php file

    include_once('includes/config.php');
    include_once('includes/functions-db.php');

    $updateQuery = 'UDATE `sample_table` SET `date` = NOW() WHERE `id` > 10';

    $result = new Query($updateQuery,$sql);
    if($result->Error()) echo $result->Error();
    else {
        // this will tell us how many rows were updated
        echo $result->Affected();
        $result->Free();
    }

    $result->Close($sql);
?>

Super easy eh?

How about pulling data from the database? That's just as easy!

Counting Records

To find out how many records a query returns we would add this function to our Query class in the functions-db.php file.

PHP

function NumRows() {
    return mysql_num_rows($this->action);
}

If I wanted to see how many records have an id larger than 5 I could use this new sample file:

PHP

<?php
    # sample3.php file

    include_once('includes/config.php');
    include_once('includes/functions-db.php');

    $selectQuery = 'SELECT * FROM `sample_table` WHERE `id` > 5';

    $result = new Query($selectQuery,$sql);
    if($result->Error()) echo $result->Error();
    else {
        // this will tell us how many rows were selected
        echo $result->NumRows();
        $result->Free();
    }

    $result->Close($sql);
?>

Using The Data Retrieved

Last but not least let's retrieve the actual data being pulled from the database. First add one last function to the Query class in the functions-db.php file.

PHP

function FetchAssoc() {
    return mysql_fetch_assoc($this->action);
}

And in two more sample files I'll demonstrate using the function to retrieve, first, one record; then, looping through an unknown number of records.

PHP

<?php
    # sample4a.php file
    # retrieving one record

    include_once('includes/config.php');
    include_once('includes/functions-db.php');

    $selectQuery = 'SELECT * FROM `sample_table` WHERE `id` = 3 LIMIT 1';

    $result = new Query($selectQuery,$sql);
    if($result->Error()) echo $result->Error();
    else {
        // print our results
        $row = $result->FetchAssoc();
        echo "id is: " . $row['id'] . "<br />\n";
        echo "name is: " . $row['name'] . "<br />\n";
        echo "date is: " . $row['date'] . "<br />\n";
        $result->Free();
        unset($row);
    }

    $result->Close($sql);
?>

PHP

<?php
    # sample4b.php file
    # retrieving and displaying multiple record

    include_once('includes/config.php');
    include_once('includes/functions-db.php');

    $selectQuery = 'SELECT * FROM `sample_table` WHERE `date` < NOW()';

    $result = new Query($selectQuery,$sql);
    if($result->Error()) echo $result->Error();
    else {
        // print our results
        echo "<table><tr><th>id</th><th>name</th><th>date</th></tr>";
        while($row = $result->FetchAssoc()){
            echo "<tr>";
            echo "<td>" . $row['id'] . "<td>\n";
            echo "<td>" . $row['name'] . "<td>\n";
            echo "<td>" . $row['date'] . "<td>\n";
            echo "</tr>";
        }
        echo "</table>";
        $result->Free();
        unset($row);
    }

    $result->Close($sql);
?>

Quick and easy. Just the way I like it!

Download

Download all the sample files and the functions-db.php file to play with! (zip)
BONUS: Now includes functions for constructing easy insert and update queries from Making MySQL Update and Insert Easier

NOTE: Download does not include the sample database or table used in the PHP sample scripts, those are reference of how to call the functions in the class.