SQL syntax: passing variable to SQL query

This is my first program and I have done extensive research trying to answer this question and I can not solve the problem:

<?php

    include "connect.php";

    if (!$connection)
      {
      die('Could not connect: ' . mysql_error());
      }

    $submit = $_POST["submit"];

    if ($submit=="Submit") {
      $date = $_POST["date"];
      $name = $_POST["name"];
      $activity = $_POST["activity"];
      $acti开发者_如何转开发vity_level = $_POST["activity_level"];

    $find_role = ("SELECT sales_role 
                     FROM role 
                LEFT JOIN USER on user.role_id = role.id 
                    WHERE user.user = '$name'");
    $find_activity_points = ("SELECT $activity_$role 
                                FROM $activity 
                               WHERE activity_level = '$activity_level'"); 

    $role = mysql_query($find_role);

    $activity_points = mysql_query($find_activity_points);
     if ($activity_points !== false) {
     }
      else {
       echo mysql_error ();
       die;
     }

     $convert_activity_points = array();

    while ($row = mysql_fetch_array($activity_points, MYSQL_ASSOC)) {
      $convert_activity_points[] = $row;
     }



    $set_points = "UPDATE $name SET $activity='$convert_activity_points' WHERE day='$date'";
     mysql_query($set_points);


    } 

    mysql_close($connection);

    ?>

And this is the error message that I get when I submit form.php to update.php: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM dial WHERE activity_level='70'' at line 1

P.S. I know there are SQL injection vulnerabilities, but I am the only person using this program and I am using it locally on my computer. I'm okay with the vulnerabilities for now.

Edit: Changed code (I really appreciate the feedback)

$find_role = "SELECT sales_role FROM role LEFT JOIN USER on user.role_id=role.id WHERE user.user='$name'";

$find_activity_points = "SELECT %s_%s FROM $activity WHERE activity_level='%d'";    

list($role) = mysql_fetch_array(mysql_query($find_role));

 $activity_points = mysql_query(
     sprintf($find_activity_points, //the main string
         $activity, $role, $activity, $activity_level) //the "arguments"
  );    

    if ($activity_points !== false) {
    }
        else {
            echo mysql_error ();
            die;
    }

Updates the table with 0 and doesn't display an error msg. Re SQL injection vulnerabilities, see my original PS statement

$role isn't defined until later in your program, and even then it's a non-scalar value which will ruin your query as well. You're trying to use the variables before they have values. They don't work that way, once that string is set it will contain the values of those variables at the time it was declared.

You probably want to use sprintf. http://php.net/manual/en/function.sprintf.php

  $find_activity_points = "SELECT %s_%s FROM %s WHERE activity_level='%d'"; 

And then

  $activity_points = mysql_query(
     sprintf($find_activity_points, //the main string
         $activity, $role, $activity, $activity_level) //the "arguments"
  );

Of course, you still need to get $role to be a (scalar) value that can be handled properly within the string. One (somewhat unsafe, but quick) way to do this would be:

  list($role) = mysql_fetch_array(mysql_query($find_role));

On this line:

$find_activity_points = ("SELECT $activity_$role FROM $activity WHERE activity_level='$activity_level'"); 

I can't see anywhere in your code that you define the value of $role prior to the execution of the query.

So, assuming that $activity == 'Eating' your SELECT statement may look something like this:

SELECT Eating_$role FROM Eating WHERE activity_level='...'

This may lead to your error as I believe MySQL doesn't allow for $ characters in table/column names.

As Babiker has suggested, you concatenating or using sprintf() can help to resolve some of these various issues.

Edit: Answering your question in the comments.

As other's have mentioned, change your $find_activity_points line to the something similar to the following:

$find_activity_points = ("SELECT " . $activity . "_" . $role . " FROM " . $activity . " WHERE activity_level='" . $activity_level . "'");

One thing to keep in mind, is when you do SQL statement concatenation ("SELECT " . $activity . ") like in my example, you open yourself up SQL Injection attacks.

At the first glance, I think that "maybe" activity_level data type is not string, but number. So you don't need to place '...' in WHERE activity_level = '$activity_level'

Edit: to be sure about what causes problems, I recommend logging, like the example here. Just include the class and log your query in a file (very easy), then examine the query itself.

Concatenating php strings to mysql strings eg:

mysql_query("SELECT * FROM `someTable` WHERE `someValue`='".$someVar'");

• And $_POST["activity_level"] is a string with ' in it.

Solution:

"SELECT `".$activity_$role."` FROM `".$activity."` WHERE `activity_level`='".mysql_real_escape_string($activity_level)."'"