Like query question

[le007]

Hi all,

 

I'm working on making a search box for my database. Can someone please elaborate it a little. I need to break up the text that is in 'search' by spaces so for example: the words searched were "book with words" and the keywords field in the database only had "book words" in it - I need to be able to search for more than the exact phrase.

 

I trying to get it right but its tricky. I need to manually enter keywords, a link and a description to my db. 3 fields. i then want a text box where people can use keywords to search and the corresponding link and description will appear. So far, below is what I'm working at but it ain't working properly.

Thanks,

 

Le007

 

 

 

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($keywords, $link, $description) or die                      ('Error connecting to mysql');

$searchy=$_REQUEST['search'];

$result = mysql_query("SELECT * FROM dbase WHERE tags LIKE '%$searchy%' ") or die(mysql_error());

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
foreach ($row as $key => $value) {$$key = $value;}
// Print out the contents of each row
echo "<a href='$url'>$details</a><br />";
}

[akitchin]

have a look at the explode() function.  this will allow you to separate the search string by spaces.  you can then toss each one into a separate LIKE statement and put them all in your WHERE clause.  you may want to filter for ambiguous words such as "and," "the," "a" etc.

[le007]

yeah akitchen, thats exactly what I need - but I need code example of that because I cant get it right. Any chance? much thanks

[akitchin]

i'll give you some basics, but to warn you, they will involve a lot of array manipulation.  if you don't know what an array is, you're going to have to go back to the VERY basics in the PHP manual (or any other programming language, for that matter):

 

<?php
$search_string = 'stuff and books and things'; // assume this is what they put into the form
$words = explode(' ', $search_string); // will contain the array [0] => 'stuff', [1] => 'and', [2] => 'books', [3] => 'and', [4] => 'things'
?>

 

$words will now contain an array of the words that were separated by spaces in the original search string.  to drop out the common words, you can use a difference function, which basically returns everything in the first array that ISN'T in any of the subsequent ones:

 

<?php
$silly_words = array('and', 'the', 'a', 'things');
$real_words = array_diff($words, $silly_words); // will contain the array [0] => 'stuff', [2] => 'books'
?>

 

from there, it's simply a matter of constructing each individual LIKE statement of the WHERE clause using a foreach() loop:

 

<?php
$likes = array();
foreach ($real_words AS $search_term)
{
  $likes[] = "tags LIKE '%$search_term%'";
}
?>

 

the reason i've done it this way is to teach you how to use implode(), and how powerful it can be.  now that we have all of our LIKE statements, we can glue them together using either 'AND' or 'OR', depending on how picky we want to be.  here i'll use AND:

 

<?php
$search_statement = implode(' AND ', $likes); // will contain "tags LIKE '%stuff%' AND tags LIKE '%books%'"
?>

 

remember, we need to use a space before and after 'AND' since there is no padding around each of our LIKE statements.

 

now glue $search_statement into your query's WHERE clause using whatever concatenation method you feel like (again, if you don't know what that is, have a google on it) and run that through the db.

[DarkWater]

Having a bunch of keywords in one column in the database goes against normalization and makes stuff like this hard.  You could have a keywords table that corresponds to records in your dbase table, then just do a JOIN.

 

SELECT * FROM dbase INNER JOIN keywords ON keywords.dbase_id = dbase.id WHERE keywords.keyword IN ('the', 'book', 'foo');

[Jabop]

Or you could explode the search string on space and then append AND $term etc etc

[DarkWater]

Or you could explode the search string on space and then append AND $term etc etc

 

Still sloppy and goes against normalization though.

[akitchin]

Or you could explode the search string on space and then append AND $term etc etc

 

Still sloppy and goes against normalization though.

 

true, but no harm in teaching a new technique (even though i agree that normalization is the better angle here).

[le007]

Thanks very much indeed akitchen for all the code. Yes, I do know what arrays are - I can follow your code relatively well. I need to re-read it a good times. I am gunna attempt it this way, I'm using a form and POST to get the data in the first place.

 

