query

dreamer

I have a little problem getting a query to return the data I want.
I have three tables, one with a column for each category in the menu on the web-page. For instance "Go shoping". The second table contains company-information from an official record. And the third contains specific information about some of the firms and those firms are to be displayed as links, thats why it's there.

The "categories"-table is used to gather what sort of business-categories should be extracted from "offreg.business"

As my query is now, it seems like it's extracting data based on ALL the words in "categories.$cat" or in "offreg.business". I want the result to be just the exact match of the whole sentence in offreg.business.

Can anyone see what's wrong here?

$cat = "Go shoping"

SELECT `categories`.`$cat` , `offreg`.`business`, `offreg`.`city`,`offreg`.`companyname`, `offreg`.`orgnum`, `details`.`orgnum`
FROM `offreg`
LEFT JOIN `details` ON `offreg`.`orgnum` = `details`.`orgnum`
LEFT JOIN `categories` ON `offreg`.`business` = `categories`.`$cat`
WHERE `categories`.`$cat` = `offreg`.`business` AND `offreg`.`city` = 'City1' OR `offreg`.`city` = `City2`OR `offreg`.`city` = 'City3'
ORDER BY `companyname`

Lets hope I didn't translate anything wrong here in the query, english isn't my mothertounge =)

Bunkermaster

In your code

$cat = "Go shoping"

SELECT `categories`.`$cat` , `offreg`.`business`, `offreg`.`city`,`offreg`.`companyname`, `offreg`.`orgnum`, `details`.`orgnum`
FROM `offreg`
LEFT JOIN `details` ON `offreg`.`orgnum` = `details`.`orgnum`
LEFT JOIN `categories` ON `offreg`.`business` = `categories`.`$cat`
WHERE `categories`.`$cat` = `offreg`.`business` AND `offreg`.`city` = 'City1' OR `offreg`.`city` = `City2`OR `offreg`.`city` = 'City3'
ORDER BY `companyname`

1. Do you have a field named Go shoping?
2. WHy create a join AND a where clause on categories.$cat = offreg.business?
3. Put a space before OR offreg.city = 'City3'
4. Add a where clause on categories.cat= '$cat'

Hope it helps

dreamer

Originally posted by Bunkermaster
1. Do you have a field named Go shoping?
2. WHy create a join AND a where clause on categories.$cat = offreg.business?
3. Put a space before OR offreg.city = 'City3'
4. Add a where clause on categories.cat= '$cat'

Hope it helps [/B]

No the column-name is "Go shoping".
Lets say the "Go shoping"-column contains these row's:
Consumer electronics
Office products
Furniture

If "Go shoping" is clicked I want to display all the companies from offreg where offreg.business = "Consumer electronics" OR "Office products" OR "Furniture".

Thats probably just my lack of sql-knowledge 🙂
Thats just a typo here in the post.
Do you mean categories.$cat = $cat

Hope this makes things a little clearer

Bunkermaster

can you paste your table definition so we can have a look at the table name and the fileds available?

Is your $cat a field or is it a variable? If a variable I am curious to see your table definition.

Don't worry you are in the right forum, you have many gurus around here 🙂 (I am a spam guru)

dreamer

Originally posted by Bunkermaster
can you paste your table definition so we can have a look at the table name and the fileds available?

Is your $cat a field or is it a variable? If a variable I am curious to see your table definition.

Don't worry you are in the right forum, you have many gurus around here 🙂 (I am a spam guru)

Table defenitions? If I export the table using phpmyadmin I get this, is that what you where looking for?

CREATE TABLE categories (
  Dining varchar(100) NULL default '',
  Go shoping varchar(100) NULL default '',
  Things to do varchar(100) NULL default ''
) TYPE=MyISAM;

INSERT INTO categories VALUES ('Hotels and resturants', 'Sports equipment', 'Cinema');
INSERT INTO categories VALUES ('Motels with diners', 'Communication-equippment', 'Theme parks');
INSERT INTO categories VALUES ('', 'Books and paper', 'Sports arenas');

The table isn't identical to the one I use, it's just a bad translation, but the idea is the same.

$cat is a variable defined by the menu. The link for "Go shopping" is for instance "index.php?cat=Go shoping".
I know I shouldn't do it that way, but I just need to get things to work, I'll deal with perfecting the code later 🙂

When it comes to the "categories"-table I add the columns manually based on what's in the menu.

You ask alot about the "field", lets just make sure that our terminology matches. The field is where columns and rows intersept?

Bunkermaster

you have space in your field names? :eek:

http://www.mysql.com/doc/en/Legal_names.html <-- read this

dreamer

Originally posted by Bunkermaster
you have space in your field names? :eek:

http://www.mysql.com/doc/en/Legal_names.html <-- read this

Damn, I was afraid it could be something like that. But I figured since I were able to create them, it was ok.
Maby this is why the phrase RTFM was created :p
I think I'll just crawl into a dark corner and sit there for a while...

But put aside my little screw-up, is my way of thinking good enough? Or is there any changes I should make on the tables?

Bunkermaster

well I fail to see the logic in your table structure. Perhaps you could explain. 🙂