CODEWITHSUNDEEP
phpmysqlrelational-database
Dan
Dan

Reputation: 997

Attaching categories from one table to entries in another MySQL

I have a database which takes user submitted data, the entries from which I want to group under one or several of about 10 categories.

So for example, you add your entry to my site, say its all about your business (a car valeting service), and I offer you the opportunity to categorize your entry in any number of 10 fixed categories (automotive, mobile service, etc), so if a user searches for businesses under the 'automotive' or 'mobile service' category, your business is returned from the query.

So as I have taken from most of the answers on here, to achieve this I have my database with three tables (structure below), one for your business entry, one listing the set categories, and one relational table to which I've added the unique key from the prior two tables.

CREATE TABLE `business` (
`bus_id` INT NOT NULL AUTO_INCREMENT, 
`bus_name` VARCHAR(50) NOT NULL, 
`bus_dscpn` TEXT NOT NULL, 
`bus_url` VARCHAR(255) NOT NULL,
PRIMARY KEY (`bus_id`)
)

CREATE TABLE `categories` (
`category_id` INT NOT NULL AUTO_INCREMENT, 
`category_name` VARCHAR(20) NOT NULL, 
PRIMARY KEY (`category_id`)
)

CREATE TABLE `tbl_works_categories` (
`bus_id` INT NOT NULL, 
`category_id` INT NOT NULL
)

What I cannot figure out for the life of me is, when you select which categories you'd like your business associated with from my form which is processed with PHP, how to actually associate them in the database!

Upvotes: 1

Views: 402

Answers (3)

mikeq
mikeq

Reputation: 815

I assume your form returns the category IDs for each category selected and that those IDs correspond with category_id in your categories table.

First you insert your business record into your business table, this will give you the auto increment number (mysql_insert_id function in PHP or whatever function for the library you are using). So, you have an $idfromfunction somewhere stored.

With that business ID, then loop through your category IDs

(perhaps already having used):

SELECT category_id, category_name
FROM categories

(... and showed this list to your user through checkboxes or drop list or whatever)

After the user chooses one $categoryidfromform, you can then insert into your tbl_works_categories table

INSERT INTO tbl_works_categories
    (bus_id, category_id)
VALUES
    ( $idfromfunction, $categoryidfromform) ;

Upvotes: 0

Abhay
Abhay

Reputation: 6645

I agree with you that it's simple. Perhaps this will answer your questions.

I assume that your form has check-boxes or a multi-select box to allow the user to select categories for his/her business. And that the "value" attribute of the check-box/select-box is set to the category ID, as stored in the categories table. So, now when the form is submitted, you'll get in the PHP script, along with other inputs from the form, an array with the IDs of the categories selected by the user. Let's call it $_POST['categories']

METHOD 1:

a. Run the query - INSERT INTO business ...

b. read the ID of the last entry made into business table using either mysql_insert_id or mysqli->insert_id, depending on either you are using MySQL or MySQLi library. Suppose you store this ID in $busId

c. From $_POST['categories'], generate a comma-separated string using implode(). Suppose you store this string in $strCategories

d.

INSERT INTO `tbl_works_categories` (`bus_id`, `category_id`)
SELECT $busId, `category_id`
FROM `categories`
WHERE `category_id` IN ($strCategories);

METHOD 2:

Alternatively, you may as well loop through the POST array and generate INSERT query, like:

$q = '';
if (!empty($_POST['categories'])) {
    $q = 'INSERT INTO `tbl_works_categories` (`bus_id`, `category_id`) VALUES ';
    foreach ($_POST['categories'] as $categoryId) {
        $q .= '(' . $busId . ', ' . $categoryId . '),';
    }
}
$q = trim($q, ','); // to remove the trailing comma
if (!empty($q)) {
    // execute query $q
}

Hope the above helps. I haven't tested any of the above codes, so you might need to do some debugging if anything is broken. Please feel free to ask if you've any questions.

Upvotes: 0

Mridul Kashatria
Mridul Kashatria

Reputation: 4187

To get a business data with all categories you can do,

SELECT bus.*, cat.category_name FROM business bus 
    JOIN tbl_works_categories twc USING (bus_id) 
    JOIN categories cat USING (category_id) 
    WHERE bus.bus_id = ?

To retrieve business data from category name, just switch the WHERE param

SELECT bus.*, cat.category_name FROM business bus 
    JOIN tbl_works_categories twc USING (bus_id) 
    JOIN categories cat USING (category_id) 
    WHERE cat.category_name = ?

Upvotes: 1

Related Questions