Page 1 of 1

Need some help creating a query

Posted: Mon Jun 04, 2007 10:24 am
by roc1479
Hi,

I'm currently trying to create a query to do a lookup based on key words and location.

I have the following fields in my database:
`cust_db`.`id`,
`cust_db`.`comp_name`,
`cust_db`.`comp_url`,
`cust_db`.`comp_logo`,
`cust_db`.`state`,
`cust_db`.`products`,

I want to create a form to do a lookup by keyword or by state. I've done that part making the location a drop down box, however, I don't really know where to begin in creating the php search query.

I've made the products field a full text field and entered data such as: lcd, laptop, desktop etc into multiple rows.

I want to be able to search by keyword or state, there if keyword is left blank, it'll pull up the result based on location and vice versa.

I would like to display the results in the following order:

comp_logo
comp_name
comp_url
products
state

for each result.

Can someone point me in the right direction?

Thanks in advance.

Posted: Mon Jun 04, 2007 12:59 pm
by superdezign
Have you written any code yet? Sometimes, it's easier to organize your thoughts by writing.

And what part of that do you need help with? You've asked for quite a few different operations.

Re: Need some help creating a query

Posted: Mon Jun 04, 2007 1:36 pm
by bdlang
roc1479 wrote:
I have the following fields in my database:
`cust_db`.`id`,
`cust_db`.`comp_name`,
`cust_db`.`comp_url`,
`cust_db`.`comp_logo`,
`cust_db`.`state`,
`cust_db`.`products`,
First thing I'd recommend is refactoring and creating at least two new tables, `state` and `product`, each with a PRIMARY KEY `id` field and a data field to store the state and product respectively. In fact, the `state` table is easy; you can create some code to auto-populate that table with all 50 states (and Puerto Rico, etc if you want) and then make sure the `state` field has a UNIQUE index and you're done. The `cust_db`.`state` column stores the PK value from this table and there's no redundant data, and lookups are extremely fast with the index.
roc1479 wrote: I want to create a form to do a lookup by keyword or by state. I've done that part making the location a drop down box, however, I don't really know where to begin in creating the php search query.
You're only allowing a single state and / or product to be searched at one time? Assuming you're using PHP and a POST type form, use your $_POST data to fashion an SQL statement and send it to the database.
roc1479 wrote: I've made the products field a full text field and entered data such as: lcd, laptop, desktop etc into multiple rows.
Bad idea. Breaks the relational model. As I mentioned above, refactor the products out into their own table; if you have a single product per `cust_db` record, then store the `product` PK value in the `cust_db`.`productID` column (or whatever you want to name it). If you have multiple products per `cust_db` record, then create another table, let's say `cust_products` that holds the `cust_db`.`id` value in relation to a `product`.`id` value. You have a many to many relationship and perform a JOIN on the three tables.
roc1479 wrote: I would like to display the results in the following order:

comp_logo
comp_name
comp_url
products
state

for each result.
The order is really irrelevant. If you're retrieving a resultset to display in PHP or another language, the script determines where and how the data is displayed.

Posted: Mon Jun 04, 2007 2:18 pm
by roc1479
Hey thanks for the response guys. However, I forgot to mention that I'm a newbie, Although i do understand the theory about having multiple tables.

I've create two addional tables, state and cust_products. State has two fields, id (PK) and name which is unique. Cust_products also has 2 fields, id and item.

I'm stuck at this point.

If i had 2 search fields, item and state, how would I write a query to pull up the data?

SELECT * FROM cust_db, cust_products, state WHERE ?

Posted: Mon Jun 04, 2007 3:44 pm
by bdlang
Well, if you've created two new tables, then the id values from those tables are now stored in columns in your `cust_db` table, correct?

`cust_db`.`state` --> `state`.`id`
`cust_db`.`product` --> `cust_products`.`id`

You need to perform a JOIN on those three tables, pulling data down from each, something like

Code: Select all

SELECT
  cust_db.comp_logo
, cust_db.comp_name
, cust_db.comp_url
, cust_products.item
, state.name
FROM cust_db
INNER JOIN state ON (cust_db.state = state.id)
INNER JOIN cust_products ON (cust_db.product = cust_products.id)
WHERE cust_products.item LIKE '%SEARCHTERM%'
AND state.id = STATE_ID
;
You substitute SEARCHTERM above of course to match the item your searchterm, and I'm assuming your state drop-down matches key:value pairs so your STATE_ID variable is actually the state id value pulled from POST data in your form, not the state name.

