Querying multiple tables (not a join)

PHP
1

The following script queries a database filled with mammal names and displays pages accordingly. The column “Taxon” simply lists the names of mammal taxons - Mammalia, Carnivora, Canidae, Canis_lupus, etc.

$MyURL = a webpage’s URL. For example, if you type in MySite/Mammalia, and Mammalia is in the database, then it displays a page.


$sql = "SELECT COUNT(Taxon) AS numberofurls
 FROM gz_mammals2
 WHERE Taxon = '$MyURL'";

$sql_result = mysql_query($sql,$conn);
$result = mysql_fetch_assoc($sql_result);

switch ($result['numberofurls'])
// switch ($result['count'])
{
 case 1:
 include_once("/inc/Page.php");
 echo "\
";
 break;

 case 0:
 include_once("/404.php");
 break;

 default:
 include_once($_SERVER['DOCUMENT_ROOT']."/Dupe.php");
 break;
}

I’d like to modify it so that it queries several tables joined together as one. For example, how could I modify it so that it queries the following three tables?*

gz_mammals2
gz_birds2
gz_plants2

  • Oops, I forgot and used the J-word. I don’t want to literally join them so much as link them together in a series. The tables are identical in structure, each one with a field named taxon.

Thanks.

2 
SELECT 'mammals' AS source
     , COUNT(*) AS numberofurls
  FROM gz_mammals2
 WHERE Taxon = '$MyURL'
UNION ALL
SELECT 'birds' AS source
     , COUNT(*) AS numberofurls
  FROM gz_birds2
 WHERE Taxon = '$MyURL'
UNION ALL
SELECT 'plants' AS source
     , COUNT(*) AS numberofurls
  FROM gz_plants2
 WHERE Taxon = '$MyURL'

you might give consideration to having just one table

also, if you need the sum of the three counts, you can either do that with php, use this UNION query as a subquery to SUM them

3

Hmmm…I guess one table would be a lot easier to deal with. It might be harder on the front end, though; I have separate tables for mammals, birds, reptiles amphibians, fish, invertebrates, plants and prehistoric creatures, each organized as a spreadsheet, which I convert into a CSV file, then import into a database table. If I modify the data in one spreadsheet, I would then have to go back to square one and re-import all the CSV files.

But I guess it wouldn’t be that hard, and if I only modified a few rows, I could make the changes directly in the database table. Since I’ll eventually have to do some joins - to add species’ common names and other data - I guess it would be far easier to work with a single table. I think you talked me into it. :wink:

Also, what do you mean by “the sum of the three counts”? I use this query to simply display pages where a URL matches something in my database. I never really understood what “count” means. Would the sum of the three counts simply = the total number of rows in all tables?

Thanks.

4

definitely consider one table if you’re still editing your data in spreadsheets

there are front end apps which make editing a table just as easy

re: counts… the query i gave you produced 3 counts, one for each table you asked for – mammals, birds, plants

if you wanted just one (mammals + birds + plants) you could do that either in php or in mysql

5

OK, thanks. What are some of those front end apps for editing tables? Or should I just search for a thread titled “MySQL table apps,” or something like that?

6

mysql.com has a good one

i personally use heidisql

7

Another one which you may well already have available to you is PHPMyAdmin

8

Duh, stupid me. Yes, I use phpMyAdmin all the time. For some reason, I take it for granted. When someone talks about database apps, I assume they’re talking about something even better than phpMyAdmin - not that I have any major complaints about phpMyAdmin. I just have to learn more about how to use it. :wink:

9

I use navicat. I like the Application to modify my database