Display Data from MYSQL using PHP Dropdown

2022-05-06 19:56 Elterriblebarista imported from Stackoverflow

I need to display data from the 'Members' table based on the selected 'grad_date' from a drop down. I have been trying to do it for a while now but have struggled to get it to work. Currently I have:

    <body>

  <!-- HTML code can reside ABOVE and BELOW the starting and closing PHP tags -->
    Select Grad Date
    <?php
        require_once 'login_name.php'; 
        $db_server = mysqli_connect(...)
        if (!$db_server) die("Unable to connect to MySQL: " . mysqli_error($db_server));

        $sql = "SELECT DISTINCT grad_date FROM Members;";

        //As we have already connected to the database, execute the query stored in the $sql variable
        $result = mysqli_query($db_server, $sql);

        //Check if the query was successfully executed: if the result is NOT FALSE


        //More advanced logic can also be done based on success or failure
        if($result)
        {
            //Create Drop-Down Menu
            echo "<select name='Members'>";

            while($row=mysqli_fetch_assoc($result))
            {
                //Store each SQL row's column name/value pair in a PHP variable
                foreach($row as $key=>$value) ${$key}=$value;

                //Load data onto drop-down menu
                echo "<option value='$grad_date'>$grad_date</option>";
            }
            echo "</select>";

        }

            ?>
            <table>
                <thead>
                    <th> member_id </th>
                    <th> member_first_name </th>
                    <th> member_last_name </th>
                    <th> team_id </th>
                </thead>
                <tbody>
                </tbody>
            </table>
  <!-- HTML code can reside ABOVE and BELOW the starting and closing PHP tags -->
    </body>

I have tried a couple different things to go from here but have not found any success. How can I do this?

See also questions close to this topic

How to upload a video using ACF in WordPress?
I am still at learning phase in WordPress. I cannot figure out in ACF which field type should I choose to upload a video file and which code should I write to show it at the front-end. Please look into the below code. I am using the_field('') but it isn't working. Can anyone help
```
                <video width="954" height="535" controls class="tm-mb-40">

                    <source src="video/wheat-field.mp4" type="video/mp4">   
                      
                    Your browser does not support the video tag.

                </video>
```
delete a table form a database using laravel command
i need to delete a database table using laravel artisan command . not like this command php artisan migrate:rollback --step=5

i need to create like this route or controller code .

Route::get('/clear/database', function () {
```
    Artisan::call('cache:clear');
    return redirect('/');
```
});

. i also try public function dd()
```
{
    Schema::drop('table_name');
}
```
but it not working . gives me error like this SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table table_name)

no foreign key for the table . what should i do ?

thanks in advance!

Creating Sticky Navbar with Drop Down Menu HTML

I am creating a HTML web page which contains a sticky navbar with drop down menu. However, when I created one, the dropdown menu does not works in the sticky navbar and so goes vise versa. below is the screenshot of both the result of the two codes.

*image with dropdown menu but without sticky navbar

*image with sticky navbar but without dropdown menu

below is the code for "image with dropdown menu but without sticky navbar"

    <!DOCTYPE html>
    <html>
    <head>
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-    awesome/4.7.0/css/font-awesome.min.css">
    <style>
    body {margin:0;font-family:Arial}

   .topnav {
    overflow: hidden;
    background-color: #333;
  }

  .topnav a {
    list-style-type: none;
    float: left;
    display: block;
    color: #f2f2f2;
    text-align: center;
    padding: 14px 16px;
    text-decoration: none;
    font-size: 17px;
    position: sticky;
  }



  .active {
    background-color: #04AA6D;
    color: white;
  }

  .topnav .icon {
    display: none;
  }

  .dropdown {
    float: left;
    overflow: hidden;
  }

  .dropdown .dropbtn {
    font-size: 17px;    
    border: none;
    outline: none;
    color: white;
    padding: 14px 16px;
    background-color: inherit;
    font-family: inherit;
    margin: 0;
  }

  .dropdown-content {
    display: none;
    position: absolute;
    background-color: #f9f9f9;
    min-width: 160px;
    box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
    z-index: 1;
  }

  .dropdown-content a {
    float: none;
    color: black;
    padding: 12px 16px;
    text-decoration: none;
    display: block;
    text-align: left;
  }

  .topnav a:hover, .dropdown:hover .dropbtn {
    background-color: #555;
    color: white;
  }

  .dropdown-content a:hover {
    background-color: #ddd;
    color: black;
  }

  .dropdown:hover .dropdown-content {
    display: block;
  }

  @media screen and (max-width: 600px) {
    .topnav a:not(:first-child), .dropdown .dropbtn {
    display: none;
  }
    .topnav a.icon {
      float: right;
      display: block;
    }
  }

  @media screen and (max-width: 600px) {
    .topnav.responsive {position: relative;}
    .topnav.responsive .icon {
      position: absolute;
      right: 0;
      top: 0;
    }
    .topnav.responsive a {
      float: none;
      display: block;
      text-align: left;
    }
    .topnav.responsive .dropdown {float: none;}
    .topnav.responsive .dropdown-content {position: relative;}
    .topnav.responsive .dropdown .dropbtn {
      display: block;
      width: 100%;
      text-align: left;
    }
  }
  </style>
  </head>
  <body>
        <div class="header">
        <h2>Scroll Down</h2>
    <p>Scroll down to see the sticky effect.</p>
  </div>

  <div class="topnav" id="myTopnav">
    <a href="#home" class="active">Home</a>
    <a href="#news">News</a>
    <a href="#contact">Contact</a>
    <div class="dropdown">
      <button class="dropbtn">Dropdown 
        <i class="fa fa-caret-down"></i>
      </button>
      <div class="dropdown-content">
        <a href="#">Link 1</a>
        <a href="#">Link 2</a>
        <a href="#">Link 3</a>
      </div>
    </div> 
    <a href="#about">About</a>
    <a href="javascript:void(0);" style="font-size:15px;" class="icon"     onclick="myFunction()">&#9776;</a>
  </div>

  <div style="padding-left:16px">
    <h2>Responsive Topnav with Dropdown</h2>
    <p>Resize the browser window to see how it works.</p>
    <p>Hover over the dropdown button to open the dropdown menu.</p>
  </div>

  <h3>Sticky Navigation Bar Example</h3>
  <p>The navbar will <strong>stick</strong> to the top when you reach its scroll position.</p>
  <p><strong>Note:</strong> Internet Explorer do not support sticky positioning and Safari         requires a -webkit- prefix.</p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>


  <script>
  function myFunction() {
    var x = document.getElementById("myTopnav");
    if (x.className === "topnav") {
      x.className += " responsive";
    } else {
      x.className = "topnav";
    }
  }
  </script>

  </body>
  </html>

