How to link SQL Select statements to a HTML button using PHP

Question

I have connected to a MySQL database using PHP. The database is about books, which has different columns such as ID, title, author and price. I'm now trying to create links "View Table", "View by title" and "View by Author" so that users can use these links to view the content of my table respectively.

I have created the three buttons, but now I can't figure out how to make them show the columns separately. Below is my php code:

    <!DOCTYPE html>
<html>
<head>
    <title>Books</title>
    <link rel="stylesheet" type="text/css" href="bookstyle.css">
</head>
<body>
<button>View Table</button>
<button>View by Title</button>
<button>View by Author</button>

<table>
    <tr>
        <th>Id</th>
        <th>Title</th>
        <th>Author</th>
        <th>Price</th>
    </tr>
<?php
$conn = mysqli_connect("localhost", "root", "", "my_book_collection");

// Check connection, if failed show error
if ($conn-> connect_error) {
    die("Connection failed:". $conn-> connect_error);
}
$sql = "SELECT id, title, author, price from books";
$result = $conn -> query($sql);

if ($result -> num_rows > 0){
// Output data for each row
    while ($row = $result -> fetch_assoc()){
        echo "<tr><td>". $row["id"]. "</td><td>". $row["title"]. "</td><td>". $row["author"]. "</td><td>". $row["price"]. "</td></tr>";
    }
echo "</table>";
}
else {
    echo "0 results";
}$conn-> close();
?>

</table>
</body>
</html>

Daniel Williams · Accepted Answer · 2018-09-20 05:00:35Z

1

You need to tell your SQL statement what column to order by. You can pass this via the link. eg

<th><a href="?order=author">Author</a></th>

then in your SQL change to

'SELECT id, title, author, price FROM books ORDER BY '.$_GET['order'].' ASC'

BUT before you implement this, please read up on SQL injection attacks and modify your code to control what value is passed to SQL.

Here are the basics:

if ($_GET['order'] == 'author') $order = 'author';
if ($_GET['title'] == 'title') $order = 'title';
etc.

The variable just needs some sanitation.

Then change your SQL statement to:

'SELECT id, title, author, price FROM books ORDER BY '.$order.' ASC'

edited Sep 20, 2018 at 5:00

answered Sep 20, 2018 at 3:38

Daniel Williams

2,3179 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alphaque Over a year ago

Sorry i'm still confused. What do you mean "then in your SQL change to"? All i'm working with is a php file. Do you mean in phpMyAdmin? Also, I will read up on SQL Injections thanks :)

Daniel Williams Over a year ago

Change your SQL statement.

Alphaque Over a year ago

When I change it to the statement you provided I get this error... "Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)"

Daniel Williams Over a year ago

Ah sorry, the GET variable needs to be in quotes SELECT id, title, author, price FROM books ORDER BY '$_GET['order']' ASC

Alphaque Over a year ago

Thanks for you help, But I figured out a different way to get it done. Bless people like you for taking time to help though :)

Alphaque · Accepted Answer · 2018-09-20 05:47:58Z

1

I figured it out on my own. This is probably not the best way to do it, but what I did was create 2 seperate .php files, one for author and one for titles. I removed the code that didn't relate to author or title (for example: id and price). So basically, I just created a new table for each and then made links to access each table seperately. Each .php file has 3 links "View Table" "View Authors" "View Titles".

answered Sep 20, 2018 at 5:47

Alphaque

231 silver badge11 bronze badges

1 Comment

Daniel Williams Over a year ago

This is a terrible way to do it!

Collectives™ on Stack Overflow

How to link SQL Select statements to a HTML button using PHP

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related