[Resolved] MySql Search Results

[Hoangsta]

I have looked through many forum on this topic of search for items in a table.

I have a table name "BusinessName" which has the usual business attributes. Anyways, I have this search engine that needs to have the business name fully type for it to search. For example, I have a business name of "Target", I have to type Target or else it wouldn't find it. Meaning, Tar will not find it or any other combinations besides Target.

My code:

$query = 'SELECT client_id,business_type_id,business_name,business_address,city,state_country,zip,business_phone,business_fax,webpagelink FROM BusinessName WHERE business_name LIKE \''.$searchterm.'\' LIMIT 0, 30';

$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo "<p>Number of records found: ".$num_results."</p>";
for ($i=0; $i <$num_results; $i++)

Please help. I don't want to just look for Target. I want to be able to search anything related to whatever the term is being searched for. Meaning, it doesn't need to be "Target". It could be "Walmart", "Nike", "phpbuilder", "Chocolate" etc.

I got my sql book with me "Teach Yourself SQL in 10 minutes" and it doesn't say much. Please Help. I'm losing weights staying up. 😃

[zhabala]

Try to add the percent sign at the end of searched string like that:

$query = 'SELECT `client_id`,`business_type_id`,`business_name`,`business_address`,`city`,`state_country`,
`zip`,`business_phone`,`business_fax`,`webpagelink` FROM `BusinessName` WHERE `business_name` LIKE \''.$searchterm.'%\' LIMIT 0, 30';

[Hoangsta]

the code with \%\ I had to do '%\' for it to work, but i ran into another problem...it searches for everything.

Example. user type in "Tar" and expect to find Target. which he does. But if user type in "aagbd" Target also is found.

I want a search engine like Yahoo.com

thanks for ur many help.

[zhabala]

I have just tried this:

mysql> select * from t1;
+----------+----------+
| fieldt11 | fieldt12 |
+----------+----------+
|        1 | AHOJ     |
|        2 | NAZDAR   |
|        3 | PA       |
+----------+----------+
3 rows in set (0.16 sec)

mysql> select * from t1 where fieldt12 like 'A%';
+----------+----------+
| fieldt11 | fieldt12 |
+----------+----------+
|        1 | AHOJ     |
+----------+----------+
1 row in set (0.04 sec)

mysql> select * from t1 where fieldt12 like 'a%';
+----------+----------+
| fieldt11 | fieldt12 |
+----------+----------+
|        1 | AHOJ     |
+----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where fieldt12 like '%';
+----------+----------+
| fieldt11 | fieldt12 |
+----------+----------+
|        1 | AHOJ     |
|        2 | NAZDAR   |
|        3 | PA       |
+----------+----------+
3 rows in set (0.00 sec)

And it seems to be working OK, show me your code

[Hoangsta]

this is my code:

<?php
trim($searchterm);
if (!$searchterm)
{
echo "You have not entered a search. Please go <a href=\"index.html\">Back</a> and try again.";
//exit;
}
$searchterm = addslashes($searchterm);
///////////////my database id name,password name, etc//////////////
if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}
mysql_select_db("SacramentoDirectory");
$sql = 'SELECT DISTINCT client_id,business_type_id,business_name,business_address,city,state_country,zip,business_phone,webpagelink FROM BusinessName LIMIT 0, 30';
//$query = 'SELECT client_id,business_type_id,business_name,business_address,city,state_country,zip,business_phone,business_fax,webpagelink FROM BusinessName WHERE business_name LIKE \''.$searchterm.'\' LIMIT 0, 30';
$result = mysql_query($sql);
$num_results = mysql_num_rows($result);
echo "<p>Number of records found: ".$num_results."</p>";
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<p><strong>".($i+1).". ";
echo htmlspecialchars( stripslashes($row["business_name"]));
echo "</strong><br> ";
echo htmlspecialchars (stripslashes($row["business_address"]));
echo ", ";
echo htmlspecialchars (stripslashes($row["city"]));
echo ", ";
echo htmlspecialchars (stripslashes($row["state_country"]));
echo " ";
echo htmlspecialchars (stripslashes($row["zip"]));
echo "<br>Phone: ";
echo htmlspecialchars (stripslashes($row["business_phone"]));
echo "&nbsp;&nbsp;<a href=\"http://{$row['webpagelink']}\">";
echo htmlspecialchars (stripslashes($row["webpagelink"]));
echo "</a></p>";
}

