title: Mysqli Prepared statement with a mysql variable
body: <p>I have a sql query that returns 5 closest locations using latitude &amp; longitude. </p>
  
  <pre><code>$conn-&gt;query("SELECT activityId, ( 3959 * acos( cos( radians(
   -0.0761144) ) * cos( radians(
    activity.latitude ) ) * cos( radians(
    activity.longitude ) - radians(
  
    51.5080937) ) + sin( radians(
    -0.0761144) ) * sin( radians(
      activity.latitude ) ) ) ) AS distance
  from activity order by distance limit 5;");
  </code></pre>
  
  <p>I need to make this a prepared statement, but to use the ? placeholder doesn't make sense as it would mean binding the same param twice. Oh and it doesn't actually work.</p>
  
  <pre><code>$stmt = $conn-&gt;prepare("SELECT activityId, ( 3959 * acos( cos( radians(
   ?) ) * cos( radians(
    activity.latitude ) ) * cos( radians(
    activity.longitude ) - radians(
  
    ?) ) + sin( radians(
    ?) ) * sin( radians(
      activity.latitude ) ) ) ) AS distance
  from activity order by distance limit 5;");
  
  $stmt = $conn-&gt;prepare("ddd", $lat, $lng, $lat);
  </code></pre>
  
  <p>So I've done a little research and from what I can gather using a variable in the mysql statement seems the best approach, but this is where i get stuck, so far I have as follows. </p>
  
  <pre><code>$stmt = $conn-&gt;prepare("
  
  set @lat = ?;
  set @lng = ?;    
  
  SELECT activityId, ( 3959 * acos( cos( radians(
   @lat) ) * cos( radians(
    activity.latitude ) ) * cos( radians(
    activity.longitude ) - radians(
  
    @lng) ) + sin( radians(
    @lat) ) * sin( radians(
      activity.latitude ) ) ) ) AS distance
  from activity order by distance limit 5;
  
  ");
  
  $stmt-&gt;bind_param("dd", $lat, $lng");
  </code></pre>
  
  <p>However now the params don't bind and I get a error response back stating this. I've had a look on google and the answer I have been directed towards consists of running two queries, the font creating the variables as just a query() then then SELECT statement in a second prepared statement. But surely then the statements are not being prepared and leaves the query open to SQL Injection? </p>
  
  <p>Is there a better way to prepare this statement so it's secure from injection attacks? </p>
  
  <p>//* EDIT *//</p>
  
  <p>After some great answers it seems that using set @var isn't valid in $sql->prepare(); I guess I can now refine the question to be; How can I run this as a prepared statement?:</p>
  
  <pre><code>$stmt = $conn-&gt;prepare("SELECT activityId, ( 3959 * acos( cos( radians(
  ?) ) * cos( radians(
  activity.latitude ) ) * cos( radians(
  activity.longitude ) - radians(
  
  ?) ) + sin( radians(
  ?) ) * sin( radians(
  activity.latitude ) ) ) ) AS distance
  from activity order by distance limit 5;");
  
  $stmt = $conn-&gt;prepare("ddd", $lat, $lng, $lat);
  </code></pre>