Searching MySQL table names

Hello,

I would like to search all of the table names in a MySQL
database using a simple HTML form and PHP. I am stuck. I have attached the
code I have so far. How do I apply the search to just the table names in the
database?

Thanks in advance,

John

<form name="search" method="post" action="<?=$PHP_SELF?>">
<input type="text" name="find" />
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";

//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}

// Otherwise we connect to our Database
mysql_connect("hostaddress", "username", "password") or die(mysql_error());
mysql_select_db("sand2") or die(mysql_error());

// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);

//Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM sand2 WHERE upper($field) LIKE'%$find%'");

I don't use MySQL, but I believe that table names are stored in the 'Tables'
table in the INFORMATION_SCHEMA. So,

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'sand2'
AND table_name LIKE '%$find%'

On Tue, 3 Feb 2009 06:13:29 +0000 (UTC), "bregent"
<webforumsuser (AT) macromedia (DOT) com> wrote:

I don't use MySQL, but I believe that table names are stored in the 'Tables'
table in the INFORMATION_SCHEMA. So,

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'sand2'
AND table_name LIKE '%$find%'

A query on the database will get the table names:

myqsl_query("SHOW TABLES FROM sand2 LIKE '%$find%'");

Gary

Gary,

Thanks, that seems to work. However, now I have another problem: printing out the entire array of the table that results from the search. Any ideas on how to do that?

Thanks,

John

On Sat, 7 Feb 2009 04:50:17 +0000 (UTC), "ArizonaJohn"
<webforumsuser (AT) macromedia (DOT) com> wrote:

Gary,

Thanks, that seems to work. However, now I have another problem:
printing out the entire array of the table that results from the search.
Any ideas on how to do that?

You're welcome, John. What problem are you having? It should be very
straightforward.

$result=mysql_query("SHOW TABLES FROM sand2 LIKE '%$find%'")
or die(mysql_error());
if(mysql_num_rows($result)>0){
while($row=mysql_fetch_row($result)){
print "$row[0]<br />";
}
}else{
print "None found";
}

Gary

Hi Gary,

I tried the loop you provided, and when I perform the search, it is only
displaying the name of the table. I would like to display the rows within the
table. Normally, I would expect a while loop to get the job done, but for some
reason it is not working for me. Am I missing something?

Thanks,

John

On Sat, 7 Feb 2009 23:40:04 +0000 (UTC), "ArizonaJohn"
<webforumsuser (AT) macromedia (DOT) com> wrote:

I tried the loop you provided, and when I perform the search, it is only
displaying the name of the table. I would like to display the rows within the
table. Normally, I would expect a while loop to get the job done, but for some
reason it is not working for me. Am I missing something?

The SQL that you're using only returns the names of tables in the database
containing the value in the $find variable. If your goal was to search in
the tables for some data, you're on the wrong track.

Gary

Hi Gary,

My goal is to search the table names, but then print the contents of the table that matches the search.

Any ideas on how I can do this?

Thanks,

John

On Mon, 9 Feb 2009 19:22:47 +0000 (UTC), "ArizonaJohn"
<webforumsuser (AT) macromedia (DOT) com> wrote:

My goal is to search the table names, but then print the contents of the
table that matches the search.

Any ideas on how I can do this?

Thanks,

You're welcome, John. Untested code, so beware of typos:

$result=mysql_query("SHOW TABLES FROM sand2 LIKE '%$find%'")
or die(mysql_error());
if(mysql_num_rows($result)>0){
while($table=mysql_fetch_row($result)){
print "<p>Table: $table[0]</p>\n";
$r=mysql_query("SELECT * FROM `$table[0]`");
while($row=mysql_fetch_row($r)){
print "<tr>"
foreach($row as $field){
print "<td>$field</td>";
}
print "</tr>\n";
}
print "</table>\n";
}
}else{
print "None found";
}

Gary

Hi Gary,

Thanks for the help. When I try the code you suggest, it gives me this error:

Parse error: syntax error, unexpected T_FOREACH

It looks like the "foreach" loop is not working. Any ideas?

Thanks,

John