Prepared statement and GROUP_Concat


#1

HI,

I’m trying to switch from mysql to mysqli with prepared statements. I read that is considered safer from sql injection.

Now my unprepared query and output are working fine (in mysqli). But I’m running into trouble with the Group_Concat function in the process. I hope somebody can point out to me where I’m going wrong (or what I am trying to make the query do this way).

$stmt = mysqli_prepare($con, "SELECT pets.PET_ID, pets.Petname, pets.imageurl, pets.str, pets.HP, pets.dex, pets.luck, pets.con, pets.int, pets.learningslots, pets.growth, pets.Levelmin, pets.Levelmax,

elements.elementname,

elementgrowth.EL_growth,

GROUP_CONCAT(DISTINCT locations.LOC_ID, ‘\t’, locations.URLtomap , ‘\t’ , locations.Location ORDER BY locations.Location SEPARATOR ‘\r\n’) AS locidlist

/note without group_concat and the join of petlocations and locations the function runs perfectly/

From pets

JOIN petlocations
ON petlocations.PET_ID=pets.PET_ID
JOIN locations
ON petlocations.LOC_ID=locations.LOC_ID
JOIN elements
ON pets.element_id=elements.element_id
JOIN elementgrowth
ON elements.element_id=elementgrowth.element_id

where elements.elementname=?");

/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "s", $selected_val1);

/note selected_val1 is user input from a form field with Post/

/* execute query */
mysqli_stmt_execute($stmt);

/* bind result variables */
mysqli_stmt_bind_result($stmt, $PET_ID, $Petname, $imageurl, $str, $HP, $dex, $luck, $con, $int, $learningslot, $growth, $Levelmin, $Levelmax, $elementname, $EL_growth, $locidlist);

/note: not sure what about the locidlist I need to bind or how/

/* store result variables */
mysqli_stmt_store_result($stmt);

/* count the rows */
$count = mysqli_stmt_num_rows($stmt);

if ($count>0)
{
and the rest to echo the result to the screen.


#2

What error or symptom are you getting that leads you to believe something isn’t working? Do you have php’s error reporting set to report and display/log all errors? Do you have error handling for the database statements so that you will know what if any errors are being returned from the database server?

If this query worked using the mysqli extension, before converting it to a prepared query, there shouldn’t be a problem.

However, using a group aggregate function without using GROUP BY in the query may not produce the result you expect and can/should be producing an error, and you are re-using the $con variable, so no database query after this point in your code will work.

BTW - the php PDO extension is much simpler and more consistent than the mysqli extension. If you can, switch to use PDO. You won’t have to deal with binding input or output variables. You can just supply an array of inputs to the execute(…) call and can just fetch data from the result set.


#3

Hi, thanks for your reply. Indeed the absence of GROUP BY seemed to be the problem, because it’s working flawlessly now!

such a simple thing, I didn’t think that could be the problem…. I will look of more info on GROUP By to see if I can understand why this is so important.

Another question: why will I be unable to re-use $con for connection to the database in a new query? (for this page it is ok, because this is the only query on it… but just for my education)


#4

You are binding $con for the pets.con result column. After you fetch any data, it will contain the column value, not the database connection.


#5

oh…good point! I have adjusted the issue. Thanks again!


#6

As long as your are doing the switch, you might as well go with PDO. Here is a tutorial to get you going.

https://phpdelusions.net/pdo