Php and mysql problem

[Liamsorsby]

My problem is I have a database that contains several thousand customers I have the php query already working so that a customer enters in a postcode or area and the customer data that is similar comes up my problem is most of these aren't customers yet and I've defined these in the data base with a Colin called customer if they are then the field contains a y if not it contains a n. So what I need is a query with the database so that it searches for the customer post code or area like which has been entered but only to show the data of the customers that the field 'customer' contains a y any help?

[waynew]

If you have a column called Customer with either a N or a Y in it, then your query could look like:

 

$query = "SELECT * FROM MyTableName WHERE Customer = 'Y'";

 

If the query becomes slow, you should look into indexing the Customer column.

 

Or have I misunderstood your question? If so, please show us your table schema (structure).

[Liamsorsby]

I don't actually have access to the files at the moment but simply the database has company name address post code telephone number and the customer field I want the database to query the post code and county and then filter out that they are a customer then display the info

[waynew]

You'll need to post the query you currently have and the table structure before anyone can help you out properly.

[Liamsorsby]

This is the code for the php just a simple submit form on the other page

 

<?php

  // Get the search variable from URL
  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("","","",""); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("allheart") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select * from FUNERAL where POSTCODE OR COUNTY like \"%$trimmed%\"  
  order by POSTCODE"; // EDIT HERE and specify your table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// google
echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "Results";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["POSTCODE"];
  $name = $row["NAME"];
  $ADD1 = $row["ADD 1"];
  $ADD2 = $row["ADD 2"];
  $ADD3 = $row["ADD 3"];
  $TOWN = $row["TOWN"];
  $COUNTY = $row["COUNTY"];
  $COUNTRY = $row["COUNTRY"];
  $TEL = $row["TEL"];
  $CUSTOMER = $row["CUSTOMER"];



  echo "</br> $count.) $name , $ADD1 , $ADD2 , $TOWN , $COUNTY , $title ;  $COUNTRY" ;
  $count++ ;
  };


$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp ";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>

[waynew]

Try:

 

/ Build SQL Query 

$query = "select * from FUNERAL where POSTCODE OR COUNTY like \"%$trimmed%\" AND CUSTOMER = 'Y' 

  order by POSTCODE"; // EDIT HERE and specify your table and field names for the SQL query

[Liamsorsby]

Will try that it returns results but not sure if there correct or not shall look tomorrow thankyou

[DavidAM]

That's not valid SQL. It would be something like:

 

$query = "select * from FUNERAL 
where (POSTCODE like \"%$trimmed%\" 
   OR  COUNTY like \"%$trimmed%\" ) 
AND CUSTOMER = 'Y'

 

be sure to include the parenthesis around the two phrases of the OR

[Liamsorsby]

Ive put the parantheses around and copied but now it returns nothing not even an error message

[fenway]

Please echo the actual query.