[RESOLVED] Searching multiple tables from url values posted using $_GET

[cainy1982]

Hi there,

Im trying to create a script that will allow you to search a database using various search terms (Ward, Party, Name, Ward Party, Ward Name, Ward Party Name, Party Name).

I was unsure how to run the query, do I need to create a seperate mysql query for each of the search variations.

I have posted what I have done so far below, and I was hoping for soem advice before I plod on with coding it up.

<?php // This is the hone page for the site.

// set the title for the html header
$page_title = 'Results';

// require the config file
require_once ('../includes/config.inc.php');

// include the header filde
// add header file once created


// require the database settings file using information from config.inc.php
require_once (MYSQL);

	// get the variables posted from the form in the url and clean before using it in a query
	$ward = mysqli_real_escape_string ($dbc, trim($_GET['ward']));
	$party = mysqli_real_escape_string ($dbc, trim($_GET['party']));
	$name = mysqli_real_escape_string ($dbc, trim($_GET['name']));

	// trim any whitespace from the free text field
	$name_trimmed = trim($name); 

	// check for emply field to select the correct query

	// This performs a seach if a user only searches by ward
	if ($trimmed == "" && $party == '*'){

	//  query tables using the cleaned values posted from the form - ward
			$q="SELECT name.cllr_id, name.first_name, name.surname, name.address_id, name.phone, name.email, name.party_id, name.ward_id, name.image FROM name WHERE (name.ward_id = $ward)";

			// For debugging return any errors or run the query 
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

// get the data ready to output into a table and query aginst the address, party and ward tables.
$rows=mysqli_fetch_array($r);

// query the address, party and ward tables using the vaules from the last query

$q2="SELECT * FROM address, party, ward WHERE (name.ward_id = ward.ward_id) AND (name.party_id = party.party_id) AND (name.address_id = address.address_id)";

// get the data ready to output into a table and query aginst the address, party and ward tables.
$rows=mysqli_fetch_array($r);

	} elseif  

	// will run a number of queries  depending on what criteria has been searched for.

[cainy1982]

Thinking about this maybe I should perform a join and query 3 tables using the search terms. Would this worj if someone only entered 1 search term for example.

[cainy1982]

This is what I have now come up with, im on my dinner so cant verify this works!

$q = "SELECT name.*, ward.*, party.* FROM name, party, ward WHERE (name.ward_id = $ward) AND (name.party_id = $party) AND (ward.ward_id = $ward) AND (party.party_id = $party) MATCH (name.first_name, name.surname) AGAINST $name SORT BY name.surname ASC";

Any comments?

[cainy1982]

finally managed to work out a soloution to this!

been banging my head against a wall for last few days but managed to get it working, welcome and comments about how to do things better!

<?php // This is the home page for the site.

// set the title for the html header
$page_title = 'Results';

// require the config file
require_once ('../includes/config.inc.php');

// include the header filde
// add header file once created


// require the database settings file using information from config.inc.php
require_once (MYSQL);

	// get the variables posted from the form in the url and clean before using it in a query
	$ward = mysqli_real_escape_string ($dbc, trim($_GET['ward']));
	$party = mysqli_real_escape_string ($dbc, trim($_GET['party']));
	$name = mysqli_real_escape_string ($dbc, trim($_GET['name']));

	// trim any whitespace from the free text field
	$name_trimmed = trim($name); 


// query the address, party and ward tables using the vaules from the last query

$q="SELECT name.*, ward.*, party.* FROM name, address, party, ward  ";

if ($ward <> "*") {

 $q = $q . "WHERE name.ward_id = '" . $ward . "' AND ward.ward_id = '" . $ward . "' "; 

} if ($party <> "*" && $ward <> "*") {

 $q = $q . "AND name.party_id = '" . $party . "' AND party.party_id = '" . $party . "'  ";

} if ($party <> "*" && $ward == "*") {

$q = $q . "WHERE name.party_id = '" . $party . "' AND party.party_id = '" . $party . "'  ";

} if ($ward <> "*" || $party <> "*") {

$q = $q . "AND name.surname LIKE '" . $name . "' ";

} if  ($ward == "*" && $party == "*") { 

$q = $q . "WHERE name.surname LIKE '%" . $name . "%' ";

}

