jamesclues Posted July 14, 2023 Share Posted July 14, 2023 (edited) I have a school assignment to complete. I am new to PHP I have tried many guides but I seem to be failing I have a MySQL database with the following setup TABLE = data Columns = id, street, restriction, dayplate, timeplate, location, area, days Example data ID street restriction dayplate timeplate location area days 1 Wood St No Ball Games MON - FRI 9am - 4pm REAR SOUTH 12345 2 Peter St No Children MON - SUN 8am - 1pm FRONT NORTH 1234567 I NEED A FILTER LIKE (DAYS) = 12345 only display mon - fri data & (AREA) if select south only display data for south I need to display the data like this STREET RESTRICTION DAY / TIME WOOD ST NO BALL GAMES MON - FRI 9am - 4pm Edited July 14, 2023 by jamesclues Quote Link to comment Share on other sites More sharing options...
ginerjm Posted July 14, 2023 Share Posted July 14, 2023 What have you been taught in your class? Are these tables defined with varchar values for the data? Where is your attempted query statement? Where is your code that you have written to attempt to run the query and then process the resulst? We like to help those who help themselves. We are not your authors. 1 Quote Link to comment Share on other sites More sharing options...
Barand Posted July 14, 2023 Share Posted July 14, 2023 Is that an assigned table design or are you able to make your own changes? I ask because it is need of some normalization... you have repeating data in the days column. you have dependent data (the dayplate is derived from the days). it would be better to store 2 time columns (start and end) instead of the timeplate to make it easier to determine, say, "show streets where there is a restriction at 8:30am." ie WHERE 08:30 BETWEEN start AND end 1 Quote Link to comment Share on other sites More sharing options...
Solution jamesclues Posted July 14, 2023 Author Solution Share Posted July 14, 2023 Basically, what I need to do is have when a user clicks on index.php?beat=NORTH, it displays the north data where they can then filter it by week I have split the week up with 1234567 so if its mon to fri it would be 12345, I need to turn the Search into a dropdown with 3 fields and label them 12345= MON - FRI OR 13456 = MON - SAT or 1234567 = MON - SUN SQL CODE -- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Jul 14, 2023 at 06:04 PM -- Server version: 10.5.19-MariaDB-cll-lve -- PHP Version: 8.1.16 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `0000` -- -- -------------------------------------------------------- -- -- Table structure for table `DATA` -- CREATE TABLE `DATA` ( `id` smallint(10) NOT NULL, `street` varchar(250) NOT NULL, `restriction` varchar(250) NOT NULL, `day` varchar(250) NOT NULL, `time` varchar(250) NOT NULL, `beat` varchar(25) NOT NULL, `week` varchar(15) NOT NULL, `map` varchar(500) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `DATA` -- INSERT INTO `DATA` (`id`, `street`, `restriction`, `day`, `time`, `beat`, `week`, `map`) VALUES (1, 'PETER STREET', 'NO BALL GAMES', 'MON - SUN', '9AM - 5PM', 'NORTH', '1234567', 'PETER STREET NEWCASTLE'), (2, 'BILL STREET', 'NO CARS', 'MON - FRI', '9AM - 3PM', 'SOUTH', '12345', 'BILL STREET NEWCASTLE'), (3, 'WOOL ST', 'NO CARD GAMES', 'MON - SAT', '9AM - 8PM', 'SOUTH', '123456', 'WOOL STREET NEWCASTLE'); -- -- Indexes for dumped tables -- -- -- Indexes for table `DATA` -- ALTER TABLE `DATA` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `DATA` -- ALTER TABLE `DATA` MODIFY `id` smallint(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; php file Code Below <!DOCTYPE html> <html lang="en"> <head> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <meta charset="UTF-8"> <title>SCHOOL STREET RULES</title> <style> #customers { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #customers td, #customers th { border: 1px solid #ddd; padding: 8px; } #customers tr:nth-child(even){background-color: #f2f2f2;} #customers tr:hover {background-color: #ddd;} #customers th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #04AA6D; color: white; } body { margin: 0; font-family: Arial, Helvetica, sans-serif; } .topnav { overflow: hidden; background-color: #333; } .topnav a { float: left; color: #f2f2f2; text-align: center; padding: 14px 16px; text-decoration: none; font-size: 17px; } .topnav a:hover { background-color: #ddd; color: black; } .topnav a.active { background-color: #04AA6D; color: white; } </style> </head> <body> <div class="container"> <div class="topnav"> <a href="index.php">HOME</a> </div> <div style="padding-top:25px"> <center><h2>street rules</h2></center> </div> <div class="row"> <div class="col-md-20 col-md-offset-0" style="margin-top: 2%;"> <div class="row"> <?php $conn = new mysqli('localhost', '0000000', '00000000', '00000000000'); if(isset($_GET['search'])){ $searchKey = $_GET['search']; $sql = "SELECT * FROM DATA WHERE Week LIKE '%$searchKey%'" ; }else $sql = "SELECT * FROM DATA WHERE week LIKE ''" ; $result = $conn->query($sql); ?> <form action="" method="GET"> <div class="col-md-6"> <input type="text" name="search" class='form-control' placeholder="Search By day" value=<?php echo @$_GET['search']; ?> > </div> <div class="col-md-6 text-left"> <button class="btn">Search</button> </div> </form> <br> <br> </div> <table class="table table-bordered" id="customers"> <tr> <th>Street / Location</th> <th>Restriction</th> <th>Day / Time</th> <th>Beat</th> <th>Map</th> </tr> <?php while( $row = $result->fetch_object() ): ?> <tr> <td><?php echo $row->street ?></td> <td><?php echo $row->restriction ?></td> <td><?php echo $row->day ?> | <?php echo $row->time ?></td> <td><?php echo $row->beat ?></td> <td> <a href="https://www.google.com/maps/place/<?php echo $row->map ?>">MAP</a> </td> </tr> <?php endwhile; ?> </table> </div> </div> </div> <P> <CENTER> <footer>© Copyright 2023 SCHOOL TEAM</footer></CENTER></P> </body> </html> Quote Link to comment Share on other sites More sharing options...
jamesclues Posted July 14, 2023 Author Share Posted July 14, 2023 2 hours ago, jamesclues said: Basically, what I need to do is have when a user clicks on index.php?beat=NORTH, it displays the north data where they can then filter it by week I have split the week up with 1234567 so if its mon to fri it would be 12345, I need to turn the Search into a dropdown with 3 fields and label them 12345= MON - FRI OR 13456 = MON - SAT or 1234567 = MON - SUN SQL CODE -- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Jul 14, 2023 at 06:04 PM -- Server version: 10.5.19-MariaDB-cll-lve -- PHP Version: 8.1.16 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `0000` -- -- -------------------------------------------------------- -- -- Table structure for table `DATA` -- CREATE TABLE `DATA` ( `id` smallint(10) NOT NULL, `street` varchar(250) NOT NULL, `restriction` varchar(250) NOT NULL, `day` varchar(250) NOT NULL, `time` varchar(250) NOT NULL, `beat` varchar(25) NOT NULL, `week` varchar(15) NOT NULL, `map` varchar(500) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `DATA` -- INSERT INTO `DATA` (`id`, `street`, `restriction`, `day`, `time`, `beat`, `week`, `map`) VALUES (1, 'PETER STREET', 'NO BALL GAMES', 'MON - SUN', '9AM - 5PM', 'NORTH', '1234567', 'PETER STREET NEWCASTLE'), (2, 'BILL STREET', 'NO CARS', 'MON - FRI', '9AM - 3PM', 'SOUTH', '12345', 'BILL STREET NEWCASTLE'), (3, 'WOOL ST', 'NO CARD GAMES', 'MON - SAT', '9AM - 8PM', 'SOUTH', '123456', 'WOOL STREET NEWCASTLE'); -- -- Indexes for dumped tables -- -- -- Indexes for table `DATA` -- ALTER TABLE `DATA` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `DATA` -- ALTER TABLE `DATA` MODIFY `id` smallint(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; php file Code Below <!DOCTYPE html> <html lang="en"> <head> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <meta charset="UTF-8"> <title>SCHOOL STREET RULES</title> <style> #customers { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; } #customers td, #customers th { border: 1px solid #ddd; padding: 8px; } #customers tr:nth-child(even){background-color: #f2f2f2;} #customers tr:hover {background-color: #ddd;} #customers th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #04AA6D; color: white; } body { margin: 0; font-family: Arial, Helvetica, sans-serif; } .topnav { overflow: hidden; background-color: #333; } .topnav a { float: left; color: #f2f2f2; text-align: center; padding: 14px 16px; text-decoration: none; font-size: 17px; } .topnav a:hover { background-color: #ddd; color: black; } .topnav a.active { background-color: #04AA6D; color: white; } </style> </head> <body> <div class="container"> <div class="topnav"> <a href="index.php">HOME</a> </div> <div style="padding-top:25px"> <center><h2>street rules</h2></center> </div> <div class="row"> <div class="col-md-20 col-md-offset-0" style="margin-top: 2%;"> <div class="row"> <?php $conn = new mysqli('localhost', '0000000', '00000000', '00000000000'); if(isset($_GET['search'])){ $searchKey = $_GET['search']; $sql = "SELECT * FROM DATA WHERE Week LIKE '%$searchKey%'" ; }else $sql = "SELECT * FROM DATA WHERE week LIKE ''" ; $result = $conn->query($sql); ?> <form action="" method="GET"> <div class="col-md-6"> <input type="text" name="search" class='form-control' placeholder="Search By day" value=<?php echo @$_GET['search']; ?> > </div> <div class="col-md-6 text-left"> <button class="btn">Search</button> </div> </form> <br> <br> </div> <table class="table table-bordered" id="customers"> <tr> <th>Street / Location</th> <th>Restriction</th> <th>Day / Time</th> <th>Beat</th> <th>Map</th> </tr> <?php while( $row = $result->fetch_object() ): ?> <tr> <td><?php echo $row->street ?></td> <td><?php echo $row->restriction ?></td> <td><?php echo $row->day ?> | <?php echo $row->time ?></td> <td><?php echo $row->beat ?></td> <td> <a href="https://www.google.com/maps/place/<?php echo $row->map ?>">MAP</a> </td> </tr> <?php endwhile; ?> </table> </div> </div> </div> <P> <CENTER> <footer>© Copyright 2023 SCHOOL TEAM</footer></CENTER></P> </body> </html> FIXXED IT MY SELF WITH ABIT OF GOGLE Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.