MySQL / PHP query
I have 3 groups of fields (each group consists of 2 fields) that I have to check against some condition. I don't check every field, but some combination, for example:
- group priceEurBus, priceLocalBus
- group priceEurAvio, priceLocalAvio
- group priceEurSelf, priceLocalSelf
My example (formatted for readability) - how can this be improved?
$rest .="
WHERE
(
((priceEurBus+(priceLocalBus / ".$ObrKursQuery.")) <= 400)
OR
((priceEurAvio+(priceLocalAvio / ".$ObrKursQuery.")) <= 400)
OR
((priceEurSelf+(priceLocalSelf / ".$ObrKursQuery.")) <= 400)
)
";
$ObrKursQuery
is the value I use to convert local currency to Euro.
Productivity increase. Your query is OR-based, which means it will stop evaluating conditions as soon as it finds that one of them is true. Try to order your conditions in such a way that, for example, in your case, the first check is most likely below 400.
Security: Use prepared statements and filter your variables before using them. In the case of $ ObrKursQuery, if it comes from user input or an untrusted source, it is a non-cyclable numeric value and you are exposed to a wide variety of sql injection problems (including SQL arithmetic injection: if this value is 0, you will get a divideByZero error, which can be used as a condition for blind sql injection).
Reading Obligation: Be sure to follow the coding guidelines and, if possible, follow some de facto standard standards, for example, start variable names in lowercase: $ ObrKursQuery → $ obrKursQuery. Also for the sake of documenting your own code, choose variable names that mean they are: $ ObrKursQuery → $ conversionRatio.
Performance / Scalability Improvements: Use a constant instead of a fixed value for 400. When you change this value in the future, you only want to change it in one place, not in the whole code.
a source to share
Never use concatenation to generate SQL, you should use prepared SQL statements with parameters.
The only way to simplify this operator without having a lot of knowledge of the problem area is to reduce the number of columns. It looks like you have three prices per item. You could create a table of product prices instead of product price columns, and that would make that single comparison and provide the flexibility to generate even more product prices in the future.
So, you need to create one-> many relationships between product and prices.
a source to share