SQL query fails in php function

Question

function getDeviceTableCount($fieldnameIn) {
$sqlDeviceCount = "SELECT COUNT(" . $fieldnameIn . ") as total FROM `devices`";
$countDeviceTone = mysqli_query($conn, $sqlDeviceCount);
$data=mysqli_fetch_assoc($countDeviceTone);
$totalQ = $data['total'];
return $totalQ;
}

Hey everyone, Thanks for taking the time to read my problem. I am making a function to return the count of a column in a MYSQL Database. Unfortunately, this function does not return the total number of items in the column.

I know the query works. I was wondering if the problem is putting a php variable in an sql string because I need the user to be able to change the table the app queries from.

If anyone can shed some light on why this doesn't work, I'd appreciate it.

• jackel414: The problem is not putting a php variable in the SQL string - that's perfectly fine. Where is the $conn variable being set? Are you sure it's not "null"?
• jaym: Make sure $fieldnameIn value is not a default MySQL keyword if it is the case add ` around your field name.
• xQbert: Does it return a count at all? Does that count happen to exclude NULL values? What does Select count(*) from devices vs Select count(Fieldname) from devices vs select count(1) from devices where fieldName is null each return?

Answer 1

in your funtion $conn is unkonwn :(

so if you prefer such a handling, give your DBDMS connection to your function

function getDeviceTableCount($fieldnameIn, $conn) {
....
}

or use global in your function (that is what you not will ,)

or build a new connection inside your function (that is what you also not will)

Answer 2

There multiple problems here. One is almost certainly the $conn. The other problem here might be the SQL injectipn which might be easily resolved with mysqli_real_escape_string() function. If you want the user to specify the table pass it as parameter also and escape it.