+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query for Show Tables? (/showthread.php?tid=32386)
Query for Show Tables? - El Forum - 07-21-2010
[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?
Query for Show Tables? - El Forum - 07-21-2010
[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";
}Query for Show Tables? - El Forum - 07-22-2010
[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;
}
Query for Show Tables? - El Forum - 07-22-2010
[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.
Query for Show Tables? - El Forum - 07-22-2010
[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.