Jump to content

[SOLVED] simple problem -- help needed

miz_luvly@hotmail.com
 Share

Recommended Posts

miz_luvly@hotmail.com Member

miz_luvly@hotmail.com

Posted
Posted

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>";
}	
}
}

?>

Link to comment
Share on other sites
MadTechie Regular Member

MadTechie

Posted
Posted

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;

Link to comment
Share on other sites
miz_luvly@hotmail.com Member

miz_luvly@hotmail.com

Posted
  • Author
Posted

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

Link to comment
Share on other sites
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.