Get data from all the tables in the database using php and mysql

advertisements

I have a series of websites, all of which must share the same information. Any updates to text must be made across all websites. Instead of editing each site individually and uploading the updates files one at a time, I figured it'd be far better to have a central source using MySQL - update the database, and all websites will be changed at once.

I have limited knowledge of PHP and MySQL - everything below is what I've been able to put together for myself so far, using various online sources:

<?php
//DB INFO///////////////////////////////////////////////////////////////////////////////////////
$host="localhost"; // Host name
$username="####"; // Mysql username
$password="####"; // Mysql password
$db_name="####"; // Database name

// Connect to server and select database
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Get all the data from the "example" table
$pge_logbookabout = "SELECT * FROM pge_logbookabout";
$pge_logbookabout = mysql_query($pge_logbookabout) or die(mysql_error());
$row_pge_logbookabout = mysql_fetch_assoc($pge_logbookabout);
?>

So far, I can use the above to select a table and echo in the HTML using:

<?php echo $row_pge_logbookabout['rep_lbapr'];?>

That's cool, but I'm only able to select one single table using this - I'd like to be able to select ALL tables, and simply enter variables in where I need them.

Will I need to repeat the third section of the above code for each table, or is there a simpler way for me to do this?


If you want to process over a series of tables you can use something like:

$query_tables = mysql_query('SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE "%table%" ');

while($table = mysql_fetch_assoc($query_tables)){

    $query = mysql_query("select * from ".$table['table_name'] );
    // code here
}

You will have to make sure you choose the proper table names in the first query so you are processing the proper tables.

Then you can use php to loop over the tables updating a particular column/field. Not sure if that is what you are looking for...

More information at http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

I agree with the above poster... switch to PDO_MYSQL.