0

I'm combining 2 pieces of code that has been written by others. The page displays records from a mySql database and the variable I'm trying to use is already being displayed using the following code:

<span class="headLeft"><?php echo cleanData($this->RECIPE->name); ?>:</span>

The sql select statement is trying to use the name field for it's lookup.

SELECT 
  name
  , Round(Sum(i.calories)/1500*100,2) as calories
  , Round(Sum(protein)/525*100,2) as protein
  , Round(Sum(fat)/300*100,2) as fat
  , Round(sum(carbohydrate)/675*100,2) as carbohydrate
  , Round(sum(fiber)/30*100,2) as fiber
  , Round(sum(sugar)/375*100,2) as sugar
  , Round(sum(saturated_fat)/150*100,2) as saturated_fat
  , Round(sum(monounsaturated_fat)/150*2,2) as monsaturated_fat
  , Round(sum(Polyunsaturated_Fat)/150*2,2) as polyunsaturated_fat
  , Round(sum(cholesterol)/200*100,2) as cholesterol
  , Round(sum(sodium)/1300*100,2) as sodium
  FROM `mr_recipes` r 
  left join ingredients i on r.id = i.recipeid

   where name = ($this->RECIPE->name)
group by name

I can't quite get the variable to work. I can't use $name and I've tried wrapping it in single and double quotes.

Cœur
  • 37,241
  • 25
  • 195
  • 267
2pourdrummer
  • 45
  • 1
  • 8
  • please simplify your question and format code in code tags – Rafael Dec 05 '14 at 23:43
  • Sorry, thought it was readable – 2pourdrummer Dec 05 '14 at 23:46
  • you are confusing SQL with php code. You can't define a php variable in mysql. Also you are calling the variable before you declared it. What is all that rounding for? – Rafael Dec 05 '14 at 23:52
  • `SET @calories = Round(Sum(i.calories)/1500*100,2)`; this is how you define a variable in MySQL but you should handle your arithmetic in your php. – Rafael Dec 05 '14 at 23:54
  • I suggest you take a look to this: [Bobby tables: A guide to preventing SQL injection](http://bobby-tables.com). It has a good explanation on how to use prepared statements to pass values to SQL queries... and it's a simple guide on how to prevent *evil* things to happen when you do so – Barranka Dec 05 '14 at 23:54
  • Try to print it first $this->RECIPE->name, then use it in your mysql query – A l w a y s S u n n y Dec 06 '14 at 00:10
  • First, I'm not trying to define the variable in SQL, the variable is already defined. I can print the $this->Recipe-> name and it appears correctly. I just need the syntax for using the php variable in the where clause. Second, I appreciate the SQL injection issue, I will look into changing it. Third, the examples all show using the $variablename in the SQL statement, but it doesn't work. $name is undefined. I'm not familar with all the $this->Recipe->name variable naming, but I somehow need it to pass the already defined variable, but can't get the syntax. – 2pourdrummer Dec 06 '14 at 01:52
  • You have not shown us the php code you are using around the SQL statement. – symcbean Dec 06 '14 at 13:27
  • I figured it out but I don't know if it's best practice. If I create a variable like $name = $this->Recipe->name, then in the where clause of the sql statement do where name = $name, it works. – 2pourdrummer Dec 07 '14 at 16:13

1 Answers1

0

Take a look to prepared statements. To prevent SQL Injections, do not concatenate SQL query and parameters.

You should use PDO it's safer.

Here is a similar issue.

Community
  • 1
  • 1
K4timini
  • 711
  • 2
  • 14
  • 34