How to create a drop down to populate page with database update form?

[AasimAzam]

If I have a drop down list and with the java script code submitChange() it populates the page with a set of results from the database, where some of the data is an input feild which I can then input data to, to update the database.

I have tried for the last four days to get somewhere with this and I am banging my head against the wall. It either works but just with one row, or it throws up an undefined error even though I have defined all the variables!

Anyway, I have to do this using PDO and/or Mysqli as I've been told, so I have learned some basics and got this so far where I can output my results

 

my data base

(structure)

id (INT)
stage (INT)
game_no (INT)
hometeam (VARCHAR)
goalsfor (INT)
goalsagainst (INT)
awayteam (VARCHAR)

+----+-------+---------+----------+----------+--------------+-----------+
| id | stage | game_no | hometeam | goalsfor | goalsagainst | awayteam  |
+----+-------+---------+----------+----------+--------------+-----------+
| 1  |   1   |    1    | teamname |    1     |       2      | teamname  | (etc)
+----+-------+---------+----------+----------+--------------+-----------+

then my php

if($results = $db->query("SELECT * FROM fixtures")){
	if($results->num_rows){
		 while($row = $results->fetch_object()){
		 	$records[] = $row;
		 }
		 $results->free();
	}
}

and echo (without formating)

<?php
    if(!count($records)){
        echo 'NO Records';
    } else {
            foreach($records as $r) {
                echo $r->hometeam;
                echo $r->goalsfor;
                echo $r->goalsagainst;
                echo $r->awayteam;
            }

            }
?>

this is as far as I can go as I can't find anything that shows me what I want to do.

So I want to create a drop downlist to select the stage eg WHERE stage=5.

 

And when that outputs the list of fixtures, the "goalsfor" and "goalsagainst" should be an input feild that I can update the results in the database (individually without refreshing the page). All done in the same page.

I understand the theory but I can put it into practice.

Thank you for taking the time to read and thank you in advance for any answers.

[chriscloyd]

Well hello this is easy if you use jquery....

 

A couple of things

 

$(document).ready(function()
{
    var appendEle = $("#updateFixture");
    
    $("#fixtures").on('change', function()
    {
        appendEle.html("");

        var stage = $(this).val();

        $.post('getFixtures.php', {stage: stage}, function(data)
        {

            var obj = $.parseJSON(data);

            $.each(obj, function(index, fixture)
            {
                var newEle = $("<div></div>");
                
                var goalsfor = $('<label>' + fixture.hometeam + ' Goals</label><input type="text" id="goalsfor_' + fixture.id + '" name="goalsfor_' + fixture.id + '" value="' + fixture.goalsfor + '" />');
                var goalsagainst = $('<label>' + fixture.awayteam + ' Goals</label><input type="text" id="goalsagaint_' + fixture.id + '" name="goalsagaint_' + fixture.id + '" value="' + fixture.goalsagaint_+ '" />');

                goalsfor.append(newEle);
                goalsagainst.append(newEle);
                newEle.append(appendEle);
            });

        });
    });

    $("#saveButton").on('click', function()
    {
        $.post('saveFixtures.php', $("#contactForm").serialize(), function(data)
        {
             if(data == "success")
             {
                 //do what you want
             }
             else
             {
                 //do what you want
             }
        });
    });

});

That is the jquery file you will need two other files to save the changes and update I can help with that too if you need

[AasimAzam]

Hi Chris,

 

Thank you for your answer.

 

I assume that one file will house the drop down and on selecting the relevant choice from the drop down, and when it populates the page, a second page to run the update part when I input results ....

 

If I assume correctly, I will be able to do the drop down list part to populate the page but not the update results part ...

[AasimAzam]

Hi would you be able to help? I'm not sure what I am doing.

[benanamen]

Post an SQL dump of your database and I will help you. You don't need javascript or jquery unless you want to add frills to the page.

[AasimAzam]

Not sure how I upload the SQL file, or should I just post the code here?

[AasimAzam]

-- phpMyAdmin SQL Dump

-- version 4.4.14

-- http://www.phpmyadmin.net

