help searching a database

[BlackRaven]

yeah hi, wondering if someone could help. The thing is i have a database withsome 100 tables, i was wondering if it would be possibile to search for a entry without having to know all the table names?

database: books
================================================
Title ¦Author¦descript¦shipping
===============================================
since all tables have the title field, i thought i could use that for the mysql query. Also would it be possible to print out the tablename if there is a match?

[ergophobe]

100 tables all with the "title" field? Are you sure you have that optimally organized?

Anyway, as for you question, it can be done. You could use a first query using

mysql_list_tables() [php.net]

to get all the names, then you could loop through the list and either keep adding an "OR" to the WHERE clause as in

OR $table_list[$i] = $title

or you could send 100 queries, which would make it easy to get the table name back.

[BlackRaven]

ok still having problems, not sure if i am going in the right way. Basically I want preform a search from the initial list of table, and print out the results along with the table name.

Example

============================================
OUTPUT
============================================
Dishwasher(12)
Microwave(3)
Stove(0)

==============================================

<?php
$user="red";
$pw="red";
$dbname = 'AppliancesANDKitchen';
$mysql_access = mysql_connect("localhost", $user, $pw);
$result = mysql_list_tables($dbname);

if (!$result) {
print "DB Error, could not list tables\n";
print 'MySQL Error: ' . mysql_error();
exit;
}
$resultcount=0;
while ($row = mysql_fetch_row($result)) {
$query = "SELECT title FROM $row[0] WHERE title LIKE '%" . $search . "%'";
$list($totalrows)=mysql_fetch_row(mysql_query($query));
if($totalrows=="0") {
$resultcount=0;
}
else{
$resultcount=$resultcount + 1;
}

}
print "$row[0]($resultcount)";
mysql_close($mysql_access);
?>

[ergophobe]

follow right up to here:

$list($totalrows)=mysql_fetch_row(mysql_query($query));

First off, I think you mean $list[$totalrows] (that is, it should be an array that is storing the rows, no?)

Second, $totalrows is undefined.

It looks like all you are trying to do based on your sample output is print the names of the tables and the number of hits in each one for the search. In that case,

$result = mysql_query($query);
$list[$row[0]] = mysql_num_rows($result);

Then, once you've fully built up your list

foreach($list as $title => $count)
{
echo "The table $title returned $count hits";
}

Alternatively, if all you're doing is building an output string, you coudl do this in your initial while loop and just save it all to one string there.

[BlackRaven]

for some reason i keep getting 3 for output, even though have different values for each table.
=====================================================
OUTPUT
=====================================================
The table airconditioner returned 3 hits
The table foxy returned 3 hits
The table two returned 3 hits
=====================================================

<?php
$user="red";
$pw="red";
$dbname = 'AppliancesANDKitchen';
$mysql_access = mysql_connect("localhost", $user, $pw);
$result = mysql_list_tables($dbname);
$search='red';
if (!$result) {
print "DB Error, could not list tables\n";
print 'MySQL Error: ' . mysql_error();
exit;
}
$resultcount=0;
$totalrows=0;
while ($row = mysql_fetch_row($result)) {
$query = "SELECT title FROM $row[0] WHERE title LIKE '%$search%'";
$list[$row[0]] = mysql_num_rows($result);
}

foreach($list as $title => $count)
{
echo "The table $title returned $count hits<br/>";
}

mysql_close($mysql_access);
?>

[ergophobe]

Opps, I wasn't paying attention... but neither were you, so we're even ;-) (you forgot a line from my example, but it wouldn't have worked anyway, because I was using the variable $result, which you were already using).

Anyway, you're counting from your first and only $result and not sending another query. It's telling you the number of tables returned.

You need something like this

while ($row = mysql_fetch_row($result)) {

$hits_query = "SELECT title FROM $row[0] WHERE title LIKE '%$search%'";
$hits_result = mysql_query($hits_query);
$list[$row[0]] = mysql_num_rows($hits_result);

}

[coopster]

Also note that the function mysql_list_tables() [php.net] is deprecated. It is preferable to use mysql_query() [php.net] to issue a

SQL SHOW TABLES [FROM db_name] [LIKE 'pattern']

statement instead.

[ergophobe]

Thanks coop! I forgot about that.

Actually, I guess I never knew, since I can't think of an instance where I've used mysql_list_tables(), but I suppose it was deprecated at the same time and for the same reasons as mysql_list_fields() [php.net].

[BlackRaven]

thanks ergophobe it works now, although i have to use the mysql_list_tables method, i still cant can't get the

$result=mysql_query("SHOW TABLES FROM $dbname");
to work. For some reason i get the

mysql_num_rows(): supplied argument is not a valid MySQL result resource in...

+++++++++++++++++++++++++++++++++++++++
Non working Versiton Using SHOW TABLES
+++++++++++++++++++++++++++++++++++++++
<?php
$user="red";
$pw="red";
$dbname = 'AppliancesANDKitchen';
$mysql_access = mysql_connect("localhost", $user, $pw);
$result = mysql_query("SHOW TABLES FROM $dbname");

$search='red';
if (!$result) {
print "DB Error, could not list tables\n";
print 'MySQL Error: ' . mysql_error();
exit;
}
$resultcount=0;
$totalrows=0;

while ($row = mysql_fetch_row($result)) {

$hits_query = "SELECT title FROM $row[0] WHERE title LIKE '%$search%'";
$hits_result = mysql_query($hits_query);
$list[$row[0]] = mysql_num_rows($hits_result);

}

foreach($list as $title => $count)
{
echo "The table $title returned $count hits<br/>";
}

mysql_close($mysql_access);
?>

[coopster]

Are you certain that every table being listed does indeed have a column named "title" in every table in your database? Try throwing in a bit of error checking to see...

$hits_query = "SELECT title FROM $row[0] WHERE title LIKE '%$search%'"; 
$hits_result = mysql_query($hits_query)  
 or exit('Error (' . mysql_errno() . '): ' . mysql_error()); 
$list[$row[0]] = mysql_num_rows($hits_result);