Php mysql refresh data

Tekky · July 24, 2022

Hello, i have a project for school. I have to display data from a mysqldata base corresponding of today date. And when i click on a button, the data must change to yesterday data instead. i already managed to display the data corresponding of the daily date. But i can't think of a way for displaying previous day data. I'm very new with php, i usually only do front-end.

I'm not asking you to do my work, but if you could give me some tracks i could follow or should look at to begin, i would really appreciate it!

thank you.

Here's my index.php code :

<!DOCTYPE html>
<html>
    

<head>
	<title>Mon chemin de vie</title>
    <!-- CSS only -->
    <link rel="stylesheet" type="text/css" href="css/navbar.css">
	<link rel="stylesheet" type="text/css" href="css/cercles.css">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
    <!-- JS script only -->
    <script src="https://kit.fontawesome.com/dacae46ffa.js" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/js/bootstrap.bundle.min.js" integrity="sha384-pprn3073KE6tl6bjs2QrFaJGz5/SUsLqktiwsUTF55Jfv3qYSDhgCecCxMW52nD2" crossorigin="anonymous"></script>
    
</head>
<?php
  $temp0=1;
  $temp1=0;
  // Configure la date local et récupere la date d'aujourd'hui
  date_default_timezone_set('Europe/Paris');
  $date = date('d/m/Y');
  echo $date;
  // Se connecte a la base de donnée
  $host = 'localhost'; 
  //identifiants local dev :
  //$host = 'localhost'; 
  $dbname = 'site';
  $username = 'client';
  $password = 'client';
    $dsn = "mysql:host=$host;dbname=$dbname"; 
    // récupérer les informations correspondant a la date d'aujourd'hui
    $currentdate = "SELECT * FROM cercles WHERE dates = '$date' "; 
    try{
    $pdo = new PDO($dsn, $username, $password);
    $stmt = $pdo->query($currentdate);
    if($stmt === false){
      die("Erreur");
    }
    
    }catch (PDOException $e){
      echo $e->getMessage();
    }

  ?>
  <body>
      <!-- Navbar only -->
  <nav class="navbar bg-light ">
      <div class="container-fluid">
        <a class="navbar-brand" href="#">Mon chemin de vie</a>
        <button class="navbar-toggler" type="button" data-bs-toggle="offcanvas" data-bs-target="#offcanvasNavbar" aria-controls="offcanvasNavbar">
          <span class="navbar-toggler-icon"></span>
        </button>
        <div class="offcanvas offcanvas-end" tabindex="-1" id="offcanvasNavbar" aria-labelledby="offcanvasNavbarLabel">
          <div class="offcanvas-header">
            <h5 class="offcanvas-title" id="offcanvasNavbarLabel">Nos pages</h5>
            <button type="button" class="btn-close" data-bs-dismiss="offcanvas" aria-label="Close"></button>
          </div>
          <div class="offcanvas-body">
            <ul class="navbar-nav justify-content-end flex-grow-1 pe-3">
              <li class="nav-item">
                <a class="nav-link active" aria-current="page" href="#">Accueil</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Naturologie</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Agenda 2023</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Contacts et prestations</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Alphabets sacrés</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Conseils planétaires</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Prénoms</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Lunologie</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Décodage de soi</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Numérologie</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Angéologie</a>
              </li>
              <li class="nav-item">
                  <a class="nav-link active" aria-current="page" href="#">Astrologie</a>
              </li>
            </ul>
            </div>
        </div>
      </div>
    </nav>
    <?php while($row = $stmt->fetch(PDO::FETCH_ASSOC)) : ?>
      <?php
      // On incrémente l'id de 1 ou moins 1 pour récupérer le jour d'avant ou après
    $jouravant=($row['id'])-1;
    $jourapres=($row['id'])+1;

      // Fonction boutton jour avant
    $previousdate = "SELECT dates FROM cercles WHERE id = '$jouravant' "; 
    try{
    $pdo = new PDO($dsn, $username, $password);
    $previousdate1 = $pdo->query($previousdate);
    if($previousdate1 === false){
      die("Erreur");
    }
    
    }catch (PDOException $e){
      echo $e->getMessage();
    }

    $afterdate = "SELECT dates FROM cercles WHERE id = '$jourapres' "; 
    try{
    $pdo = new PDO($dsn, $username, $password);
    $afterdate1 = $pdo->query($afterdate);
    if($afterdate1 === false){
      die("Erreur");
    }
    
    }catch (PDOException $e){
      echo $e->getMessage();
    }
  ?>
      <?php while($row1 = $previousdate1->fetch(PDO::FETCH_ASSOC)) : ?>
          <?php while($row2 = $afterdate1->fetch(PDO::FETCH_ASSOC)) : ?>
    <!-- Cercles only -->
    <div class="cercles">
      <div class="d-flex justify-content-around">
          <div class="quote-wrapper">
              <blockquote class="text circle-bg-rose">
                <p>Énergie <?php echo htmlspecialchars($row['Energie']); ?></p>
              </blockquote>
            </div>
            <div class="quote-wrapper">
              <blockquote class="text circle-bg-bleu">
                <p>Jeudi </br><span class="planets"><?php echo htmlspecialchars($row['Planete']); ?></span></p>
              </blockquote>
            </div>
            <div class="quote-wrapper">
              <blockquote class="text circle-bg-vert">
                <p>Jardin Conseil Pratique </br><span class="small-text"><?php echo utf8_encode($row['JardinConseil']); ?></span></p>
              </blockquote>
            </div>
      </div>

      <div class="d-flex justify-content-around flex-mid">
        <div class="quote-wrapper mid">
          <blockquote class="text circle-bg-violet">
            <p><?php echo htmlspecialchars($row1['dates']); ?><form method="post"><input type="submit" name="button1"class="btn btn-link fa fa-arrow-left arrow-button" value="Button1" /></p></form>
          </blockquote>
        </div>
        
        <div class="big-quote-wrapper">
          <blockquote class="text circle-bg-red">
            <p><?php echo htmlspecialchars($row['dates']); ?>
              <img src="assets/img/image-1.png"class="text-4xl" width="320" height="90" loading="lazy">
              <?php echo htmlspecialchars($row['Lune']); ?>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<?php echo htmlspecialchars($row['Soleil']); ?>
              <?php echo utf8_encode($row['TypeLune']); ?>
            </p>
          </blockquote>
        </div>
        <div class="quote-wrapper mid">
          <blockquote class="text circle-bg-violet">
            <p><?php echo htmlspecialchars($row2['dates']); ?><button class="btn btn-link fa fa-arrow-right arrow-button"></button></p>
          </blockquote>
        </div>
      </div>

      <div class="d-flex justify-content-around">
        <div class="quote-wrapper">
          <blockquote class="text circle-bg-ange">
            <p>Ange</br></br><?php echo htmlspecialchars($row['Ange']); ?></p>
          </blockquote>
        </div>
        <div class="quote-wrapper">
          <blockquote class="text circle-bg-beige">
            <p>Saint</br></br><?php echo htmlspecialchars($row['Saint']); ?></p>
          </blockquote>
        </div>
        <div class="quote-wrapper">
          <blockquote class="text circle-bg-vert-clair">
            <p>Conseil du jour</br><span class="small-text"><?php echo utf8_encode($row['ConseilJour']); ?></span></p>
          </blockquote>
        </div>
      </div>
  </div>
  <?php endwhile; ?>
  <?php endwhile; ?>
  <?php endwhile; ?>
