PHP MYSQL LEARNING

jamesclues · July 14, 2023

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

ginerjm · 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.

Barand · 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

jamesclues · 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>&copy; Copyright 2023 SCHOOL TEAM</footer></CENTER></P>
</body>
</html>

jamesclues · 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>&copy; Copyright 2023 SCHOOL TEAM</footer></CENTER></P>
</body>
</html>

FIXXED IT MY SELF WITH ABIT OF GOGLE

Sign In

PHP MYSQL LEARNING

Recommended Posts

jamesclues

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

jamesclues

Link to comment

Share on other sites

jamesclues

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information