[SOLVED] simple problem -- help needed

[email protected] · May 24, 2007

Hi

i need help with the following the script. Its driving me crazy. when i try to search for rep_name together with rep2 and search for rep_name and grade i get the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/vhost/farmiloesinjamaica.co.uk/html/final/process_advance_search_admin.php on line 50

Could not run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and rep2 LIKE '%neil%' and full_name LIKE 'Andrew Dawson' Group by DATE_OF_ENTRY' at line 1

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/vhost/farmiloesinjamaica.co.uk/html/final/process_advance_search_admin.php on line 69

when i try to search by grade the search page return blank

i am really really confused. can someone point me into the right direction as to where i am going wrong.

please refer to the 2 scripts below

many thanks

<?php
require 'conn.php';
require 'page_header.php';
$end=time();
include "start_timer.php";
include "end_timer.php";
?>
<html> 
<head>
  <title>Searching Rep DB</title> 
</head> 
<body bgcolor=#ffffff>
  <a href="logout.php">Log out</a><br><br>
  <FORM><INPUT TYPE="button" VALUE="Back" onClick="history.go(-1);return true;"> </FORM>
  <h1 align=center>Searching Reports</h1> 
   <b>Flease fill in 1 or any combination to refine search.</b>
   <table border="0" width=100%>
     <form method="get" action="process_advance_search_admin.php">
      <tr>
       <td colspan=2>search by Account No:<input type=text name='account_no' size=10 maxlength=255></td>
     </tr>
      <tr>
       <td colspan=2>search by Customer Name:<input type=text name='customer_name' size=30 maxlength=255></td>
     </tr>
      <tr>
       <td colspan=2>search by Date:<input type=text name='date' size=30 maxlength=255></td>
     </tr>
      <tr>
       <td colspan=2>search by Rep2:<input type=text name='rep2' size=30 maxlength=255></td>
     </tr>
      <tr>
       <td colspan=2>search by Grade:
        <select name="grade">
         <option value="" selected>Please choose </option>
         <option value="A">A</option>
         <option value="B">B</option>
         <option value="C">C</option>
         <option value="D">D</option>
         <option value="N">N</option>
     </select>
</td>
     </tr>
      <tr>
       <td colspan=2>search by Rep:
        <select name="repname">
         <option value="" selected>Please select Name </option>
         <!-- option value="Alan Fishwick">Alan Fishwick </option -->
         <option value="Andrew Dawson">Andrew Dawson </option>
         <option value="Dominic Condon">Dominic Condon </option>
         <option value="James Hynds">James Hynds </option>
         <option value="Mark Flowers">Mark Flowers </option>
         <option value="Peter Coatham">Peter Coatham </option>
         <option value="Ray Deal">Ray Deal </option>
         <option value="Telephone Sales">Telephone Sales </option>
         <option value="Tim Farmiloe">Tim Farmiloe </option></td>
      </select>
     </tr>
      <tr>
       <td><input type=submit name='all' value='Search All Reports'></td>
     </tr>
    </table>
   </form>

<a href="backup_reports1.xls" target="_blank">Archive</a> (05/06-08/06)
  </html>

<?php
require 'conn.php';
require 'page_header.php';
$end=time();
include "start_timer.php";
include "end_timer.php";

echo '<a href="logout.php">Log out</a><br><br>';

