Php and Mysqli - can you use a prepared statement on a SELECT clause that doesn't have a WHERE clause?

Question

SELECT id, title, posted, duration, thumbnail, email, first_name
FROM customers

While it is not necessary, you can use a prepared statement with this query and many do just for consistency. — Jay Blanchard, Jan 09 '17 at 16:28
Thank you for response Jay. How would I go about creating a prepared statement with the example SQL I posed in my question? — codeman, Jan 09 '17 at 16:31
It depends on which API (MySQLi, PDO) you choose to use. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 09 '17 at 16:32
you can't bind a table/column if that's what you're wondering/asking. — Funk Forty Niner, Jan 09 '17 at 16:34
Possible duplicate of [Can I parameterize the table name in a prepared statement?](http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) — Funk Forty Niner, Jan 09 '17 at 16:37
Yes, but as you have no parameters to bind why not use `mysqli_query($sql);` — RiggsFolly, Jan 09 '17 at 16:37
I had looked at many examples online and in books. None of them show how to execute a prepared statement without a WHERE clause. Im asking if you would be kind enough to show me an example using the sample SQL I posed in my question. I'm a newbie coder and the concept of how to use a prepared statement on this sort of SQL eludes me. — codeman, Jan 09 '17 at 16:39
tell us, is this what you want to do? `SELECT ? FROM ?` type of thing? or `SELECT cols FROM ?` @codeman — Funk Forty Niner, Jan 09 '17 at 16:39
You can `prepare` any valid SQL statement. But it is only necessary to `prepare` it if 1) you want to run it many times. or 2) You want to bind parameters to placeholders i.e. `SELECT * from table WHERE id = ?` — RiggsFolly, Jan 09 '17 at 16:42
Hello Fred thanks for response. I am trying to use a prepared statement on the sample SQL I posed in my question. Im not sure what is the correct coarse of action since there is no WHERE clause. I couldn't find any examples like the one i posed in my question. — codeman, Jan 09 '17 at 16:44
@codeman welcome. Now, there's nothing stopping your from using `prepare()` since it is valid, but you just can't do `SELECT col1, col2, col3 FROM ?` or `SELECT ?,?,? FROM ?` if that is also what you may be envisioning to do/use. There's no use or added benefit in using `prepare()` really, you can just use `query()`. — Funk Forty Niner, Jan 09 '17 at 16:45

Jay Blanchard · Answer 1 · 2017-01-09T16:47:54.020

0

Here is a brief example (too long for comments) since you're using MySQLi:

Object oriented style:

/* prepare query */
$stmt = $mysqli->prepare("SELECT id, title, posted, duration, thumbnail, email, first_name FROM customers");

/* execute query */
$stmt->execute();

Procedural style (where $conn is the database connection):

/* prepare */
$stmt = mysqli_prepare($conn, "SELECT id, title, posted, duration, thumbnail, email, first_name FROM customers");

/* execute query */
mysqli_stmt_execute($stmt);

edited Jan 09 '17 at 16:47

answered Jan 09 '17 at 16:45

Jay Blanchard

34,243
16
77
119

Thank you Jay. So in this kind of SQL query, I wouldn't use question marks as place holders? – codeman Jan 09 '17 at 16:49
You're right @RiggsFolly, and there are other methods. I send all of my queries to a function, so `prepare()` is just part of the architecture - every query is handled by the same function. – Jay Blanchard Jan 09 '17 at 16:49
Thank you so much Jay and Riggs for your quick response and insight much appreciated! – codeman Jan 09 '17 at 16:51
I knew I was right from the moment this question was posted lol – Funk Forty Niner Jan 09 '17 at 16:52
@codeman *"I wouldn't use question marks as place holders?"* - as I said a few times already and the answer is "no" ;-) – Funk Forty Niner Jan 09 '17 at 16:53

score 0 · Answer 2 · answered Jan 09 '17 at 16:54

As you have no obvious reason to prepare this statement i.e. you dont say you want to run this query many times within this script and you have no placeholders for parameters to be bound to the query you can simply use mysqli_query() like this

$sql = 'SELECT id, title, posted, duration, thumbnail, email, first_name
        FROM customers';

$result = mysqli_query($con, $sql);

while ( $row = $result->fetch_assoc() ) {
    echo $row['id'];   // or whatever
}

Please remember that you cannot use placeholders ? for column names or table names, in this or in a mysqli_prepare() if that is what you were ultimately working up to, as @Fred-ii- has been suggesting

score 0 · Answer 3 · edited May 23 '17 at 11:46

Posting as a community wiki.

There's nothing stopping you from using prepare() (in MySQLi_), since it is valid.

You just can't do, and as a few examples.:

SELECT col1, col2, col3 FROM ?
SELECT ?,?,? FROM ?
SELECT ?,?,? FROM table

If you plan / envision on doing this, since that would constitute as binding a table/column which isn't allowed in prepared statements, as much as we'd like it to work as.

However, there is nothing stopping you from using what is called a "safelist".

Here are a few references:

In reference to using prepare() over just query() can be found in the following answer for the related question:

MySQL prepared statement vs normal query. Gains & Losses

Php and Mysqli - can you use a prepared statement on a SELECT clause that doesn't have a WHERE clause?

3 Answers3