MySQL - SELECT query with variable in it won't execute

Question

So I'm trying to create a dynamic SELECT query that will only show the columns that were chosen previously and stored in a string $stANCol.

$idServ = 17; //$_GET(Selected);
$queryIsSelected = "SELECT $stANCol FROM camposservico WHERE idServico = ? ;";              
$stmt = $mydb->prepare($queryIsSelected);        
$stmt->bind_param("i", $idServ);                
$stmt->execute();

The echo from $stANCol is

morada, dieta, dataIntervencao, sondaVesical, ecd,
sinaisVitais, riscoQueda, riscoAlergia, riscoTransfusao, riscoMultiRisco, obsQuadro, obs

and these are the columns that may change depending on what is selected or not.

The echo from $queryIsSelected is

SELECT morada, dieta, dataIntervencao, sondaVesical, ecd, 
sinaisVitais, riscoQueda, riscoAlergia, riscoTransfusao,
riscoMultiRisco, obsQuadro, obs FROM camposservico WHERE idServico = ? ;

And this query works just fine on phpMyAdmin, hence my question. Am I doing something wrong or is it impossible to have a string variable used on the SELECT?

EDIT: Turns out there was nothing wrong with any of this code, had a pending statement lurking on my page that was messing with this one. Thanks for all the replies everyone :)

Does SELECT * FROM camposservico WHERE idServico = ? work? — Tessmore
– Tessmore, Commented Mar 12, 2014 at 12:29
Oh, actually now it doesn't anymore. I messed up somewhere else then. I guess the question is irrelevant now. Thanks for the reply, sorry for wasting time. — Andrade
– Andrade, Commented Mar 12, 2014 at 12:36

BMN · Accepted Answer · 2014-03-12 12:25:57Z

3

remove the ; from your query

your's:

$queryIsSelected = "SELECT $stANCol FROM camposservico WHERE idServico = ? ;";

after removing ;

$queryIsSelected = "SELECT $stANCol FROM camposservico WHERE idServico = ? ";

it will get execute.

edited Mar 12, 2014 at 12:25

BMN

8,57614 gold badges51 silver badges80 bronze badges

answered Mar 12, 2014 at 12:24

dpk

6411 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Andrade Over a year ago

Thanks for the reply but that didn't make a difference. And I had it without ";" before, I actually added it when I wrote the question here :)

dpk Over a year ago

what does ? stands for in idServico = ?

Andrade Over a year ago

The idServico in the WHERE clause is being set on the bind_param, hence the "?". It will always be an int that is also chosen by the users.

dpk Over a year ago

what error are you getting? try, manually insert the value instead of ?.. and see whether its getting execute or not? e.g. $queryIsSelected = "SELECT $stANCol FROM camposservico WHERE idServico = 111 ";

Andrade Over a year ago

That's exactly what I tried first. I keep getting the "Fatal error: Call to a member function execute() on a non-object ...".

|

user3305084 · Accepted Answer · 2014-03-12 12:28:21Z

0

I have added single quotes in your query

$queryIsSelected = "SELECT $stANCol FROM `camposservico` WHERE `idServico` = ? ";

answered Mar 12, 2014 at 12:28

user3305084

1 Comment

Andrade Over a year ago

Thanks for the reply but it's stil the same. Most of my queries don't have any single quotes, and still execute. The problem must be something else that is really getting on my nerves

majidarif · Accepted Answer · 2014-03-12 12:41:24Z

0

Try this

$queryIsSelected = "SELECT $stANCol FROM camposservico WHERE idServico = ? ;";              
$stmt = $mydb->prepare($queryIsSelected);        
$stmt->bind_param("i", $idServ);  

// move this to here
$idServ = 17;

$stmt->execute();

also try to use something like this for debugging

if($stmt === false) {
   trigger_error('Wrong SQL: ' . $queryIsSelected . ' Error: ' . $mydb->errno . ' ' . $mydb->error, E_USER_ERROR);
}

edited Mar 12, 2014 at 12:41

answered Mar 12, 2014 at 12:31

majidarif

20.4k18 gold badges97 silver badges137 bronze badges

1 Comment

majidarif Over a year ago

Updated answer, maybe something is going wrong with your statement.

Collectives™ on Stack Overflow

MySQL - SELECT query with variable in it won't execute

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related