CODEWITHSUNDEEP
phpsqlpdo
user1079425
user1079425

Reputation:

Can't fetch results of an SQL statement in PHP

i'm working on a search function in PHP, so i do want to be able to research any keyword in all database tables, but i can't fetch the result of the SQL statement which will do :

SELECT * FROM All_Tables

here's my code :

$getTables = $this->db->query("show tables");
$tmpString = '';

while ($table_data = $getTables->fetch(PDO::FETCH_NUM))
{
    $tmpString.=$table_data[0].',';
}

$ALL_DATABASE_TABLES = substr($tmpString,0,strlen($tmpString)-1); //Remove the last ,

echo " $ALL_DATABASE_TABLES "; //  Works, it shows all database tables

$query = "SELECT * FROM $ALL_DATABASE_TABLES" ; 

$stmt = $this->db->query($query) or die(print_r($this->db->errorInfo())) ;

echo "Cool1"; // Works
echo "$ALL_DATABASE_TABLES "; //Works

// This Loop doesn't work-----------------------
while ($row = $stmt->fetch(PDO::FETCH_NUM)) 
{
    echo "Cool2"; // Doesn't work

    echo "$row[0]" ; // Doesn't work
}
//----------------------------------------------

$stmt->closeCursor();

Do you have any idea about that ? Thank you guys

Upvotes: 0

Views: 146

Answers (1)

newfurniturey
newfurniturey

Reputation: 38416

Your code does the following:

  1. Select's a full list of tables in your database.
  2. Combines the full list found in #1 into a comma-separated list.
  3. Uses the comma-separated list from #2 in a SELECT * query, thus attempting to select every record from every table in your database in a single result.

I'm surprised you actually don't receive an error or timeout/warning. The reason is is because using a comma-separated list will attempt to do a join between every table. From the manual:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

When your table-list grows to even three or four tables and you have 5 or six columns in each table with a long list of rows, your query will take forever to run. Now, a normal database has 10+ tables (at least) - so MySQL would either throw an error or just timeout (in my experience) - which is why I find it odd to you're not receiving one.

What exactly is your goal, besides selecting every record from every table in your database?

If you really just want to list every record in every table, you can perform a separate SELECT * for each table:

$getTables = $this->db->query("show tables");
$tables = array();

while ($table_data = $getTables->fetch(PDO::FETCH_NUM)) {
    $tables[] = $table_data[0];
}

foreach ($tables as $table) {
    $stmt = $this->db->query('SELECT * FROM ' . $table) or die(print_r($this->db->errorInfo()));
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        echo $row[0] . '<br />';
    }
    $stmt->closeCursor();
}

Upvotes: 2

Related Questions