Ad:

PHP Discussion Thread
Marked Solved
Views: 2954

Nov 7th, 2009

complex sql database search using php code

Expand Post »

Hi, I am trying to create a search function on an html page. I have written the page and part of a php page.

I trying to get help with the search as there are 3 criteria;

So far I have made this search form;

(textbox name=searchterm) Brand:
(dropdown name=type) Type: (3 options; 'PC', 'LAPTOP', 'PC and LAPTOP')
(dropdown name=on0) Online or Highstreet: (2 options; 'ONLINE', 'HIGHSTREET')
(textbox name=postcode) postcode: (this is hidden until the user chooses 'HIGHSTREET' on 'on0'

so I am looking for the php code for the 'type' choice if the user says 'PC' then it will search for the 'Brand' in field 'PCBrand' but if the user chooses 'LAPTOP' then it searches for the 'Brand' in field 'LBrand' and also if they make choice 'PC and LAPTOP' then it searches for 'Brand' in both fields 'PCBrand' and 'LBrand'

To add to this I am trying to find how to also query the 'Online or highstreet' by selecting only records where field 'ONLINEHIGHSTREET' equals 'ONLINE' or 'HIGHSTREET' and if 'HIGHSTREET' then take first 2 letters of 'postcode' and search in field 'postcode_code'

Anyones advice would be very much appreciated

Last edited by mattyp791; Nov 7th, 2009 at 8:33 am.

Similar Threads

mattyp791

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 7th, 2009

-1

Re: complex sql database search using php code

I suggest that you use a relational model for your DB. The Type should have its own table, as should OnlineHighstreet (although this isn't essential). Your main table should then have integers (tinyint) stored under these fields (foreign key on the other tables). This means that your 'dropdowns', should have the following structure:

PHP Syntax (Toggle Plain Text)
<select id="type" name="type">
  <option value="1">PC</option>
  <option value="2">LAPTOP</option>
  <option value="3">PC and LAPTOP</option>
</select>
<select id="type" name="type">
  <option value="1">PC</option>
  <option value="2">LAPTOP</option>
  <option value="3">PC and LAPTOP</option>
</select>

The $_POST['type'] variable will then be either 1, 2 or 3. You then just search for the integer in your table.

Last edited by ardav; Nov 7th, 2009 at 10:19 am.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Nov 7th, 2009

Re: complex sql database search using php code

Hi, many thanks for your prompt response. I understand what you mean and I have a field in the database for both 'type' and also for 'onlinehighstreet' but could i ask which php code i should be writing that will actually request this from the sql database?

here is the code i have used so far in php

$result=$db->query("SELECT PCBrand, LBrand, PCLAPTOP, ONLINEHIGHSTREET, Retailer, Address, Address_2, Town, County, Postcode, Postcode_code, Phone, Website FROM
database WHERE PCLAPTOP LIKE 'type' AND PCBrand LIKE '%$searchterm%'");

however obviously the above will only currently search field PCBrand whereas that needs to be a variable based upon the answer to type

Thanks

Last edited by mattyp791; Nov 7th, 2009 at 1:11 pm.

mattyp791

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 7th, 2009

Re: complex sql database search using php code

Process your $_POST and do some switch function to see what type the user selected and set your $search_type

PHP Syntax (Toggle Plain Text)
$result=$db->query("SELECT PCBrand, LBrand, PCLAPTOP, ONLINEHIGHSTREET, Retailer, Address, Address_2, Town, County, Postcode, Postcode_code, Phone, Website FROM
database WHERE PCLAPTOP LIKE 'type' AND " .$search_type. " LIKE '%$searchterm%'");
$result=$db->query("SELECT PCBrand, LBrand, PCLAPTOP, ONLINEHIGHSTREET, Retailer, Address, Address_2, Town, County, Postcode, Postcode_code, Phone, Website FROM
database WHERE PCLAPTOP LIKE 'type' AND " .$search_type. " LIKE '%$searchterm%'");

If this is wrong i might have understood your question wrong.

Last edited by ax8l; Nov 7th, 2009 at 3:17 pm. Reason: wrong answear

ax8l

Reputation Points: 10

Solved Threads: 2

Newbie Poster

Offline

10 posts
since May 2009

Permalink

Nov 8th, 2009

Re: complex sql database search using php code

Click to Expand / Collapse Quote originally posted by ax8l ...

Process your $_POST and do some switch function to see what type the user selected and set your $search_type
PHP Syntax (Toggle Plain Text)
$result=$db->query("SELECT PCBrand, LBrand, PCLAPTOP, ONLINEHIGHSTREET, Retailer, Address, Address_2, Town, County, Postcode, Postcode_code, Phone, Website FROM
database WHERE PCLAPTOP LIKE 'type' AND " .$search_type. " LIKE '%$searchterm%'");
$result=$db->query("SELECT PCBrand, LBrand, PCLAPTOP, ONLINEHIGHSTREET, Retailer, Address, Address_2, Town, County, Postcode, Postcode_code, Phone, Website FROM database WHERE PCLAPTOP LIKE 'type' AND " .$search_type. " LIKE '%$searchterm%'");
If this is wrong i might have understood your question wrong.

Hi, actually i think you are spot on - thanks. I wasn't aware of switch functions but that seems to be what i'm after.

I am going to try to work it out - so far i have tried the following code but it is nt working.

$result=$db->query("SELECT PCBrand, LBrand, PCLAPTOP, ONLINEHIGHSTREET, Retailer, Address, Address_2, Town, County, Postcode, Postcode_code, Phone, Website FROM
database WHERE PCLAPTOP LIKE 'type' AND switch ($type){
case "LAPTOP":
echo "LBrand";
break;
case "PC":
echo "PCBrand";
break;
case "both":
echo "PCLAPTOP";
break;
}
LIKE '%$searchterm%'");

I think your suggestion is to create a search_type that takes type and changes to names of fields - is this what you mean? I am going to try and do that - thanks for you help - much appreciated

Last edited by mattyp791; Nov 8th, 2009 at 9:45 am.

mattyp791

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 8th, 2009

Re: complex sql database search using php code

I am trying to set up the switch function but i have something wrong with my code.

$search_type=$db->escapeString($_GET['type']);
switch ($type){
case "PC":
echo "PCBrand";
break;
case "LAPTOP":
echo "LBrand";
break;
case "PC and LAPTOP":
echo "PCLBrand";
break;
}

The issue is that the search ends up looking for a field in database called the value of type so i don't think the switch is set up incorrectly - please could someone take a look. Should I be putting this code within the html page that has the form or is it fine as it is in the php process search page?

mattyp791

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 8th, 2009

-1

Re: complex sql database search using php code

It seems to me that you have a bit of an odd DB structure (sorry no offence intended).

Models
id (PK)
manufacturer (FK on manufacturer id)
name (e.g. Inspiron 1520)
description (blurb for model)
type (PC/Laptop)

Manufacturers
id (PK)
manufacturer_name

Retailers
id (PK)
retailer_name
onlineH
address_1
address_2
post_code
tel
fax
www
email
map_url

Stock
id (PK)
retailer_id (FK on retailer id)
model_id (FK on model id)

This structure should allow easy additions to each table.
SQL queries will require INNER JOINs.

I don't know if this helps.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

Permalink

Nov 9th, 2009

Re: complex sql database search using php code

Hi, no offence taken - i'm fairly new to all this so am greatful to any advise.

In reply to your comments, i can change the db format but at the moment I have one row for each retailer store and for the manaufactures i put them all into one field. If I have one retailer store for every invidicual brand i will end up with a huge database though?

mattyp791

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 9th, 2009

Re: complex sql database search using php code

Click to Expand / Collapse Quote originally posted by panditjee.com ...

k. that code is working fine.

which code?

mattyp791

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 9th, 2009

-1

Re: complex sql database search using php code

Click to Expand / Collapse Quote originally posted by mattyp791 ...

Hi, no offence taken - i'm fairly new to all this so am greatful to any advise.

In reply to your comments, i can change the db format but at the moment I have one row for each retailer store and for the manaufactures i put them all into one field. If I have one retailer store for every invidicual brand i will end up with a huge database though?

No you won't - DBs can hold millions of records. At the moment you seem to be replicating data - which is a bit of a no-no with regard to DB design. Creating a 'relational model' will avoid errors, although it may look more complicated.

The the 'stock' table, to my mind, is essential, even though it seems as though it will be massive. However, if ALL stores/retailers stock exactly the same (ALL) models, you could dispense with this table.

If you use one table for everything, you are storing duplicate addresses, brand names etc etc. This setup invites errors. If a simple typo is made, your resultset will not list all records you expect.

Last edited by ardav; Nov 9th, 2009 at 10:02 am.

ardav

Sponsor

Featured Poster

Reputation Points: 1067

Solved Threads: 955

Disgraced Poster

Offline

6,728 posts
since Oct 2006

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: find user visiting topic in my website?

Next Thread in PHP Forum Timeline: PHP email script incomplete for windows and does not send

DaniWeb Message

Cancel Changes

User Name
Password