How do i getEvents based on the logged in user with Ajax and PHP SQL statement?

Question

I have a calendar application where users can submit calendar events(day, night, unavailable to the title column of my DB) for selected dates. More like an availability booking. I have a database table called events with id, username, name, title, date and status columns(all status =1). This function below outputs all event inputs by all users on the dates but i want to be able to output only data for the $_SESSION user. Thanks

function getEvents(date){
                $.ajax({
                    type:'POST',
                    url:'functions.php',
                    data:'func=getEvents&date='+date,
                    success:function(html){
                        $('#event_list').html(html);
                    }
                });
    
                // Add date to event form
                $('#event_date').val(date);
            } 




 function getEvents($date = ''){
            $date = $date?$date:date("Y-m-d");
        
            $eventListHTML = '<h2 class="sidebar__heading">'.date("l", strtotime($date)).'<br>'.date("F d", strtotime($date)).'</h2>';
        
            // Fetch events based on the specific date
            global $db;
              $stmt = $db->prepare("SELECT * FROM events WHERE date = ? AND username = ?");
    $stmt->bind_param("ss", $date, $username);
    $stmt->execute();
    $result = $stmt->get_result();
            if($result->num_rows > 0){
                $eventListHTML .= '<ul class="sidebar__list">';
                $eventListHTML .= '<li class="sidebar__list-item sidebar__list-item--complete">Availability</li>';
                $i=0;
                while($row = $result->fetch_assoc()){ $i++;
                    $eventListHTML .= '<li class="sidebar__list-item"><span class="list-item__time">'.$i.'.</span>'.$row['title'].'</li>';
                }
                $eventListHTML .= '</ul>';
            }
            echo $eventListHTML;
        }

When i do this "SELECT title FROM events WHERE date = '".$currentDate."' AND username = '".$username."' AND status = 1" it doesnt output any events() even though my $_SESSION username is stored in the db — Sam, Jun 21 '22 at 14:57
You talk about events id, username, name, title, date and status. First of all, I bet you should use iduser instead of username. In second place... you should add `data:'func=getEvents&date='+date+'iduser='+loggeduser` and use it in the query that you have in `getEvents` function (`"[...] WHERE date = '".$date."' AND status=1 AND iduser=$loggeduser"` — VFG, Jun 21 '22 at 15:01
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements 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 / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Jun 21 '22 at 15:17
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [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. If you learnt your current technique from a tutorial or book, please don't use it again. — ADyson, Jun 21 '22 at 15:17
`even though my $_SESSION username is stored in the db `...ok and how have you debugged that? Have you verified that `$username` contains a valid value, for example? You don't seem to have tried to investigate it at all. — ADyson, Jun 21 '22 at 15:18
So either there's actually some hidden difference between the values or there are perhaps genuinely no rows with that username _and_ status of 1 _and that date matching. We can't see your database or the Session value so we can't be 100% certain. Or it could be a SQL formatting issue - switch to using prepared statements first of all, as I advised, and see if that helps. If nothing else it will make your code more secure and reliable in general, so it's a good use of time either way. — ADyson, Jun 21 '22 at 15:36

score 1 · Answer 1 · answered Jun 23 '22 at 15:05

I finally figured it out. It wasnt a problem with the Select statement. My session variable wasn't being accessed by my AJAX-called PHP script. Meaning I needed to set the sessions variable on both the page that the Ajax functions reside and my index page.

if ( is_null($_SESSION) || !$_SESSION )
{
    session_start();
}//doesnt leave a warning that sesssion has been called previously

This way, the session_start() is being called for each request.

How do i getEvents based on the logged in user with Ajax and PHP SQL statement?

1 Answers1