if (($_GET['account_no']!="") or ($_GET['customer_name']!="") or ($_GET['date']!="") or ($_GET['repname']!="") or ($_GET['GRADE']!="") or ($_GET['rep2']!="")) 
{
		$sql = "select distinct repactivity.*, customer_table.CUSTOMER_NAME, customer_table.GRADE, customer_table.address3, DATE_FORMAT(DATE_OF_ENTRY, '%d-%m-%Y %r') AS DATE_OF_ENTRY, concat(outstanding, ' ', outstanding2, ' ') AS follow_up from repactivity, customer_table where customer_table.ACCOUNT_NO=repactivity.account_no AND";
	if ($_GET['account_no']!="")
	{
		$sql = $sql." repactivity.account_no LIKE '".$_GET['account_no']."%'"; 
	}
	if ($_GET['customer_name']!="")
	{
		if ($_GET['account_no']!="")
			$sql = $sql." and";
		$sql = $sql." CUSTOMER_NAME LIKE '".$_GET['customer_name']."%'"; 
	}
	if ($_GET['date']!="")
	{
		if (($_GET['account_no']!="") or ($_GET['customer_name']!=""))
			$sql = $sql." and";
		$sql = $sql." DATE_OF_APPROACH LIKE '%".$_GET['date']."%'"; 
	}
	if ($_GET['grade']!="")
	{
		if ($_GET['repname']!="")
			$sql = $sql." and";
		$sql = $sql." GRADE LIKE '".$_GET['grade']."%'"; 
	}
	if ($_GET['rep2']!="")
	{
		if (($_GET['account_no']!="") or ($_GET['customer_name']!="") or ($_GET['date']!="") or ($_GET['repname']!=""))
			$sql = $sql." and";
		$sql = $sql." rep2 LIKE '%".$_GET['rep2']."%'"; 
	}
	if ($_GET['repname']!="")
	{
		if (($_GET['account_no']!="") or ($_GET['customer_name']!="") or ($_GET['date']!="") or ($_GET['rep2']!=""))
			$sql = $sql." and";
		$sql = $sql." full_name LIKE '".$_GET['repname']."' Group by DATE_OF_ENTRY DESC"; 
	}


	$result = mysql_query($sql);
$number=mysql_num_rows($result);
if (!$result) 
{
	echo 'Could not run query: ' . mysql_error();
}


echo "<table border=1 bordercolor='tan' class='rep' width ='150%' " .
     	"cellspacing='5'"."cellpadding='5'>\n";
echo "<tr border=1><th class='searchHeader'><caption> <b>Search Results</b><br>Total Reports = $number</caption> </th></tr>\n";
echo "<tr><td><b>Account No:</b></td> <td><b>Customer Name:</b></td> <td><b>Grade:</b></td> <td><b>Town:</b></td> <td><b>Date of Approach:</b></td> <td width='50%'><b>Report:</b></td> <td width='50%'><b>Outstanding:</b></td> <td><b>Type of Approach:</b></td> <td width='14%'><b>Rep Name: </b></td> <td width='14%'><b>Rep2: </b></td> <td><b>Date of entry: </b></td> <td><b>Updated Status:</b></td><td><b>No of follow-ups:</b></td> <td><b>View/add Follow-Up</b></td></tr>";

if ($result and !mysql_num_rows ($result)) 
{
echo "<tr class='row1'><td>No results found that match the ";

   } 
   else 
   {
while($row = mysql_fetch_array($result, MYSQL_BOTH)) 
{
echo "<tr><!-- td>".$row['activity_id']."</td --> 
      <td>".$row['account_no']."</td>
      <td>".$row['CUSTOMER_NAME']."</td>
      <td>".$row['GRADE']."</td>
      <td>".$row['address3']."</td>
      <td>".$row['DATE_OF_APPROACH']."</td>
      <td>".$row['report']."</td>
      <td>".$row['follow_up']."</td>
      <td>".$row['type_of_approach']."</td>
              <td>".$row['full_name']."</td>
      <td>".$row['rep2']."</td>
      <td>".$row['DATE_OF_ENTRY']."</td>
      <td>".$row['status']."</td>
      <td>".$row['reply']."</td>
      <td bgcolor='#FFFFFF'><a href='view_topic_report.php?activity_id=".$row['activity_id']."'>view here</a><BR></td></tr>";
  //    include ('view_topic_report.php?activity_id=$activity_id'); echo "</td>";
}	
}
}

?>

MadTechie · May 24, 2007

Your logic with the AND's doesn't make sense.. thats the problems

try something like

$sql="SELECT blar, blar, blar, blar ";
$sql2 = "";
if( X!= "")
{
if($sql2 != "")
{
$sql2 .= " AND ";
}
$sql2 .= " X=Y";
}
//..
$sql = $sql." WHERE ".$sql2;

[email protected] · May 24, 2007

thanks for the help i have managed to solve some of the problem but i am still having trouble when searching for repname and rep2 but it works fine when searching on its own or with customer_name or account_no. just to get it clear i want to know why there was a problem with 'ands' when the other search criteria works fine.

i am the same error as above

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/vhost/farmiloesinjamaica.co.uk/html/final/process_advance_search_admin.php on line 50

Could not run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and rep2 LIKE 'neil%' full_name LIKE 'Andrew Dawson' Group by DATE_OF_ENTRY DESC' at line 1

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/vhost/farmiloesinjamaica.co.uk/html/final/process_advance_search_admin.php on line 68

MadTechie · May 24, 2007

Read my last post its the same problem!

Sign In

[SOLVED] simple problem -- help needed

Recommended Posts

[email protected]

Link to comment

Share on other sites

MadTechie

Link to comment

Share on other sites

[email protected]

Link to comment

Share on other sites

MadTechie

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information