Tag: MySQL

See if you can spot the error!

$ControlColumns = array("col1", "col2", "col3", "col4", "col5");
$params = array();
$cols = array();
$colnames = array_column($ControlColumns, 1);

$bindtypes = "sssss";

foreach ($colnames as $col)
{
    $params[] = &$cols[$col];
}

$result = mysqli_stmt_bind_param($stmt, $bindtypes, $params);

Here’s the error:

Number of elements in type definition string doesn’t match number of bind variables in E:\XAMPP\xampp\htdocs\PHPTest\insert.php

And assume that the $stmt is a perfectly prepared statement with multiple parameters. In other words, it works fine.

Give up?

I guess I’m just stupid, but it took me an hour of messing around before I finally remembered that mysqli_stmt_bind_param was not designed to take an array input.

Use call_user_func_array to call that function instead.  Remember to reference the variables.

$params = array();
$params[] = $stmt;
$params[] = &$bindtype;

$result = call_user_func_array('mysqli_stmt_bind_param', $params);

 

No placeholders to bind to in MySQL prepared statements.

I suppose this is just common sense, but I really couldn’t find anything on the subject. I had a situation where I wanted to list everything in the database. In this case, there was no need for a WHERE clause in my query.

Binding an empty string using mysqli_stmt_bind_param($stmt, “”, array()); will throw an error. So I added a check to see if there are any parameters to bind. If not, don’t call this function. It seems to work and I’m still waiting for the other shoe to drop.

Enclosing MySQL Columns with Grave Accents

Now here’s the reason why I created this blog.  It’s an uncommon solution.  Uncommon because I had a hard time finding a solution by using google and ended up altering the method until it worked in a way that made some sense.

Let me start by saying that there are some strange nuances with SQL columns when you surround them with grave accents.

The problem?  I was  trying to enclose the column names with grave accents to keep my code readable and reliable even though I was using JOINS.

Let me start with what did not work…  `Table_Name.Column_Name`.  I immediately got a “unknown column in field list” error.

After some poking around, this is what did work Table_Name.`Column_Name`.  Note how only the column name is surrounded by the grave accents.  This style works, leading me to believe that Table_Name can be enclosed by a separate set of grave accents.

MySQL special characters

I was playing around with a certain query only to come up with 0 results every time.  After looking around, I found that, in MySQL, the samples that actually tried to surround the table column names used the the grave symbol (`), not to be confused by the single quotes (‘).  The grave symbol is found on the same button as the tilde (~).

For example, if there was a table column called Name…

Using ‘Name’ in your query would probably create 0 results.

Using `Name` in your query would probably create the desired number of results.

http://stackoverflow.com/questions/7857278/what-is-the-meaning-of-grave-accent-aka-backtick-quoted-characters-in-mysql