need help select * tables in db

[seany123]

$tableList = array('table1_episodes', 'table2_episodes', 'table3_episodes', 'table4_episodes', 'table5_episodes');

 

the only constant is that they all end in _episodes.. anyway i can get them all?

[requinix]

SHOW TABLES LIKE "%_episodes"

But why do you need five of these tables? I bet there's a better, faster, and more efficient way to do it.

[seany123]

i have a script that needs to run through all these tables to check that the infomation inside is good... but i dont fancy writing out all the tables plus if i add more tables etc...

 

the code you provided didnt seem to work, i go this error...

 

Can't find file: '.\series\tables@0020like@0020@0022@0025_episodes@0022.frm' (errno: 22)

 

for example i need to use the above query like so...

 

        foreach($tableList as $tableName)
        {
            $query = "SELECT * FROM `{$tableName}`";
            $result = mysql_query($query) or die(mysql_error());
            if(mysql_num_rows($result)>0)
            {
                //There are records in this table, exit the loop and use $tableName
                break;
            }
        }

[requinix]

How are you running that query?

 

I was asking about the tables because I don't know of any good reason why you'd need five (or more) of the same thing. You probably should be storing everything in just one table.

[seany123]

something like:

 

    $get_episodes = mysql_query("SELECT * FROM `{$tableName}`);
    
    while($episode_row = mysql_fetch_assoc($get_episodes))
    {
        echo $episode_row['url'];
    }

 

as for the reason they are in different tables, well i could put them all in the same tables and assign each set with an id, but i have alot of pages in this format and it would take a long time to change it about... also its much easier to navigate to the correct place when all i have to do is open the correct table in mysql.

[TeNDoLLA]

Its not definitely easier way to make so many episodes table, and neither will it be easier to access them all instead of a one table. If I were you I would re-design your database before it grows more. Someday its gonna be even harder to change than now.

[seany123]

Its not definitely easier way to make so many episodes table, and neither will it be easier to access them all instead of a one table. If I were you I would re-design your database before it grows more. Someday its gonna be even harder to change than now.

 

everything is working fine on my site the way it is, i just need the above get * tables for 1 diagnostics script... there must surely be a way to do it.

[seany123]

anybody know any other method to do this?

 

 

[PFMaBiSmAd]

everything is working fine on my site the way it is

 

^^^ No it's not. If it where working fine, it would be easy for you to query for the data that you want, and you would have been done two days ago when you first started this thread.

[EdwinPaul]

2 remarks:

SHOW TABLES LIKE "_episodes"              // without the %-sign

 

You could read: http://php.net/manual/en/language.variables.variable.php

[seany123]

that threw up the same error as before.. hmm

[seany123]

can i use that...

SHOW TABLES LIKE "_episodes"

 

with this

 

 

           $tables = mysql_list_tables("series"); 

           while (list($table) = mysql_fetch_row($tables)) { 
              echo "$table <br />"; 
           } 

 

[PFMaBiSmAd]

mysql_list_tables -

This function is deprecated. It is preferable to use mysql_query() to issue an sql SHOW TABLES [FROM db_name] [LIKE 'pattern'] statement instead.

 

 

[seany123]

i have gone a different way...

 

$showtablequery = "
SHOW TABLES
FROM
`series`
LIKE
'%_episodes%'
";

$showtablequery_result	= mysql_query($showtablequery);
while($showtablerow = mysql_fetch_array($showtablequery_result))
{
echo $showtablerow[0]."<br />";
}

 

which echo's all the correct tables, but anyone know how i can get them in here...

 

$tableList = array('table1_episodes', 'table2_episodes', 'table3_episodes', 'table4_episodes', 'table5_episodes');

 

[xyph]

It's not fun to help someone that doesn't want to be helped.

 

You're going to have a query in a loop, which is terrible. You're performing 5+ queries to get data that should be available in one.

 

I avoid saying this as context is important, but you're doing it wrong!

 

It's really easy to store your table results in an array.

 

$tables = array();
while($showtablerow = mysql_fetch_array($showtablequery_result))
{
$tables[] = $showtablerow[0];
}

 

If you want to avoid using a query in a loop, look up using SELECT...UNION in MySQL and possibly implode() in PHP

[seany123]

well i am trying to help myself, in the end i did write the code to collect all the tables from the database by myself with just a little help with the LIKE.. so i dont know how you can say i dont want to be helped???

 

anyway, what you posted doesnt seem to do the trick. maybe is there is a way to do something like this...

 

$tableList = array($showtablerow[0].", ");

 

problem with that is that it looks for tables with a , on the end, instead of seperating the results with a ,

 

 

any ideas??

[seany123]

what is it called what im trying to do?

 

multidimensional array?

 

ive been trying to google to find something similar without much success.

[EdwinPaul]

Is this what you mean? It's only slightly different from what  is previously suggested:

 

<?php
$result = mysql_query("show tables like '%_episodes%' from series");
while(list($table) = mysql_fetch_array($result))
{
echo $table;
}
?>

[seany123]

Is this what you mean? It's only slightly different from what  is previously suggested:

 

<?php
$result = mysql_query("show tables from ".$dbname);
while(list($table) = mysql_fetch_array($result))
{
echo $table;
}
?>

 

thankyou for your reply, i was beginning to give up hope...

 

 

its almost except it was hoping to get a , in between each result.

 

so i can add it in like this

$tableList = array($table);

 

so it basically imitates something like this...

        $tableList = array('table_episodes', 'table2_episodes', 'table3_episodes', 'table4_episodes', 'table5_episodes');

[EdwinPaul]

Read http://php.net/manual/en/function.implode.php or http://php.net/manual/en/function.explode.php

[PFMaBiSmAd]

In Reply #14 in this thread, xyph showed you how to make an array with all the values.

[seany123]

i was unable to get that to work... i think i shall just give up on this and put the tables in manually.

[seany123]

i am now able to get them echo'd with a comma like so

 

$showtablequery = "
SHOW TABLES
FROM
`series`
LIKE
'%_episodes%'
";

$showtablequery_result	= mysql_query($showtablequery);
while($showtablerow = mysql_fetch_array($showtablequery_result))
{
    $showtablerow[0];
    $comma_separated = implode(", ", $showtablerow);

    echo $comma_separated; // lastname,email,phone        
}

 

however the result lists through all the tables then at the end says

' doesn't exist

 

also im still struggling on how i can implement it with this...

 

$tableList = array('table1, table2, table3');

 

i have tried

 

$tableList = array($comma_separated);
$tableList = $comma_separated;

 

but that doesnt work

[ebmigue]

Are the *_episodes table exactly the same? Do they have the same attributes?

 

If so, I suggest you start migrating them now into one table, as the other posters suggested.

 

If you need help doing that, I'm sure many would lend a hand here, as  that would be relatively trivial.

[seany123]

Are the *_episodes table exactly the same? Do they have the same attributes?

 

If so, I suggest you start migrating them now into one table, as the other posters suggested.

 

If you need help doing that, I'm sure many would lend a hand here, as  that would be relatively trivial.

 

yes they all have the same structure but contain different data unique to each table...

 

the problem i have with merging them together is that i have web scrapers which work @ inserting them into seperate tables...

 

unless there was a way i could make a php script which inserts all the data from all the tables into 1 table.

 

 

the website and tables do exactly what they should, this script im trying to make is just needed to simply verify all the URLs which will be in each table... instead of doing them 1 table at a time i though it would be easier all together with 1 script..

 

again i could just add them manually to the array i have, but that means i have to re update the script when new tables are created.