0

I really dislike writing SQL queries within my PHP.

Given the following example piece of PHP:

script.php

$name = 'Bill'
$query = "SELECT * FROM a_table WHERE name='$name'";

I'd like to instead write the sql query in it's own file and include (or otherwise) get the contents into my script:

query.sql

SELECT * FROM a_table WHERE name='$name'

script.php

// This doesn't work obviously
$query = '"'.include('query.sql').'"';

Note that I'd like to be able to reference PHP variables within the SQL query (e.g: $name is setup declared in script.php but used as part of the query).

s-low
  • 706
  • 2
  • 8
  • 21
  • Watch out for SQL injection when writing such queries. See: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Pierre-Loup Pagniez Sep 22 '16 at 08:39
  • @Pierre-LoupPagniez thanks for that link. In my case all user input was being cleaned separately before being used in queries, but the attached question/answer is fantastic – s-low Sep 22 '16 at 09:22

2 Answers2

3

nice and simple solution:

 $sql = file_get_contents("query.sql");

And to reference variables, I suggest using PDO where you reference like this

$query = $db->query( "SELECT x FROM x WHERE x = :example")
$query->execute(array("example"=>$value));

...But you can do something similar to this in mysqli, etc.

Caspar Wylie
  • 2,818
  • 3
  • 18
  • 32
1

query.sql:

SELECT * FROM a_table WHERE name=:name

script.php:

$query = file_get_contents('path/to/query.sql');

Then bind the parameters and execute the query.

See How can I prevent SQL-injection in PHP? for why you should bind variables instead of concatenating them into your query string.

Community
  • 1
  • 1
timclutton
  • 12,682
  • 3
  • 33
  • 43