0

so I know this is a frequently asked question on stackoverflow but I'm tearing my hair out trying to get this to work. I'm currently building a blogging site and with it I'm trying to develop a real-time notification system with AJAX following an online tutorial. The AJAX code and everything else works, but the SQL used in the tutorial doesn't make use of prepared statements and I'm unsure on how to edit the code to make use of prepared statements. Below is a copy of the code itself:

$query = "SELECT tblblogpost.heading
FROM tblblogpost, tblconfirmedposts, tbluser
WHERE tblblogpost.postID=tblconfirmedposts.postID
AND tblblogpost.userID=tbluser.userID
AND tbluser.userID=$uID
AND tblconfirmedposts.confirmed=1
AND tbluser.position=1
ORDER By tblblogpost.postDate DESC LIMIT 10";
$result = mysqli_query($mysqli, $query);
$output = '';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{
  $output .= '
  <li>
  <a href="#">
  <strong>'.$row["comment_title"].'</strong><br />
  </a>
  </li>
  ';
}
}
else{
    $output .= '<li><a href="#" class="text-bold text-italic">No Notifications Available</a></li>';
}

What I have tried so far, is to re-write the entire query using mysqli_prepare and making use of mysqli_stmt_bind_param to bind the parameters. However, this has not worked. If needed I can post a copy of what I tried using mysqli_prepare.

This code does work well, but I dislike that I have variables inside of my SQL string, I'd much prefer to use prepared statements. If I can have a bit of assistance in just the re-writing aspect, I would be very much grateful.

Also, I do understand that my SQL statement is messy. I'm not very well versed in it, but for what I'm doing the query works fine. I would really like to focus on trying to rewrite the code to make use of prepared statements so that I can replace the $uID with a "?" and bind the variable instead.

$query = "SELECT tblblogpost.heading
FROM tblblogpost, tblconfirmedposts, tbluser
WHERE tblblogpost.postID=tblconfirmedposts.postID
AND tblblogpost.userID=tbluser.userID
AND tbluser.userID=?
AND tblconfirmedposts.confirmed=1
AND tbluser.position=1
ORDER By tblblogpost.postDate DESC LIMIT 10";

if($stmt=mysqli_prepare($mysqli, $query)){
            //bind post ID for the query
            //mysqli_stmt_bind_param($stmt, "i", $uID);

            //execute query
            mysqli_stmt_execute($stmt);

            //get results
            $result=mysqli_stmt_get_result($stmt);

            while($row=mysqli_fetch_array($result))
            {

              $output .= '
              <li>
              <a href="#">
              <strong>'.$row["heading"].'</strong><br />
              </a>
              </li>
              ';

            }//end of while loop
        }//end of stmt
SkellyBro
  • 1
  • 1
  • 3
    I would also recommend to switch over to PDO. It is so much easier to use. – Dharman Jun 15 '20 at 23:25
  • You might also be interested in reading up on [SQL joins](https://www.tutorialspoint.com/sql/sql-inner-joins.htm). You can run into some unexpected problems trying to use multiple tables in your `FROM` clause – Phil Jun 15 '20 at 23:28
  • @phil I included a copy of the code I tried using `mysqli_prepare` – SkellyBro Jun 15 '20 at 23:31
  • @Dharman thank you! I'll read up on the linked post now and I'll also look into PDO and SQL Joins! – SkellyBro Jun 15 '20 at 23:31
  • You should un-comment `//mysqli_stmt_bind_param($stmt, "i", $uID);`, it's pretty important – Phil Jun 15 '20 at 23:32
  • @Phil My mistake, I un-commented the line, but unfortunately the code still does not work. – SkellyBro Jun 15 '20 at 23:34
  • 1
    What do you mean does not work? What happens? Do you get an error? [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Jun 15 '20 at 23:36
  • @Dharman Well, honestly. Nothing, Using the very first SQL query and the AJAX code together, I am able to see the headings of all the blogposts a user has made. But if I use `mysqli_prepare` code, I'm no longer able to see any headings via the AJAX code, even though the SQL string for both queries is the same and the `while` code is also the same. – SkellyBro Jun 15 '20 at 23:42
  • 1
    @Dharman I'll read through that error message post and see what I can find to help. – SkellyBro Jun 15 '20 at 23:43

0 Answers0