PHP: MySQL Select Query vs String Operation

Question

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;
        }
    }
}

But a string of 100,000 names in memory is going to be painfully difficult to change (especially in code), and take a large chunk of PHP memory as well.... so while it might seem faster, it's going to cause a lot of problems elsewhere in your system.... if you need to change details of the names in your string, or add new names/delete old ones, then you should be using a database — Mark Baker
– Mark Baker, Commented Mar 18, 2015 at 9:22
But stop using the old, deprecated MySQL extension, and start using MySQLi or PDO with prepared statements/bind variables — Mark Baker
– Mark Baker, Commented Mar 18, 2015 at 9:24
I think, you need to put your question here : codereview.stackexchange.com — Jenis Patel
– Jenis Patel, Commented Mar 18, 2015 at 9:24

Community · Accepted Answer · 2017-05-23 11:57:03Z

3

The database is pretty sure a good idea.

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.
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)
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

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)

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Mar 18, 2015 at 9:22

Roman Holzner

5,9782 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mark Baker Over a year ago

Plus the exploding string to array in OPs second method will be a noticeable overhead with 100,000 names; so it will slow as it grows, whereas a (properly indexed) database won't

Mohammad Over a year ago

Thank your, but if i want use while and get function for 1000 number , database is goo ?! simple code : for($i = 0;$i < 1000; $i++) { find($i); }

Roman Holzner Over a year ago

I don't exactly understand your question, what do you mean by "database is goo"?

Roman Holzner Over a year ago

I just wondered why no question is accepted and realized @Mohammad has only 45 reputation...

Collectives™ on Stack Overflow

PHP: MySQL Select Query vs String Operation

1 Answer 1

The database is pretty sure a good idea.

Your Code

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

The database is pretty sure a good idea.

Your Code

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related