CODEWITHSUNDEEP
phpmysqlarraysexplode
Mohammad
Mohammad

Reputation: 327

PHP: MySQL Select Query vs String Operation

I have a database and a string with about 100,000 key / value-pair records and I want to create a function to find the value.

Is it better to use a string or a database, considering performance (page load time) and crash safety? Here are my two code examples:

1.

echo find("Mohammad");

function find($value){
    $sql = mysql_query("SELECT * FROM `table` WHERE `name`='$value' LIMIT 1");
    $count = mysql_num_rows($sql);
    if($count > 0){
        $row = mysql_fetch_array($sql);
        return $row["family"];
    } else {
        return 'NULL';
    }
}

2.

$string = "Ali:Golam,Mohammad:Offer,Reza:Now,Saber:Yes";

echo find($string,"Mohammad");

function find($string,$value){
    $array = explode(",",$string);
    foreach($array as $into) {
        $new = explode(":",$into);
        if($new[0] == $value) {
            return $new[1];
            break;
        }
    }
}

Upvotes: 0

Views: 223

Answers (1)

Roman Holzner
Roman Holzner

Reputation: 5928

The database is pretty sure a good idea.

  1. Databases are fast, maybe they are not as fast as basic String operations in PHP, but if there is a lot of data, databases will probably be faster. A basic select Query takes (on my current default Desktop Hardware) about 15ms, cached less than 1ms, and this is pretty much independend of the number of names in your table, if the indexes are correct. So your site will always be fast.

  2. Databases won't cause a StackOverflow or an out of memory error and crash your site (this is very depending on your PHP-Settings and Hardware)

  3. Databases are more flexible, imagine you want to add / remove / edit names after creating the first Data-Set. It's very simple with "INSERT INTO", "DELETE FROM" and "UPDATE" to modify the data, better than editing a string somewhere in your code with about 100.000 entries.

Your Code

  1. You definitly need to use MySQLi or PDO instead, code maybe like this:
$mysqli = new mysqli("host", "username", "password", "dbname");
$stmt = $mysqli->prepare(
    'SELECT string FROM table WHERE name = ? LIMIT 1'
);
$stmt->bind_param("s", $value);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($string);
$stmt->fetch();
$stmt->close();

This uses MySQLi and Prepared Statements (for security), instead of the depracated MySQL extension (in PHP 5.5)

Upvotes: 3

Related Questions