Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not sure how to deal with this query

    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.
    Code:
     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

  • #2
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,085
    Thanks
    2
    Thanked 23 Times in 23 Posts
    Use pagination to split the results into smaller pages.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #3
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Code:
    <?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;
    
    ?>

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by jedimastermopar View Post
    ... 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.

  • #5
    Regular Coder
    Join Date
    Nov 2006
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    Code:
    $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

    Code:
    <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>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •