Page 1 of 2

[SOLVED] Search All Can't seem to be able to set this up

Posted: Thu May 19, 2005 11:11 am
by Addos
I wonder (as a beginner) if anybody can help me out on this. I have a ‘select box’ dynamically populated from my MySQL database and at the moment it returns all the details listed for example:

Details 1
Details 2
Details 3

But I can’t find out how set this up to ‘search all’ for example:

Search All
Details 1
Details 2
Details 3

This is the little bit of code that I’m using at the moment:

Code: Select all

<?PHP echo "<select name='locations_idpk' id='locations_idpk'>\n";
	  	while($dbRow = mysql_fetch_array($getLocations)){ 
  		echo "<option value='"
		. $dbRow["locations_idpk"]  
		. "'>"
		. $dbRow["locations_name"]
		."</option>\n"; 
		} echo "</select>\n"; 
		?> Location
What do I need to do in order to set this up?

Thanks for any help
Brian

Posted: Thu May 19, 2005 11:40 am
by Burrito
when you say search all, do you mean you want it to select every option in the select list? Is the search all a checkbox outside of the select taht would select them all? Is the search all just an option in your select?

Any of those could be options and they're all possible, but we need more information to really help you out.

if you want to use a checkbox to select everything in the <select>, you're going to need a little JS (which I can help you with).

a little more info please....

Posted: Thu May 19, 2005 11:41 am
by phpScott
add it to your echo "<select ... line befor your php while loop.

Posted: Tue May 24, 2005 2:00 pm
by Addos
Thanks Burrito and sorry for not giving enough information.

Basically I have a dropdown select box and I don’t simply want the first item in the database to be shown in the list. I want it to be ‘Search All’ at the top of the list so that if somebody chooses to search the entire database rather than making a selection they will be able to.

You can see an example of my problem here http://www.irishmusicteachers.ie The first item on both of these select boxes are ‘location’ and ‘instrument’ and I want for example to be able to ‘search all’ of either so for example I’d like to be able to select Dublin 25 and ‘Instrument’ to be ‘Search all’ and visa versa.

This is a snip of the code that I’m using.

Thanks very much for your time.

Brian

Code: Select all

<?PHP echo "<select name='Counties_IDPK' id='Counties_IDPK'>\n";
	  
		while($dbRow = mysql_fetch_array($rstSearchLocations)){ 
  		echo "<option value='"
		. $dbRow["Counties_IDPK"]  
		. "'>"
		. $dbRow["Counties_Name"]
		."</option>\n"; 
		} echo "</select>\n"; 
		?>
      Choose County</td>
    </tr>
	<tr>
	  <td><?PHP echo "<select name='Instruments_IDPK' id='Instruments_IDPK'>\n"; 
		while($dbRow = mysql_fetch_array($rstSearch)){ 
  		echo "<option value='"
		. $dbRow["Instruments_IDPK"]  
		. "'>"
		. $dbRow["Instruments_Name"]
		."</option>\n"; 
		} echo "</select>\n"; 
		?>
      Choose Instrument or subject

Result page

Code: Select all

mysql_select_db($database_imtdatabase, $imtdatabase);
$query_rstResults = 'SELECT * FROM teachers,inst_teach,locations,instruments
WHERE teachers.contents_IDPK = inst_teach.teacher_IDFK
AND   inst_teach.instrument_IDFK = instruments.instruments_IDPK
AND   teachers.counties_IDFK = locations.counties_IDPK';

 //If "both" is selected, the filter won't be used.
 //If  "school" is selected, the clause should read: AND Teachers.TeachTypes_ID IN (1,3)
 //If "private" is selected, the clause should read: AND Teachers.TeachTypes_ID IN (2,3)

if ($_POST['School'] != '3') {
  $query_rstResults .= ' AND teachers.teachtypes IN (' .$_POST['School'].',3)';
 }
if (isset($_POST['Counties_IDPK'])) {
  $query_rstResults .= ' AND locations.counties_IDPK = ' . $_POST['Counties_IDPK'];
  }
if (isset($_POST['Instruments_IDPK'])) {
  $query_rstResults .= ' AND instruments.instruments_IDPK = ' .$_POST['Instruments_IDPK'];
  }

Posted: Tue May 24, 2005 3:26 pm
by Burrito

Code: Select all