echo $q = $q . ";";

// For debugging return any errors or run the query 
echo $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

// get the data ready to output into a table
$rows=mysqli_fetch_array($r);

echo $rows['surname'];

[cainy1982]

Sorry the code above was wrong it didnt check for values in the name field.

Haven't tested this live yet!

<?php // This is the home page for the site. 

// set the title for the html header 
$page_title = 'Results'; 

// require the config file 
require_once ('../includes/config.inc.php'); 

// include the header filde 
// add header file once created 


// require the database settings file using information from config.inc.php 
require_once (MYSQL); 

// get the variables posted from the form in the url and clean before using it in a query 
$ward = mysqli_real_escape_string ($dbc, trim($_GET['ward'])); 
$party = mysqli_real_escape_string ($dbc, trim($_GET['party'])); 
$name = mysqli_real_escape_string ($dbc, trim($_GET['name'])); 

// trim any whitespace from the free text field 
 $name_trimmed = trim($name); 


// Set the main query the wil be the dafult query if someone performs a blank search - bring back all results
$q="SELECT name.*, ward.*, party.* FROM name, address, party, ward  "; 

// if the ward field has a value then we will add the value to the $q
if ($ward <> "*") { 

	// append the value to the query string
	$q = $q . "WHERE name.ward_id = '" . $ward . "' AND ward.ward_id = '" . $ward . "' "; 

// if the party and ward field has a value add it the query string
} if ($party <> "*" && $ward <> "*") { 

	 // append the value to the query string
 	$q = $q . "AND name.party_id = '" . $party . "' AND party.party_id = '" . $party . "'  "; 

// if only party has a value add it the query string
} if ($party <> "*" && $ward == "*") { 

	// include the WHERE statement and add the value to the query string
	$q = $q . "WHERE name.party_id = '" . $party . "' AND party.party_id = '" . $party . "'  "; 

// If ward or party has a value and the user has enter some text in the search box add the value to the query string
} if ($ward <> "*" || $party <> "*" && $name <> "NULL") { 

	// append the value to the query string
	$q = $q . "AND name.surname LIKE '" . $name . "' "; 


// if ward and party party dont have a value and name does
} if  ($ward == "*" && $party == "*" && $name <> "NULL") { 

	// clude the WHERE statement and add the value to the query string
	$q = $q . "WHERE name.surname LIKE '%" . $name . "%' "; 

		} // close the statement

// create the full query strong
echo $q = $q . ";"; 

// For debugging return any errors or run the query 
echo $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc)); 

// get the data ready to output into a table 
$rows=mysqli_fetch_array($r); 

echo $rows['surname']; 

[leatherback]

Some suggestion on the way you could handle the query too. I am not sure I used all the right fields and conditions, but just look at the output of the echo, and see whether this is something that would woirk for you:

// base select query joins the 4 tables on their corresponding fields:

$q = "select * from name 
      left join address 
        on name.name_id = address.name_id
      left join party 
        on name.party_id = party.party_id
      left join ward 
        on name.ward_id = ward.word_id ";

// Set the sections for subset:
$qs = array();

if($ward <> "*") 
  {
  $qs[] = "ward.ward_id = '" . $ward . "' ";
  }

if($party <> "*")
  {
  $qs[] = " party.party_id = '" . $party . "' ";
  }

if($ward <> "*" || $party <> "*") 
  {
  $qs[] = " name.surname LIKE '%" . $name . "%' ";
  }

// Glue the pieces together
$where = "";
if(n_elements($qs) > 0)
  {
  $where = implode(' and ', $qs);
  }

$q .= " where ".$where;

Echo $q;

[cainy1982]

Thanks for the reply, the left join query works much better.

Much much appreciated!!!!

Ta for taking the time to respond