PHP: Mysqli prepared statement with "select *"

Question

This is how I presently fetch from the DB:

if ($stmt = $mysqli->prepare ( "SELECT fname,lname from $table_name 
where cno >=? LIMIT 50" ))
    {
        $stmt->bind_param ( "i", $cno); 
        $stmt->execute ();
        $stmt->bind_result ($fname,$lname); 
        $arrayUsers = array();

        while ($stmt->fetch())
        {
            if (isset ($fname))
            {
                $arrayUsers[] = array(
                        "fname" => $fname,
                        "lname" => $lname);

}
}
$stmt->close ();
        }
        $mysqli->close ();

and it works great. But if I change my select to SELECT * from... my bindings fail. Does that mean if I have a large number of fields, I will still have to specify each and every field or is there a way to use select *?

---- updated ---

if (empty($arrayUsers))
    {
        return 0;
    }
    else
    {
        return $array;
    }

I do, it's in the function parameters, i didnt write that here as it just needlessly takes up space — Ryan, Mar 27 '15 at 20:16
When you say bindings are you referring to bind_param or bind_result? bind_result needs to have a variable for each column returned. Make sure it matches the number of columns — Yusuf Moola, Mar 27 '15 at 20:19
I mean how can I use `select *` instead of `select field1,field2` etc — Ryan, Mar 27 '15 at 20:20
@JohnConde - He shouldn't. `SELECT * FROM 'foo'` is not the same as `SELECT * FROM foo`. — Álvaro González, Mar 27 '15 at 20:25
You can always do a `SELECT *`, it is the *conditions* you're binding for. — Jay Blanchard, Mar 27 '15 at 20:25
@JayBlanchard, check my update. If I do a select * I always get the result of zero. — Ryan, Mar 27 '15 at 20:27
You would just not use `bind_result()` in that case, you would use [`get_result()`](http://stackoverflow.com/questions/18753262/example-of-how-to-use-bind-result-vs-get-result) — Jay Blanchard, Mar 27 '15 at 20:30
Aha! That's the answer I was looking for! Thanks! Could you write it in an answer form below? — Ryan, Mar 27 '15 at 20:31

meda · Answer 1 · 2015-03-27T21:56:43.550

2

SELECT * is never a good idea.

It's better to be explicit.

If you had only 2 columns it would have worked.

That's how bind_result works the number variable needs to match the columns.

In addition to that it needs to be in the same order

Edit: Example in pdo:

if ($stmt = $pdo->prepare ("SELECT * from `$table_name` where cno >=:cno LIMIT 50" )){
    $stmt->execute([':cno'=>$cno]);
    $arrayUsers = $stmt->fetchAll();
    $stmt->close();
}
$pdo->close();

edited Mar 27 '15 at 21:56

answered Mar 27 '15 at 20:19

meda

45,103
14
92
122

Thanks for replying! If just have a few columns I don't care, I have some tables with a lot more than 2 or 10 and I'm just curious as to how I can get a `select *` instead of explicitly calling each column. Not sure if its even possible... – Ryan Mar 27 '15 at 20:23
1

Its possible if you know in advance how many column you are fetching and bind all variables. This is dangerous your code will break if you ever add or drop column – meda Mar 27 '15 at 20:29
Voted your answer up. I realize the pitfalls of a `select *` but I just wanted to know how it could be done / if it could be done and Jay answered my question. Thanks again :) – Ryan Mar 27 '15 at 20:33
1

@Ryan yeah thats what you need to achieve it, also PDO can do it easily if you dont mind switching good luck – meda Mar 27 '15 at 20:34
Thanks! Got an example with PDO? – Ryan Mar 27 '15 at 21:43

score 2 · Accepted Answer · answered Mar 27 '15 at 21:00

If you need to perform a selection of all of the columns:

SELECT * FROM `table`

You would use PHP's get_result() rather than bind_result().

bind_result() is better when you're specifying each column that you're retrieving where get_result() will allow you to work with a more generic return of data from your tables.

PHP: Mysqli prepared statement with "select *"

2 Answers2

Linked

Related