Darkwater, your way does certainly look easier but I don't know how to join tables to be honest. At least you all understand me, all I need is the simplest way to get a few keywords correspond with a link and a description (sounds so simple).

 

Jabop, your way also looks easy?

 

Thank you all for your help so far anyway.

[DarkWater]

I showed you a query you could use.  The table would be very simple:

 

CREATE TABLE keywords (

        id INT NOT NULL AUTO_INCREMENTING,

        dbase_id INT NOT NULL,

        keyword VARCHAR(50)

);

 

Then you could use the query I gave earlier.

[le007]

SELECT * FROM dbase INNER JOIN keywords ON keywords.dbase_id = dbase.id WHERE keywords.keyword IN ('the', 'book', 'foo');

 

dbase is name of the db, keywords is name of one table but whats keywords.dbase_id? Where does it connect to the second table and should the', 'book', 'foo' not be a string or why have you like that?

[DarkWater]

SELECT * FROM dbase INNER JOIN keywords ON keywords.dbase_id = dbase.id WHERE keywords.keyword IN ('the', 'book', 'foo');

 

dbase is name of the db, keywords is name of one table but whats keywords.dbase_id? Where does it connect to the second table and should the', 'book', 'foo' not be a string or why have you like that?

 

Sorry, I was out with some friends. keywords.dbase_id is the name of the ID column that joins dbase to keywords and the . syntax is to eliminate ambiguity.  Each of the string in an IN clause has to be it's own string, which is easily done with PHP and then handled by MySQL.

[le007]

Not a problem Darkwater - thanks.

 

So far I'm about here:

 

<?php
$conn = mysql_connect("localhost", "root", "") OR die ('Could not connect to mySQL: ' . 

mysql_error());;

mysql_select_db("db1") OR die ('Could not select a Database: ' . mysql_error());

    $sql = "SELECT * FROM one";

    $data = mysql_query( $sql ) or die( "Could not get threads" ); 

    while ( $data2 = mysql_fetch_array( $data ) ) 
{
$keyword = $data2[keyword];
// fill in other 2
$link = $data2[link];
$description = $data2[description];

echo $keyword;
echo $link;
echo $description;
}

 

But I'm fighting on - gunna get this one way or the other, some good guys like yourself have helped me out and I do know the fundamentals involved - I'm expert on css and obviously enough html but php has eluded me somewhat - I just haven't needed it too much up until this point. I do now though. I'm just toying with the db to try and figure it out first. I really then need to make a form - post the data to the about code and use the like expression. I know earlier akitchen gave me all the code I just need to piece it together - do you think your way is easier?

Regards,

Le007

[DarkWater]

