node.jspostgresqlexpressejs

Why can't I filter posts in my postgreSQL database by author? (Issue Resolved)


So I have a service where I let users post book reviews. I myself have posted two. In my database I keep track of the user names that they input when submiting a post.

for some weird reason, it will let me filter reviews on my 'home-page' for any author name that I created locally on my own machine, but not for any authors inputted from foreign users. Any one have any idea?

here is the relevant code from my backend:

//Get All Posts
app.get("/", async (req, res) =>{

    let data = await db.query("SELECT * FROM posts");
    posts = data.rows;
    console.log(posts);
    res.render("index.ejs",{
        posts: posts,
    });
});

//Get all posts in order of ascending ID
app.get("/asc", async (req, res) =>{

    let data = await db.query("SELECT * FROM posts ORDER BY id ASC");
    posts = data.rows
    console.log(posts)
    res.render("index.ejs",{
        posts: posts,
    });
});

// Get all posts in order of descending ID
app.get("/desc", async (req, res) =>{

    let data = await db.query("SELECT * FROM posts ORDER BY id DESC");
    posts = data.rows
    console.log(posts)
    res.render("index.ejs",{
        posts: posts,
    });
});

// Load the new entry page
app.get("/add", async (req, res) =>{
    res.render("new.ejs")
});

// Add entry to database
app.post("/submit", async (req, res) =>{
    let author = req.body.author;
    let book = req.body.book;
    let review = req.body.review;
    let rating = req.body.rating;

    const result = await axios.get(`https://openlibrary.org/search.json?q='%'||${book}||'%'&limit=1`);
    let coverID = result.data.docs[0].cover_edition_key;
    
    const fullDate = new Date()
    const day = fullDate.getDate()
    const month = fullDate.getMonth() + 1
    const year = fullDate.getFullYear()
    let date = `${day}/${month}/${year}`

    await db.query("INSERT INTO posts (author, date, descr, rating, book_auth, cover_id) VALUES ($1, $2, $3, $4, $5, $6)",
        [author, date, review, rating, book, coverID] );


    res.redirect("/");
});

//Load the update page
app.get("/update:id", async (req, res) =>{
    let id = req.params.id;
    let data = await db.query("SELECT * FROM posts WHERE id = ($1)",[id]);
    let posts = data.rows[0];
    res.render("new.ejs", {
        posts: posts,
    });

    console.log(posts)
});

//Update post
app.post("/edit:id", async (req, res) =>{
    let id = req.params.id;
    let author = req.body.author;
    let book = req.body.book;
    let review = req.body.review;
    let rating = req.body.rating;
    await db.query("UPDATE posts SET author = ($1), descr = ($2), rating = ($3), book_auth = ($4) WHERE id = ($5)", [author, review, rating, book, id]);
    res.redirect("/");
})

//Filter by author of Review
app.get("/author/:auth", async (req, res) =>{
    let author = req.params.auth;
    let data =  await db.query("SELECT * FROM posts WHERE author ILIKE ($1) AND author IS NOT NULL AND author != ''", [author]);
    let posts = data.rows;
    console.log(author);
    res.render("index.ejs", {
        posts: posts,
    });
});

//Delete post
app.get("/delete:id", async (req, res) =>{
    let id = req.params.id;
    await db.query("DELETE FROM posts WHERE id = ($1)", [id]);

    res.redirect("/");
});

here is the code from my index.EJS:

<% posts.forEach(post => { %>
    
<section class="Posts">
    <div class="leftSide">
      <!--If coverID = Null, render a placeholder cover saved in public images folder-->
      <% if(post.cover_id === null){ %>
        <img class="book-cover" src="/images/images.png">
      <!--Otherwise, fetch the cover for the book using the open library API-->
      <% } else { %>
        <img class="book-cover" src="https://covers.openlibrary.org/b/olid/<%= post.cover_id %>-L.jpg" alt="Book Cover">
      <% } %>
    </div>
      
      <!--On right side, EJS templating displays the details for each post-->
    <div class="rightSide">
        <div class="postContainer">
            <a class="authorTag" href="/author/<%= post.author %>"><h3 class="author"><%= post.author %>'s</a> Review of:</h3>
            <h2 class="book-author"><%= post.book_auth %></h2>
            <div class="divider"></div>
            <p class="review"><%= post.descr %></p>
            <div class="divider"></div>
            <div class="rating-updateBox">
              <p class="rating">Rating: <%= post.rating %>/10</p>
              <a href="/update<%= post.id %>"><button class="update-Button">Update</button></a>
            </div>
            <div class="date-deleteBox">
              <p class="date"><%= post.date %></p>
              <a href="/delete<%= post.id %>"><button class="delete-Button">Delete</button></a>
            </div>
        </div>
    </div>
</section>

<% });%>

here is my new.EJS where new posts are added

<!--If there is a post, render the update post form instead of the new post form-->
    <% if(locals.posts){ %>

    <form action="/edit<%= posts.id %>" method="post">
        <div id="infoBox">
            <p class="info">Your Name</p>
            <p class="info">Book Name</p>
        </div>
        <div id="auth-titleBox">
          <input id="newAuthor" type="text" name="author" placeholder="Write YOUR name here." required value="<%= posts.author %>">
          <input id="newTitle" type="text" name="book" placeholder="Write the name of the book here." required value="<%= posts.book_auth %>">
        </div>
        <div class="divider"></div>
        <textarea id="newDesc" name="review" placeholder="Write your review here." required><%= posts.descr %></textarea>
        <div class="divider"></div>
        <input id="rating" name="rating" type="number" min="0" max="10" placeholder="Rating" required value="<%= posts.rating %>">
        <input id="submit" type="submit">
    </form>

    <% } else { %>
    
    <!--New post form-->    
    <form action="/submit" method="post">
        <div id="infoBox">
            <p class="info">Your Name</p>
            <p class="info">Book Name</p>
        </div>
        <div id="auth-titleBox">
          <input id="newAuthor" type="text" name="author" placeholder="Write YOUR name here." required>
          <input id="newTitle" type="text" name="book" placeholder="Write the name of the book here." required>
        </div>
        <div class="divider"></div>
        <textarea id="newDesc" name="review" placeholder="Write your review here." required></textarea>
        <div class="divider"></div>
        <input id="rating" name="rating" type="number" min="0" max="10" placeholder="Rating" required>
        <input id="submit" type="submit">
    </form>

    <% } %>

When displaying the post details on the front end, each post has the author of the review's name displayed at the top. This name is a href that includes the post's author's name as a parameter. Then, in the backend, I parse that parameter and render all the posts from my databse where the author name matches the parsed parameter. This works, but only for authors I have created on my machine. Not for any author names other users created, despite the fact that those author values were saved to my database as I can see them in pgAdmin in the table.


Solution

  • I found the issue. Just thought I'd post this incase anyone has the same issue.

    The users inputted their author name with a space after the last character in the name. This caused the issue. I just added a .trim() method to the end of my variable that parsed the author input in my route that added posts to the database. This has resolved the problem.

    // Add entry to database
    app.post("/submit", async (req, res) =>{
        let author = req.body.author.trim();
    

    Thanks