I have a query built, that when run in the MySQL app, shows all the expected results. However, when I go to loop through it in my PHP code, it only shows one row as result.
No matter how I play around with the SQL code, it still displays all of the results in my MySQL app, but for some reason, something within my PHP code is causing the issue and I can't ferret it out.
Lastly, although it displays the one row of results, i still get the error
PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, string given in .... on line 144 " error.
My query is below, with $tID getting the URL variable id
SELECT *
FROM tbl_test_results tr
LEFT JOIN tbl_test_ordered tor
ON tr.testorderedID = tor.testorderedID
LEFT JOIN tbl_test t
ON tr.testID = t.testID
WHERE tor.testorderedID = '$tID'
ORDER BY t.test_name ASC
My code:
<!DOCTYPE html>
<html dir="ltr">
<head>
<?php include($_SERVER['DOCUMENT_ROOT'].'/gezond/includes/header_2019.php'); ?>
<title>gezond | cronkflies.com</title>
<style>
.tablesorter-pager .btn-group-sm .btn {
font-size: 1.2em; /* make pager arrows more visible */
}
</style>
<script type="text/javascript">
$(function() {
$("table").tablesorter({
theme : "bootstrap",
widthFixed: true,
// widget code contained in the jquery.tablesorter.widgets.js file
// use the zebra stripe widget if you plan on hiding any rows (filter widget)
// the uitheme widget is NOT REQUIRED!
widgets : [ "filter", "columns", "zebra" ],
widgetOptions : {
// using the default zebra striping class name, so it actually isn't included in the theme variable above
// this is ONLY needed for bootstrap theming if you are using the filter widget, because rows are hidden
zebra : ["even", "odd"],
// class names added to columns when sorted
columns: [ "primary", "secondary", "tertiary" ],
// reset filters button
filter_reset : ".reset",
// extra css class name (string or array) added to the filter element (input or select)
filter_cssFilter: [
'form-control',
'form-control',
'form-control', // select needs custom class names :(
'form-control',
'form-control',
'form-control'
]
}
})
.tablesorterPager({
// target the pager markup - see the HTML block below
container: $(".ts-pager"),
// target the pager page select dropdown - choose a page
cssGoto : ".pagenum",
// remove rows from the table to speed up the sort of large tables.
// setting this to false, only hides the non-visible rows; needed if you plan to add/remove rows with the pager enabled.
removeRows: false,
// output string - default is '{page}/{totalPages}';
// possible variables: {page}, {totalPages}, {filteredPages}, {startRow}, {endRow}, {filteredRows} and {totalRows}
output: '{startRow} - {endRow} / {filteredRows} ({totalRows})'
});
});
</script>
</head>
<body class="stretched">
<section id="content">
<div class="content-wrap">
<!-- <div class="section bottommargin-lg header-stick">
</div>-->
<div class="container">
<div class="row">
<div class="col-lg-12">
<div class="page-header">
</div>
<?php
if (!defined('DB_SERVER')) define('DB_SERVER', 'localhost');
if (!defined('DB_USER')) define('DB_USER', 'xxx');
if (!defined('DB_PASSWORD')) define('DB_PASSWORD', 'xxx');
if (!defined('DB_TABLE')) define('DB_TABLE', 'xxx');
// The procedural way
$mysqli = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD, DB_TABLE);
$mysqli->set_charset("utf8");
$mysqli->query("SET NAMES 'utf8'");
if (mysqli_connect_errno($mysqli)) {
trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR);
}
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
die("Invalid ID specified.");}
else {
$tID = mysqli_real_escape_string($mysqli, $_GET['id']);
}
$query = "
SELECT *
FROM tbl_test_results tr
LEFT JOIN tbl_test_ordered tor
ON tr.testorderedID = tor.testorderedID
LEFT JOIN tbl_test t
ON tr.testID = t.testID
WHERE tor.testorderedID = '$tID'
ORDER BY t.test_name ASC ";
$result = mysqli_query($mysqli, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($mysqli), E_USER_ERROR);
echo "
<table id='table_vluchten' class='table-bootstrap table-sm table-bordered table-striped' width='100%'>";
echo "<thead class='thead-inverse'>";
echo "<tr>";
echo "<th class='filter-false' data-sorter='false'> </th>
<th ><div align='left'>Test</div></th>
<th ><div align='left'>Result</div></th>
<th ><div align='left'>Units</div></th>
<th ><div align='left'>Low</div></th>
<th ><div align='left'>High</div></th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
if($result) {
while($row = mysqli_fetch_assoc($result)) {
$test = $row['test_name'];
if(!isset($row['test_result'])) {
$result = $row['test_result_t'];
} else {
$result = $row['test_result'];
}
$low = $row['test_low'];
$high = $row['test_high'];
$units = $row['test_unit'];
echo "<tr >";
echo "<td style='width:50px;'><span style='font-size:14px;text-align: center;'><i class='fas fa-list-ul'></i></span></td>";
echo "<td style='width:200px'><span style='font-size:14px'>" .$test. "</span></td>";
echo "<td style='width:100px'><span style='font-size:14px'>" .$result. "</span></td>";
echo "<td style='width:100px'><span style='font-size:14px'>" .$units. "</span></td>";
echo "<td style='width:100px'><span style='font-size:14px'>" .$low. "</span></td>";
echo "<td style='width:100px'><span style='font-size:14px'>" .$high. "</span></td>";
echo "</tr>";
}
echo "</tbody>";
echo "</table>";
}
mysqli_close($mysqli);
?>
</div>
</div>
</div>
</div>
</section>
<script>
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
});
</script>
</body>
</html>
Your code is messy. You are mixing a lot procedural style and object oriented style:
...
$mysqli->set_charset(... <-- object oriented style
$mysqli->query(... <-- object oriented style
if (mysqli_connect_errno(... <-- procedural style
mysqli_connect_error(... <-- procedural style
I would recommend to chose either one or another but not to mix it all the time.
But @Qirel is right the answer for the particular problem you are asking is to rename variable here:
if(!isset($row['test_result'])) {
//$result = $row['test_result_t']; // <-- this reset your mysql result to string variable
$t_result = $row['test_result_t']; // <-- lets use another variable name
} else {
//$result = $row['test_result']; // <-- this reset your mysql result to string variable
$t_result = $row['test_result']; // <-- lets use another variable name
}