My SQL Query help please!

bribren2001

hey all

the following is php code for bringing back information in the database but what i want it to be able to do is bring back the information even though the search query is spelled incorrectly eg

if i had a movie like the "matrix" stored in my database and a user enters "metrix" in the search field then it should be able to still bring back the Matrix details

any help would be of great benefit

thanks in advance!

<?php
session_start();
header("cache-control: private");

$itemName = $_POST;
// Make a MySQL Connection
mysql_connect("localhost", "gamesemp_bill", "qwerty") or die(mysql_error());
mysql_select_db("gamesemp_cart") or die(mysql_error());

$_SESSION = $itemName;

// Get all the data from the "items" table
$result = mysql_query("SELECT * FROM items WHERE itemName = '$itemName'");
//or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Game</th>
<th>Description</th>
<th>Price</th>
<th>Qty</th>
<th>Format</th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td></tr>";

}
echo "</table>";

?>

Ph3n0m

First of all that search code will only return information based on finding somthing implicit - i.e. if you enter Metrix and it exists it will return something, but if its not in the database it will return nothing

You have to modify the mysql query to return similiar results to your mis spelled words - you could try to count the characters in the search string and then putting them in an array, do a search and return any results that match x amount of items in the array as part of the string - it will result in a longer search time as it would have to look in all itemName and do the comparisson with each one

bribren2001

thanks Ph3n0m for your reply

unfortunately i would not be very experienced in using my sql query code and was wondering was there much to the changing of this code(does it require much extra code?)

Ph3n0m

well then this will help



assuming the name of your search field is "itemName"

on your search results page do this: (dont forget to replace test with your database and table names)

//gets the first character of your search string
$f = substr ($itemName, 0,1);
echo $f;

//gets the last character of your search string
$f2 = substr ($itemName, -1, 1);
echo $f2;


$connection = mysql_connect("localhost", "root", "") or die ("Couldnt connect to database");
$db = mysql_select_db("test", $connection) or die ("Couldnt select database");
$sql = "SELECT title from test WHERE title LIKE '$f%$f2'"; 
// the above line search for any words that start with the first character and end with the last character