?>

[zhabala]

$sql = 'SELECT DISTINCT client_id,business_type_id,business_name,business_
address,city,state_country,zip,business_phone,webp
agelink FROM BusinessName LIMIT 0, 30';

It is easy, you have omitted the WHERE part of the SQL SELECT statement, then it is returning all the records

you have to add something like that:

WHERE `business_name` LIKE \\\''.$searchterm.'%\\\\' LIMIT 0, 30';

There have to be another backslash just three aposthrophes before the % sign, but I can write it here because of some restrictions.

Zdenek

[Hoangsta]

that code of urs work but now when my search is empty, and I hit search, it search everything. hey, i can't complain much, but thanks a lot. if you could see what you can think of to why my search fill is blank, yet searches everything...

[zhabala]

You can check the content of the search field and add the percent sign only, when it contains something. Otherwise you can display a message which will say "No search criteria entered" or something like that.

[Hoangsta]

zhabala, how did u learn php? and what books do you use?

[zhabala]

It is hard to answer question. I am old time programmer, I programm for about 20 yrs, so it was easy to start using another programming language.

I do not use any book about PHP, I have crawled through the PHP manual, which is available at their web site. Only to know, what it alows me to do.

Zdenek

[Hoangsta]

zhabala,

my code from above:

echo " <a href=\"http://{$row['webpagelink']}\">";
echo htmlspecialchars (stripslashes($row["webpagelink"]));

I tried to insert in target="_blank" within a combination of

echo " <a href=\"http://{$row['webpagelink']}\">";

and I don't know why it didn't work.

also, my code from above:

echo "You have not entered a search. Please go <a href=\"index.html\">Back</a> and try again.";
exit;
}
$searchterm = addslashes($searchterm);
///////////////my database id name,password name, etc//////////////
if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;

cuts off my footer. but this code, also from above:

echo "<p>Number of records found: ".$num_results."</p>";

includes my footer. I concluded that the exit; cuts off my footer. If I remove it, then when I search with nothing, it will show everyrecord.

thanks in advance

[zhabala]

You have to show me whole code, then I can help you. Show the code, which is not working as you want.

Zdenek

[Hoangsta]

echo " <a href=\"http://{$row['webpagelink']}\">";

if I modified to

echo " <a href=\"http://{$row['webpagelink']target="_blank"}\">";

or

echo " <a href=\"http://{$row['webpagelink']}target="_blank"\">";

or

echo " <a href=\"http://target="_blank"{$row['webpagelink']}\">";

I also tried some weird combination....

[zhabala]

Proper variant should be

echo " <a href=\"http://{$row['webpagelink']}\" target=\"_blank\">";

[Hoangsta]

your good. I give you props. I wanna be just like you with programming one day. You ever think about making a webpage of your own to display php information?

[zhabala]

Thanx,
no, I have never thought about site like that you mentioned. I thought about some commercial site, but I did not thought out anything :-) I think, that now we are so called off topic, so if you wanna talk with me about things not related to the PHP you can use my e-mail address [e-mail].

Zdenek

[Hoangsta]

I'm trying to put an image in the code:

echo " <a href=\"http://{$row['webpagelink']}\" target=\"_blank\">";

I have stored the image on my server...name map.gif.

echo "<a href=\"http://{$row['webpagelink']}<IMG SRC=\"map.gif\"\" target=\"_blank\">";

I don't know....

what i'm trying to output is this Map it! (image)

[zhabala]

Image links are created the same way as the text links, but there is an IMG tag instead of some text, like this:

echo "<a href=\"http://{$row['webpagelink']}\" target=\"_blank\"><IMG SRC=\"map.gif\"></A>";

[Hoangsta]

thanks again for all your help. Ur very resourceful.

[zhabala]

Whether you wanna pay small satisfaction fee for my help, please take a postcard with your city and send it to:

Zdenek Habala
Blazovice 288
Blazovice
664 08
Czech Republic

😃 🆒