<?PHP echo "<select name='Counties_IDPK' id='Counties_IDPK'>\n
<option value=\"%\">Search All</option>";
	  
		while($dbRow = mysql_fetch_array($rstSearchLocations)){ 
  		echo "<option value='"
		. $dbRow["Counties_IDPK"]  
		. "'>"
		. $dbRow["Counties_Name"]
		."</option>\n"; 
		} echo "</select>\n"; 
		?>
      Choose County</td>
    </tr>
	<tr>
	  <td><?PHP echo "<select name='Instruments_IDPK' id='Instruments_IDPK'>\n
<option value=\"%\">Search All</option>"; 
		while($dbRow = mysql_fetch_array($rstSearch)){ 
  		echo "<option value='"
		. $dbRow["Instruments_IDPK"]  
		. "'>"
		. $dbRow["Instruments_Name"]
		."</option>\n"; 
		} echo "</select>\n"; 
		?>
      Choose Instrument or subject

Result page

Code: Select all

mysql_select_db($database_imtdatabase, $imtdatabase);
$query_rstResults = 'SELECT * FROM teachers,inst_teach,locations,instruments
WHERE teachers.contents_IDPK = inst_teach.teacher_IDFK
AND   inst_teach.instrument_IDFK = instruments.instruments_IDPK
AND   teachers.counties_IDFK = locations.counties_IDPK';

 //If "both" is selected, the filter won't be used.
 //If  "school" is selected, the clause should read: AND Teachers.TeachTypes_ID IN (1,3)
 //If "private" is selected, the clause should read: AND Teachers.TeachTypes_ID IN (2,3)

if ($_POST['School'] != '3') {
  $query_rstResults .= ' AND teachers.teachtypes IN (' .$_POST['School'].',3)';
 }
if (isset($_POST['Counties_IDPK'])) {
  $query_rstResults .= ' AND locations.counties_IDPK like ' . $_POST['Counties_IDPK'];
  }
if (isset($_POST['Instruments_IDPK'])) {
  $query_rstResults .= ' AND instruments.instruments_IDPK like ' .$_POST['Instruments_IDPK'];
  }
BTW, those POST vars are always going to be set, if you want to check to make sure they have a value, then check the value don't use ISSET().

Posted: Tue May 24, 2005 4:15 pm
by Addos
Thanks very much for your fast reply. I apologies for my ignorance but I have added this <option value=\"%\">Search All</option>"; to a separate test page http://www.irishmusicteachers.ie/index_test.php and at the moment I’m getting the error

“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 instruments.instruments_IDPK = 65 ORDER BY teachers.conte”


Do I need to now amend the results page bellow with an additional query?

Code: Select all

if ($_POST['School'] != '3') {
  $query_rstResults .= ' AND teachers.teachtypes IN (' .$_POST['School'].',3)';
 }
if  ($_POST['Counties_IDPK']) {
  $query_rstResults .= ' AND locations.counties_IDPK like ' . $_POST['Counties_IDPK'];
  }
if ($_POST['Instruments_IDPK']) {
  $query_rstResults .= ' AND instruments.instruments_IDPK like ' .$_POST['Instruments_IDPK'];
  }
Many thanks again for your excellent help

Brian :wink:

Posted: Tue May 24, 2005 4:36 pm
by Burrito
the "%" is a wildcard in SQL (everything). You need to make sure it's encapsulated in single quotes because you're searching a string. I did notice on the snippet that you sent that you didn't close the single quote on the second portion of the variable...I just assumed that were closing it on the next declaration.

just make sure that you have single quotes around both sides of it.

Posted: Wed May 25, 2005 5:24 am
by Addos
Thanks again for you help.
I have been plugging away at this for a few hours but I still keep getting the error
“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 instruments.instruments_IDPK = 65 ORDER BY teachers.conte”

http://www.irishmusicteachers.ie/index_test.php for example.

I have made the changes you suggest but for some reason it still throws an error.

This is the bit of code that I’m using and I think I successfully made the corrections to it.

Code: Select all

<?PHP echo "<select name='Counties_IDPK' id='Counties_IDPK'>\n
	   <option value='%'\">Search All</option>"; 
		while($dbRow = mysql_fetch_array($rstSearchLocations)){ 
  		echo "<option value='"
		. $dbRow["Counties_IDPK"]  
		. "'>"
		. $dbRow["Counties_Name"]
		."</option>\n"; 
		} echo "</select>\n"; 
		?>
Thanks again for any advice

Brian

Posted: Wed May 25, 2005 9:04 am
by Burrito
echo your query and post it in here...

Posted: Wed May 25, 2005 10:02 am
by Addos
Thanks,
Is this what you mean? Sorry if I have not followed your reply but this is what I use after the select box form has passed its values.
Thanks for your patience

