complex sql database search using php code

[mattyp791]

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

[diafol]

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:

<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.

[mattyp791]

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

[ax8l]

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

$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.

[mattyp791]

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

$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

[mattyp791]

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?

[diafol]

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.

[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?

[mattyp791]

k. that code is working fine.

which code?

[diafol]

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.