Search Form Problem

PHP
1

Hello again,

I’ve created a search form but for some reason it’s only working on one field not the other. Search process looks like this.


mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
	mysql_select_db($db_name);

	$search_term = $_POST['search_term'];

	$sql_1 = mysql_query("SELECT DISTINCT info.scheme_id, info.scheme_title FROM (info, tags) WHERE info.scheme_title LIKE '%$search_term%' OR tags.tag_name LIKE '%$search_term%'");

	$result_num = mysql_num_rows($sql_1);
	echo "<p>Search found $result_num results.</p>";

	while($row = mysql_fetch_array($sql_1)){

		echo "<h3>" . $row['scheme_title'] . "</h3>";
		echo "<p>";
		$hex_values = mysql_query("SELECT hex_value FROM colours WHERE scheme_id=" . $row['scheme_id']);
		$number_colour = mysql_num_rows($hex_values);
		$nc = 1;
		while ($colour = mysql_fetch_array($hex_values)){
			echo "#".$colour['hex_value'];
			if($nc < $number_colour){
				echo " - ";
				$nc++;
			}
		}
		echo "</p>";
		$values = mysql_query("SELECT hex_value FROM colours WHERE scheme_id=" . $row['scheme_id']);
		while ($colours = mysql_fetch_array($values)){
			echo "<img src='images/colour_box.png' style='background-color: #".$colours['hex_value']."' />";
		}
		$tags = mysql_query("SELECT tag_name FROM tags JOIN scheme_tags USING ( tag_name ) WHERE scheme_id=".$row['scheme_id']);
		$number_tags = mysql_num_rows($tags);
		$nt = 1;
		echo "<p>";
		while ($tag = mysql_fetch_array ($tags)){
			echo $tag['tag_name'];
			if($nt < $number_tags){
				echo ", ";
				$nt++;
			}
		}
		echo "</p>";
	}

It doesn’t work with the tag_name, when you search for a tag it displays all records in the database

Any thoughts?
Thanks,
Steph.

2

I think my problem requires more information
i have 4 tables

info
colours
tags
scheme_tags


'info' (
'scheme_id' INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'scheme_title' VARCHAR( 150 ) NOT NULL
)

'colours' (
'colour_id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'scheme_id' INT NOT NULL ,
'hex_value' CHAR( 6 ) NOT NULL
)

'tags' (
'tag_name' VARCHAR( 50 ) NOT NULL PRIMARY KEY,
'tag_count' INT NOT NULL ,
)

'scheme_tags' (
'scheme_tag_id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'scheme_id' INT NOT NULL ,
'tag_name' INT NOT NULL
)

So the search works on the scheme_title but not on tag_name either from tags table or scheme_tags table. I changed the query as suggested but still no love there.

3

ok thanks Steph

you need to set the relationship between the two TABLES

  • TABLE info
  • TABLE tags

how do you relate them?

might something like


WHERE info.id=tags.tag_infoId

tags.tag_infoId here represents as a foreign key stored in TABLE tags that would identify a specific record that would match a record from TABLE info i.e. info.id

then append your search query


AND info.scheme_title LIKE '%" . $search_term . "%' OR tags.tag_name LIKE '%" . $search_term . "%'

filter and escape :slight_smile:

4

ok, can you please show your HTML form

5

Just a simple lil form


	<form  method="POST" action="search.php"  id="searchform">
	      <input  type="text" name="search_term">
	      <input  type="submit" name="submit" value="Search">
	</form>
6

Thankyou, I tried that but it still displays all records. i am just not sure how to get it to work.

7 

$sql_1 = mysql_query("SELECT DISTINCT info.scheme_id, info.scheme_title FROM (info, tags) WHERE info.scheme_title LIKE '%" . $search_term . "%' OR tags.tag_name LIKE '%" . $search_term . "%'");

don’t forget to filter and escape Steph :slight_smile:

8

i can’t have a tag id because the tag_name is unique and linked into count which updates if the tag is entered into another scheme, the scheme/tag relation is listed in the scheme_tags table. so i can’t see a way of relating them, i did try to pull tag name from the scheme_tags table replacing tags in that query but the problem was the same.

what about subqueries? or a different join type?

9

ok thanks
let’s go back to your main search query
FROM (info, tags)

how do you relate TABLE info and TABLE tags now :smiley:

10

oops when i typed that up i made a mistake, it is a varchar. i use the scheme_tags table to relate the scheme_id and the tag_name and the tag_name is a unique key

11

i also tried getting the tag_name from scheme_tags table but that failed too.

12

yep thanks for giving more informations Steph

you are making the search query on these 2 tables

  • TABLE info
  • TABLE tags

FROM (info, tags)


$sql_1 = mysql_query("SELECT DISTINCT info.scheme_id, info.scheme_title FROM (info, tags) WHERE info.scheme_title LIKE '&#37;" . $search_term . "%' OR tags.tag_name LIKE '%" . $search_term . "%'");

but when I saw the structure you posted, they have no relation at all


'info' (
'scheme_id' INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'scheme_title' VARCHAR( 150 ) NOT NULL
)
'tags' (
'tag_name' VARCHAR( 50 ) NOT NULL PRIMARY KEY,
'tag_count' INT NOT NULL ,
)

If you are going to search from these 2 TABLES, like what I’ve said you need to relate them :slight_smile:

possible solution

  • add a foreign key tag_id from TABLE tags to TABLE info

another problem i noticed is you don’t have tag_id on TABLE tags so you might want to add a tag_id on TABLE tags as well

other inconsistencies;
on TABLE scheme_tags, you have tag_name field type as INT

  • ‘tag_name’ INT NOT NULL

'scheme_tags' (
'scheme_tag_id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'scheme_id' INT NOT NULL ,
'tag_name' INT NOT NULL
)

how would you try to correctly relate it with tag_name ON TABLE tags if you have tag_name field type as VARCHAR

  • ‘tag_name’ VARCHAR( 50 ) NOT NULL PRIMARY KEY

'tags' (
'tag_name' VARCHAR( 50 ) NOT NULL PRIMARY KEY,
'tag_count' INT NOT NULL ,
)

I think you’ve missed a tag_id field on TABLE tags and that is what you need :slight_smile:

sounds like a MySQL problem

13

Just so you can see the ALMOST final result, waiting on a design update from the creative mind that doesn’t belong to me.

Aleyst.net/cslv4

14

yey glad we worked it out together. thanks for your patience Steph :slight_smile:
and don’t forget to always filter and escape :slight_smile:

15

HA! Thanks so much! I knew there was something i was missing but i just couldn’t put my finger on it :smiley:

Absolute legend

Cheers,
Steph

16 

$sql_1 = mysql_query("SELECT DISTINCT info.scheme_id, info.scheme_title FROM (info, tags, scheme_tags) WHERE scheme_tags.tag_name=tags.tag_name AND info.scheme_id=scheme_tags.scheme_id AND (info.scheme_title LIKE '%" . $search_term . "%' OR tags.tag_name LIKE '%" . $search_term . "%')");