limit a company to search only coupons for that particular company
I have a php/ajax search whereby once the company representative is logged in, they can search for one of their coupons instead of having to scroll to find them.
When they search, the results should only display coupons assigned to their company but at the moment they can search any coupon in the database. This is obviously a problem. It would be pretty hard for them to guess but still, I don't like to leave things to chance. This would be easy if I had the company ID in the coupon database table but I don't. I only have the product ID in the coupon table.
The most obvious solution would be to add the company ID to the coupon table and then in my SQL statement say WHERE `company_id` = ? and then ? would be a Session variable with the value of their company ID when they logged into their account.
But I wanted to know if there was another suggestion without having to alter the database?
When they search, the results should only display coupons assigned to their company but at the moment they can search any coupon in the database. This is obviously a problem. It would be pretty hard for them to guess but still, I don't like to leave things to chance. This would be easy if I had the company ID in the coupon database table but I don't. I only have the product ID in the coupon table.
The most obvious solution would be to add the company ID to the coupon table and then in my SQL statement say WHERE `company_id` = ? and then ? would be a Session variable with the value of their company ID when they logged into their account.
But I wanted to know if there was another suggestion without having to alter the database?
You will have to change something about the database, but you might not need to alter the coupons table. You might be able to add a table of company IDs and use a junction table to connect the company IDs to the coupons.
ASKER
@ Peos John,
Not sure I understand your question. The coupon table has the product ID stored in it and the products table has the product ID and company ID. Then there is a company table which has the company ID.
@ Ray,
I have a table for companies which has company ID and company name etc but I don't know how to join them if the company ID isn't in the coupon table. If it was I wouldn't even need to think about a join and I could have done something like:
But now there is no connection between the company table and the coupon table. The product ID doesn't actually help because it isn't in the company table, only in the coupon and products table.
Not sure I understand your question. The coupon table has the product ID stored in it and the products table has the product ID and company ID. Then there is a company table which has the company ID.
@ Ray,
I have a table for companies which has company ID and company name etc but I don't know how to join them if the company ID isn't in the coupon table. If it was I wouldn't even need to think about a join and I could have done something like:
$stmt = $link->prepare(SELECT `coupon` FROM `coupons` WHERE `companyID` = ?);
$stmt->bind_param("i", $_SESSION['companyID'];But now there is no connection between the company table and the coupon table. The product ID doesn't actually help because it isn't in the company table, only in the coupon and products table.
ASKER
I think I managed to do it but I have to join 3 tables which seems like a real long way around!
I basically inner joined the coupon table to the product table ON product ID from products table = product id from coupon table inner join company table ON company id from company table = company id from products table WHERE company ID = SESSION ID of logged in company user.
It seems to be showing me the correct info. Would it be bad to just add the company ID as an extra column in the coupons table?
I basically inner joined the coupon table to the product table ON product ID from products table = product id from coupon table inner join company table ON company id from company table = company id from products table WHERE company ID = SESSION ID of logged in company user.
It seems to be showing me the correct info. Would it be bad to just add the company ID as an extra column in the coupons table?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How do you assign the coupons to the company?