0

I'm trying to rewrite all of my standard MySQLi queries to MySQLi Prepared Statements.

I've noticed a problem though, whenever I have a variable that contains a hyphen, the execute() fails.

The variables I'm dealing with ($project_id) look like this: 'AAD0012003-01'.

$get_progress_done = $db->prepare("SELECT COUNT(*) as rows FROM testvoorstage_checklists.?");
        $get_progress_done->bind_param("s", $project_id);
        $get_progress_done->execute();
        $get_progress_done->store_result();
        $get_progress_done->bind_result($rows);

        while($get_progress_done->fetch()) {            
            echo $rows;
        }

I've been searching for a solution for a couple of days now, and I still haven't found a way to 'escape' the hyphen in a variable.

I know the queries do work, because I've tried them in PHPMyAdmin with a set variable and they are working fine there.

I'm learning Prepared Statements, and I would love to know how to fix this because I have quite alot of variables that contain special characters.

The error I'm getting is:

Call to a member function bind_param() on a non-object

zerkms
  • 240,587
  • 65
  • 429
  • 525
Kevin Sleegers
  • 427
  • 2
  • 5
  • 16
  • 1. Check errors 2. You can only bind values, not database objects' names – zerkms Nov 26 '13 at 09:15
  • possible duplicate of [Can I parameterize the table name in a prepared statement?](http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) – Álvaro González Nov 26 '13 at 09:19
  • Thanks for your answers. Ah okay, I already thought so. However, in all other statements where I use the same variable to get like a certain row from a table it's failing aswell. Is there a way to escape a variable? Because when I try the query in PHPMyAdmin and insert `AAD0012003-01` (with backticks) directly in the query it runs just fine. The '-' seems to be the only problem. – Kevin Sleegers Nov 26 '13 at 09:20

1 Answers1

0
  1. Your problem has nothing to do with hyphens or prepared statements.
  2. The real problem is your database design. Instead of having a separate table for the each project, you have to store all the projects in the same table.
  3. Please name your variables sensibly. What $db->prepare returns is not, by any means, whatever $get_progress_done - it's merely a mysqli statement.

So, this code will do

$sql  = "SELECT COUNT(*) as rows FROM projects where project_id = ?";
$stmt = $db->prepare($sql);
$stmt->bind_param("s", $project_id);
$stmt->execute();
$stmt->bind_result($rows);
$stmt->fetch();
echo $rows;

However, I will never in my life understand PHP users who are writing 7 lines of code where only one is enough.

Your Common Sense
  • 154,967
  • 38
  • 205
  • 325
  • Thanks, I will try that! :) – Kevin Sleegers Nov 26 '13 at 09:22
  • I've been thinking about my Database Design but I have no idea how to store all the projects in one table. I'm definitely not an Database expert. Do I have to create multiple tables to store the values from a project? Like one table for all the 'project departments', another one for 'components', etc? And then create one table where I merge like all the data from other tables together? – Kevin Sleegers Nov 26 '13 at 09:28
  • You better ask another question. There are quite a lot of database experts hanging around. – Your Common Sense Nov 26 '13 at 09:30
  • Alright, thanks for your time. I will accept your answer because it's the solution for my problem when I get my Database Design right. – Kevin Sleegers Nov 26 '13 at 09:31