Is there a better way to do this?

[cassidy]

Sorry in advance for the complex question. i've tried to simplify it as much as possible to get to the root of my question.
I'm having trouble with the length of time it takes the below script to get data from a mysql database. I know there's probably a simpler way to query the database to speed things up but i can't figure it out. basically i'm querying 3 tables and printing out a page with multiple rows of info. the link to the page that the visitor clicks on has a variable attached to it using the GET method like this:

www.mywebpage.com/test.php?projectID=12345

Then the php queries the database like this:

<?
//[....make database connection....]

//get first chunk of info based on the variable in the weblink
$result = mysql_query("SELECT table1.viewerID, table2.email, max(table1.timeout), count(table1.viewerID)
FROM table1, table2 WHERE projectID=$projectID");

//this WHILE loop will print out multiple rows of info based on the above query
//and also the query inside the FOREACH loop below
while ($row = mysql_fetch_array($result))
{
$id = $row[0];
$email = $row[1];
$datetime = $row[2];
$visits = $row[3];

print "$datetime, $email, $visits, ";

//the "$events" variable below is a dynamic array that is carried over from a previous session page.
//I have written it out here for the purpose of clarity.
$events=array("event1", "event2", "event3");

foreach ($events as $value)
{
    $result2 = mysql_query("SELECT columnA FROM table3 WHERE columnB=$value
                            and columnC=$id");
    $rowB = mysql_fetch_array($result2);

    print "$rowB[0], ";
}

print"<br>";
}

//[...close database connection...]
?>

The end result is a page with multiple rows like this:

Jan 10 , [e-mail], 3, 27, 12, 57
Jan 08 , [e-mail], 55, 42, 7, 101

It works, but once i start getting into higher numbers of rows (above a few hundred) it starts taking a VERY long time to return data. It takes over 2 minutes for 1000 rows with 10 columns each. My guess is that the FOREACH loop inside the WHILE loop is not the best way to go about this but it's the only way i could think of to accomplish what i needed since the quantity of data in the "$events" array is always changing. It could be 3 events or 10 or whatever. I have similar queries that don't have a FOREACH loop inside the WHILE loop, and they seem to retrieve similar amounts of data much faster. Is there a better way to formulate these queries to get faster results? Thanks, Dave

[jweissig]

hi,

can you please explain when the foreach loop is doing? If i know what you have this table for then i might be able to help you.

Can you please paste your current database design and a small example of what each field does and what data you would like to extract.

I am sure there is a very simple way to solve this problem.

• Justin

[ultraslacker]

The first query introduces a cross join which is resource intensive - you are returning every possible combination!

Iterative queries and cross joins can be avoided through proper sql and database design.

[cassidy]

Basically, this script gets data from a database that shows viewers actions on a media file they looked at, and prints the data into a table for viewing. One script works with multiple projects by using the $projectID variable to define what data to get. The first query is simple because it's always the same from project to project; date, email address, and number of views. The second part of the script is where i run into trouble because the data is always changing. The number of columns retrieved from the database changes from project to project so it's very dynamic in nature. The FOREACH loop steps through an array that defines the specific elements of each project (and is retrieved from a previous query on a previous page using sessions) for the purpose of adding the appropriate info onto each row that starts with the afformentioned layout, so the end result looks something like this:

date email address number of views button1 button2
12/21 [e-mail] 25 43 21

(that may not print out with the correct alignment but you get the idea)

This is a very simplified version of the DB but it's something like this:
Table1 columns= viewerID timein timeout
Table2 columns= viewerID email projectID
Table3 columns= projectID number eventname

Here's a modified version of the script to correspond to the above info:

<?
//[....make database connection....]

$result = mysql_query("SELECT table1.viewerID, table2.email, max(table1.timeout), count(table1.viewerID) FROM table1, table2 WHERE table1.projectID=$projectID AND table1.viewerID=table2.viewerID");

while ($row = mysql_fetch_array($result))
{
$id = $row[0];
$email = $row[1];
$datetime = $row[2];
$visits = $row[3];

print "$datetime, $email, $visits, ";

//the "$events" variable below is a dynamic array that is carried over from a previous session page.
//I have written it out here for the purpose of clarity.
$events=array("event1", "event2", "event3");

foreach ($events as $value)
{
    $result2 = mysql_query("SELECT number FROM table3 WHERE eventname=$value
                            and projectID=$id");
    $rowB = mysql_fetch_array($result2);

    print "$rowB[0], ";
}

print"<br>";
}

//[...close database connection...]
?>

I hope this helps clarify things. once again, the script works. it's just that it really slows down when the number of rows start to creep up into the thousands. Thanks for your help, Dave

[cassidy]

ultraslacker,

the first query in my first post was incorrect upon second look. i needed to define the WHERE part of the query better. in trying to simplify this for posting, i made an error. sorry about that. please take a look at my second post and see if what you are saying is still true. thanks for the help. Dave

[joeball]

for starters,

instead of assigning arrays to the queried rows.. IE:

while ($row = mysql_fetch_array($result))
{
$id = $row[0];
$email = $row[1];
$datetime = $row[2];
$visits = $row[3];

print "$datetime, $email, $visits, ";

try this instead:

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print ("$row[column1], $row[column2], $row[visits]");
}

i don't know your column names...

hint: exec your query using the
mysql tool if u have shell access and see how long it takes,
this will tell ya if Mysql is the problem or your PHP code...

nate,

[ultraslacker]

SELECT table1.viewerID, table2.email, max(table1.timeout), count(table1.viewerID)
FROM table1, table2 WHERE projectID=$projectID

SELECT number FROM table3 WHERE eventname=$value
and projectID=$id

$projectid == $id, so why not just incorporate an additional join (OUTER or INNER) in the first query? Add a loop to build the WHERE clause for 'eventname = $value'.