Please keep in mind this is just an example of how to perform a join with these tables, YMMV. You may not want all the columns I've indicated, and you may want more.

Posted: Tue Jun 05, 2007 8:15 am
by roc1479
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hey bdlang,

Thanks very much for pointing me in the right direction.  I've redone my tables:

customer --> custID(int 11 pk), custNAME(varchar 50)

product --> productID(int 11 pk), item, custID(int 11 -- Stores the custID from the customer table)

state --> stateID(int 11 pk), state, custID(int 11 -- Stores the custID from the customer table) 
I also gave the state field a unique index.

I used the following query:

[syntax="sql"]SELECT
`customer`.`custNAME`,
`product`.`item`,
`state`.`stateNAME`
FROM
`customer`
Inner Join `product` ON `customer`.`custID` = `product`.`custID`
Inner Join `state` ON `customer`.`custID` = `state`.`custID`
WHERE
`product`.`item` LIKE'%$_POST[item]%' OR
`state`.`stateNAME` = '$_POST[state]' ");
This query seems to work, however, what if I wanted to pull up multiple products that belongs to a particular company? Eg. Let say a search is ran for lcd, i would like it to pull up the companies that offers lcd as a product, as well as any additional products they offer.

How can I implement validation to this query. I don't want users to just click on search and have it pull up everyhing. I'd like to do some checks on the initial submit to make sure a search was actually entered.

If something looks wrong, please do advise me because I'm trying to learn how to do this as we go along.

Thanks in advance.


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Jun 05, 2007 1:57 pm
by bdlang
roc1479 wrote: This query seems to work, however, what if I wanted to pull up multiple products that belongs to a particular company? Eg. Let say a search is ran for lcd, i would like it to pull up the companies that offers lcd as a product, as well as any additional products they offer.
It's your design, not the query. Keep reading. ;)
roc1479 wrote: customer --> custID(int 11 pk), custNAME(varchar 50)

product --> productID(int 11 pk), item, custID(int 11 -- Stores the custID from the customer table)

state --> stateID(int 11 pk), state, custID(int 11 -- Stores the custID from the customer table)
Ok, let's start with the table makeup. Your `customer` table should store one thing, customer data, correct? The `state` table should only concern itself with a list of states matched to a PK value.

So in going along with this, each customer (we'll assume) will have one state related to it, i.e. they are from one state only. In this case, you want the customer's state ID value stored in the customer table, not the other way around. Your table schema assumes there will be only one customer per state. Even if you had a customer with branches in multiple states, that still becomes another `customer` record, not another `state` record.

In regards to the `product` table, it too, should only store product related data matched to a PK value. I suppose it depends on the way you think of it; think of the customer -> product relation. Does a single customer only ever relate to one product? In other words, does 'Jims Electronics' have a monopoly on LCD displays? I doubt it. It's likely that Jim has some competition and there are a dozen companies that sell / service / manufacture LCD displays. Therefore LCD should be a single record in the `product` table, and yet another table, `customer_products` link the two. I make mention of this in my first post.

Maybe some data will help illustrate. Here's some sample records in the `product` table:
( PK productID | item )

Code: Select all

1 | LCD display
2 | laptop
3 | desktop
Here's a couple of records in the `customer` table:
( PK custID | custName | custState )

Code: Select all

1 | Jim's Electronics | 1
2 | Bob's Computer Emporium | 13
3 | Silvio's Stellar Service Co | 43
We can guess that the `custState` values map to `state`.`id` keys, further mapping to state names.

Ok, so we have a `customer` table keeping track of customer data, and a `product` table keeping track if products.
Now, the fourth table, `customer_products` make the link:
( FK custID | FK productID )

Code: Select all

1 | 1
2 | 1
2 | 2
2 | 3
3 | 3
So, Jim only carries LCD displays, while Bob carries all three products, and Silvio only deals with desktops. Many to many relationship. When you do a query, you'll reference this table to match keys on, and the other tables provide the data.

Absorb that and if you need help with a query let me know.