Jump to content

PHP MYSQL LEARNING


jamesclues
Go to solution Solved by jamesclues,

Recommended Posts

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 by jamesclues
Link to comment
Share on other sites

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.

  • Thanks 1
Link to comment
Share on other sites

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

 

  • Thanks 1
Link to comment
Share on other sites

  • Solution

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>

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.