University homework, my heads completely melted and yeah, I know its probably very basic PHP MYSQL but I cant get my head around what exactly he means.

 

I have a table set up, shown below ;

 

-- phpMyAdmin SQL Dump
-- version 3.2.4-rc1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 18, 2010 at 01:54 PM
-- Server version: 5.0.88
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


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

--
-- Database: `labuser`
--

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

--
-- Table structure for table `Member`
--

CREATE TABLE IF NOT EXISTS `Member` (
 `MemberName` char(20) NOT NULL default '',
 `Age` smallint(6) NOT NULL,
 `Instrument` varchar(20) NOT NULL,
 `BandName` char(20) NOT NULL,
 PRIMARY KEY  (`MemberName`),
 KEY `fkey` (`BandName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Member`
--

INSERT INTO `Member` (`MemberName`, `Age`, `Instrument`, `BandName`) VALUES
('Chris Berry', 27, 'Vocals', 'Depeche Mode'),
('Craig Dodds', 21, 'Bass', 'A Perfect Circle'),
('Craig Taylor', 24, 'Drums', 'Depeche Mode'),
('Dan Lavery', 22, 'Vocal', 'Tool'),
('Dave Brown', 24, 'Bass', 'Tool'),
('Joe Hill', 22, 'Guitar', 'Tool'),
('Joe Soap', 32, 'Bass', 'Placebo'),
('John Boyle', 31, 'Drums', 'Placebo'),
('Keron Tudor', 35, 'Guitar', 'Placebo'),
('Leroy Jenkins', 24, 'Bass', 'Depeche Mode'),
('Marc Beggan', 39, 'Drums', 'Stone Roses'),
('Mark Begley', 23, 'Vocals', 'A Perfect Circle'),
('Mark Farmer', 32, 'Bass', 'Stone Roses'),
('Maynard Keenan', 22, 'Drums', 'A Perfect Circle'),
('Paddy Lynn', 33, 'Guitar', 'Stone Roses'),
('Paddy Maguire', 24, 'Guitar', 'A Perfect Circle'),
('Phil Patterson', 21, 'Drums', 'Tool'),
('Ryan Gallagher', 22, 'Guitar', 'Depeche Mode'),
('Ryan Morgan', 29, 'Vocals', 'Stone Roses'),
('Stuart Baird', 27, 'Vocals', 'Placebo');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Member`
--
ALTER TABLE `Member`
 ADD CONSTRAINT `fkey` FOREIGN KEY (`BandName`) REFERENCES `band` (`BandName`);

 

Get the band member name and age for members whose band name is <drop down value>

 

I have to Implement the retrieval query:

 

- Using a drop down list to display the values taken from the database for one of the

WHERE or AND clause values.

 

- Print the result to an html table.

 

Does that I mean I need to populate the drop down list from the table ? How would I go about doing that? Really confused, anyone help me out by explaining what the hell he means. Cheers.

 

Shown Below is the HTML/PHP. Nothing as of yet. LOL

 

<html>
<body>

<form action="down.php" method="post">
<select name="Band Name">
<option value="1"></option>
<option value="2"></option>
<option value="3"></option>
<option value="4"></option>
<option value="5"></option>
</select>
</form>
<?php
$con = mysql_connect("localhost","labuser","");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("labuser", $con);






</body>


</html>

Once you have connected to the database preform a simple SELECT as shown below:

 

<?
$sql = "SELECT
           BandName
        FROM
           Member";
$result = mysql_query($sql);

//Loop through the results and create the select
?><select name="band_name"><?
  while($data = mysql_fetch_assoc($result)){
       ?><option value="<?=$data['BandName'];?>"><?=$data['BandName'];?></option><?
  }
?></select><?

?>

Then on the page that will recieve the post

 

if(isset($_POST['band_name'])){

//This should be validated and checked to prevent SQL injections

$sql = "SELECT
           BandName
       FROM
           Member
       WHERE
           BandName = '".$_POST['band_name']."'";

$result = mysql_query($sql);

while($data = mysql_fetch_assoc($result)){
     echo $data['MemberName'];
}
}

 

This was very quickly wrote and very basic, but it should help you on your way.

 

Hope your studying goes well

Nathan

 

Attain Design

Thanks Nathan. Has got me well underway. +1 .

Totally agree with rallport.

 

Ideally this should be in three tables,

 

• member
  
• band
  
• instrument
  

 

Thanks