PHP/pdo display data after selection from dropdown box

[Flanelman]

Hey guys, I'm having a lot of trouble trying to load data after I select something from a dropdown box. I've tried googling in lots of different worded ways but it just keeps coming up with how to fill the drop down box from things from the database, which I've already done:

<?php
		include('CreateTable.php');
		$all='SELECT sport FROM eventsTable';
		$result = $pdo->query($all);

		foreach($result as $Sport){

?>
<option value ="<?php echo $Sport['sport']; ?>"><?php echo $Sport['sport']; ?></option>
<br>
<?php
}
?>

So this loads all the sports in my database into the drop down box, but now I want to when they select one of the options, load the related data from the database and display it in a table on another page.

for example (my data is about the 2016 Olympics):
If they select running from the drop down box, I want it to the load a page with all the runners on it it, in a table.

my tables and fields:

$createQuery ="CREATE TABLE eventsTable
		(
			eventID		INT(6) NOT NULL AUTO_INCREMENT,
			sport		VARCHAR(20) NOT NULL,
			event		VARCHAR(30) NOT NULL,
			
			PRIMARY KEY (eventID)
			
		)";
		$pdo->exec($createQuery);


$createQuery ="CREATE TABLE AthleteTable
		(
			athleteID		INT(6) NOT NULL AUTO_INCREMENT,
			firstName		VARCHAR(20) NOT NULL,
			lastName		VARCHAR(20) NOT NULL,
			gender			VARCHAR(20) NOT NULL,
			image			VARCHAR(20) NOT NULL,
			eventID			INT(20) NOT NULL,
			medal 			VARCHAR(6) NOT NULL,
			
			CONSTRAINT SportEvents FOREIGN KEY (eventID) REFERENCES eventsTable(eventID),
			PRIMARY KEY(athleteID)
			
		)";
		$pdo->exec($createQuery);

here was my attempt:

<?php
$all='SELECT AthleteTable.athleteId,lastName,firstName,gender,image,sport,eventID,medal
					FROM ((AthleteTable INNER JOIN eventTable ON eventTable.eventID=AthleteTable.eventID)
					WHERE AthleteTable.sport = //the sport selected through the drop down box?
                    
                    //then foreach thorugh and print them in a table
                    ?>

Any help here would be greatly appreciated been stuck on this for ages, thanks.

[dannytech357]

If you're asking how to make the dropdown trigger an update, here's how:

<form action="/query.php" method="GET">
  <select name="sport">
    <option value="baseball">Baseball</option>
  </select>
  <button type="submit" value="Query"/>
</form>

Of course you would probably load the sports from the database. Anyway, this is easily accessible inside the query.php file (or whatever you make it):

$_GET["sport"];

It's been a while since I've written PHP, but what i remember using PDO is this:

$conn = new PDO("mysql:host=ip_here;dbname=database_name_here;charset=utf8", "username_here", "password_here");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = "SELECT AthleteTable.athleteId,lastName,firstName,gender,image,sport,eventID,medal
          FROM ((AthleteTable INNER JOIN eventTable ON eventTable.eventID=AthleteTable.eventID)
          WHERE AthleteTable.sport = :sport";

//Creates prepared statement
$stmt = $conn->prepare($query);

//Executes the query
$stmt->execute(array(":sport" => $_GET["sport"])); // Binds parameters, here is where you add the value from the dropdown

//Sets fetch mode to the given parameter
$stmt->setFetchMode(PDO::FETCH_ASSOC);

//Returns the data in the given fetch mode
return $stmt->fetchAll();

What this will do is create a prepared statement (the query is parsed before you input values), to prevent SQL injection. It will then query the database and return an associative array of the results. You can then incorporate them into the page.

[Flanelman]

3 hours ago, dannytech357 said:

snip

Hey thanks for the response, I don't quite understand how that gets the selected sport from the drop down? As in how does that $_GET know that the passed in sport is from the drop down? (sorry I'm not all that good with PHP)

[dannytech357]

<form action="/query.php" method="GET">
  <select name="sport">
    <option value="baseball">Baseball</option>
  </select>
  <button type="submit" value="Query"/>
</form>

The form is the key here. When you submit a form to /query.php, it submits it like this, with the values:

/query.php?sport=baseball, and PHP parses that into an associative array called $_GET, which in this case would be array("sport" => "baseball")