below is the code for "image with sticky navbar but without dropdown menu"

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-    awesome/4.7.0/css/font-awesome.min.css">
<style>


body {
  font-size: 20px;
}
body {margin:0;}

ul {
  list-style-type: none;
  margin: 0;
  padding: 0;
  overflow: hidden;
  background-color: #333;
  position: -webkit-sticky; /* Safari */
  position: sticky;
  top: 0;
}

li {
  float: left;
}

li a {
  display: block;
  color: white;
  text-align: center;
  padding: 16px 20px;
  text-decoration: none;
}

li a:hover {
  background-color: #111;
}
    
/*======================================================================*/

body {
  background-color:white;
}
ul {
  list-style-type: none;
  margin: 0;
  padding: 0;
  overflow: hidden;
  background-color: #38444d;
}

li {
  float: left;
}

li a, .dropbtn {
  display: inline-block;
  color: white;
  text-align: center;
  padding: 14px 16px;
  text-decoration: none;
}

li a:hover, .dropdown:hover .dropbtn {
  background-color: red;
}

li.dropdown {
  display: inline-block;
}

.dropdown-content {
  display: none;
  position: absolute;
  background-color: #f9f9f9;
  min-width: 160px;
  box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
  z-index: 1;
}
    
.dropdown-content a {
  color: black;
  padding: 12px 16px;
  text-decoration: none;
  display: block;
  text-align: left;
}