My way is certainly not easier, but it's certainly better.  There's a tradeoff between ease and best practice in almost every situation though.  Also, you have two ; on the mysql_error(); line, and read my post here.  My fingers are in painnn.  Just kidding.  But read it, and correct your array syntax (you'll see what I mean after you read it) and make sure you understand it.  That's the most important.

[le007]

Yep I did read your post and got it all done. Excellent - at least thats working and inserting properly which is always nice

On paper it doesnt look like an overtly huge project:

 

DB

connect

 

form -> post data

grab data

 

match data from the form to a link and description

 

I guess it relays all back to the whole standard word phrase, 'and', 'or', 'the' etc.

[DarkWater]

Okay, how I'd PERSONALLY do it is like this:

 

1. Create the form

The form would be something like:

Link [                            ] (input box)

Description

[                              ]

[                              ]

[                              ] (textarea)

Keywords [                          ] (input box)

 

The keywords would be a comma separated list, i.e:

book,test,air,magic,php

 

2. Process the keywords and other data

This is probably the most "tricky" part other than the retrieval query.  It would look something like:

<?php
//$link, $description, $keywords are all populated and sanitized.  However you want to do it. xD
$sql = sprintf('INSERT INTO dbase (link, description) VALUES ("%s", "%s")', $link, $description);
$result = mysql_query($sql) or die(mysql_error()); //easy part
$id = mysql_insert_id(); //get the ID of the new dbase row to use in the keywords query!
$keywords = explode(',', $keywords);
foreach ($keywords as $keyword) {
         $sql = sprintf('INSERT INTO keywords (dbase_id, keyword) VALUES (%d, "%s")', $id, $keyword);
         $result = mysql_query($sql) or die(mysql_error()); //insert the keyword and be sure to use the id of the row in dbase to "link" tables
}

 

That's the basic idea of the insertion.

 

3. Retrieve data and loop through rows

I already posted the exact query you'll need, and showed you how to loop.  See what you can do.

[le007]

Just for testing I tried this:

 

$result = mysql_query("SELECT * FROM one WHERE 'keyword' LIKE 'Peter' ") or die(mysql_error());

 

echo $result;

 

Got that whole resource '4 thing again and I really did read all your comments - I understand that its just telling me what php is doing.

[DarkWater]

You didn't actually get the result set.

 

$result = mysql_query("SELECT * FROM one WHERE keyword LIKE 'Peter'") or die(mysql_error()); //NO ' ' AROUND KEYWORD COLUMN!

while ($row = mysql_fetch_assoc($result)) { //get data into $row

print_r($row) . "<br />";

}

[le007]

*Snap*

 

I was working on it and got it this way, not sure if its as efficient:

 

while($row1 = mysql_fetch_array($result)){
  echo $row1['keyword'] . " " . $row1['link']. " " . $row1['description'];
  echo "<br />";
  }

[DarkWater]

Same thing, I just used print_r() because I just wanted you to see how the array looked.  Also, why are you using $row1 instead of simply $row?  It's just easier.

[le007]

Well I had $row in already to select everything so I didn't wanna duplicate the variable name -

 

I now have form sending A singular keyword to the this page and it is recogonising it fine - its printing the correct corresponding data. I need to do a lot of work to it now though. I need to see if it'll work if one word is entered even if two words are in the keyword field and obviously enough delve into the whole 'common words' issue. Thanks though buddy, I'm getting there.

 

<?php
$conn = mysql_connect("localhost", "root", "") OR die ('Could not connect to mySQL: ' . mysql_error());;

mysql_select_db("wow") OR die ('Could not select a Database: ' . mysql_error());


$sql = mysql_query("SELECT * FROM one") OR die(mysql_error());
while ($row = mysql_fetch_assoc($sql)) {
     echo $row['keyword'] . "<br />";
     echo $row['link'] . "<br />";
     echo $row['description'] . "<br />";
}


$searchy=$_REQUEST['search'];

$result = mysql_query("SELECT * FROM one WHERE keyword like '%$searchy%' ") or die(mysql_error());

while($row1 = mysql_fetch_array($result)){
  echo $row1['keyword'] . " " . $row1['link']. " " . $row1['description'];
  echo "<br />";
  }

?>

[DarkWater]

I just noticed something that's kind of bad practice.  Don't use $_REQUEST, and instead, use the proper superglobal that correlates to where you data is coming from, be it $_GET, $_POST, or $_COOKIES.  Also, I'd still use my method of a keywords table.  I'll try to help you with it, and it'll make life much easier down the line.

[le007]

Amazingly, it works? Someone it recogonises any word in the keyword field and produces the correct corresponding data......

[le007]

Right ok I gotta get outta here for a few minutes - ok I'll go with your advice and try the second table. You've helped out a lot, thanks a heap.  I've just literally tried any letter and indeed it works. It produces both results - I'm quite happy with the progress done already, its a decent enough start considering I didnt even know how to connect to the db initially. I'm learning though..... Cheers Darkwater, back soon.

[DarkWater]

Alright, see ya'.