array vs db table (speed/efficiency)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tbbd
Forum Newbie
Posts: 20
Joined: Sat Apr 22, 2006 2:54 pm

array vs db table (speed/efficiency)

Post by tbbd »

I am going to query several db tables, but I was wandering which is the best way to get the tables names that I will be querying.
(1)Put them in an array inside a file, $tables = array("a", "b", "c"); then do my foreach loop and query table_a, table_b, table_c (of course using the array variables names)
(2)Make a db table with the tables names of a,b,c, then query that table and do my a,b,c tables queries while using a while to fetch_array from the tables listings
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Re: array vs db table (speed/efficiency)

Post by hawleyjr »

tbbd wrote:I am going to query several db tables, but I was wandering which is the best way to get the tables names that I will be querying.
(1)Put them in an array inside a file, $tables = array("a", "b", "c"); then do my foreach loop and query table_a, table_b, table_c (of course using the array variables names)
(2)Make a db table with the tables names of a,b,c, then query that table and do my a,b,c tables queries while using a while to fetch_array from the tables listings
It really depends on what you're trying to do...Do you know which tables? Are all the queries the same?

The easiest way is to run a benchmark test.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: array vs db table (speed/efficiency)

Post by RobertGonzalez »

tbbd wrote:I am going to query several db tables, but I was wandering which is the best way to get the tables names that I will be querying.
(1)Put them in an array inside a file, $tables = array("a", "b", "c"); then do my foreach loop and query table_a, table_b, table_c (of course using the array variables names)
(2)Make a db table with the tables names of a,b,c, then query that table and do my a,b,c tables queries while using a while to fetch_array from the tables listings
You're going to be doing multiple queries while inside a loop? Why are you going to do that?
tbbd
Forum Newbie
Posts: 20
Joined: Sat Apr 22, 2006 2:54 pm

Post by tbbd »

ok, there will be a list of db tables (table_a, table_b, table_c), each table will have diffeerent fields, but they will all have an id and name

I have it setup like this (simple example of what I'm doing)

Code: Select all

//this actually does more then just list name, it makes each row a form entry also
function listnames($table)
{
echo "Table: ".$table;
$result = mysql_query("select * from table_$table ORDER BY name");
while ( $row = db_fetch_array($result);
echo "<br>".$row["name"] ."<br><br>" .$row["text"];
}


//this is where I was wandering if I should use an array or pull a db listing
$tables = array("a", "b", "c");

foreach ( $tables as $table)
{
listnames($table);
}
I don't know anything about doing benchmark test, so not sure what you mean by that (I mean I Know what one is, but don't know what software you use to do it, or how you actuall yod one), I run wxp, apache, php4, mysql5



Now I did this several different ways, and yes it does not work well if I put the query code in the foreach loop for some reason..if I put it in a function (like it is there) and then call it, the speed is incredibly faster....right now there is 15 tables and each table only has between 25-60 rows and it processes in like a second.... however when the function wasn't called, it would actually do all the queries before echoing anything (I think), because it take a while to process then all of a sudden my screen would be filled up with the echoes, but with the function call, it appears to echo the query as it does it, then move to the next table and echo it there.... so all that is working ok, I was jhust wandering which would be faster to get that tables array from?

And of course if there is a better way to do this, I am open for suiggestions
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Is there no way that you can join the tables in the SQL rather than running multiple queries?

ie:

Code: Select all

SELECT field1, field2, ... 
FROM table1, table2, ...
WHERE 
 -- join syntax
...
...
Post Reply