[RESOLVED] Coding a database search

[escargot]

Hi all,

After a bit of help if you'd be so kind.

I'm developing a website for my business and in these times of economic turmoil, I'm attempting it myself (read; can't afford a proper web developer).

As a relative novice to PHP/MySQL I've rambled my way through and basically created a database and the php code necessary to import data to the website. However, as part of the website functionality, I also want a simple 1 textfield form that people can use to enter keywords, this will then search all cells in the database and display & format the information on a new page.

I've got nowhere on this so far so any help will result in a virtual pint being offered.

[djjjozsi]

Download a php mysql generator from http://www.sqlmaestro.com/ , and you get a search and editor in about 1 minutes 🙂

This is a good painkiller.

Hello, jjozsi.

[escargot]

Right, I'm getting closer 🙂

This is the form code on index.php

<form method="POST" action="results.php">
Search Word: <input type="text" name="search">
<input type="SUBMIT" value="Search!">
</form>

And this is the results.php page code

<?php

$connection = mysql_connect("localhost","user","pass");
mysql_select_db("colvertgroup_co_uk_job",$connection);
$search = false; 
if (!empty($_POST['search']))
 $search = $_POST['search']; 
$result = mysql_query("SELECT * FROM tjob WHERE job_ref LIKE '%$search%' OR job_title LIKE '%$search%' OR job_desc LIKE '%$search%' OR job_location LIKE '%$search%' OR salary LIKE '%$search%' OR job_type LIKE '%$search%' OR key_words LIKE '%$search%'");


if($search){

while ($row = mysql_fetch_array($result))
{
for ($i=0; $i<mysql_num_fields($result); $i++)
echo $row[$i] . " ";
echo "<br /><br>";
}
}
?></div>

However... it will only search one string. For example, if I type in 'Managing Director', it will find and display all rows where Managing Director appears. If however, I want to search, 'Managing Director North West' so only the MD jobs in the North West appear, it doesn't find anything (because - I think - it's searching as one large string). Do I need to find some way of breaking up the form input so it would be Managing director AND north west?

[escargot]

djjjozsi;10902712 wrote:

Download a php mysql generator from http://www.sqlmaestro.com/ , and you get a search and editor in about 1 minutes 🙂

This is a good painkiller.

Hello, jjozsi.

Thanks for the link mate, unfortunately that piece of software is windows only, I'm using OS X. Also, I'd really rather learn it myself (with help and advice) as my intention is to develop the site into something with a lot more functionality further down the line.

[escargot]

Right, I'm getting closer 🙂

This is the form code on index.php

<form method="POST" action="results.php">
Search Word: <input type="text" name="search">
<input type="SUBMIT" value="Search!">
</form>

And this is the results.php page code

<?php

$connection = mysql_connect("localhost","user","pass");
mysql_select_db("colvertgroup_co_uk_job",$connection);
$search = false; 
if (!empty($_POST['search']))
 $search = $_POST['search']; 
$result = mysql_query("SELECT * FROM tjob WHERE job_ref LIKE '%$search%' OR job_title LIKE '%$search%' OR job_desc LIKE '%$search%' OR job_location LIKE '%$search%' OR salary LIKE '%$search%' OR job_type LIKE '%$search%' OR key_words LIKE '%$search%'");


if($search){

while ($row = mysql_fetch_array($result))
{
for ($i=0; $i<mysql_num_fields($result); $i++)
echo $row[$i] . " ";
echo "<br /><br>";
}
}
?></div>

However... it will only search one string. For example, if I type in 'Managing Director', it will find and display all rows where Managing Director appears. If however, I want to search, 'Managing Director North West' so only the MD jobs in the North West appear, it doesn't find anything (because - I think - it's searching as one large string). Do I need to find some way of breaking up the form input so it would be Managing director AND north west?

[djjjozsi]

to the WHERE clause let's add CONCAT

From my table:

$fields='`tag` , `title` , `message` , `lead`';

if(!empty($_POST["searchword"]))
{

$word=mysql_real_escape_string($_POST["searchword"]);
$sql="SELECT $fields
FROM news
WHERE CONCAT ( $fields ) LIKE '%$word%'";
/* results comes here.*/
}

If its needed, then explode the search words. (str_explode)

hello, jjozsi.

[escargot]

Ok, my code now looks like this:

<?php

$connection = mysql_connect("localhost","user","pass");
mysql_select_db("colvertgroup_co_uk_job",$connection);
$search = false; 
$fields='job_title , job_ref , job_desc , job_location , salary , job_type , key_words '; 

