Read php function into drop down box

[Flanelman]

Hey guys so I'm trying to put my php function onto a drop down box on the page which will run the query within the function when the option is chosen from the drop down box.


here is the function:

function goldQuery(){
        $selectGold = "Select * FROM AthleteTable Where (medal Like 'G%')";
		$golds = $pdo->query($selectGold);
    return $selectGold;
	}

and here is the drop down box:

 

<select>
  <option value="All athletes">All athletes</option>
  <option value="Gold medalists">Gold medalists</option> <!-- want to call the function here -->
  <option value="Silver medalists">Silver medalists</option>
  <option value="Bronze medalists">Bronze medalists</option>
</select>

and then when this option is selected from the drop down list it should display a table with gold medalists within. I was thinking this could be done similarly to having an onclick call on a button but couldn't find anything similar that worked, any help would be appreciated!  
 

[Hazy125]

First.... You're returning the SQL query string? Do you mean to be returning $golds?

 

Second, the one you're after is onChange();

 

So,

<select onChange="fetchMedalists()">

 

[Flanelman]

2 minutes ago, Hazy125 said:

First.... You're returning the SQL query string? Do you mean to be returning $golds?

 

Second, the one you're after is onChange();

 

So,

<select onChange="fetchMedalists()">

 

oh yeah I was, thanks!

[Bigun]

Is that returning anything? AFAIK, the value collected there should be the result of the query, not the actual data,  
 

print_r($selectGold);

 

But if your sure of that, I'll move on.

Are you wanting sub-selections in the dropdown box with a "Gold Medalist" divider?  If so, it can be accomplished one of two ways:
 

1. No Javascript - run query at beginning of page load, then populate menu with some embedded PHP looping
2. With javascript - Kinda covered in Hazy's post

[Flanelman]

9 hours ago, Bigun said:

Is that returning anything? AFAIK, the value collected there should be the result of the query, not the actual data,  
 

print_r($selectGold);

 

But if your sure of that, I'll move on.

Are you wanting sub-selections in the dropdown box with a "Gold Medalist" divider?  If so, it can be accomplished one of two ways:
 

1. No Javascript - run query at beginning of page load, then populate menu with some embedded PHP looping
2. With javascript - Kinda covered in Hazy's post

pretty much all i'm looking to accomplish is have my queries related to the drop down items, so one will be gold medalist another silver etc and when i press gold I want it to display all the gold medalists in the database.

edit: so heres a query i want to list all the sports in the database:

 

$selectSports = "Select sport FROM AthleteTable";
	$sports = $pdo->query($selectSports);

and then i tried to foreach through and print them into the drop down box:

 

<select name=Sports> 
<?php foreach($sports as $row) { ?>
	<option value="<?php echo $row['sport'];?>"><?php $row[sport]; ?></option>
</select>
<?php } ?>

but the drop down box is still empty, any idea what i've done wrong here?

[Hazy125]

6 hours ago, Flanelman said:

pretty much all i'm looking to accomplish is have my queries related to the drop down items, so one will be gold medalist another silver etc and when i press gold I want it to display all the gold medalists in the database.

That is a mess of tags wow. A slightly cleaner way would be (provided your query is actually returned and in the $row array like your example shows)

 

<select>
	<?php
		foreach($sports as $row){
			echo '<option value="' . $row["sport"] . '">' . $row["sport"] . '</option>';
		}
	?>
</select>

As to why it's still empty, do a print_r() of $sports and that will tell you if anything is returned and the format of the array. I would say that if you have a few different columns in that table that instead of using $row['sport'] you will need to use $row[0]['sport'] or some such

[vorticalbox]

it might be easier to return your data as a json then use angularJS to filter the results based on the drop down.

 

I am out today at an air show, I will try get a script set up tomorrow if you would like?

[Flanelman]

1 hour ago, vorticalbox said:

it might be easier to return your data as a json then use angularJS to filter the results based on the drop down.

 

I am out today at an air show, I will try get a script set up tomorrow if you would like?

I got it sorted! thanks for the offer tho, appreciate it!

just having some trouble actually displaying the data now:
here's the fields in the tables:

eventTable:

			eventID	INT(6) NOT NULL AUTO_INCREMENT,
			sport	VARCHAR(20) NOT NULL,
			event	VARCHAR(20) NOT NULL,
			
			PRIMARY KEY(eventID)

