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
array vs db table (speed/efficiency)
Moderator: General Moderators
Re: array vs db table (speed/efficiency)
It really depends on what you're trying to do...Do you know which tables? Are all the queries the same?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
The easiest way is to run a benchmark test.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: array vs db table (speed/efficiency)
You're going to be doing multiple queries while inside a loop? Why are you going to do that?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
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)
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
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);
}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
Is there no way that you can join the tables in the SQL rather than running multiple queries?
ie:
ie:
Code: Select all
SELECT field1, field2, ...
FROM table1, table2, ...
WHERE
-- join syntax
...
...