Query for Show Tables?

[eluser]diywm[/eluser]
I wondered if there was a way to do a MySQL query on a database to get a resultset of all tables whose names fit a criteria (without having to go to the information_schema database).

I wanted to do a SHOW TABLES LIKE 'fred%' to get all tables with names starting with fred. I can do that, but it seems to return no results that I can loop through.

Do I have to go to the meta data in the information_schema if I want to do this?

[eluser]KingSkippus[/eluser]
If your database name is, say, blogdb, you can loop through like this:

Code:

$this->load->database();
$sql = 'show tables';
$sth = $this->db->query("SHOW TABLES LIKE 'fred%'");
foreach ($sth->result_array() as $row) {
  echo htmlentities($row['Tables_in_blogdb'])."<br />\n";
}

[eluser]diywm[/eluser]
I had not realised the key name to the array would be something like 'Tables_in_blogdb' - thanks for pointing this out. It turns out that because I am doing a like, I have to build the key to be like this, where the like parameter forms part of the key : Tables_in_blogdb (b00043%)

$sql = "SHOW TABLES LIKE '".$tableprefix."%'";
$query = $this->query($sql);
foreach ($query->result_array() as $row){
$tablename = $row['Tables_in_blogdb ('.$tableprefix.'%)'];
echo tablename;
}

[eluser]KingSkippus[/eluser]
Just for your reference, I got that by running the above code with the following line instead of the foreach loop:

Code:

echo '<pre>'.htmlentities(var_export($sth->result_array(), TRUE)).'</pre>';

I tend to use that little snippet a lot while troubleshooting stuff, it shows all of the elements of $sth->result_array, which is where I got the 'Tables_in_database' name from.

[eluser]diywm[/eluser]
Thanks a lot KingSkippus - your help is and was very much appreciated. I didn't know about htmlentities either. So much to learn. I am fighting overwhelm.