PHP MySQL, Find Tables with variations of a name-CodePudding

I have one or more tables in my MySQL DB. Named: 'SCI-01-123' AND 'SCI-01-123-1', etc.

I want to search for all table names with the base name: 'SCI-01-123' and the added -1, -2 etc. My $result->num_rows shows 2 rows, but I do not get two names when I implode the array. Only the first.

What am I doing wrong? Is my query wrong or should I handle the array differently. I have looked at the PHP and MySQL documentation, but I can not find a solution.

$patient_id = 'SCI-01-123'; //looking for 'SCI-01-123' AND 'SCI-01-123-1'

$query = "SHOW TABLES LIKE '$patient_id%'";

if ($result = mysqli_query($mysqli, $query)) {
    echo "num_rows: $result->num_rows\n";
    $array =  $result->fetch_assoc();

    echo implode("\n", $array) . "\n";

    foreach ($array as $value) {
        echo "$value\n";
    }
    // both echo only the first table name: SCI-01-123
    
    mysqli_free_result($result);
} else {
    echo  "query error";
}

Any help is much appreciated.

CodePudding user response：

$result->fetch_assoc() - Fetch the next row of a result set as an associative array

To fetch all result rows as an associative array or a numeric you should instead use

$result->fetch_all(MYSQLI_ASSOC) or $result->fetch_all(MYSQLI_NUM)

Try this

$patient_id = 'SCI-01-123'; //looking for 'SCI-01-123' AND 'SCI-01-123-1'

if ($result = mysqli_query($mysqli, "SHOW TABLES LIKE '$patient_id%'")) {
    echo "num_rows: $result->num_rows\n";
    $array = $result->fetch_all(MYSQLI_NUM); // returns a numeric array or arrays

    foreach ($array as $arr) {
        echo $arr[0].PHP_EOL;
    }
    
    mysqli_free_result($result);
}else{
    echo  "query error";
}