PHP网站内从MySQL到JSON到AJAX的数据如何？

Question

I've got really ambitious problem today as I want to achieve something ridiculously stupid but satisfying. Basically, I do have a database with data for gym exercises

CREATE TABLE IF NOT EXISTS `gp_progs` (
  `prog_id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `exer` varchar(250) NOT NULL,
  `pic` varchar(15) NOT NULL,
  PRIMARY KEY (`prog_id`),
  UNIQUE KEY `prog_id` (`prog_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `gp_progs`
--

INSERT INTO `gp_progs` (`prog_id`, `name`, `exer`, `pic`) VALUES
(1, 'ABS', 'TO DO ABS YOU NEED TO DO THIS AND THAT', 'abs.jpg'),
(3, 'Arms2', 'this is what we want', 'abs.jpg'),
(7, 'Biceps', 'curls', 'abs.jpg');

I have treated it after digging the code for many hours with this code in PHP

$jsondb = "data/prog.json";
$q = "SELECT * FROM gp_progs";
$r = @mysqli_query ($dbc, $q);
/*$json = array();
while ($row = mysqli_fetch_assoc($r)){
    $json[] = $row;
    }

$jsondata =  json_encode($json, JSON_PRETTY_PRINT);
if(file_put_contents($jsondb, $jsondata)) {
            echo 'Data successfully saved';
        }

It gave me a json file from which I realy want to build AJAX functional app like this one. JS:

$(function() {                                    // When the DOM is ready

  var times;                                      // Declare global variable
  $.ajax({
    beforeSend: function(xhr) {                   // Before requesting data
      if (xhr.overrideMimeType) {                 // If supported
        xhr.overrideMimeType("application/json"); // set MIME to prevent errors
      }
    }
  });

  // FUNCTION THAT COLLECTS DATA FROM THE JSON FILE
  function loadTimetable() {                    // Declare function
    $.getJSON('data/events.json')              // Try to collect JSON data
    .done( function(data){                      // If successful
      times = data;                             // Store it in a variable
    }).fail( function() {                       // If a problem: show message
      $('#event').html('Sorry! We could not load the timetable at the moment');
    });
  }

  loadTimetable();                              // Call the function


  // CLICK ON THE EVENT TO LOAD A TIMETABLE 
  $('#content').on('click', '#event a', function(e) {  // User clicks on event

    e.preventDefault();                                // Prevent loading page
    var loc = this.id.toUpperCase();                   // Get value of id attr

    var newContent = '';                               // Build up timetable by
    for (var i = 0; i < times[loc].length; i++) {      // looping through events
      newContent += '<li><span class="time">' + times[loc][i].time + '</span>';
      newContent += '<a href="data/descriptions.html#';
      newContent += times[loc][i].title.replace(/ /g, '-') + '">';
      newContent += times[loc][i].title + '</a></li>';
    }

    $('#sessions').html('<ul>' + newContent + '</ul>'); // Display times on page

    $('#event a.current').removeClass('current');       // Update selected item
    $(this).addClass('current');

    $('#details').text('');                             // Clear third column
  });

  // CLICK ON A SESSION TO LOAD THE DESCRIPTION
  $('#content').on('click', '#sessions li a', function(e) { // Click on session
    e.preventDefault();                                     // Prevent loading
    var fragment = this.href;                               // Title is in href

    fragment = fragment.replace('#', ' #');                 // Add space after#
    $('#details').load(fragment);                           // To load info

    $('#sessions a.current').removeClass('current');        // Update selected
    $(this).addClass('current');
  });


  // CLICK ON PRIMARY NAVIGATION
  $('nav a').on('click', function(e) {                       // Click on nav
    e.preventDefault();                                      // Prevent loading
    var url = this.href;                                     // Get URL to load

    $('nav a.current').removeClass('current');               // Update nav
    $(this).addClass('current');

    $('#container').remove();                                // Remove old part
    $('#content').load(url + ' #container').hide().fadeIn('slow'); // Add new
  });

});

HTML:

<section id="content">
      <div id="container">
        <h2>Upcoming Events in Yorkshire</h2>

        <div class="third">
          <div id="event">
            <a id="sh" href="sh.html"><img src="img/sheffield.fw.png" alt="Sheffield, South Yorkshire" />Sheffield</a>
            <a id="hu" href="hu.html"><img src="img/hull.fw.png" alt="Hull, East Yorkshire" />Hull</a>
            <a id="ls" href="ls.html"><img src="img/leeds.fw.png" alt="Leeds, West Yorkshire" />Leeds</a>
            <a id="yk" href="yk.html"><img src="img/york.fw.png" alt="York, West Yorkshire" />York</a>
          </div>
        </div>
        <div class="third">
          <div id="sessions">
            <p>Select an event from the left</p>
          </div>
        </div>
        <div class="third">
          <div id="details"></div>
        </div>

      </div><!-- #container -->
    </section><!-- #content -->


    <script src="js/jquery-1.11.0.min.js"></script>
    <script src="js/events.js"></script>

So the result I want to see is to click on the group of exercises e.g. Arms, which will open next exercises e.g. Biceps and then onclick I want to see programme with pictures. But I cannot find out how to change JS so it will give me what I want. Spent on it already 13 hrs and still cannot find anything online.

If something is not clear please let me know as I am still learning how to use overflow.

Thanks in advance!

This is for PHP website with an use of JS, MySQL, Google API and HTML of course.

Edit: If it was not too clear, I want to get MySQL data to JSON (which I have done already)

[
{
"prog_id": "1",
"catg": "chest",
"name": "Chest",
"exer": "Three exercises per muscle group. Chest: Bench Press (3 sets of 10), Chest cable fly(3 sets of 10) and dumbbell fly (3 sets of 10)",
"pic": "abs.jpg"
}
]

And now I want to use it in AJAX in way of: on page I want to see Groups - 'catg' which on click will open list next to group on the same page with Muscle to train 'name' which afterwards open last list next to previous also on the same page showing Descirption 'exer' and Picture/s 'pic'. Just like in the picture below:

Answer 1

I think your problem is that you don't know how to get data from MySQL to JSON in PHP, then get that JSON into Javascript in a form that lets you manipulate it easily.

Here's how I do it. The key here is the use of str_replace.

PHP, using my own SQL() function to retrieve the result set via fetch_all(MYSQLI_ASSOC):

$subcategorydata =
  SQL("select * from subcategoryoptions order by category, subcategoryoption");
$subcategories = 
  str_replace('\\"','\\\\"',
  str_replace("'","\'",
  json_encode($subcategorydata)));

Javascript (direct rather than via ajax in my case):

var subcategories = '<?php echo $subcategories; ?>';
var jsonSubcategories = JSON.parse(subcategories);
for (var row in jsonSubcategories) { ....

EDIT: Additional code to load 2 layers, toggling the display of the lower level on/off according to user clicks. This version assumes you've pulled all the data out of MySQL in one go (I've just hard-coded it) - you would probably want to use ajax to load stuff dynamically in practice - and my code is definitely not optimal, but it should do the job.

Main div into which the data is loaded is followed by the javascript to load it. Note the hide(), show(), toggle() and set() functions and the onclick.

<div id=main></div>

<script>
function set(div, value) {
    document.getElementById(div).innerHTML = value;
}

function hide(div) {
    var x = document.getElementById(div);
    x.style.display = "none";
}

function show(div) {
    var x = document.getElementById(div);
    x.style.display = "block";
}

function toggle(div) {
    var x = document.getElementById(div);
    if (x.style.display === "none") { show(div); } else { hide(div); }
}

var json='[{"heading":"This is the first heading","detail":"This is the first detail"},{"heading":"This is the second heading","detail":"This is the second detail"}]';
var jsonData = JSON.parse(json);

var html = '';
for (var row in jsonData)
  {
  html += '<div id=hdr' + row + ' onclick=toggle(\'dtl' + row + '\')>';
  html += '<b>' + jsonData[row].heading + '</b></div>';
  html += '<div id=dtl' + row + '>' + jsonData[row].detail + '</div>';
  }
set('main','Click on the headings to toggle the detail<br>' + html);
for (var row in jsonData)
  {
  hide('dtl' + row);
  }
</script>