A SELECT query I can't get run as I want

Marco Gasi
Marco Gasi used Ask the Experts™
on
He everybody.
To make a long story short, I have a product table with among other columns has prod_code, short_description and description. Now I have to select the prod_code where short_description or description are equal to a given value. I run this query in a loop where the given value changes depending on values of another table.

This is the query:
$sql = "SELECT prod_code FROM products WHERE short_description=\"" . addslashes( $value ) . "\" or description=\"" . addslashes( $value ) . "\"";

Open in new window

Once I have the prod_code I have to run a second query to update another table where I want to put the prod_code in the record which should correspond to the value used in the first query.
But since there are at least 2 products with an identical description (don't ask me why: clients are always right), this second query result wrong:

UPDATE es SET prod_code='SOLARGREEN' WHERE id='1118' is right short_description

UPDATE es SET prod_code='SOLARGREEN' WHERE id='1119' is right description

UPDATE es SET prod_code='SOLARBL' WHERE id='1120' is right short_description

UPDATE es SET prod_code='SOLARGREEN' WHERE id='1121' is wrong: this is the secon description identical to the one above and the prod code should be SOLARBL

I'm not sure if this makes sense or if it's quite cryptic: I tried to be as concise as possible.
If you need more info, I'll be happy to tell you everything you need.

Any idea about I can change the first query?


The problem is that there is at least 2 products which have an identical description so I end to have that
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
I'm not entirely sure what you need, but if the data integrity is screwed (you have two identical descriptions, for example) then you're always likely to have to compromise.

Don't really understand how you get to the UPDATE queries - what's the relevance of the ID - how do you know ID=1121 should be SOLARBL and not SOLARGREEN

If you want to SELECT based on a clause that returns more than one record, or you want to UPDATE a single record but have no way of uniquely identifying that record, then you'll have to decide which record is the correct one. How you do this will vary depending on your software requirements, but you're unlikely to ever get this right 100% of the time. Adding LIMIT 1 to the end of your queries will limit the query to affecting a single record, but you'll have no consistent way of knowing which one.

Maybe more info will help us to help you
Marco GasiFreelancer
Top Expert 2010
Commented:
Hi Chris. Happy to hear you.
I tried to add LIMIT 1, but with no success. Ok, I'll try to explain everything in the more concise way I can.
Anyway, working on your suggestion I realized it was simpler rebuild teh tables. The task in fact was needed only in development: in production that query is not needed.
Thank you.
Marco GasiFreelancer
Top Expert 2010

Author

Commented:
Solved rebuilding table

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial