...

View Full Version : Not sure how to deal with this query



jedimastermopar
11-27-2006, 05:45 PM
Ok, I am up for suggestions on how to deal with this problem.
I have a master databas eserver housing lots of stuff. and a lamp intranet server. I want people to be able to be able to search and sort the results of the below query.

I can easily just display the query as a raw table but its result is about 8000 rows and growing daily so its a bit bulky for a single page. I was then toying with the idea of converting the table result to a pdf but that is not working out so well.

So how would you go about feeding this data to a user?

I was also considering just writing a search page to search the database but performance is a concern on the main db server.


SELECT company_0.CompanyID, company_0.CompanyName, branch_0.Address, branch_0.City, branch_0.PostalCode, branch_0.Phone, branch_0.Fax, branch_0.BranchID, contact_0.ContactID, contact_0.Name
FROM soileng.branch branch_0, soileng.company company_0, soileng.contact contact_0
WHERE company_0.CompanyID = branch_0.ParentCompanyID AND branch_0.BranchID = company_0.MainBranchID AND contact_0.ContactID = branch_0.MainContactID AND branch_0.BranchID = contact_0.ParentBranchID

vinyl-junkie
11-27-2006, 08:13 PM
Use pagination (http://www.tutorialized.com/tutorial/Pagination-with-PHP/6925) to split the results into smaller pages.

jedimastermopar
11-27-2006, 08:43 PM
Ok I broke the full result into pages, but the navigation text seems to be stuck beside the table? how do I get it below the table. My html skills suck. I am working on modifying the select statement to include a search result, Its a little bit quirky, since it is joining info from multiple tables to a single result set



<?php
@mysql_connect("xxxx.xxxx.xxx.xxx","user","userspass");
@mysql_select_db("mytable") or die("Can not find database");

$rowsPerPage = 10;
$pageNum = 1;

if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;

$query = "SELECT company_0.CompanyID, company_0.CompanyName, branch_0.Address, branch_0.City, branch_0.PostalCode, branch_0.Phone, branch_0.Fax, branch_0.BranchID, contact_0.ContactID, contact_0.Name
FROM soileng.branch branch_0, soileng.company company_0, soileng.contact contact_0
WHERE company_0.CompanyID = branch_0.ParentCompanyID AND branch_0.BranchID = company_0.MainBranchID AND contact_0.ContactID = branch_0.MainContactID AND branch_0.BranchID = contact_0.ParentBranchID ";

$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error, query failed');

echo '<table align="left" cellspacing="0" cellpadding="5">
<tr><td align="left"><b>CompanyID</b></td>
<td align="left"><b>CompanyName</b></td>
<td align="left"><b>Address</b></td>
<td align="left"><b>City</b></td>
<td align="left"><b>PostalCode</b></td>
<td align="left"><b>Phone</b></td>
<td align="left"><b>Fax</b></td>
<td align="left"><b>BranchID</b></td>
<td align="left"><b>ContactID</b></td>
<td align="left"><b>Name</b></td>
</tr>';

while(list($CompanyID, $CompanyName,$Address,$City, $PostalCode,$Phone, $Fax, $BranchID,$ContactID, $Name) = mysql_fetch_array($result))
{
echo"<tr><td>$CompanyID</td>
<td>$CompanyName</td>
<td>$Address</td>
<td>$City</td>
<td>$PostalCode</td>
<td>$Phone</td>
<td>$Fax</td>
<td>$BranchID</td>
<td>$ContactID</td>
<td>$NamE</td></tr>";
}
echo '</table>';
echo '<br>';

$result = mysql_query($query) or die('Error, query failed you sucka');
$numrows = mysql_num_rows($result);

$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' [Prev] '; // we're on page one, don't enable 'previous' link
$first = ' [First Page] '; // nor 'first page' link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' [Next] ';
$last = ' [Last Page] ';
}

echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;

?>

guelphdad
11-27-2006, 09:03 PM
... t its result is about 8000 rows and growing daily ...

I was also considering just writing a search page to search the database but performance is a concern on the main db server.

your data is of an insignificant size to a db server. are your tables properly indexed? that is of much more importantce. dbs can handle millions of rows seemlessly.

jedimastermopar
11-27-2006, 09:45 PM
Ok starting to make a bit of headway, I am just having problems adding a search function to my original query.
The following just returns the error message



$var = $_POST['q'] ;
$search = $_POST['search'] ;
$trimmed = trim($var); //trim whitespace from the stored variable

@mysql_connect("192.168.0.215","root","chunky"); //(mysql server, username, password)

@mysql_select_db("soileng") or die("Can not find database"); //type here your database

// how many rows to show per page
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query = "SELECT company_0.CompanyID, company_0.CompanyName, branch_0.Address, branch_0.City, branch_0.PostalCode, branch_0.Phone, branch_0.Fax, branch_0.BranchID, contact_0.ContactID, contact_0.Name
FROM soileng.branch branch_0, soileng.company company_0, soileng.contact contact_0
WHERE company_0.CompanyID = branch_0.ParentCompanyID AND branch_0.BranchID = company_0.MainBranchID AND contact_0.ContactID = branch_0.MainContactID AND branch_0.BranchID = contact_0.ParentBranchID AND Company_ID LIKE '%$trimmed%' order by '%$search%'";

$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error, query failed, You silly Dude');



This is where I get the user input and call the php statement



<form name="form" action="displaycontacts.php" method="get">
Look for
<input type="text" name="q" />
under
<label>
<select name="column">
<option value="CompanyID">Company ID</option>
<option value="CompanyName" selected="selected">Company Name</option>
<option value="Address">Address</option>
<option value="City">City</option>
<option value="PostalCode">Postal Code</option>
<option value="Phone">Phone</option>
<option value="Fax">Fax</option>
<option value="BranchID">Branch ID</option>
<option value="ContactID">Contact ID</option>
<option value="Name">Contact Name</option>
</select>
</label>
<input type="submit" name="Submit" value="Search" />
</form>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum