Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE day = 'monday'' at line 1

[Pratik30]

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE day = 'monday'' at line 1 in C:\xampp\htdocs\TTMS\files\assignSubstituteFormValidation.php:42 Stack trace: #0 C:\xampp\htdocs\TTMS\files\assignSubstituteFormValidation.php(42): mysqli_query(Object(mysqli), 'SELECT * FROM ...') #1 {main} thrown in C:\xampp\htdocs\TTMS\files\assignSubstituteFormValidation.php on line 42

 

 

<?php


//$days = array("monday","tuesday","wednesday","thursday","friday","saturday");
//echo $_POST['CN'];
//echo $_POST['SB'];
include('sms.php');
session_start();
$whose = $_SESSION['shown_id'];
$sub = $_POST['SB'];
$class = $_POST['CN'];
$days = array("monday", "tuesday", "wednesday", "thursday", "friday", "saturday");
$day = $days[($class - 8) / 8];

$periods = array("period1", "period2", "period3", "period4", "period5", "period6");
$period = $periods[($class - 1) % 8];
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$query = mysqli_query(mysqli_connect("localhost", "root", "", "ttms"), "SELECT * FROM teachers WHERE faculty_number = '$whose'");
$row = mysqli_fetch_assoc($query);
$whose_name = $row['name'];
$query = mysqli_query(mysqli_connect("localhost", "root", "", "ttms"), "SELECT * FROM teachers WHERE faculty_number = '$sub'");
$row = mysqli_fetch_assoc($query);
$sub_name = $row['name'];
$whose = strtolower($whose);
$sub = strtolower($sub);
$query = mysqli_query(mysqli_connect("localhost", "root", "", "ttms"), "SELECT * FROM $sub WHERE day = '$day'");
$row = mysqli_fetch_assoc($query);

$available = false;
if ($row[$period] == "-<br>-" || $row[$period] == "-<br>" || $row[$period] == "-") {
    $message = 'Message Sent!';
} else if (!isset($_POST['pwd'])) {
    echo "<script type='text/javascript'>alert('Selected substitute teacher is not available!');
        window.location.href = 'generatetimetable.php?display='$whose;</script>";
}

$query = mysqli_query(mysqli_connect("localhost", "root", "", "ttms"), "SELECT * FROM $whose WHERE day = '$day'");   //this is line no.42
$row = mysqli_fetch_assoc($query);
$pieces = explode("<br>", $row[$period]);
/*echo $pieces[0]; // piece1
echo $pieces[1];
echo $whose_name;
echo $sub_name;
echo "<br>";*/
$string = "Hello " . $sub_name . ", You have to take class " . $pieces[0] . " of " . $whose_name . " in " . $pieces[1] . "\n\n-Sent from TimeTable Management System AMU";
$_SESSION['s'] = $string;
echo 'Sending SMS...';

if (isset($_POST['pwd'])) {
    echo "<script type='text/javascript'>alert('Message Sent!');
        window.location.href = 'generatetimetable.php?display=" . $whose . "';</script>";
}
?>
<div class="content">
    <form method="post" id="smsform"><input type="hidden" name="uid" value="sender mobile number goes here"/>
        <input type="hidden" name="pwd" value="way2sms password goed here"/><input type="hidden" name="to" value="recipient mobile number goes here"/>
        <input type="hidden" name="msg" value="<?php echo $_SESSION['s'] ?>"/>
        <input type="hidden" value="Send SmS" id="send"/></form>
</div>
<script>
    var send = document.getElementById('smsform');
    send.submit();
</script>

 

[requinix]

1. You don't do any validation of $class to make sure it's a number between 8 and 55 before trying to use it with $days
2. Same for $class as used with $periods, except values of 7, 8, 15, 16, 23, 24, and so on aren't valid
3. You do a mysqli_connect for every single mysqli_query. All you need is one at the beginning of the script, then assign the connection to a variable and use that
4. You're connecting as root, and without a password. Do things right and create a proper user with a proper password.
5. If $whose is a string then don't put the variable directly into a query - use prepared statements instead
6. Same for $sub, except it's even worse because that value came from the user, and a malicious user could easily screw up your database by doing something as simple as inserting an apostrophe
7. And then you turn around and use $sub as a table name? That's completely wrong: never use unknown variables, especially from $_GET or $_POST, as table or column names.
8. Fixing the above should resolve the error message too
9. Why are there <br>s in your data? That doesn't make sense and suggests you're doing something weird with the data, like putting HTML into it.
10. Using isset with the pwd in the $_POST only checks if there was a field named "pwd" in your form. If that field is left empty, isset will still return true.
11. And assuming pwd is a password, shouldn't you be checking that password?
12. When you do check the password, make sure that your code does not keep running after that. Right now the user will get redirected with some Javascript, yes, but all your code kept on running.
13. And you're doing the thing where you use a variable as a table again, this time with $whose
14. Are you putting multiple values into your $whose table? Don't. One value at a time, using multiple rows if you have to.
15. On that subject, apparently $period is a column name? Don't. What you're doing with $day, where you have a column named "day" and multiple rows for the days, is what you should be doing with periods too.
16. ...Are you putting an actual password into your form? And your own phone number as the sender?
17. What's the point of the "msg" form field? Either that or what's the point of the "s" message you put into the session? What's going on with this?
18. And why have a form that automatically submits? If you want to do something immediately then do it - don't send a form to the user (which they won't see for long) that is just going to come right back to you.
19. All your various outputs need to be escaped with functions like htmlspecialchars so that someone like me can't insert <>s or "s that will mess up your HTML, or worse let me hijack some other user's browsing sessions for my own malicious purposes.