$sql_result = mysql_query($sql) or die("There are no news stories.");
$num = mysql_numrows($sql_result);
if ($num == "0"){
echo "There are no news stories in the database.<P>";
}
else {
echo "<table border='1'>";
echo "<tr> <th>Game</th>
<th>Description</th>
<th>Price</th>
<th>Qty</th>
<th>Format</th></tr>";
while ($row = mysql_fetch_array($sql_result))  
{  
echo "<tr><td>"; 
echo $row['itemName'];
echo "</td><td>"; 
echo $row['itemDesc'];
echo "</td><td>"; 
echo $row['itemPrice'];
echo "</td><td>"; 
echo $row['itemQty'];
echo "</td><td>"; 
echo $row['Format'];
echo "</td></tr>"; 
}  

bribren2001

thanks Ph3n0m ill give it a go

scargill

there is a function that might be of help. It should give you an idea of how 'similar' two strings are - its a bit hit and miss but it might give you what you need.

SOUNDEX(str)
Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is 4 characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphanumeric characters are ignored in the given string. All international alpha characters outside the A-Z range are treated as vowels:

mysql> SELECT soundex('Matrix');
-> 'M362'
mysql> SELECT SOUNDEX('Metrix');
-> 'M362'

Ph3n0m

let me know if that code i gave you works or not

bribren2001

will do ,just a few teething problems at the moment

thanks again

bribren2001

at the moment it giving me the following error

Parse error: parse error, unexpected $ in /home/gamesemp/public_html/findgames.php on line 61


<?php
session_start();
header("cache-control: private");

$itemName = $_POST;


$f = substr ($itemName, 0,1);
echo $f;

//gets the last character of your search string
$f2 = substr ($itemName, -1, 1);
echo $f2;


$connection = mysql_connect("localhost", "gamesemp_bill", "qwerty") or die ("Couldnt connect to database");
$db = mysql_select_db("gamesemp_cart", $connection) or die ("Couldnt select database");
$sql = "SELECT itemName from items WHERE itemName LIKE '$f%$f2'";
// the above line search for any words that start with the first character and end with the last character

$sql_result = mysql_query($sql) or die("There are no news stories.");
$num = mysql_numrows($sql_result);
if ($num == "0"){
echo "There are no news stories in the database.<P>";
}
else {
echo "<table border='1'>";
echo "<tr> <th>Game</th>
<th>Description</th>
<th>Price</th>
<th>Qty</th>
<th>Format</th></tr>";
while ($row = mysql_fetch_array($sql_result))
{
echo "<tr><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td><td>";
echo $row;
echo "</td></tr>";
}
echo "</table>";
?>

Ph3n0m

well for a start there arent 61 lines of code there, can you post up the entire contents of findgames.php

bribren2001

that the entire contents of findgames.php- i dont no why its lookin for line 61


thanks

Ph3n0m

oops, I left out a }, just copy whats below

<?php
session_start();
header("cache-control: private");

$itemName = $_POST['itemName'];


$f = substr ($itemName, 0,1);
echo $f;

//gets the last character of your search string
$f2 = substr ($itemName, -1, 1);
echo $f2;


$connection = mysql_connect("localhost", "gamesemp_bill", "qwerty") or die ("Couldnt connect to database");
$db = mysql_select_db("gamesemp_cart", $connection) or die ("Couldnt select database");
$sql = "SELECT itemName from items WHERE itemName LIKE '$f%$f2'"; 
// the above line search for any words that start with the first character and end with the last character

$sql_result = mysql_query($sql) or die("There are no games.");
$num = mysql_numrows($sql_result);
if ($num == "0"){
echo "There are no games in the database.<P>";
}
else {
echo "<table border='1'>";
echo "<tr> <th>Game</th>
<th>Description</th>
<th>Price</th>
<th>Qty</th>
<th>Format</th></tr>";
while ($row = mysql_fetch_array($sql_result)) 
{ 
echo "<tr><td>"; 
echo $row['itemName'];
echo "</td><td>"; 
echo $row['itemDesc'];
echo "</td><td>"; 
echo $row['itemPrice'];
echo "</td><td>"; 
echo $row['itemQty'];
echo "</td><td>"; 
echo $row['Format'];
echo "</td></tr>"; 
} 
echo "</table>";
}
?>

bribren2001

sorry for bearing all these problems on you but just two left

- it wont bring back the details unless it is word for word
- it is only bringn back the name and not the other details

thanks a million!

Ph3n0m

what do you mean by

bribren2001 wrote:

- it wont bring back the details unless it is word for word

and to get all details, change

$sql = "SELECT itemName from items WHERE itemName LIKE '$f%$f2'";

to

$sql = "SELECT * from items WHERE itemName LIKE '$f%$f2'";

bribren2001

if i dont enter for example FIFA Soccer 2005 in its full it will not return any details

Ph3n0m

try the above fix I suggested and see if that works

bribren2001

ye it brings back the full details but it still wont take a mispelt or even just half the word to bring back the details

Ph3n0m

well the code I gave you, will give you results based on any item that has a name starting with the first letter and ending with the last letter of your search. - i.e. YOu search for Mertix - it will give you all items spelt M***x

basically what you need to do is generate 2 queries

1) any item that starts and ends with 1st and last letter of search

and/or

2) take search and look for any item like that i.e. LIKE '%$itemName%'

bribren2001

Ye will do, thanks a million Ph3n0m for your brilliant help

greatly appreciated

thanks again

Ph3n0m

incidentally how were you performing the mis spelled search?

bribren2001

just by for example Fifa Soccer 2005

i would search for 'fifa' and leave out the rest

Ph3n0m

bribren2001 wrote:

just by for example Fifa Soccer 2005

i would search for 'fifa' and leave out the rest

well as I said on page 1, you have to have 2 queries, the first one I have already given you and the second would be another LIKE comparisson (I gave you the syntax for it, you just have to decide which search to perform first - partial search and then all items named Letter****Letter or the vice versa

If you are stuck, PM me and I will see if I have time to give you a dig out

bribren2001

PM sent your way
cheers