MySQLi vs PDO Prepared Statements

PHP
41

Yeah, I think I had my bindings for offset and limit in the wrong order, so limit was set to 0. Got it working, thanks.

The partial query was building up to the full string, i.e.


$this->_queryString = "SELECT $this->_fields FROM $this->_table";
		
	if ($this->_where_field != "") {
		$this->_queryString .= " WHERE $this->_where_field = ?";
	}
		
	if ($this->_orderby != "") {
		$this->_queryString .= " ORDER BY ?";
	}
		
	if ($this->_limit != "") {
		$this->_queryString .= " LIMIT ?, ?";
	}

$stmt = $dbh->prepare($this->_queryString);
		
	$stmt->bindParam(1, $this->_where_value, PDO::PARAM_INT);
	$stmt->bindParam(2, $this->_orderby, PDO::PARAM_STR);
	$stmt->bindParam(3, $this->_offset, PDO::PARAM_INT);
	$stmt->bindParam(4, $this->_limit, PDO::PARAM_INT);

That way the class is more flexible and can work with queries that don’t have a LIMIT etc. I am going to pass it all in as an array later, but wanted to get it working in general, and it seems to. Is that okay to do, so use conditionals to build the query string like that?

42

Yes, that’s fine. I just meant that when you call prepare(), its argument needs to be the full query. You can’t use parameter binding to add pieces of SQL syntax (“#, #”) after the fact.

43

Thanks for the help so far. I’m working a full time construction job as well as another 7-8 hours a day on this project, plus raising a family, so my head is kind of mush sometimes. I really want it done right though, no more security holes and hacked together code, so I am stepping through it and i refuse to just copy/paste code, i want to understand it and write it myself.

When I create a new PDO does it need to be inside the method I called prepare() in or can I make a dbConnect method and use $this->dbh?

44

Hi…

Funnily I had to give up with PDO when using stored procs. Error messages bore no relation to the actual errors. Buggy as hell in this department. Switched to MySQLi and all was fine.

yours, Marcus

45

Sounds scary. I’ve never used stored procs much, but I did run into some memory leaks at some point, which were related to PDO.

Buggy or not, PDO seems to be more widely used in the community.

46

You can - and indeed you should - reuse the same connection object (Eg. the PDO instance) throughout the application. Either pass it in to the object/methods that need it or make it global. I recommend the former.

47

I’d agree.

MySQLI has a shockingly horrid API in some places, like mysqli_stmt_bind_param(), which forces you to use call_user_func_array() in certain situations.

48

That’s not fair. MySQLi has a full OOP API which is perfectly functional in all cases. What you’re talking about are incredibly old interfaces, which I don’t think most sane people have used in a long time.

49

The OO interface shares the same lunacy as the functional interface.

Having a function take variable number of arguments in this instance is poor.

The MySQLI_Stmt::bind_param() & MySQLI_Stmt::bind_result() methods are really ugly to use and have to resort to call_user_func_array(), as you would with the functional equivalents.

Whereas PDO, can bind parameters and columns individually.

50

Ah, I understand what you’re getting at.

That’s a legitimate complaint, and one I don’t have an answer for. I would guess that the decision was made in the hope that you could bring the API closer to a “Do what I mean” state. Personally, if I’m writing a public API, I prefer to make things a bit funkier on the back end if it makes it easier on the developer using the API (assuming it doesn’t introduce security or performance issues).