Why does this insert statement laugh at me?

This works:

$sql_50 = "CREATE TABLE #tmp_Results
	(
		id	int IDENTITY,
		dec_PercentageCompleted decimal(10,2) 
	)";
$mssql_stmt_50=$mssql_pdo->query($sql_50);

$dec_percentage=.2;

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (dec_PercentageCompleted) VALUES (:dec_percentage)");

$mssql_stmt_11->bindValue(':dec_percentage', $dec_percentage,  PDO::PARAM_INT);

$mssql_stmt_11->execute();


echo "done";

Open in new window


And might I say, it took forever to figure out how to get a decimal to be inserted without getting an error, but, unfortunately, I've got some other data that needs to be inserted. So, while the above milestone was a breakthrough, the party was shorlived because when I do this:

$sql_50 = "CREATE TABLE #tmp_Results
	(
		id	int IDENTITY,
		str_ProjectName varchar(150),
		dec_PercentageCompleted decimal(10,2) 
	)";
$mssql_stmt_50=$mssql_pdo->query($sql_50);

$project_name="Project List";
$dec_percentage=.2;

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (str_ProjectName, dec_PercentageCompleted) VALUES (':project_name', :dec_percentag')");

$mssql_stmt_11->bindValue(':project_name', $project_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':dec_percentage', $dec_percentage,  PDO::PARAM_INT);

$mssql_stmt_11->execute();


echo "done";

Open in new window

I get this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in /var/www/html/testsite/c0gusb1/pdo_sql_test.php:71 Stack trace: #0 /var/www/html/testsite/c0gusb1/pdo_sql_test.php(71): PDOStatement->execute() #1 {main} thrown in /var/www/html/testsite/c0gusb1/pdo_sql_test.php on line 71

What do I need to change?
Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (str_ProjectName, dec_PercentageCompleted) VALUES (':project_name', :dec_percentage')");

You are missing an e...
0
Brian TaoSenior Business Solutions ConsultantCommented:
And you're having an extra trailing ' for :dec_percentage in $mssql_stmt_11
0
Bruce GustPHP DeveloperAuthor Commented:
First off, I fixed the obvious mistakes as far as the trailing "'" and the missing "e" in "percentage." When I saw those errors, I was hoping it was something that obvious that was causing me troubles. However embarassing it might be to overlook something that obvious, at this point, I just want to smell the aroma of success.

So, I made those changes and I got the exact same error. Here's the corrected code, just so we can both be certain Bruce isn't missing something obvious:

$project_name="Project List";
$dec_percentage=.2;

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (str_ProjectName, dec_PercentageCompleted) VALUES (':project_name', :dec_percentage)");

$mssql_stmt_11->bindValue(':project_name', $project_name, PDO::PARAM_STR);
$mssql_stmt_11->bindValue(':dec_percentage', $dec_percentage,  PDO::PARAM_INT);

$mssql_stmt_11->execute();


echo "done";

Open in new window


Notice, I'm definining "$dec_percentage=.2;" without the double quotes and I'm leaving the single ticks of ":dec_percentage" in my sql statement thinking that this is approproate since it's an integer value and that's what worked the one time I was able to insert a decimal value without an error.

But despite that seemingly being in place in a way that would gaurantee a positive outcome, PLUS having fixed the errors y'all saw, I'm still getting the same error message.

Where am I blowing it?
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Slick812Commented:
Not sure this will help here, But as memory serves, the period in PHP has a special code syntax proportion, and whenever I use any numeric "decimals"  I Always use a Zero like -
     $dec_percentage= 0.2;
because I think that the period can not preceed it and be interpreted as a "number".
  Hope this helps, but I see no other shortcomings.
0
Bruce GustPHP DeveloperAuthor Commented:
Folks, this is the only way I was able to get the query to work without an error. Do you see something in this "solution" that triggers something in your brain as far as what I may have been doing wrong that prevented the query from working to begin with.

Basically, what I did was run the query without "binding" the decimal value and just inserted that raw. It works, which is great, but seems like one of the big selling points about PDO is it's default ability to ward off SQL Injection. While what I've done eliminates the error, it seems like I'm putting a band aid on something that merits a real remedy.

Any ideas?

Here's what worked...

$sql_50 = "CREATE TABLE #tmp_Results
	(
		id	int IDENTITY,
		dec_PercentageCompleted decimal(10,2),
		str_FullName varchar(150)
	)";
$mssql_stmt_50=$mssql_pdo->query($sql_50);

$dec_percentage=.2;
$full_name="Bruce Gust";

$mssql_stmt_11=$mssql_pdo->prepare("insert into #tmp_Results (dec_PercentageCompleted, str_FullName) VALUES ($dec_percentage, ':full_name')");

$mssql_stmt_11->bindValue(':full_name', $full_name,  PDO::PARAM_STR);

$mssql_stmt_11->execute();

Open in new window

0
Slick812Commented:
I was hoping someone else could add something here, but since there are no comments, I will say something, as an opinion.
I have never understood why anyone would use the name formatted place holder as -
     ':project_name', :dec_percentage
I always use the question mark place holder, and then just for less cumbersome code work I do not use the PDO bindParam( ), I just send up an array in execute -
     $stmt->execute( array($post1, $post2) );

here is an example from the manual -
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));