Brian

Code: Select all

<?php
mysql_select_db($database_database, $database);
$query_rstResults = 'SELECT * FROM teachers,inst_teach,locations,instruments
WHERE teachers.contents_IDPK = inst_teach.teacher_IDFK
AND   inst_teach.instrument_IDFK = instruments.instruments_IDPK
AND   teachers.counties_IDFK = locations.counties_IDPK';

if ($_POST['School'] != '3') {
  $query_rstResults .= ' AND teachers.teachtypes IN (' .$_POST['School'].',3)';
 }
if ($_POST['Counties_IDPK']) {
  $query_rstResults .= ' AND locations.counties_IDPK = ' . $_POST['Counties_IDPK'];
  }
if ($_POST['Instruments_IDPK']) {
  $query_rstResults .= ' AND instruments.instruments_IDPK = ' .$_POST['Instruments_IDPK'];
  }
$query_rstResults .= ' ORDER BY teachers.contents_IDPK';
$rstResults = mysql_query($query_rstResults, $imtdatabase) or die(mysql_error());
$row_rstResults = mysql_fetch_assoc($rstResults);
$totalRows_rstResults = mysql_num_rows($rstResults);
 ?>

Posted: Wed May 25, 2005 1:50 pm
by Burrito
no, echo your sql query I mean.

do the following you should:

Code: Select all

$query = "select blah from bling where bling = 'blah'";
echo $query

Posted: Wed May 25, 2005 2:38 pm
by Addos
Thanks again for your help.

If I try to run this test I still get the same error as previously posted. If though I select the first item in the list from the first select box, e.g. ‘Dublin 1’ & ‘Accompanist’ I get the following:

Code: Select all

SELECT * FROM teachers,inst_teach,locations,instruments 
WHERE teachers.contents_IDPK = inst_teach.teacher_IDFK
AND inst_teach.instrument_IDFK = instruments.instruments_IDPK
AND teachers.counties_IDFK = locations.counties_IDPK
AND teachers.teachtypes IN (1,3)
AND locations.counties_IDPK = 1
AND instruments.instruments_IDPK = 8
ORDER BY teachers.contents_IDPK
I have set this test up here so you can see the different results for yourself.

http://www.irishmusicteachers.ie/index_test.php

Thanks
B

Posted: Wed May 25, 2005 2:56 pm
by Burrito
visit your site I did, notice a few problems I have.

1) an extra double quote after your <option value='%'"> you have
2) still missing the single quotes around your value you are

assume I do now that this is a number field on the database? If the case this is, add the single quotes on the form page you will need as the query will require the single quotes for the '%' only.

echo the query above the the actual function you need to to output the results before it dies.

ex:

Code: Select all

$query = "select blah from bling where bling = 'blah'";
echo $query;
mysql_query($query)
  or die(mysql_error());
fix the suggestions I made above and post the query in here you should. Better able to help you I will be.

Posted: Wed May 25, 2005 3:51 pm
by Addos
Hi,
I have made those changes and sorry for not spotting the extra “ after you pointing this out so many times.

Here is the result now of running the ‘echo’

SELECT * FROM teachers,inst_teach,locations,instruments WHERE teachers.contents_IDPK = inst_teach.teacher_IDFK AND inst_teach.instrument_IDFK = instruments.instruments_IDPK AND teachers.counties_IDFK = locations.counties_IDPK AND locations.counties_IDPK = % AND instruments.instruments_IDPK = 65 ORDER BY teachers.contents_IDPKYou 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 instruments.instruments_IDPK = 65 ORDER BY teachers.contents_IDPK' at line 4

Again I have applied this to the test page so you can se it in action:
http://www.irishmusicteachers.ie/index_test.php

The field in the database that is been query by % is a number as you rightly pointed out !

Thanks so much

Brian

Posted: Wed May 25, 2005 4:00 pm
by Burrito
need the single quotes around the % you still do.

look like this it should:

Code: Select all

SELECT * FROM teachers,inst_teach,locations,instruments WHERE teachers.contents_IDPK = inst_teach.teacher_IDFK AND inst_teach.instrument_IDFK = instruments.instruments_IDPK AND teachers.counties_IDFK = locations.counties_IDPK AND locations.counties_IDPK = '%' AND instruments.instruments_IDPK = 65 ORDER BY teachers.contents_IDPK
add the single quotes on the form var I suggested.

ex:

Code: Select all

echo "<option value=\"'%'\">Select All</option>";