Is this a school assignment or something?

[Pratik30]

@requinix Can you please fix the overall code?
this is for my college project

 

Edited June 11, 2023 by Pratik30

[ginerjm]

If they are teaching to code like this in college, get another college.

[mac_gyver]

the error is (most likely) because you haven't validated ALL the inputs to your code before using them and because your schedule data is not properly stored. you should build any sql query statement in a php variable, so that you can echo it to see what it actually is.

$_SESSION['shown_id'] is an input to your code. you must validate it before using it. if it isn't set, it means that there is no a current/selected teacher and all the code that's dependent on that value should not be executed. you would instead setup and display an error message letting the user know what's wrong and how to correct it. e.g. there's no current/selected teacher. either login or select a teacher.

what does adding var_dump($_SESSION['shown_id']); show? if it's not set or it is not an expected value, you will need to find out why.

you should have ONE table holding the schedule data for all the teachers, with a separate row for each teacher (faculty_number) , day, and period. there would only be rows for data that exists.

as to the posted code -

1. the code for any page should be laid out in this general order - 1) initialization, 2) post method form processing, 3) get method business logic - get/produce data needed to display the page, 4) html document.
2. the post method form processing should - 1) detect if a post method form was submitted, 2) keep the form data as a set, in an array variable, then operated on elements in this array variable throughout the rest of the code, 3) trim all the data at once, 4) validate all the form data, storing user/validation errors in an array using the field name as the main array index, 5) after the end of the validation logic, if there are no errors, use the form data, 6) after using the form data (which can produce more errors), if there are no errors, perform a redirect to the exact same url of the current page to cause a get request for that page.
3. use 'require' for things your code must have for it to work.
4. include/require are not functions. the () around the filename are unnecessary clutter and should be removed.
5. don't copy variables to other variables for nothing. just use the original variables that data is in. for data fetched from a query, fetch it into a uniquely named array variable, then use elements in this array variable throughout the rest of the code.
6. some major comments in your code, describing what each section is doing, would be helpful. e.g. get the name for the current/selected teacher, get the schedule for the current/selected teacher on the selected day and period, ...
7. you would query the single schedule table, using a SELECT COUNT(*) ... query, to find if the selected substitute teacher (faculty_number) is available on the selected day and period. if this query doesn't match any data (the COUNT(*) term is zero), it means that the selected substitute teacher is not available.
8. you would then query the single schedule table to get the row of data for the current/selected teacher (faculty_number) on the selected day and period, to build the sms message.
9. you should use a prepared query any time you are supplying any dynamic value to the query when it gets executed.
10. if using a prepared query with the mysqli extension seems overly complicated, this would be a good time to switch to the much simpler and more modern PDO extension.
11. type='text/javascript' is no longer used in the <script> tag.

[gizmola]

On 6/11/2023 at 8:30 AM, ginerjm said:

If they are teaching to code like this in college, get another college.

ginerjm: everyone starts somewhere.  A student learning to code is only going to provide code representative of where they are at a particular point in time.  This is likely for an introductory class, and not a bootcamp or professional certification.

[ginerjm]

Actually I was most appalled at the approach to combine multiple instructions into one statement making it difficult for a new user to find or recognize any error that may occur within that stage of operation.  For an instructor (or syllabus) to demonstrate this method is definitely no way to help a beginner to understand what is going on.

Just my $.02

[gizmola]

On 6/13/2023 at 8:25 AM, ginerjm said:

Actually I was most appalled at the approach to combine multiple instructions into one statement making it difficult for a new user to find or recognize any error that may occur within that stage of operation.  For an instructor (or syllabus) to demonstrate this method is definitely no way to help a beginner to understand what is going on.

Just my $.02

It's alright my friend, I don't disagree, but at the point someone admits they are a student, we have to expect novice code that will do all sorts of things in a way that no pro coder would ever do.