Open in new window

this seems like a more efficient code write to me, and you do not need to go through EVERY $post, and set up a bindParam( ) for it.
    Also, please notice that there is a string in the $color, , and you do NOT need to place any quotes of any kind around the ?  in the SQL text, for the $color variable , as these place holders do not go by the old SQL text setup rules that you use without execute-prepare

= = = = = = =
and just to be sure, I hope you have set the PHP to show all errors and warnings, , as maybe there is a notice or warning about some variable that is spoiling your execute.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bruce GustPHP DeveloperAuthor Commented:
Hey, Slick!

When you use "bindParameter," it seems that with PDO::PARAM_STR and PDO::PARAM_INT, you're identifying the datatype. I've already used your suggestion and I love the fact that there's nowhere near as much "typing" to get the same result. But I want to know "why" it works and not just "that" it works.

How by using the array approach does PHP know how to escape apostrophes and determine the difference between an integer and a string?
0
Slick812Commented:
OK, this is very, very complex, , and to make it even more confusing, the PDO, you talk about here uses a further removal- abstraction, because it is a "Code" wrapper around the fundamental mysqli code work.

I will talk about the operations in the newer mysqli for prepare and execute, as the PDO  does not do any Database operations at all, in MSSQL, SQLite3, PortugueseSQL, or mysqli, , it just uses "Drivers" for the database engine initialization, and then tries to use common code to translate and do the same operations in all the engines.

In the Old way using a single DB  query(SQL);  had Everything sent to the MySQL engine in a single operation, with a single string sent. SO, you had to place the SQL syntax and the DATA segments , all in ONE string, and send it , then the database engine would need to parse and separate the SQL from the Data segments out off this single string.  But this was prone to errors in parsing, and SQL injections.
The new prepare() and execute() uses a TWO stage database engine transfer, one for SQL syntax transfer , and one for DATA transfer -

In the old way of using just the  
     query("select id, name from names where city ='$city'")
Everything, as the SQL syntax, and the DATA ($city in above), are combined into a single string and sent to the MySQL engine.
In the prepare - execute, First ONLY the SQL is sent, no data -
   prepare("select id, name from names where city = ?")
please notice that there are NO single quotes around the ? in the prepare, as in the previous around the $city .
You do not have to define any strings in a prepare DATA, because it is only looking at the SQL syntax with prepare().

when you use the  execute()  then a Second database operation takes place, and sends JUST THE DATA, no SQL, , so you never need to escape the DATA in the  execute(), because the escaping is for a "String" insertion, and to parse that data "string" out of the SQL string later.
The data segments are not changed in any way, because it is never inserted into a String, but keep separate, for better performance and insertion safety.

- - - - - - - - - - - - -
OK as to the - "difference between an integer and a string? "
The MySQL engine code base for prepare( ) is written in C++ code, and in many code languages, you Must define all variables as integers, strings, real-float , , so the MySQL have a bindParam( ) in C++ code to be used, and They MUST have the variable "Types" (string, integer) in that, because you can avoid development errors, if you get an exception to tell you that you have coded the incorrect variable type. . . However PHP is an Untyped language, so all of the PHP bindParam( )  variable "typing" is just BULL, not really used, only there because the C++ code requires it.
In My testing in the mysqli statement, bindParam() -
     $stmt->bind_param('ss', $int1, $real1);
even though the  $int1 and $real1  are NUMBERS not strings, the  bind_param() with the 'ss', will NOT throw a warning or exception, and will work, because of the "Automatic" type changing that PHP will do, as it places an interger into a string or real.  There is also some type changing in any types you place in the type string 'sid', but if you use a number type like i or d for a string, it does NOT throw a warning or exception, it just may or may not send nothing.

Please keep in mind that the Variables in the  bindParam()   are used by Reference, in code you can not see in the C++ code of PHP operations for the execute( ).

Now to the PDO, this uses a mixed and fizzeled way to get the execute array in -
     $sth->execute(array($calories, $colour));
to the mysqli as Data, but it is NOT done in PHP code, but in the C++ code of the PDO wrapper .

I will say this -
I do not use the PDO for MySQL, if I can avoid it, there were many problems with the way that PDO tried to change over to the newer mysqli "Drivers" in PDO code to do the prepare and execute. Even in the PHP ver 5.4 , it will default to using the old way of emulating the prepare - execute in mysqli, where is just does the SQL string substitution, insertion, and not the actual mysqli prepare().
I have to add this -
    $dbPDO->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
or else it does not do a real prepare and execute, it just emulates it.  Even if I use that code, I have seen PDO in php version 5.4, that can NOT use the newer mysqli drivers, and still will do the emulate.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Slick, that is a great explanation and you should really submit that as an article.

Fyi, you are talking about MySQL and Bruce is using MS SQL.  From the microsoft side, I am used to having to declare the variable types and I have carried that over to php myself.  But your explanation is great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.