0

Im switching from good ol' MySQL to MySQLi (late bloomer, I know) and have chosen MySQLi over PDO.

Firstly, I like associative arrays (eg: $db_data['whatever_field']), so I cannot use prepared statements because that doesnt return associative arrays (by default / without workarounds).

So I have to manually prepare my MySQLi statements myself, eg:

'SELECT * FROM admin_users WHERE email='. $mysqli->real_escape_string($_COOKIE['admin']['email']) .' AND password='. $mysqli->real_escape_string($_COOKIE['admin']['password']);

As you can imagine, this could get tiresome! Is there a short $mysqli->real_escape_string() or what do you guys do?

Toretto
  • 4,721
  • 5
  • 27
  • 46
SupaMonkey
  • 876
  • 2
  • 9
  • 25
  • 1
    MySQLi generally requires slightly more verbose code than PDO and considerably more verbose than `mysql_*()`. Don't worry about the extra code - do what you need to do to pull associative arrays out of a prepared statement, because as you are doing it, you are not getting the primary security benefit of parameterized queries. – Michael Berkowski Oct 16 '12 at 14:04
  • I use PDO and don't worry about escaping it. – wesside Oct 16 '12 at 14:04
  • What do you mean it wont return an array? did you try [`mysqli_stmt::get_result`](http://www.php.net/manual/en/mysqli-stmt.get-result.php) and [`mysqli_result::fetch_assoc`](http://www.php.net/manual/en/mysqli-result.fetch-assoc.php)? Also im curious at to why you chose `Mysqli` over `PDO`... `PDO` is som much easier to work with in a variety of ways. – prodigitalson Oct 16 '12 at 14:08

3 Answers3

3

You are not going to use Mysqli over PDO under my watch son!

Firstly, I like associative arrays (eg: $db_data['whatever_field']), so I cannot use prepared statements because that doesnt return associative arrays (by default / without workarounds).

That's a bad excuse.

PDOStatement::fetch

accept as first parameter the fetch style which can be set to PDO::FETCH_ASSOC.

Also, 

 PDO::setAttribute

makes you able to choose your default fetch style, in particular setting PDO::ATTR_DEFAULT_FETCH_MODE to PDO::FETCH_ASSOC.

Reference:

Shoe
  • 74,840
  • 36
  • 166
  • 272
  • Actually hes saying he WANTS assoc back from his executions and hes trying to figure out how to do it with mysqli... +1 because i support your effort to convert him to PDO like a sane person ;-) – prodigitalson Oct 16 '12 at 14:32
  • ok, so youve converted me. PDO it is. Now, do I use fetchAll and iterate through the array manually or just use fetch in a while loop? According to [this answer](http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop) for another question. fetchAll is faster but uses significantly more memory where fetch is marginally slower and uses far less memory. Sounds like fetch is the winner? – SupaMonkey Oct 16 '12 at 15:56
  • @SupaMonkey, I have seen a lot of `while ($a = $query->fetch())` but I think it's really up to you. In my personal preferences I think "`fetch`-method" is cleaner. – Shoe Oct 16 '12 at 16:17
  • @Supa: It depends.. you can run into the memory problem real quick with fetch all if you have a ton of records. If I have even the slightest reason to beleive at some point in the future ill be dealing with a large number of result i use `while`/`fetch`. – prodigitalson Oct 16 '12 at 22:55
  • Maybe you guys can help me with another question in the PDO line; Im starting to pull out my hair and wish I stuck with MySQLi! [Check it here](http://stackoverflow.com/questions/12999287/php-pdo-issue-with-sanitised-order-by-fields) – SupaMonkey Oct 22 '12 at 05:33
1

If you prepare statements you don't need escaping, that's one of the main purposes of preparing statements.

I support the explanation by Jeffrey of why use PDO, and I also want to contribute that mysqli is a weird library and really hard to test if you ever have to. The way you bind variables by reference is quite not easy on the eyes. I personally recommend you reconsider your choice. Hopefully one day we deprecate mysql and mysqli.

fd8s0
  • 1,897
  • 1
  • 15
  • 29
0

There's a function named fetch_assoc. Further reading from php manual http://php.net/manual/en/mysqli-result.fetch-assoc.php

mysqli_result::fetch_assoc -- mysqli_fetch_assoc — Fetch a result row as an associative array

Ofir Baruch
  • 10,323
  • 2
  • 26
  • 39