PHP sort database by domain name

Question

I have a problem, I have to take the domain name from the database, and create a knokpu with which I can sort the database by this domain, that is, if I clicked on the gmail button, then users with gmail should be sorted, if there are yahoo users then when you click on the yahoo button, the sort will also be performed. The problem is that I don't know how to properly associate a button with sorting ... I will be very grateful for your help.

Here is my php code.

<!DOCTYPE html>
<html>
<head>
  <title>Display all records from Database</title>
</head>
<body>

<h2>All emails</h2>

<table border="2">
  <tr>
    <td>Sr.No.</td>
    <td>E-mail</td>
    <td>Delete</td>
    <td>CSV</td>
  </tr>
  <a href="?orderBy=email">
    <button>Sort By E-mail</button>
</a>
<a href="?orderBy=date">
    <button>Sort By Date</button>
</a>
<a href="?orderBy=">
    <button>Export as CSV</button>
</a>
</br>

<?php
include "php/server.php"; // Using database connection file here
$orderBy = array('email', 'date'); // create array of sort options
$order='date';  // default sort option
if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
  $order = $_GET['orderBy'];
}                              // change sort option depending on $order
$query = 'SELECT * FROM users ORDER BY '.$order;
$records = mysqli_query($db, $query);     // fetch data from database

$dom=array();


include "php/search.php";  //include search engine
if(isset($_POST["submit"])){        //if button is pressed fecth data from search
  $records = mysqli_query($db, $searchquery); 
}else{      //if not fecth data from db
  $records = mysqli_query($db, $query); 
} 
while($data = mysqli_fetch_array($records))
{
  $parts = explode('@', $data['email']);
  $domain = array_pop($parts);

?>
  <tr>  
    <td><?php echo $data['email']; ?></td>  
    <td><?php echo $data['date']; ?></td>  
    <td><a href="php/delete.php?id=<?php echo $data['id']; ?>">Delete</a></td>
    <td><a><input type="checkbox" id="horns" name="horns"></td>  
    <td><?php echo $domain; ?></td>
  </tr> 
  <tr>
  <input type="submit" name="<?php $domain; array_push($dom,$domain)?>" value="<?php echo $domain; ?>">
</tr>

<?php
}
var_dump($dom);
?>
<h3>Emails is sorting by:<?php echo $order ?></h3>
</table>
</body>
</html>

Design:

Pass a parameter that tells you what to sort by, just like you're doing for date and email. Why do you feel this would be any different? — El_Vanja, Jan 30 '21 at 11:41
@El_Vanja I just can't think of how to make the search for the given domain name work when the buttons are clicked. — Natrien, Jan 30 '21 at 12:49
@MohdAlomar I want that when loading the database, I get all kinds of domains that are in it, buttons with the name of the domains appear, and by clicking them I should get records only with such a domain.(So if I click on gmail.com,shout appear only emails with domain gmail.com) — Natrien, Jan 30 '21 at 13:14
You need to use [pattern matching](https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html). Please use parametrized query to avoid [SQL Injection](https://www.php.net/manual/en/security.database.sql-injection.php). Please visit this answer to learn how to [avoid SQL Injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Mohd Alomar, Jan 30 '21 at 13:20

Hanief Han · Accepted Answer · 2021-02-01T02:40:38.617

I think what you mean is, you want to filter the data based on which the email provider button that clicked?

you can add the HTML like this:

...
<a href="?filterBy=gmail.com">
  <button>gmail.com</button>
</a>
<a href="?filterBy=inbox.lv">
  <button>inbox.lv</button>
</a>
<a href="?filterBy=yahoo.lv">
  <button>yahoo.lv</button>
</a>
...

In PHP you need to update this line:

...
if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
  $order = $_GET['orderBy'];
}
$filter = "";
if (isset($_GET['filterBy'])) {
  $filter = "WHERE `email` LIKE ?";
}
$query = 'SELECT * FROM users '.$filter.' ORDER BY '.$order;
$prepare = mysqli_prepare($db, $query);
if (isset($_GET['filterBy'])) {
   $param = "%".$_GET['filterBy'];
   $prepare->bind_param('s', $param);
}
$prepare->execute();
// $records = mysqli_query($db, $query); // change this query to:
$records = $prepare->get_result();
...

Your code is widely open for [SQL Injection](https://www.php.net/manual/en/security.database.sql-injection.php). Please read this answer to [prevent SQL Injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Mohd Alomar, Jan 31 '21 at 21:42

PHP sort database by domain name

1 Answers1