PHP Forum

**Isabella_Smith** · 04:05 AM

select * from tblbusinessinfo where city like 'meerut' and subcategory1 and subcategory2 and subcategory3 like 'Restaurant';

Here's the more information about it..................please help...........its important.....

PHP Code:


			
<?
                      $sql_c="select * from tblbusinessinfo where subcategory1 and subcategory2 and subcategory3 like '".$_REQUEST['get_searchword']."%' and city like '".$_REQUEST['city_search']."%'  order by business_id";
                      $result_c=mysql_query($sql_c);
                      $tot_record=mysql_num_rows($result_c);
                      
                        $record_per_page=20;
                        $scroll=4;
                        $zz=$_REQUEST['get_searchword'];
                        $xz=$_REQUEST['city_search'];
                        $page1=new page1(); 
                        
                                
                        $page1->set_page_data($_SERVER['PHP_SELF'],$tot_record,$zz,$xz,$record_per_page,$scroll,true,true,true);
                        $result_c = mysql_query($page1->get_limit_query($sql_c));
                        // For paging purpose 
                        if($_GET['page']=="" || !isset($_GET['page']) || $_GET['page']==0)
                            $sno=1;
                        else
                            $sno = ($record_per_page*$_GET['page'])+1;
                      
                      $sno_22 = 0;
                      if($tot_record > 0)
                      {
                      while($row_c=mysql_fetch_array($result_c))
                      { 
                        $sno_22++;
                        if($sno_22%3==1)
                        {
                      ?>

thanks

In result of above coding.................its working for the city parameter............but not matching the keywords...............Please help me to find out.......where I mistook..

Your help would be highly appreciated.

Thanks

**lizciz** · 01-30-2010, 04:15 AM

Not entirely correct. You have to set a comparison for each field, as in

PHP Code:


			
$query = "select * from table where field1 like '$value1' and field2 like '$value2' and field3 like '$value3"; // and so on

**Isabella_Smith** · 01-30-2010, 04:20 AM

Quote:

Originally Posted by lizciz

Not entirely correct. You have to set a comparison for each field, as in

PHP Code:


			
$query = "select * from table where field1 like '$value1' and field2 like '$value2' and field3 like '$value3"; // and so on

Thanks lizciz,

But I want to match 1 value with 3 fields............is this possible.........

or

is this possible if I could put these 3 keywords into 1 filed....and that can be recognized specifically............

If I use "enum" and specifies these fields like 'a', 'b', 'c'.....will this work for current condition.........as I am comparing a value with 3 fields........

**mtishetsky** · 01-30-2010, 04:35 AM

Simple way:
select * from tbl where (field1 like '{$value}%' or field2 like '{$value}%' or field3 like '{$value}%') and city like '{$city}%'

Correct way, assuming that a restaurant can be located in different cities and a city can contain multiple restaurants:
table "cities" with fields id, name, etc
table "category" with fields id, name, anything_else
table "business" with fields id, name, whatever_more
table "bcc" with fields business_id, city_id, category_id

select * from bcc join cities c on bcc.city_id = c.id join business b on bcc.business_id = b.id join category t on bcc.category_id = t.id where t.name = 'Restaurant' and city = 'Las Vegas'

Still in this aproach it is preferred to use numeric identificators instead of string values to increase performance.

**Isabella_Smith** · 01-30-2010, 05:09 AM

Thanks mtishetsky,

I am going with your suggestions, but here one more thing I want to mention.....that........A record (restaurant) may have many keywords rather than 1.

Please let me know how I'll compare them all with the input given by user.

Please let me know if we can add it into the query you gave me:

select * from bcc join cities c on bcc.city_id = c.id join business b on bcc.business_id = b.id join category t on bcc.category_id = t.id where t.name = 'Restaurant' and city = 'Las Vegas'

**mtishetsky** · 01-30-2010, 05:21 AM

Probably I don't get your question. What is a "keyword" here? How does your actual data look like?

**Isabella_Smith** · 05:28 AM

Its for yellow pages directory.....structure is something like this..............

business_id int(10)

business_name varchar(25)
contact_person varchar(25) subcategory1 int(10)
subcategory2 int(10)
subcategory3 int(10)
state varchar(15)
city int(15)
location varchar(15)
address1 varchar(15)
address2 varchar(15)
postcode int(10)
website varchar(20)
email varchar(20)
phone int(30)
fax int(15)
mobile int(30)
description varchar(250) utf8_general_ci

So, a record may have many keywords/category........for example........Restaurant, Non-Veg Restaurant, Chinese etc.....

**Isabella_Smith** · 01-30-2010, 05:54 AM

Further more, if you have other suggestion for the table structure..............as one record/business can be listed into multiple categories....................

Your posts are really helpful.............any of your suggestions would be highly appreciated.

**mtishetsky** · 01-30-2010, 07:22 AM

Definitely, if you can to put a business to more than one category you should move categories to a separate table. This will also allow you to specify any number of categories per business if required, not only three. In this case your select query will look like the following:

select * from category c join business b on b.category_id = c.id where c.name = 'Restaurant' and b.city = <numeric ID of Las Vegas>

Such kind of query will require an index on name field in category table, so it would be more efficient to use string names only for displaying and refer to rows of category table by numeric ID:

select * from category c join business b on b.category_id = c.id where c.id = <numeric ID of Restaurant category> and b.city = <numeric ID of Las Vegas>

If you need to select businesses from multiple categories, your query will change into this one:

select * from category c join business b on b.category_id = c.id where c.id in (<ID of Restaurant category>,<ID of Chinese category>, <ID of whatever else>) and b.city = <numeric ID of Las Vegas>

**Isabella_Smith** · 01-30-2010, 07:52 AM

Quote:

Originally Posted by mtishetsky

Definitely, if you can to put a business to more than one category you should move categories to a separate table. This will also allow you to specify any number of categories per business if required, not only three. In this case your select query will look like the following:

select * from category c join business b on b.category_id = c.id where c.name = 'Restaurant' and b.city = <numeric ID of Las Vegas>

Such kind of query will require an index on name field in category table, so it would be more efficient to use string names only for displaying and refer to rows of category table by numeric ID:

select * from category c join business b on b.category_id = c.id where c.id = <numeric ID of Restaurant category> and b.city = <numeric ID of Las Vegas>

If you need to select businesses from multiple categories, your query will change into this one:

select * from category c join business b on b.category_id = c.id where c.id in (<ID of Restaurant category>,<ID of Chinese category>, <ID of whatever else>) and b.city = <numeric ID of Las Vegas>

'

Thanks a lot...I'll try it......thanks again.

**janijani** · 01-30-2010, 09:03 AM

When you want to check this value

Code:

$query = "select * from table where field1,field2,field3 like( '$value1' and  '$value2' and  '$value3')"; // and so on

check like this or post the comment