</body>
</html>

ginerjm · July 24, 2022

Have you script subtract 1 from the date currently being used. Simple date arithmetic. Read up on the date functions in the PHP manual.

Barand · July 24, 2022

1 hour ago, Tekky said:
$date = date('d/m/Y');

That is your main problem - or it will be. Database dates in that format are totally useless.

Always store dates in Y-m-d format. Such dates can be sorted/compared and can be used by the inbuilt datetime function.

To get today's data...

SELECT * FROM cercles WHERE dates = curdate()

To get yesterday's...

SELECT * FROM cercles WHERE dates = curdate() - interval 1 day

The above will not work with your d/m/Y dates.

Tekky · July 24, 2022

2 hours ago, Barand said:
That is your main problem - or it will be. Database dates in that format are totally useless.

Always store dates in Y-m-d format. Such dates can be sorted/compared and can be used by the inbuilt datetime function.

To get today's data...
SELECT * FROM cercles WHERE dates = curdate()
To get yesterday's...
SELECT * FROM cercles WHERE dates = curdate() - interval 1 day
The above will not work with your d/m/Y dates.

Thank you a lot, this make a lot more sense, and i'll fix that right now. But my other problem is that, my php is loading when the page is loaded, so basically :

step 1 -> the page load, and so php load informations from :

SELECT * FROM cercles WHERE dates = curdate()

step 2 -> The user have two buttons, one to load data from the previous day, and another one to load the data on the next day. To do this, i can use :

SELECT * FROM cercles WHERE dates = curdate() - interval 1 day

(or + interval 1 day instead for the second button).

But in my index.php, i don't know how to reload all the data on the button click since i didn't make it a function? I tried to make it a function but it kinda broke all the html since it's in a while and so the html is only loaded if the database can return the values asked.. I don't know if i made my problem clear enough for you to understand..

Anyways, thank you already for the fast answer, i really appreciate it !

Barand · July 24, 2022

Here's an alternative using PHP to do to the date arithmetic

<?php

// create pdo connection here

$date = $_GET['date'] ?? date('Y-m-d');          // date defaults to today if no input
$days = $_GET['days'] ?? 0;                      // days defaults to 0

$dt = new DateTime($date);
$newdate = $dt->modify("$days days")->format('Y-m-d');

$disabled = $newdate == date('Y-m-d') ? 'disabled' : '';    // if today, disable tomorrow button

$res = $pdo->prepare("SELECT * FROM cercles WHERE dates = ? ");
$res->execute([$newdate]);

?>
<!doctype html>
<html>
<head>
<title>Sample</title>
<style type="text/css">

</style>
</head>
<body>
<form>
    <button name='days' value='-1'>&lt;</button>
    <input type='date' name='date' value='<?=$newdate?>' >
    <button name='days' value='1' <?=$disabled?> >&gt;</button>
</form>
<table>
    <?php
        // output your data rows here
    ?>
</table>
</body>
</html>

Tekky · July 25, 2022

Hello, thank you a lot for the fast and the quality of the answer. I really appreciate it. It really helped me to understand what i was missing. But you didn't used any function in the small alternative you showed. However, i believe a function is necessary to have the buttons in different <div> and so different <form> ? Like this : (sorry for the bad quality drawing).

Edited July 25, 2022 by Tekky

Barand · July 25, 2022

What would you want this function to do?
Why "different forms"?

Tekky · July 25, 2022

Just now, Barand said:

What would you want this function to do?

Why "different forms"?

Yes, i asked myself the same questions.. I figured it out, all i has to do was to setup the div in the same form, and put as hidden the input and then it worked fine. Thank you for all the help, i'll mark as solution your comments and we can close the topic.

Thank you again. Have a great day

Sign In

Php mysql refresh data

Recommended Posts

Tekky

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Tekky

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Tekky

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Tekky

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information