Get data from all tables in database using php and mysql

Question

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?

possible duplicate - mysql-select-all-tables-from-a-database — EvilEpidemic
– EvilEpidemic, Commented Jun 11, 2014 at 14:39
you cannot do select * from *. at BEST you could use join to pull in multiple related tables. but those tables have to be related, you can't join two completely different tables and expect to get sensible results. nor can you do union on fundamentally different tables as well. — Marc B
– Marc B, Commented Jun 11, 2014 at 14:39
You need to research JOINS - this is the basics of MySQL here, so any online tutorial or book should cover it. A tip though: do not use mysql: it is deprecated and thus unsecure. Use mysqli or PDO instead. — ElendilTheTall
– ElendilTheTall, Commented Jun 11, 2014 at 14:40
i hope you're not suggesting selecting every table in every query. that's a horrible idea — Jeff Hawthorne
– Jeff Hawthorne, Commented Jun 11, 2014 at 14:45
Agreed -I'd like to do this as cleanly as possible, as I don't want to pick up any bad habits as I move forward. I think I was suggesting that, but wasn't aware that it's a terrible way to do things. Suggestions? — alexconnor7
– alexconnor7, Commented Jun 11, 2014 at 14:51

Community · Accepted Answer · 2023-11-17 19:22:09Z

2

To display all records in a table, you need to do:

while($row_pge_logbookabout = mysql_fetch_assoc($pge_logbookabout)){
   echo $row_page_logbookabout['COLUMN'];
}

However if you mean that you want to display all records in each table, therefore you need separate queries to do so.

$query = mysql_query("select * from table1");
while($row_table1 = mysql_fetch_assoc($query)){
 // code here
}


$query = mysql_query("select * from table2");
while($row_table2 = mysql_fetch_assoc($query)){
 // code here
}

Please note this way of connecting to database, quering and fetching data will be deprecated starting PHP 5.5.0. Alternatively you can use PDO prepared statements

edited Nov 17, 2023 at 19:22

CommunityBot

11 silver badge

answered Jun 11, 2014 at 14:42

CMPS

7,7874 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Funk Forty Niner Over a year ago

"Alternatively you can use PDO_MYSQL" - The use of mysqli_ and/or PDO are not safeguards against SQL injection; not on their own that is. Using mysqli_* with prepared statements, or PDO with prepared statements will.

CMPS Over a year ago

@Fred-ii- Thanks I should have mentioned that, fixed it now

brechmos · Accepted Answer · 2014-06-11 14:52:15Z

0

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.

answered Jun 11, 2014 at 14:52

brechmos

1,3161 gold badge11 silver badges22 bronze badges

9 Comments

alexconnor7 Over a year ago

Okay, it looks like PDO is the way to go for me - I'm going to research making this change now but will my server environment need to change? Will I need to update phpmyadmin or anything?

brechmos Over a year ago

PDO is a different set of PHP methods to do queries into your database. So nothing should have to change on the backend.

brechmos Over a year ago

I can send you some basic code to get you going if you want. Good luck!

alexconnor7 Over a year ago

That'd be great, thanks! Here's what I have so far to connect to DB: $pdo = new PDO("mysql:host=localhost;dbname=####", 'username', 'password');

alexconnor7 Over a year ago

I'm currently looking into how to echo data from db

|

Gokhan Dilek · Accepted Answer · 2014-06-11 14:43:49Z

0

Select multiple tables using mysql

Update mutiple tables using mysql

How are you hosting your websites? Are they on the same hosting? If that is the case, you can use localhost. Otherwise you will need to enter the external hosting mysql database credentials.(multisite single database setup).

answered Jun 11, 2014 at 14:43

Gokhan Dilek

4,4224 gold badges25 silver badges28 bronze badges

1 Comment

alexconnor7 Over a year ago

Yes, they are all on the same hosting, however thanks for the tip :-)

hlscalon · Accepted Answer · 2014-06-11 14:48:30Z

0

First you select all tables, then you get the data from each one. You could do this if you are using mysql_*, but I strongly recommend to use mysqli_* or PDO.

$result = mysql_query("show tables"); // Select all tables
while($table = mysql_fetch_array($result)) {
     $queryT = mysql_query("select * from '".$table[0]."'");
     while($rtable = mysql_fetch_array($queryT)){
          // data of the table
     }
}

answered Jun 11, 2014 at 14:48

hlscalon

7,6004 gold badges35 silver badges41 bronze badges

2 Comments

alexconnor7 Over a year ago

Thanks - I'm not familiar with mysqli or PDO, however judging by feedback so far it looks as though it'd be a good idea to look into these.

brechmos Over a year ago

I would suggest to go straight to PDO. It isn't hard to use. There are lots of tutorials around (e.g., code.tutsplus.com/tutorials/…). You might also want to look at prepared statements (helps to sanitize queries).

Collectives™ on Stack Overflow

Get data from all tables in database using php and mysql

4 Answers 4

2 Comments

9 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

9 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related