AthleteTable

			athleteID	INT(6) NOT NULL AUTO_INCREMENT,
			lastName	VARCHAR(20) NOT NULL,
			firstName	VARCHAR(30) NOT NULL,
			gender		CHAR(1) NOT NULL,
			image		VARCHAR(20) NOT NULL,
			eventID		INT(6) NOT NULL, 
			medal		VARCHAR(6) NOT NULL,
			
			PRIMARY KEY(athleteID)

 
here's the code for the loop that's meant to write out the data:

<tr><th>LastName</th><th>FirstName</th><th>Gender</th><th>Sport</th><th>Event</th><th>Medal</th><th>Photo</th></tr>

<?php
		include('CreateTableOly.php');
		$all='SELECT AthleteTable.athleteId,lastName,firstName,gender,image,sport,eventID,medal
					FROM ((AthleteTable INNER JOIN eventTable ON eventTable.athleteId=AthleteTable.athleteId)
					WHERE AthleteTable.eventID='.$_POST['eventID'];	
		foreach($result as $Sport)
		{?>
			<!-- fill the table in here -->
			<td><?php $Sport['lastName'] ?></td>
			<td><?php $Sport['firstName']?></td>
			<td><?php $Sport['gender']?></td>
			<td><?php $Sport['image']?></td> <!-- add image tag to this -->
			<td><?php $Sport['sport']?></td>
			<td><?php $Sport['eventID']?></td>
			<td><?php $Sport['medal']?></td>
		<?php
		}
		?>

and the error i'm getting is in the image below.

If you could help me with this I'd be very grateful, Also unsure on how to get the table to show only when the sport is selected, because they're there at that point when nothing has been selected. Thanks

[Mr_KoKa]

You have SQL injection there, you should use prepared statements, I mean using $_POST variable to build your SQL query (there is not even (int) cast) so user can add anything at the end and it will be executed this way.

 

Your problem with undefined index is connected with your other problem, the "not displaying table when nothing has been selected yet". So the line 38 is where you accessing $_POST['eventID']

 

This is because when you accessing page, you're not sent any form data by POST so $_POST array is empty and trying to access any index will fail with an error as above.

 

To avoid this, you need to check if isset($_POST['eventID']) and this is also the solution for table, if you have no selection made, there is no post data sent, don't access $_POST array at the index, and don't show up table yet.

 

About SQL injections, I don't know what do you use, but please tell me it is either PDO or mysqli, mysql functions are deprecated.

[Flanelman]

1 hour ago, Mr_KoKa said:

You have SQL injection there, you should use prepared statements, I mean using $_POST variable to build your SQL query (there is not even (int) cast) so user can add anything at the end and it will be executed this way.

 

Your problem with undefined index is connected with your other problem, the "not displaying table when nothing has been selected yet". So the line 38 is where you accessing $_POST['eventID']

 

This is because when you accessing page, you're not sent any form data by POST so $_POST array is empty and trying to access any index will fail with an error as above.

 

To avoid this, you need to check if isset($_POST['eventID']) and this is also the solution for table, if you have no selection made, there is no post data sent, don't access $_POST array at the index, and don't show up table yet.

 

About SQL injections, I don't know what do you use, but please tell me it is either PDO or mysqli, mysql functions are deprecated.

I use PDO, I'm not really that great with php so the POST thing is all kinda blurry to me. What would I have to do solve this error? 

[Mr_KoKa]

if(isset($_POST['eventID'])){
	$all = 'SELECT AthleteTable.athleteId,lastName,firstName,gender,image,sport,eventID,medal
					FROM ((AthleteTable INNER JOIN eventTable ON eventTable.athleteId=AthleteTable.athleteId)
					WHERE AthleteTable.eventID = :eventID';
	$stmt = $pdo->prepare($all);
	$stmt->bindValue(':eventID', $_POST['eventID'], PDO::PARAM_INT);

	if($stmt->execute()){
		$result = $stmt->fetchAll();

		//Build your table here.
	}
}

This will make your table appear only when form with event selection has been sent. Preparing your query makes PDO to send separately the query and the data used in query, so mysql server wont use data a s query, so data can contain anything and won't be interpreted as query.