Update the data of an existing table in PHP

I need to make a table of searches made by the user in php. My code fails to encapsulate every new result in the table. I’d like you not to create a new table every time and delete the data, but to gradually add to the data already in the table. Also I have a problem with highlighting accents or special characters in php, how can I fix it? All my data is taken from a column (MySQL database = cherubini, table = lemmi) where it has already been normalized and therefore it works. In my code I have already tried both php and js, but in both cases they conflict with the search results. I am learning web development on my own by asking many questions about my problems, I hope they are useful. My PHP code doesn’t work because there isn’t my table in your own. You’ve to just to change database and table and two queries. The new table data must be added after the last element in the table. This is my code – it contains PHP code and MySQL connection – :

<html>
  <head>
    <link rel="icon" href="https://icons.iconarchive.com/icons/custom-icon-design/flatastic-1/512/search-icon.png">
    <title>Vocabulary searching</title>
    <style>
      body { margin-left: 20px; font-size: 20px; }
      a { text-decoration: none; margin-left: 180px; background: #00ff00; color: red; }
      table { border-collapse: collapse; }
      table, th, tr { border: 1px solid black; background: #00ff00;color: red; width: 336px;}
      .form { margin-top: 30px; }
      #text { width: 336px; }
      #button { visibility: hidden; }
    </style>
  </head>
  <body>
    <form class="form" action="main.php" method="GET">
      <input type="text" name="text" id="text" placeholder="Premi invio per cercare nel vocabolario Cherubini">
      <button type="submit" name="button" id="button"></button>
      <button type="submit" name="reset" id="reset">Pulisci la tabella</button>
    </form>
    <table><tr><th>Cronologia delle ricerche</th></tr></table>
    <?php
      error_reporting(0);
      ini_set('display_errors', 0);
      $conn = mysqli_connect('localhost', 'root', '', 'cherubini');
      $c = 0; $text2 = "";
      if(isset($_GET['button'])) {
        $text2 = $_GET['text'];
        $result = $conn->query("SELECT * FROM lemmi WHERE LemmaNo LIKE '%".$text2."%' ORDER BY LemmaNo");
        $count=$result->num_rows;
        if(empty($count)) {
          $r = $conn -> query("SELECT * FROM history ORDER BY ID");
          while($row = mysqli_fetch_assoc($r)) {
            $history[$c] = $row['Parola'];
            echo "<tr><a href='main.php?text=".$row['Parola']."&button=' title='Cerca'>".$history[$c]."</a></tr>"."<br>"; $c++;
          } $c = 0; echo "<br>";
        }
        else {
        $stmt = $conn->prepare("INSERT INTO history (Parola) VALUES (?)");
        $stmt->bind_param("s", $text2);
        $stmt->execute(); $stmt->close();
        $r = $conn -> query("SELECT * FROM history ORDER BY ID");
        while($row = mysqli_fetch_assoc($r)) {
          $history[$c] = $row['Parola'];
          echo "<tr><a href='main.php?text=".$row['Parola']."&button=' title='Cerca'>".$history[$c]."</a></tr>"."<br>"; $c++;
        } $c = 0; echo "<br>"; }
        while($row = mysqli_fetch_assoc($result)) {
          $modifiedrawData = preg_replace('/'.implode('|', array($text2)).'/i', '<span style="background-color:yellow;"><b>$0</b></span>', $row['LemmaNo']);
          echo "<b>Lemma: </b>".$modifiedrawData."<br>";
          $modifiedrawData = preg_replace('/'.implode('|', array($text2)).'/i', '<span style="background-color:yellow;"><b>$0</b></span>', $row['Definizione']);
          echo "<b>Definizione: </b>".$modifiedrawData."<br><br>";
          $c++; } }
        if(isset($_GET['reset'])) { $conn->prepare("DELETE FROM history")->execute()->close(); }
     ?>
  </body>
</html>

JavaScript
​x
 
<html>  <head>    <link rel="icon" href="https://icons.iconarchive.com/icons/custom-icon-design/flatastic-1/512/search-icon.png">    <title>Vocabulary searching</title>    <style>      body { margin-left: 20px; font-size: 20px; }      a { text-decoration: none; margin-left: 180px; background: #00ff00; color: red; }      table { border-collapse: collapse; }      table, th, tr { border: 1px solid black; background: #00ff00;color: red; width: 336px;}      .form { margin-top: 30px; }      #text { width: 336px; }      #button { visibility: hidden; }    </style>  </head>  <body>    <form class="form" action="main.php" method="GET">      <input type="text" name="text" id="text" placeholder="Premi invio per cercare nel vocabolario Cherubini">      <button type="submit" name="button" id="button"></button>      <button type="submit" name="reset" id="reset">Pulisci la tabella</button>    </form>    <table><tr><th>Cronologia delle ricerche</th></tr></table>    <?php      error_reporting(0);      ini_set('display_errors', 0);      $conn = mysqli_connect('localhost', 'root', '', 'cherubini');      $c = 0; $text2 = "";      if(isset($_GET['button'])) {        $text2 = $_GET['text'];        $result = $conn->query("SELECT * FROM lemmi WHERE LemmaNo LIKE '%".$text2."%' ORDER BY LemmaNo");        $count=$result->num_rows;        if(empty($count)) {          $r = $conn -> query("SELECT * FROM history ORDER BY ID");          while($row = mysqli_fetch_assoc($r)) {            $history[$c] = $row['Parola'];            echo "<tr><a href='main.php?text=".$row['Parola']."&button=' title='Cerca'>".$history[$c]."</a></tr>"."<br>"; $c++;          } $c = 0; echo "<br>";        }        else {        $stmt = $conn->prepare("INSERT INTO history (Parola) VALUES (?)");        $stmt->bind_param("s", $text2);        $stmt->execute(); $stmt->close();        $r = $conn -> query("SELECT * FROM history ORDER BY ID");        while($row = mysqli_fetch_assoc($r)) {          $history[$c] = $row['Parola'];          echo "<tr><a href='main.php?text=".$row['Parola']."&button=' title='Cerca'>".$history[$c]."</a></tr>"."<br>"; $c++;        } $c = 0; echo "<br>"; }        while($row = mysqli_fetch_assoc($result)) {          $modifiedrawData = preg_replace('/'.implode('|', array($text2)).'/i', '<span style="background-color:yellow;"><b>$0</b></span>', $row['LemmaNo']);          echo "<b>Lemma: </b>".$modifiedrawData."<br>";          $modifiedrawData = preg_replace('/'.implode('|', array($text2)).'/i', '<span style="background-color:yellow;"><b>$0</b></span>', $row['Definizione']);          echo "<b>Definizione: </b>".$modifiedrawData."<br><br>";          $c++; } }        if(isset($_GET['reset'])) { $conn->prepare("DELETE FROM history")->execute()->close(); }     ?>  </body></html>

Answer

You can use something like follows;

 <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

JavaScript
 
 <?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB";​// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {  die("Connection failed: " . $conn->connect_error);}​$sql = "SELECT id, firstname, lastname FROM MyGuests";$result = $conn->query($sql);​if ($result->num_rows > 0) {  // output data of each row  while($row = $result->fetch_assoc()) {    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";  }} else {  echo "0 results";}$conn->close();?>​

Source : https://www.w3schools.com/php/php_mysql_select.asp

You can modify this to match with your table name, columns etc.

Advertisement

Answer