Echoing From Multiple Tables

Tim_Silva

Hi,

Here is another situation when I need the PHPBuilder’s experts help.

My health-related site is currently echoing news articles from one specific subject (arthritis). The table has about 100 rows with a few standard fields (ID, title, content… etc). It works fine as it is.

What I would like to do now is to echo not only from the arthritis table but also from two other tables (alzheimers and cancer). All the tables are in the same database. So that this random 5 articles will be coming not from one single table (arthritis) but from all three. Btw, all 3 tables have the same structure.

This is the current (simplified) code with only one table.

<?php 
$hostname="myserver.net";
$username="myusername";
$password="mypass";
$dbname="mydb";
$usertable="arthritis";

mysql_connect($hostname,$username,$password) or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());

$data = mysql_query("SELECT * FROM $usertable ORDER BY RAND() LIMIT 5") 
or die(mysql_error()); while($info = mysql_fetch_array( $data ))

echo $info['title'];
echo "<br>";
echo $info['content']; 
?>

Thanks in advance for your always kind and helpful responses.

Tim

laserlight

What I would like to do now is to echo not only from the arthritis table but also from two other tables (alzheimers and cancer). All the tables are in the same database. So that this random 5 articles will be coming not from one single table (arthritis) but from all three. Btw, all 3 tables have the same structure.

You should combine all of them into one table, and instead have another table for disease/illness info.

Tim_Silva

Laserlight,

But on the long run, when the database grows to thousand of rows and hundreds of diseases, wouldn't be better to have different tables for different illnesses? Couldn't these tables be joint and queried at the PHP level? Or is it really better to do it in the database itself and not in the code?

I am more than willing to do it either way, I just want to be sure of doing it the "right" way.

Thanks,

Tim

Piranha

If it is only thousands of rows then it is a really small database. Relational databases are designed to handle millions and even billions of rows, and to do what you want with them. Programming language is not designed to do this kind of work.