mysql / php / insert data into many tables

morleymouse · 05-01-05

hello,

I have a database with a table, lets call it "main" and this is like the... well main table.

Then I have other tables of various names, but they hall have a prefix lets say "other_"

So my database consists of tables such as:
main
other_morleymouse
other_tv
other_coffee

etc...

Next step is, when I add a new row to "main" I want that row to automatically be added to all the "other_" tables. Obviously ill be adding this data via a php script rather than phpmyadmin, so in this script im looking for a way to get all the "other_" tables. Im fine with inserting stuff but I think ill need some form of loop and some way of getting the prefixed tables. And thats where im not so sure, not even sure if its possible, hope it is though!

All help much appreciated, as always

ianm · 05-01-05

Code:

SHOW TABLES LIKE 'other_%';

Returns a list of tables within the current database that start with other_

You can then just loop through them and do your insert/update to each one.

It does however raise the question of the structure of your database in the first place! Do you really need to have all these duplicate rows?

morleymouse · 05-01-05

im not sure yet, but I think so, its not so much duplicated as, slightly altered in each case. its complicated, as you've probably guessed.

General release to the public should be not too far away now, its something special too! <rolf harris style> can you guess what it is yet </rolf harris style>

ill have a play with that, cheers.

morleymouse · 05-01-05

okay that works, but it also picks up another table called "otherstuff" which I dont want to add anything to yet. So is there like a

SHOW TABLES LIKE 'other_%' but != 'otherstuff'

there will only be this one other table with 'other' at the front, and Im not really sure why it picks that up as I thought the _ would stop it being a match?

also, as im not in my loop, is there a way to get the table name? I can get field names etc but no idea how to get the actual table name

thanks!

ianm · 05-01-05

_ is actually the wildcard for a single character so that's probably why it picks up 'otherstuff'. I don't think you can specify a not like bit - but as it's only 1 table you could always cater for it in the populating tables logic.

Quote:

also, as im not in my loop, is there a way to get the table name?

Not quite sure what you mean here. If you run "SHOW TABLES" as a query then it returns a dataset that just has 1 column in it (the name of each of the tables). So you can work with the dataset in exactly the same way that you would for the results of any other query.

morleymouse · 05-01-05

sorry about that, my sloppy typing.

what I meant was now im in my loop, how do I get the table name, so like...

$qry = "SHOW TABLES LIKE 'other_%'";
while ($row = mysql_fetch_array($qry, MYSQL_ASSOC))
{
// Do my stuff here, but I need the name of the table that its currently on, as this gets used.
$table_name = "???";
}

I need the table name as it gets used in the code in the loop.

ianm · 05-01-05

OK with you now.

The name of the field that the query returns depends on the name of your database, so it's probably easier to use MYSQL_NUM rather than MYSQL_ASSOC then the table name is in field [0]. So your code looks something like this -

Code:

$qry = "SHOW TABLES LIKE 'other_%'";
while ($row = mysql_fetch_array($qry, MYSQL_NUM))
{
// Do my stuff here, but I need the name of the table that its currently on, as this gets used.
$table_name = $row[0];
}

morleymouse · 05-01-05

I see, ive never used anything over than MYSQL_ASSOC before, so with this MYSQL_NUM where before I was using $row[column_name] will I now be using its ID?

so say the tables set up like:

username | password | age
would I call these with...
$row[1] | $row[2] | $row[3]

and $row[0] is the table name then?

ianm · 05-01-05

Close.

If you've got a normal query like

SELECT username, password, age from users

then $row[0] is the same as $row['username']
and $row[1] is the same as $row['password']
and $row[2] is the same as $row['age']

What you are doing with the SHOW TABLES query is returning 1 column ($row[0]) that contains the names of the tables. Try thinking of it as a query like 'SELECT tablenames FROM database' and you'll get the idea.

There is also a fieldname that you can use with MYSQL_ASSOC but its name is something like tables_in_databasename so it's easier just to use [0] rather than the name.

Also the MYSQL_NUM only applies for that 1 query, you can still carry on using MYSQL_ASSOC for your other queries so that you can use the column names.

morleymouse · 05-01-05

aw deer, now I get this when I run the script

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /x/x/x/x/index.php on line 48

and lines 47, 48 and 49 are...

$qry = 'SHOW TABLES LIKE "other_%"';
while ($row = mysql_fetch_array($qry, MYSQL_NUM))
{