PHP JSON from mysql database not showing any result

Question

I have a problem getting the data in JSON format via the data.php file. When I try see JSON I got this: "{"draw":0,"recordsTotal":null,"recordsFiltered":120,"data":[]}"

but needs the php script to retrieve data from the table all entries for the column date, name, id like datatables AJAX instructions . When doing this using the local XAMPP server, the script retrieves the entries from the mysql table and the data is displayed as a table using datatables.

Here is my PHP code


$connect = new PDO("mysql:host=localhost;dbname=abc", "root", "PASSWORD");
$query = "SELECT * FROM abc_result ";
if(isset($_POST["search"]["value"]))
{
    $query .= '
    WHERE DATE LIKE "%'.$_POST["search"]["value"].'%"
    OR NAME LIKE "%'.$_POST["search"]["value"].'%"
    OR ID LIKE "%'.$_POST["search"]["value"].'%"
    ';
}
if(isset($_POST['DATE']))
{
    $query .= 'ORDER BY '.$column[$_POST['DATE']['0']['column']].' '.$_POST['DATE']['0']['dir'].' ';
}
else
{
    $query .= 'ORDER BY ID DESC ';
}
$query1 = '';
if($_POST['length'] != -1)
{
    $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$result = $connect->query($query . $query1);
$data = array();
foreach($result as $row)
{
 $sub_array = array();
 $sub_array[] = $row['DATE'];
 $sub_array[] = $row['NAME'];
 $sub_array[] = $row['ID'];
 $data[] = $sub_array;
}
function count_all_data($connect)
{
    $query = "SELECT COUNT(*) FROM abc_result";
    $statement = $connect->prepare($query);
    $statement->execute();
    $result = $statement->fetchColumn();
    return $result;
}

$output = array(
    "draw"      =>  intval($_POST["draw"]),
    "recordsTotal"  =>  count_all_data($connect),
    "recordsFiltered"   =>  $number_filter_row,
    "data"      =>  $data
);

echo json_encode($output);

I'm trying to retrieve data from my mysql database and display it using datatables. locally I've been able to do it but I'd like to do it on a NAS (QNAP) so that when I go to a website the table would be visible by anyone who visits it. To do this, I installed MariaDB 5 and PHPmyAdmin. I was able to connect to the database but the php code does not display the data as I would like it to

`When I try see JSON I got this: "{"draw":0,"recordsTotal":null,"recordsFiltered":120,"data":[]}"`...yes, that is JSON. It's not clear what precisely you were expecting to see instead - please clarify. — ADyson, Jan 25 '23 at 15:10
`and display it using datatables`...this part seems to be missing from your example here. Is the issue actualy relating to getting this JSON output linked with the table? Again it's unclear. `but when I try to do the same on the NAS server`....this sentence appears to be incomplete. Please review your question thoroughly and provide additional information so we can understand precisely what you're trying to do and precisely what you expected to see from your code, compared to what you are getting now. See also [ask], the [tour] and how to produce a [mre] of the issue, for more guidance.Thanks — ADyson, Jan 25 '23 at 15:11
P.S. **Warning:** Your code is vulnerable to SQL Injection attacks. You should use prepared statements **with parameters** to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / PDO. **Never** insert unparameterised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. This code should not be anywhere near a live server. — ADyson, Jan 25 '23 at 15:13
https://phpdelusions.net/pdo also contains good examples of writing safe SQL using mysqli. See also: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. You seem to have learnt about prepared statements, but missed the important fact that they are not useful unless you also use parameters instead of adding data directly into the SQL string. — ADyson, Jan 25 '23 at 15:14
@ADyson Thank you for your response and for your valuable comments. Currently I am the only one who has access to the NAS server so I will work on security but I can't pass the step related to displaying the correct JSON format for AJAX datatables. — SROAD_, Jan 25 '23 at 15:56
Ok. You still didn't really explain the issue very clearly in terms of specifics. But it seems, from looking at your JSON output, like your query does not return any rows. Is that the main problem? — ADyson, Jan 25 '23 at 16:06
Ok. So have you debugged anything about what it's doing? Most likely, the parameters supplied to your PHP script have resulted in a SQL query which doesn't match anything which is in the database. Have you looked what the final SQL query that PHP generates is? Does it match what you expected, based on the inputs provided? Have you checked in the database on your NAS that there is the right data in there? — ADyson, Jan 25 '23 at 16:25
Yes, I checked several times to make sure there is no error in the database, table and column names. All names are entered correctly in the database and in the script. The query executed in the database shows all the data. — SROAD_, Jan 25 '23 at 16:54
I'm not talking about _errors_, I'm talking about the query simply not returning anything because there's no matching data in the database - i.e the WHERE clause specifies criteria which don't correspond to any rows in the data. That isn't an error as such, but it may be a logical bug, or it may simply be that your database contains nothing relevant. Since we don't know either the content of the database or the exact SQL which your PHP script generated during your testing, we cannot easily help you with that...you need to do some more careful analysis. — ADyson, Jan 25 '23 at 16:57
Having said that. it's possible that your PDO code does actually silently crash somewhere...I don't know if you've got PDO set to throw PHP errors when that happens or not - see [PDO error handling](https://phpdelusions.net/pdo#errors) for how to enable that, and how to properly handle such errors if they do occur. — ADyson, Jan 25 '23 at 17:00

PHP JSON from mysql database not showing any result

0 Answers0