Jump to content

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

djb2002

By djb2002
in PHP Coding Help

 Share

Recommended Posts

djb2002 Newbie

djb2002

Posted
Posted

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

<?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> </h3>
</div>
<div align="center">
	<h3><font face="Tahoma" size="3">Your search didn't match any records.</B></font></h3>

	<h3> </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{ 
} 
?>

Thanks in advance for any help,

Daniel
 

mac_gyver Prolific Member

mac_gyver

Posted
Posted

if the code is producing pagination links (you either left this part of the code out or they are in the header.php and/or footer.php code), then you need two SIMILAR queries, one to get the total number of matching rows and a second one to get the actual rows for any logical page.

 

the two queries need the same WHERE .... clause, so you should be building that part of the queries in a php variable. the first query should only select COUNT(*) on the table with the common where clause. you will need to change the logic that retrieves the total number of rows so that it fetches the COUNT(*) value from the result set rather than using the _num_rows() function. the second query would be the same as your current final query (you would use the common where clause you have built in a the php variable in it, so that you only have the where clause built in one place should you need to change it in any way.)

djb2002 Newbie

djb2002

Posted
  • Author
Posted

Thank you for your reply - It is appreciated.

 

Sorry, I believe some of the code I previously used was producing pagination links - However, my page does not use this and instead displays all results on a single page.

 

Is this the cause of the problem ?

 

Thanks

Daniel

mac_gyver Prolific Member

mac_gyver

Posted
Posted
my page does not use this and instead displays all results on a single page.

 

 

you would probably want to remove all the unneeded php logic and the LIMIT clause in the query, so that you are only forming, running, testing, and using the result from ONE query.

djb2002 Newbie

djb2002

Posted
  • Author
Posted

Thanks for your reply.  Unfortunately that is easier said that done (for me), as I only have pretty basic knowledge of PHP and MySQL / queries etc.

 

I'll see what I can work out.  If you have any pointers it would really be appreciated.

 

Thanks

Daniel

jcbones Newbie

jcbones

Posted
Posted

djb2002,

 

I tried to clean up your script as best as possible.  The reason I hate dreamweaver and other WYSIWYG editors is because they often add redundant data that is very confusing when trying to debug your scripts.  It is doubly difficult for someone else to do it, as they don't really know how it is suppose to be displayed.  This cleanup job removed as much redundant stuff as possible, fully commented the script, and hopefully it will display as you wish it to.  But, it may need some tweaks.

 

 

<?php 
require_once('Connections/databaseconnect.php'); //include file exit if not found.
require('functionformat.php'); //include file, exit if not found.
mysql_select_db($database_stemel, $stemel); //select database.
 
/*
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); //prepare string for database interaction.
 
$unconfirmed_header = 0; //un-used.
$confirmed_header = 0;//un-used.
$rows_started = 0;//control variable used on line 89
 
if (strlen($search_name)  > 2) { //if the search parameter is longer than 2 characters.
 
/*this block is un-needed UNLESS you want pagination.
 * ***
$maxRows_Recordset1 = 1250;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
$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());
 
if (isset($_GET['totalRows_Recordset1'])) {
 $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
 }
 
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
*/
 
//query string.
$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";
 
$Recordset1 = mysql_query($query_Recordset1); //run the query
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1); //create variable storing the number of rows of database data returned.
}
//starting output.
?>
 
<!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 there are database rows.
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) { //get them until there are no more.
if(++$rows_started == 1) { //using control to determine if this is the first loop of data.
//if this is first loop of data, build the table headers.
?> 
<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
if ($row_Recordset1['confirmed'] < 1) { //if this is the first loop of data, and the confirmed returns as 0 (less than 1) then all the data returned is unconfirmed, print out a table cell stating this.
?>
 <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>
<?php
} //end confirmed if
} // end first loop if
$class = ($row_Recordset1['confirmed'] == 1) ? 'windowbg2' : 'windowuv'; //if confirmed is equal to 1, set the class to windowbg2, else it is 0 so set it to windowuv.
if ((empty($last_confirmed) || $last_confirmed == 1) && $row_Recordset1['confirmed']  == 0) { //if $last_confirmed is empty(first loop) or is equal to 1 AND the current is equal to 0, then all remaining numbers are unconfirmed
//print a column cell stating all numbers below are unconfirmed.
?>
<tr>
<td class=windowbg2 colspan="6" align=center BGCOLOR=#FFFFCC><B>Unconfirmed Numbers</B></td>
 </tr>
<?php
} //end last confirmed if.
if (strlen($row_Recordset1['url'])  >  2) { //if the url is longer than 2 characters, print a link, otherwise print a string.
echo '<td class=' . $class . ' 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=' . $class . ' width=28% align=center BGCOLOR=#FFFFCC>'.$row_Recordset1['company_name'].'</a></td>'; 
}
//print out the rest of the row data.
 echo '<td class=' . $class . ' width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['phone_no']).'</td>';
echo '<td class=' . $class . ' width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mobile_no']).'</td>';
echo '<td class=' . $class . ' width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['home_no']).'</td>';
echo '<td class=' . $class . ' width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mob2_no']).'</td>';
echo '<td class=' . $class . ' width=24% align=center BGCOLOR=#FFFFCC>*'.$row_Recordset1['other_info'].'</td></tr>';
 
$last_confirmed = $row_Recordset1['confirmed'];  //set the last confirmd to the current confirmed, so that on the next row we can tell if we have run out of confirmed numbers.
 
} //end while
//nothing changed below this line:
 ?>
 
</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> </h3>
</div>
<div align="center">
<h3><font face="Tahoma" size="3">Your search didn't match any records.</B></font></h3>
 
<h3> </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{ 
} 
?>
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.