--

-- Host: 127.0.0.1

-- Generation Time: Nov 08, 2015 at 05:57 PM

-- Server version: 5.6.26

-- PHP Version: 5.6.12


SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

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: `alltime`

--


-- --------------------------------------------------------


--

-- Table structure for table `fixtures`

--


CREATE TABLE IF NOT EXISTS `fixtures` (

  `id` int(11) NOT NULL,

  `stage` int(11) NOT NULL,

  `game_no` int(11) NOT NULL,

  `hometeam` varchar(255) NOT NULL,

  `goalsfor` int(11) DEFAULT NULL,

  `goalsagainst` int(11) DEFAULT NULL,

  `awayteam` varchar(255) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;


--

-- Dumping data for table `fixtures`

--


INSERT INTO `fixtures` (`id`, `stage`, `game_no`, `hometeam`, `goalsfor`, `goalsagainst`, `awayteam`) VALUES

(1, 1, 1, 'Team 1', 1, 0, 'Team 2'),

(2, 1, 2, 'Team 3', 1, 3, 'Team 4'),

(3, 3, 3, 'Team 1', 2, 0, 'Team 3');


--

-- Indexes for dumped tables

--


--

-- Indexes for table `fixtures`

--

ALTER TABLE `fixtures`

  ADD PRIMARY KEY (`id`);


--

-- AUTO_INCREMENT for dumped tables

--


--

-- AUTO_INCREMENT for table `fixtures`

--

ALTER TABLE `fixtures`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;

/*!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 */;

[benanamen]

This is a quickie to get you going.

<?php
$hostdb   = 'localhost';
$dbname   = 'phphelp_fixtures';
$username = 'root';
$password = '';
$table    = 'fixtures';

    $pdo = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql  = "SELECT * FROM $table";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll();
?>
<form class="form-horizontal" role="form" action="<?= $_SERVER['SCRIPT_NAME'] ?>" method="post">
<div class="form-group">
  <label class="col-md-4 control-label" for="id">Select</label>
  <div class="col-md-4">
    <select id="id" name="id" class="form-control">
    <?php foreach ($result as $row) :?>
    <option value="<?= $row['id']; ?>"><?= $row['hometeam']; ?></option>
    <?php endforeach; ?>
    </select>
  </div>
</div>

<div class="form-group">
  <div class="col-md-offset-4 col-md-4">
    <button id="submit" name="submit" class="btn btn-primary">Submit</button>
  </div>
</div>
</form>


<?php
if (isset($_POST['id']))
    {
    $sql  = "SELECT * FROM $table WHERE id=?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array($_POST['id']));
    $result = $stmt->fetchAll();

        foreach ($result as $row)
        {
        echo "{$row['hometeam']} {$row['awayteam']}";
        }
    }
?>

Edited November 8, 2015 by benanamen

[mac_gyver]

for time being, forget about "individually without refreshing the page." you have to be able to write the program logic that does what you want first, and us just posting code isn't going to help you learn how to do that.

 

start by making this all one page, but using just html and php (your page should work, even if someone has disabled javascript - you can output a submit button for your form(s) using a <noscrpt> tag.) then, you can add things like on change events and ajax requests after you have gotten all the code written and working.

 

it sounds like you are making the U part of a CRUD (Create, Read, Update, Delete) exercise. the first step is to define the steps that accomplish the work flow. wouldn't these be something like -

 

1) list the available stages, with a way of selecting one.

2) if a stage value has been submitted, as a get parameter (you are controlling what is being displayed on the page), (safely) use the value to retrieve the record(s) that match that stage, display them, each as an individual (based on your stated goal) 'edit' form for updating the values.

3) if an edit form has been submitted, as post data (you are altering data values at this point), (safely) use the submitted form data to update the correct record.

4) repeat  until you have updated all the records that you want or you pick a different stage value.

 

you would basically use this list as comments in your code and write the code that implements each of these steps of the work flow. the code on your page should be laid out as suggested in this - http://forums.phpfreaks.com/topic/297824-database-issues-and-working/?do=findComment&comment=1519095