if(!empty($_POST['search'])) 
{ 

$word=mysql_real_escape_string($_POST['search']); 
$sql="SELECT $fields 
FROM tjob 
WHERE CONCAT ( $fields ) LIKE '%$search%'"; 
/* results comes here.*/ 
} 

if($search){

while ($row = mysql_fetch_array($sql))
{
for ($i=0; $i<mysql_num_fields($sql); $i++)
echo $row[$i] . " ";
echo "<br /><br>";
}
}
?>

BUT, it now won't work at all (ie, won't display any results), have I missed something obvious?

[djjjozsi]

$search is false in your code.

[escargot]

Yep, I realised i'd made a few errors so I've changed it to this:

<?php

$connection = mysql_connect("localhost","user","pass");
mysql_select_db("colvertgroup_co_uk_job",$connection);
$fields='job_title , job_ref , job_desc , job_location , salary , job_type , key_words '; 
$word = false; 
if(!empty($_POST['search'])) 
{ 
$word=mysql_real_escape_string($_POST['search']); 
$sql="SELECT $fields 
FROM tjob 
WHERE CONCAT ( $fields ) LIKE '%$word%'"; 
/* results comes here.*/ 

if($word){

while ($row = mysql_fetch_array($sql))
{
for ($i=0; $i<mysql_num_fields($sql); $i++)
echo $row[$i] . " ";
echo "<br /><br>";
}
}
}
?>

Still returns nothing though. Not sure what's wrong with it at all.

[djjjozsi]

$sql is not equal with $result!

<?php

$connection = mysql_connect("localhost","user","pass");
mysql_select_db("colvertgroup_co_uk_job",$connection) ;

$fields='`job_title` , `job_ref` , `job_desc` , `job_location` , `salary` , `job_type` , `key_words`';
$word = false;
if(!empty($_POST['search']))
{
	$word=mysql_real_escape_string($_POST['search']);
	$sql="SELECT $fields
	  FROM `tjob` WHERE concat($fields) LIKE '%$word%'";


if($word!=false)
{
	print $sql."<br />";
	$result=mysql_query($sql);
	while ($row = mysql_fetch_row($result))
	{
		for ($i=0; $i<mysql_num_fields($result); $i++)
		echo $row[$i] . " ";
		echo "<br /><br>";
	}
}
}
?>

[escargot]

We're getting there mate. 🙂

The only problem now is that the results page displays this:

SELECT job_title , job_ref , job_desc , job_location , salary , job_type , key_words FROM tjob WHERE concat(job_title , job_ref , job_desc , job_location , salary , job_type , key_words) LIKE '%managing director%'

As well as the search results. Eg;

SELECT job_title , job_ref , job_desc , job_location , salary , job_type , key_words FROM tjob WHERE concat(job_title , job_ref , job_desc , job_location , salary , job_type , key_words) LIKE '%managing director%'
Managing Director 3 Our client has aspirations of floating on AIM within 2 years, as part of this ambitious growth plan, they are looking to recruit a managing director who has the necessary gravitas and ability to manage the strategy and direction for the business. You will either be from a similar background or be a proven general manager looking for the next step. London 100,000 + bonus + car + pension + healthcare Permanent

