[SOLVED] What's the best way of combining three queries to the same database?

[jonnymorris]

First off, I'm totally new to PHP and MySQL, never having touched them before now.  I'm just editing someone else's code in an attempt to streamline things.  The original author is not at hand to help out, unfortunately.

 

At present we have three separate html pages that reference three separate php pages which send the queries off to a MySQL database (on Apache I believe, though I do not know any version numbers, I'm not even sure how I could find them out as I only have basic network access to the server), all on an individual basis.  I currently need to use these three different html pages (which contain html forms) to produce a daily report by copying the results into a single document, which is time consuming and tiresome; it would be very much quicker if I could have a single form with one submit button to fire off the three queries and give me the results all on one page, or better yet form it into an email and fire that off to my boss!  Though I really don't know if that's possible on a PC setup.  I'm not even sure what OS the Apache server is running on.  The input data is going to be the same, except that each query returns stats for one of the three sites the company runs, let's call them site1, site2, site3, and this is the only part that is different to the three php files (name of the site).

 

My question is really how can I best achieve this - can I run three different queries on the same database in one go, would I have to run them sequentially and then collate the data, or is there another way?  I understand it may require learning some PHP to introduce loops to run the queries and collect the data, I'll probably be asking for help on that in the PHP forum later!  All the MySQL code is already there in the php files, it's just reading data from the database, is there any way that I could harm the server or database if I mess this up?

 

Thanks for any advice

 

 

 

 

[fenway]

Too little info to answer your question.

[jonnymorris]

It's OK, I've since worked this out.

 

It's fairly complicated, but the nub of the thing is this set of loops.  $trimmedsite can be any one of three places, or 'all sites' which is denoted by %. 

displayresults() contains a variable-driven MySQL query with html table output (generated according to which site and other parameters are requested from an html form on a preceding html page).  The important variables to follow here are $table and $trimmedsite. 

The main else part is for displaying just a single site's data (it is only possible to select one individual site or all sites).  It may not be the best loop ever created but it works and is a vast improvement over the previous system.

displaysearch() just displays what the user selected as their search criteria as confirmation of what the results show.

 

Sorry I was a bit lazy with my variable naming!

 

if ($trimmedsite == "%")
{ 
displaysearch ($trimmednumber, $site, $var8, $trimmedday, $trimmedmonth, $trimmedyear, $month);

for ($i = 1; $i <= 3; $i++)
	{
	if ($i == 1) { $table = "swyxmalton"; $site = "Malton";
$trimmedname = $trimmednamemalton;
	}
	else if ($i == 2) { $table = "swyxseamer"; $site = "Seamer";
				$trimmedname = $trimmednameseamer;
				}
	else { $table = "swyxleeds"; $site = "Leeds";
		$trimmedname = $trimmednameleeds;
		}
	displayresults ($trimmedsite, $table, $site, $var, $trimmedday, $trimmedmonth, $trimmedyear, $trimmedname, $var8);
	}
}

else {
$table = $trimmedsite;

if ($table == "swyxmalton") { $trimmedname = $trimmednamemalton;	
				$site = "Malton"; }

else if ($table == "swyxseamer") { $trimmedname = $trimmednameseamer;
						$site = "Seamer"; }

else if ($table == "swyxleeds") { $trimmedname = $trimmednameleeds; 
					$site = "Leeds"; }

else { echo "<p>There was an error. Please click the Back button in your browser and try again.  
If the error persists please contact the systems administrator.</p>"; }

displaysearch ($trimmednumber, $site, $var8, $trimmedday, $trimmedmonth, $trimmedyear, $month);
displayresults ($trimmedsite, $table, $site, $var, $trimmedday, $trimmedmonth, $trimmedyear, $trimmedname, $var8);
}