.dropdown-content a:hover {background-color: #f1f1f1;}

.dropdown:hover .dropdown-content {
  display: block;
}


footer {
  text-align: center;
  padding: 3px;
  background-color: DarkSalmon;
  color: white;
}

</style>
</head>
<body>

<div class="header">
  <h2>Scroll Down</h2>
  <p>Scroll down to see the sticky effect.</p>
</div>

<ul>
  <li><a href="#home">Home</a></li>
    <li><a href="#news">News</a></li>
    <li class="dropdown">
      <a href="javascript:void(1)" class="dropbtn">Dropdown</a>
      <div class="dropdown-content">
        <a href="#">Link 1</a>
        <a href="#">Link 2</a>
        <a href="#">Link 3</a>
      </div>
    </li>
</ul>

<h3>Sticky Navigation Bar Example</h3>
<p>The navbar will <strong>stick</strong> to the top when you reach its scroll position.</p>
<p><strong>Note:</strong> Internet Explorer do not support sticky positioning and Safari requires a -webkit- prefix.</p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>


<footer>
  <p>Author: Hege Refsnes<br>
  <a href="mailto:hege@example.com">hege@example.com</a></p>
</footer>


</body>
</html>

Please i need some help with this as i am new to html and css.

Javascript: Change element class if button has a certain value
```
        <div class="main-div main-div2"> 
          <button id="update-btn" class="btn update-btn"> 
              Up to date
          </button>
        </div>
```
I want to make it so if update-btn has a value of "Up to date", change the div class from "main-div main-div2" to "main-div main-div3". Otherwise, if it's any other value, change it to "main-div main-div2"

What kind of loop would be good for this Javascript function too if I want it to be checking constantly?

Thank you.

Boostrap vertical sizing with different style width and height

I am trying to set verticaly icon on profile photo card inside div to be in the middle in white box(div), but dont know why it doesnt works. When I use style="width: 300px; height: 300px; for div square I can center it on horizontaly, but not verticaly :/ Can someone explain me what I am doing wrong?

Anyway is there any way how to resize bootstrap icons except display-1???

 <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.8.1/font/bootstrap-icons.css">
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.12.9/dist/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    
  <div class="container-sm offset-md">
        <h1 class="p-4 m-4">Nastavení profilu</h1>
        <div class="row">
            <div class="col">
                <div class="card p-4 m-4">
                    Osobní údaje
                    <input type="text" name="name" class="form-control" placeholder="Jméno">
                    <input type="text" name="surname" class="form-control" placeholder="Příjmení">
                    <input type="email" name="email" class="form-control" placeholder="E-Mail">
                    <input type="date" name="name" class="form-control">
                </div>
                
            </div>

            <div class="col-4">
                <div class="card p-4 m-4 bg-light">
                    Profilová fotografie
                    <div class=" square text-center border display-1 m-3 bg-white mx-auto">
                        <i class="bi bi-person-fill align-middle justify-content-center text-secondary"></i>
                    </div>

                    <input type="file" id="customFile" name="file" hidden="">
                        <div class="text-center">
                            <button class="btn btn-success" for="customFile">Nahrát</button>
                            <button type="button" class="btn btn-danger">Smazat</button>
                            
                            <p class="text-muted mt-3 mb-0">Poznámka: Minimální velikost 300px x 300px</p>
                        </div>
                </div>
                
            </div>
        </div>

        <div class="row">
            <div class="col">
                <div class="card p-4 m-4">
                    Změna hesla
                </div>
                
            </div>

            <div class="col">
                <div class="card p-4 m-4">
                    Zobrazit/skrýt podrobnosti
                </div>
            </div>
        </div>
    </div>

Why does this Sql query shows the possible changes but does not implement it?

So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

how to get weeklytotal and yesterday record in mysql in one table

Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

Table name-dailydata-

Sample data

employee_id	date	total
20	2022-04-25	10
20	2022-04-26	20
20	2022-04-27	20
20	2022-04-28	10
20	2022-04-29	20
20	2022-04-30	30
20	2022-04-31	40
20	2022-05-01	50
40	2022-04-26	20

expected output

employee_id	weekly_total	yesterday_record
20	200	40
40	20	0

mysql query to get weekly data

select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'

Join to another table only matching specific records

I have a table of ports:

drop table if exists ports;
create table ports(id int, name char(20));

insert into ports (id, name ) values
  (1, 'Port hedland'), 
  (2, 'Kwinana');

And a table of tariffs connected to those ports:

drop table if exists tariffs;
create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));

insert into tariffs (id, portId, price, expiry ) values
    (1, 2, 11.00, 1648408400), 
    (2, 2, 12.00, 1648508400), 
    (3, 2, 13.00, 1648594800), 
    (4, 2, 14.00, 1651273200),
    (5, 2, 15.00, 2250000000 );

insert into tariffs (id, portId, price, expiry ) values
    (1, 1, 21.00, 1648408400), 
    (2, 1, 22.00, 1648508400), 
    (3, 1, 23.00, 1648594800), 
    (4, 1, 24.00, 1651273200),
    (5, 1, 25.00, 2250000000 );

Each tariff has an expiry.

I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp 1648594700 the right tariff is:

SELECT * FROM tariffs 
WHERE 1648594700 < expiry AND portId = 2
ORDER BY expiry
LIMIT 1

Result:

id  portId  price   expiry
3   2   13.00   1648594800

However, in my application I want to be able to pull in the right tariff starting from the ports record.

For one record, I can do this:

SELECT * FROM ports 
LEFT JOIN tariffs on tariffs.portId = ports.id 
WHERE 1648594700 < tariffs.expiry AND ports.id = 2
LIMIT 1

Result:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800

This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.

What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a price field that matches the constraint above (that is, the record with the highest expiry compared to 1648594700 for each port).

This obviously won't work:

SELECT * FROM ports 
left join tariffs on tariffs.portId = ports.id
where 1648594700 < tariffs.expiry

Since the result of the query, testing with timestamp 1648594700, would be:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
2   Kwinana 4   2   14.00   1651273200
2   Kwinana 5   2   15.00   2250000000
1   Port he 3   1   23.00   1648594800
1   Port he 4   1   24.00   1651273200
1   Port he 5   1   25.00   2250000000

Instead, the result for all ports (before further filtering) should be:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
1   Port he 3   1   23.00   1648594800

Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?

create a select box in which data is populated from selecting another select box and it is editable

I have created two select box. in both, fetching data from database. By selecting first select box the second select box gets auto populated. What I want to do is that this second select box should be editable that is the user can type input also. What I have done now is that used datalist for second select box which is doing the thing, but I want to show the auto populated data as selected option in datalist
Excel Single Use Dropdownlist

My problem is the following. I have two tables. The first table is a guest list where the information on who is the guest and how many beds he need is stored. Screenshot And the second table contains the Apartment names and the number of provided beds. Now I want to make a dropdown list in the guest list table where only Beds are shown that provide the right amount of beds. And after a Apartment is selected it cant be picked a second time. Can someone provide me a code for this solution? Thank you very much!

Related questions

Display Data from MYSQL using PHP Dropdown

See also questions close to this topic