www.webdeveloper.com
Results 1 to 3 of 3

Thread: PHP Code - Seems to run a MySQL query twice !!?? - Not sure why

  1. #1
    Join Date
    Jul 2013
    Posts
    2

    PHP Code - Seems to run a MySQL query twice !!?? - Not sure why

    Hi,

    I'm currently setting up some new code for a website I am working on - It is PHP based, and is accessing a MySQL database.

    I've been looking at the logs on the server, and it seems everytime someone does a search using the site, it actually runs the query twice - Once using the 'LIMIT' command, and once without. If this is the case, then it is surely wasting resources as it should only be running it once.

    Can anyone spot where this is going wrong, and offer any assistance and guidance ?

    Here is the code in question (I don't know if you need all the code):

    Code:
    <?php require_once('Connections/databaseconnect.php'); ?>
    <?php require('functionformat.php'); ?>
    <?php
    
    /*
    The following strips out the use of the % and _ wildcard characters, before running the query.
    */
    
    $pattern = array('/%/','/_/','/!/','/$/','/^/','/{/','/}/','/,/','/=/','/"/','/|/','/:/','/;/');
    $replace = array('','');
    $search_name = preg_replace($pattern, $replace, $_POST[search_name]);
    
    $search_name = mysql_real_escape_string($search_name);
    
    $unconfirmed_header = 0;
    $confirmed_header = 0;
    
    if (strlen($search_name) < "2") {
    
    }
    
    else {
    
    $maxRows_Recordset1 = 1250;
    $pageNum_Recordset1 = 0;
    
    if (isset($_GET['pageNum_Recordset1'])) {
      $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
    }
    $startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
    
    mysql_select_db($database_stemel, $stemel);
    $query_Recordset1 = "SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1 WHERE (confirmed = '0' OR confirmed = '1') AND (company_name LIKE '%$search_name%' OR other_info 
    
    LIKE '%$search_name%' OR url LIKE '%$search_name%') ORDER BY confirmed DESC, company_name ASC, company_number ASC";
    $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
    $Recordset1 = mysql_query($query_limit_Recordset1, $stemel) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    
    if (isset($_GET['totalRows_Recordset1'])) {
      $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
    } else {
      $all_Recordset1 = mysql_query($query_Recordset1); 
      $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
    }
    $totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
    
    }
    ?>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <META NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW">
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    
    <?php
    /* This section passes on received values as hidden fields after validating null entries. */
    include ('header.php');
    ?>
    
    <link rel="stylesheet" href="search.css" type="text/css" />
    
      <?php
    if (($totalRows_Recordset1) > 0) { 
    
    		if ($row_Recordset1['confirmed'] & 1) { 
    		
    		?>
    <table width="100%" align="center" border="0">
      <tr>
        <td><font size="2">
    
    <div class="seperator"></div>
    
    <div class="boardcontainer">
    <table cellpadding="4" cellspacing="1" border="0" width="100%">
    <tr><td colspan="6" class="catbg" height="18" >Main Database</td></tr>
    
    <tr>
    	<td class="windowbg" width="28%" align="center">Company Name</td>
    	<td class="windowbg" width="12%" align="center">Phone No</td>
    	<td class="windowbg" width="12%" align="center">Mobile No</td>
    	<td class="windowbg" width="12%" align="center">Home No</td>
    	<td class="windowbg" width="12%" align="center">Personal Mobile</td>
    	<td class="windowbg" width="24%" align="center">Other Info</td>
    </tr>
    
    <?php 
    $confirmed_header=1;
    } 
    
    /*
    Message that is displayed when no CONFIRMED numbers are returned from the database
    */
    
    				{				
    
    
    if ($confirmed_header < 1)
    {
    
    			?>	
    			<table width="100%" align="center" border="0">
    			<tr>
    		    <td><font size="2">
    
    			<div class="seperator"></div>
    
    			<div class="boardcontainer">
    			<table cellpadding="4" cellspacing="1" border="0" width="100%">
    			<tr><td colspan="6" class="catbg" height="18" >Main Database</td></tr>
    
    			<tr>
    				<td class="windowbg" width="28%" align="center">Company Name</td>
    				<td class="windowbg" width="12%" align="center">Phone No</td>
    				<td class="windowbg" width="12%" align="center">Mobile No</td>
    				<td class="windowbg" width="12%" align="center">Home No</td>
    				<td class="windowbg" width="12%" align="center">Personal Mobile</td>
    				<td class="windowbg" width="24%" align="center">Other Info</td>
    			</tr>
    
    
      <tr>
        <td class=windowbg2 colspan="6" align=center BGCOLOR=#FFFFCC><B>No confirmed numbers have been found that match the criteria you entered.  One or more have been found in the unconfirmed list, and are listed below.</B></td>
      </tr>
    
    
    </table>
    </div>
    				<?php
    				}{}
    				} ?>
      <?php do { ?>
      <tr>
    <?php
         if ($row_Recordset1['confirmed'] & 1) { 
    
    				if (strlen($row_Recordset1['url']) > "2") {
    						 	 echo '<td class=windowbg2 width=28% align=center BGCOLOR=#FFFFCC><a href=http://www.mywebsite.com/external.php?site='.$row_Recordset1['url'].' target="_blank">'.$row_Recordset1
    
    ['company_name'].'</a></td>';   } else {
    		 
    		   echo '<td class=windowbg2 width=28% align=center BGCOLOR=#FFFFCC>'.$row_Recordset1['company_name'].'</a></td>';		 
    		 }
          echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['phone_no']).'</td>';
         echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mobile_no']).'</td>';
         echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['home_no']).'</td>';
         echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mob2_no']).'</td>';
         echo '<td class=windowbg2 width=24% align=center BGCOLOR=#FFFFCC>*'.$row_Recordset1['other_info'].'</td></tr>';
            } else { 
    	if ($unconfirmed_header < 1) {
    ?>
    
    </tr>
    </table>
    </div><br />	
    
    <table width="100%" align="center" border="0">
      <tr><td><font size="2">
    <div class="seperator"></div>
    
    <div class="boardcontainer">
    <table cellpadding="4" cellspacing="1" border="0" width="100%">
    
    <tr><td colspan="6" class="catbg" height="18" >Unconfirmed Numbers</td></tr>
    
    <tr>
    	<td class="windowbg" width="28%" align="center">Company Name</td>
    	<td class="windowbg" width="12%" align="center">Phone No</td>
    	<td class="windowbg" width="12%" align="center">Mobile No</td>
    	<td class="windowbg" width="12%" align="center">Home No</td>
    	<td class="windowbg" width="12%" align="center">Personal Mobile</td>
    	<td class="windowbg" width="24%" align="center">Other Info</td>
    </tr>
    
    <?php
    	$unconfirmed_header = 1;
    				}
    
    				if (strlen($row_Recordset1['url']) > "2") {
    						 	 echo '<td class=windowuv width=28% align=center BGCOLOR=#CCFFFF> '.$row_Recordset1['company_name'].'</td>';   } else {
    		 
    		   echo '<td class=windowuv width=28% align=center BGCOLOR=#CCFFFF>'.$row_Recordset1['company_name'].'</a></td>';
    		 		 
    		 }
    
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['phone_no']).'</td>';
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['mobile_no']).'</td>';
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['home_no']).'</td>';
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['mob2_no']).'</td>';
         echo '<td class=windowuv width=24% align=center BGCOLOR=#CCFFFF>*'.$row_Recordset1['other_info'].'</td></tr>';
            } 
    ?>
    
      <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
    
    </tr>
    </table>
    </div><br />
    
    <div class="seperator">
    <table cellpadding="4" cellspacing="1" border="0" width="100%">
    <tr>
    	<td class="titlebg" align="center" colspan="2">
    		Info Centre
    	</td>
    </tr>
    
    	<td class="windowbg2">
    		<div style="float: left; width: 59%; text-align: left;">
    		<span class="small">Please update us with any corrections as soon as possible.</span><br />
    		</div>
    		<div style="float: left; width: 40%; text-align: left;">
    		<div class="small" style="float: left; width: 49%;"><span style="color: red;"><b>lllll</b></span></div>
    		</div>
    	</td>
    </tr>
    </table>
    </div>
        </font></td>
      </tr>
    </table>
    
    <?php } else { ?>
    
    <body bgcolor="#FFFFCC">
    	<tr><td colspan=10>
    	 <center><table border=1 bordercolor=navy cellpadding=0 cellspacing=0><tr><td bgcolor="#CCCCFF"><div class=TableTitle>
    		<p align="center"><b><font face="Tahoma">NO RESULTS FOUND</font></b></div></td></tr><tr><td BGCOLOR=#FFFFCC>
    <div align="center">
    	<h3>&nbsp;</h3>
    </div>
    <div align="center">
    	<h3><font face="Tahoma" size="3">Your search didn't match any records.</B></font></h3>
    
    	<h3>&nbsp;</h3>
    </div>
    </td></tr></table></center>
    </td></tr></table><br>
    
    <?php
    }
    ?>
    <br>
    <?php
    require ('footer.php');
    
    if (($totalRows_Recordset1) > 0) { 
    mysql_free_result($Recordset1);
    mysql_close();
    }else{ 
    } 
    ?>

  2. #2
    Join Date
    Feb 2012
    Location
    Tallahassee, FL
    Posts
    280
    Looks like you are telling it to run twice.

    First Time:
    PHP Code:
    $Recordset1 mysql_query($query_limit_Recordset1$stemel) or die(mysql_error()); 
    Then a couple lines down:
    PHP Code:
    if (isset($_GET['totalRows_Recordset1'])) {
      
    $totalRows_Recordset1 $_GET['totalRows_Recordset1'];
    } else {
      
    $all_Recordset1 mysql_query($query_Recordset1); 
      
    $totalRows_Recordset1 mysql_num_rows($all_Recordset1);


  3. #3
    Join Date
    Jul 2013
    Posts
    2
    Thank you.

    I presume I could therefore just remove the line from the second piece of code you quoted that says:

    Code:
     $all_Recordset1 = mysql_query($query_Recordset1);
    I hadn't spotted that, or realised that is what it was actually doing.

    I think a lot of my code is redundant, but without knowing enough about PHP, it is hard to see it.

    Thanks again,
    Daniel

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles