SELECT * in prepared statements

[MFA]

How does one go about converting an SQL query such as:

 

SELECT * FROM members WHERE userid='$userid' AND code=$code'

 

into a prepared statement for PHP. I understand how to use prepared statements however I'm having trouble with the * part of the query and I'd rather not have to write out all the table coulmns because I have about 20.

 

EDIT: I'm using mysqli procedural

Edited March 16, 2013 by MFA

[requinix]

Do you actually need every single column? Generally you'll only need a handful so you should be listing them out.

 

But that has nothing to do with prepared statements.

SELECT * FROM members WHERE userid = ? AND code = ?

The parameters are only for actual values in the query, not names or syntax.

[Failing_Solutions]

Using * will get every column. If you are trying to limit overhead then you would write out each colum you need.

 

Example

Select user_id, user_name from members...

[MFA]

 

Do you actually need every single column? Generally you'll only need a handful so you should be listing them out.

 

But that has nothing to do with prepared statements.

SELECT * FROM members WHERE userid = ? AND code = ?

The parameters are only for actual values in the query, not names or syntax.

 

I was just going to type "Yes, I know but then how can i bind the result for each column without having to type out a new variable for each one. Is there not a similar function to mysqli_fetch_assoc for prepared statements?" BUT then it hit me that it is exactly the same thing as using mysqli_fetch_assoc still requires me to write out the column name anyway!

 

 If you are trying to limit overhead then you would write out each colum you need.

 

What is limit overhead?

 

 

One final question about prepared statements, will the example below work or must all paramaters be replaced by a '?' - The solution would obviousluy be to create a variable to replace '1' but I'm wondering if this is unnecessary?

 

"SELECT email,password FROM members WHERE email = ? AND password = ? AND validate=1"

[kicken]

What is limit overhead?

If you select fields that you are not using, then it requires a bunch of extra processing and memory that may not be necessary if you limit the selection to only fields you need. The more you select, the more work mysql has to do in order to locate the data and pull it out of the database initially, then your result set is larger and requires more time and memory to transfer from mysql to your PHP script.

 

So by listing out only the fields you need your scripts would likely work quicker w/ less memory, and having the fields listed also provides some implicit documentation about what fields the script uses and what their names are (so you dont have to reference the db table structure when you work on the script in the future).

 

 

 

One final question about prepared statements, will the example below work or must all paramaters be replaced by a '?'

It will work fine, you can have non-parameterized values along with parameterized values in the same query if you want. Constant values like the 1 in that query should just be left as it is and not converted to a parameter.

[Failing_Solutions]

kicken gave the explanation. Selecing * grabs all the columns. In small applications this may have no consquences however in larger more complex applications you should try to limit your use to gettting exactly what you need, when you need it.