我可以将所有SQL查询写入phpMyAdmin / HTML文件，还是需要使用其他程序？

Question

I have a database in phpMyAdmin that I have set up with XAMPP. I am working on a website that shows statistics from the user inputted scores in the database. Say that I would like to show the score percentile to the user after they submit their score: where do I write the query for that? In the HTML/PHP code? In phpMyAdmin? Somewhere else like a workbench or PopSQL?

I have successfully gotten the website to display the average score in any given table by writing this code to the HTML file:

<?php 
$sql = "SELECT AVG(score) AS score FROM $input_subject";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_object($result) ;
echo nl2br("Average score for $input_subject: 
 
" 
. round($row->score)); 
?>

It works, but when I search for tutorials for example for the percentile query or something like the CHECK function (to not accept anything less than 0 or more than 120) it seems that the queries are always written somewhere else than the HTML file.

Also, when I try to write the SQL code in phpMyAdmin, it always shows a bunch of error messages, even though I copy/pasted it in (changing the table names etc., of course).

So, do I need to look into some other programmes were to write the queries in or can I just write them into the HTML-file or in the phpMyAdmin? I'm a total newbie with this so anything helps!

Answer 1

Yes, you can write SQl queries(PHP) before your html code and also in between your html code, but make sure to change file extension to .php from .html otherwise PHP code will be printed on browser as it is.

"it seems that the queries are always written somewhere else than the HTML file."

we do this while using AJAX. we send data to server (a PHP file where we process data received) and server will give response. All this happens behind the scenes without page reloading.

in your case you can create a new PHP file lets say process.php, add your PHP code into it.

process.php

<?php 
$sql = "SELECT AVG(score) AS score FROM $input_subject";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_object($result) ;
echo nl2br("Average score for $input_subject: 
 
" 
. round($row->score)); 
?>

send user inputted scores from HTML file through ajax to PHP file (process.php) and response received from that file can be displayed in html file without page reloading.

you can go through the ajax api by following link jQuery-AJAX