如何使用函数从mysql中拉出多行

Question

How can I pull multiple rows from a database using a function?

The function I have is:

function search($subject, $table) {
    $query = "SELECT {$subject} ";
    $query .= "FROM {$table} ";
    $content = mysql_query($query);
    return $content;
}

On the page which is calling the function I have:

if (isset($_POST['search'])){
    $search = $_POST['search'];
}

$content = search($subjectName, $tableName);

while ($results = mysql_fetch_assoc($content)){
    $phrase = $results[$subjectName];
    //if phrase exists in database
    if (strpos($search,$phrase) !== false) {
        echo $phrase;
    //if phrase does not exist in database
    } else {
        echo 'fail';    
}

This setup does not work, however if I put everything into the function it works:

function search($subject, $table, $where = 0, $is = 0) {
    global $search;
    $query = "SELECT {$subject} ";
    $query .= "FROM {$table} ";
    if ($where > 0) {
        $query .= "WHERE {$where} = '{$is}' ";
    }
    $content = mysql_query($query);
    while ($results = mysql_fetch_assoc($content)){
        $phrase = $results[$subject];
        //if phrase exists in database
        if (strpos($search,$phrase) !== false) {
            echo $phrase;
        //if phrase does not exist in database
        } else {
            echo 'fail';
        }
    }
    return $content;
}

On Page:

search('main_subject', 'main_search');

The problem is that I would like to call that function again in the if statement to have it search for another phrase. Is there an easier way to do this?

EDIT: The current setup pulls the first item in an infinite loop.

Answer 1

There are a number of issues that should be addressed here:

First, if you are trying to search a field for a specific partial match, you would likely want to use SQL LIKE construct.

SELECT field FROM table WHERE field LIKE '%search phrase%'

Doing this would eliminate the need for you to iterate through each row trying to do a string comparison for your search phrase, as you would only be returned the rows that match the search phrase and nothing more.

Second, using global to make data available to your function is really poor practice. You really should be passing any data needed by the function to the function as a parameter. This would include your search string and probably your database connection/object.

function search($field, $table, $search, $db) {
    ...
}

Third, You are doing nothing at all to prevent against SQL injection right now. You need to escape the input data or, better yet use prepared statements.

Fourth, you really should not be using the mysql_* functions. They are deprecated. Try using mysqli or PDO (and I would highly recommend going ahead and learning how to use prepared statements with either of these.) You might start with mysqli ,at it provide procedural functions similar to mysql_* so the learning curve might be less steep (though really most experienced developers would prefer the object-oriented usage).

Fifth, to your original question. If you want search for multiple phrases, there are a couple approaches you can take. You can either pass all the phrases at once like this:

SELECT field FROM table WHERE field LIKE '%search phrase%' OR field LIKE '%another search phrase%'

Or, you could just make iterative function calls to get the results you want. This really depends on whether you only want to search for the second phrase if the first is not successful (use the iterative approach) or whether you just want all possible matches in one query (use the LIKE-OR-LIKE approach).