(Ignore the actual data, i've not formatted it properly yet; just want to get the code right first).

[escargot]

Gah... right, I've stopped being an idiot. The reason it was displaying that was the use of the print function. Silly me.

However, whilst it now searches the database beautifully, I think we need to look at str_explode as it still won't allow me to search by typing in 'managing director north west' and displaying only managing directors located in the north west (it currently finds no results at all).

[djjjozsi]

you can also make better search engine then this CONCAT style.
and lets design a pager for the results.

After the search, you can make ORDERING , Pager, And Filtering opportunity.

But to tell you the truth, this database design is quite not good. You need to read about database normalisation.

Hello, jjozsi.

[escargot]

djjjozsi;10902750 wrote:

you can also make better search engine then this CONCAT style.
and lets design a pager for the results.

After the search, you can make ORDERING , Pager, And Filtering opportunity.

But to tell you the truth, this database design is quite not good. You need to read about database normalisation.

Hello, jjozsi.

That sounds good to me. When the database has lots of jobs in, I'd like to be able to set a max number of jobs per page, then have the ability to move through the next page etc.

I'll have a read up on the database normalisation stuff too, I freely admit being a novice so it's likely I could improve it with a bit more knowledge.

Equally though, I still need to get that search string working properly too 🙁

[escargot]

Me again.

After a weekend of banging my head against a brick wall with regards to full text searching. I've decided to incorporate a drop down menu of locations into the search form. Only problem now is getting that to incorporate into the php script.

Here's what I've got so far (sufficed to say; it isn't working):

<?php 

$connection = mysql_connect("localhost","user","pass"); 
mysql_select_db("colvertgroup_co_uk_job",$connection) ; 

$fields='`job_title` , `job_ref` , `job_desc` , `job_location` , `salary` , `job_type` , `key_words`'; 
$word = false; 
if(!empty($_POST['search'])) 
{ 
$word=mysql_real_escape_string($_POST['search']);
$location=($_POST['Location']);

$sql="SELECT * 
FROM `tjob` WHERE $fields LIKE ('%$word%' AND '$location')"; 


if($word!=false) 
{ 

$result=mysql_query($sql); 
while ($row = mysql_fetch_row($result)) 
{ 
for ($i=0; $i<mysql_num_fields($result); $i++) 
echo $row[$i] . " "; 
echo "<br /><br>"; 
} 
} 
} 
?>

And the form code HTML:

<form method="POST" action="results1.php">
<p>Keyword: 
<input name="search" type="text" value="job title"><br />
Location:<select name="Location" size="1" id="Location">
<option value="Scotland">Scotland</option>
<option value="North West">North West</option>
<option value="North East">North East</option>
<option value="Wales">Wales</option>
<option value="West Midlands">West Midlands</option>
<option value="East Midlands">East Midlands</option>
<option value="South West">South West</option>
<option value="London">London</option>
<option value="South East">South East</option>
</select><br />

<INPUT type="image" name="search" src="Graphics/buttons/search_15.gif" border="0">
</p>
</form>

Any help greatly appreciated as I'm about to throw this fking mac out of the window.

[djjjozsi]

you did not CONCAT the field names,

location is a variable, but you did not use it as SQL FILTER

See the previous posts, and lets try to use that with an addition to use $_POST["location"] as a condition. See the CORRECT query in phpmyadmin, you can make it using the phpmyadmin query builder, then use that SQL command in your php code replaced the SANTIZED variables from the dropdown.

[escargot]

Ok mate;

mysql_select_db("colvertgroup_co_uk_job",$connection) ; 

$fields='`job_title` , `job_ref` , `job_desc` , `job_location` , `salary` , `job_type` , `key_words`'; 
$word = false; 
if(!empty($_POST['search'])) 
{ 
$word=mysql_real_escape_string($_POST['search']);
$location=($_POST['Location']);

$sql="SELECT * 
FROM `tjob` WHERE CONCAT( $fields ) LIKE ('%$word%' AND '$location')"; 


if($word!=false) 
{ 

$result=mysql_query($sql); 
while ($row = mysql_fetch_row($result)) 
{ 
for ($i=0; $i<mysql_num_fields($result); $i++) 
echo $row[$i] . " "; 
echo "<br /><br>"; 
} 
} 
} 

So that query is wrong?

[escargot]

Ok;

The query I'm running in phpmyadmin is:

SELECT job_title , job_ref , job_desc , job_location , salary , job_type , key_words FROM tjob WHERE CONCAT (job_title , job_ref , job_desc , job_location , salary , job_type , key_words) LIKE 'Managing Director'

This theoretically according to our code, should return 2 results. However, it's not returning anything at all.

If I do;

SELECT job_title from tjob WHERE CONCAT(job_title) LIKE 'Managing Director'

I get the 2 results. I'm confused.

[escargot]

It lives, it breathes, IT WORKS! 😃

mysql_select_db("colvertgroup_co_uk_job",$connection) ; 

$fields='`job_title` , `job_ref` , `job_desc` , `job_location` , `salary` , `job_type` , `key_words`'; 
$word = false; 
if(!empty($_POST['search'])) 
{ 
$word=mysql_real_escape_string($_POST['search']);
$location=($_POST['Location']);

$sql="SELECT * FROM `tjob` WHERE (`job_title` LIKE '$word' OR `job_ref` LIKE '$word' OR `salary` LIKE '$word' OR `job_type` LIKE '$word' OR `job_desc` LIKE '$word' OR `key_words` LIKE '$word') AND `job_location` LIKE '$location'";

if($word!=false) 
{ 

$result=mysql_query($sql); 
while ($row = mysql_fetch_row($result)) 
{ 
for ($i=0; $i<mysql_num_fields($result); $i++) 
echo $row[$i] . " "; 
echo "<br /><br>"; 
} 
} 
} 
?>

Now to tidy it all up, add error handling and make the results look pretty but woohoo, atleast it works!!

[djjjozsi]

Now to tidy it all up, add error handling and make the results look pretty but woohoo, atleast it works!!

You know, the next step is to check if the two condition have set.
The beauty of the programming if you make it of your own! 🙂

Hello, jjozsi.