?
Solved

get results based on drop down from same database

Posted on 2015-02-18
24
Medium Priority
?
485 Views
Last Modified: 2015-02-18
Hello hope you guys can help. i been looking on web fro couple of days and cannot find a example to assist...

what i am try to do is let users select from a drop down option a RACE and then the results of that RACE would show below

drop down working on working drop down

code so far
<?php
// Connect database
include("db.php");

$sql="SELECT ra.name FROM  races AS ra INNER JOIN seasons AS s ON ra.year = s.year WHERE (s.year = 2014) GROUP BY s.year, ra.round, ra.name, ra.date ORDER BY ra.round ";
$result=mysql_query($sql);

$options="";

while ($row=mysql_fetch_array($result)) {

    $id=$row["raceId_id"];
    $hotel_name=$row["name"];
    $options.="<OPTION VALUE=\"$id\">".$hotel_name." (".$hotel_name.")";
}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />


<title>Race Results</title>
</head>

<body>

<form id="myform" name="myform" method="post" >

<SELECT NAME="F1">
<OPTION VALUE=0>Choose
<?=$options.="<OPTION VALUE=\"$id\">".$hotel_name.'</option>';?>
<br /><br /> </select><input name="Button1" type="button" value="button"></form>

</div>
</body>
</html>

Open in new window


code (mysql) for results is

SELECT d.surname, con.name AS Construtor, res.positionOrder FROM     races AS ra INNER JOIN results AS res ON ra.raceId = res.raceId         INNER JOIN     seasons AS s ON ra.year = s.year         INNER JOIN     drivers AS d ON res.driverId = d.driverId         INNER JOIN     constructors AS con ON res.constructorId = con.constructorId WHERE     (s.year = "2014")and r.name LIKE  ?????????? RACE SELECTED IN DROP DOWN ???????? GROUP BY s.year , ra.round , ra.name , ra.date , d.surname , con.name , res.points , res.positionOrder ORDER BY ra.round DESC , res.positionOrder

Open in new window


hOPE YOU CAN HELP
0
Comment
Question by:James Murrell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
24 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40616873
OK. First things first - the mySQL extension in PHP is deprecated so you should consider switching to mySQLi or PDO sooner rather than later. Have a read through Rays article here

The normal process is to submit the SELECT value to a server-side script (php), that would process the information you sent, run a database query, and return the formatted data back to the browser. Traditionally this is done by wrapping your fields in a <form> tag and then submitting them to the server script using a Submit button. In your case, you probably want to make an AJAX request, so that it happens asynchronously when you change the dropdown.

First thing to do is to make sure your HTML page is working the way you want it to. Currently it's not. None of your <options> have have values, so nothing will get sent to the server. You should run your page through the W3C HTML validator and fix any errors (currently there are 57!) - http://validator.w3.org/. Unless you have a very good reason NOT to, then start using HTML5.

Once you've done that, create a simple PHP script, call it data.php, and drop in the following code:

<?php
var_dump($_POST);

Open in new window


Set your form's action attribute to data.php and add a submit button.

Now you can submit your form and you'll see exactly what's being sent.

Once you've done that, then you know your page is working correctly, and your form is submitting the data.

Let me know when you've done it and we'll move on to the next step - adding in the jQuery.
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40616966
thanks for the info regarding mySQLi this is something I have planned to look into at later date... also not worried about HTML code verified yet.... ( this site is for few friends doing fantasy f1 that used to be done on excel - so this will be updated as months go on) --

It will be 100% html5 once got site working

any way back to problem I have done as asked and on data.php i get

array(2) { ["F1"]=> string(0) "" ["Button1"]=> string(5) "Enter" }

Open in new window

array(2) { ["F1"]=> string(0) "" ["Button1"]=> string(5) "Enter" }

Open in new window

0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617012
OK. If you don't validate your HTML, then it becomes more difficult to debug. You'll never know where in your application a problem exists. At least if you have valid HTML, you know it's not that. Later down the road, you'll wish you built this correctly from the start - your call though.

The reason you're getting the POST array output like that is because none of your <option> tags have values, and that's what gets passed when you submit a form. This is what your HTML looks like after PHP has done it's bit:

<SELECT NAME="F1">
<OPTION VALUE=0>Choose Race
<OPTION VALUE="">Australian Grand Prix (Australian Grand Prix)
<OPTION VALUE="">Malaysian Grand Prix (Malaysian Grand Prix)
<OPTION VALUE="">Bahrain Grand Prix (Bahrain Grand Prix)
<OPTION VALUE="">Chinese Grand Prix (Chinese Grand Prix)
<OPTION VALUE="">Spanish Grand Prix (Spanish Grand Prix)

Note all the empty values - there's nothing to submit.

In your code, you're trying to put raceId_id in there, but your SQL query only ever selects name. Update your SQL to include the raceIdD_id column.

At the moment, these errors are happening silently. Right at the start of your code (before the include), add the following to turn on error reporting:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Open in new window


You can always turn it off again (and you should) when your application goes live
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 31

Author Comment

by:James Murrell
ID: 40617022
Apologies: i thought Value was working - it is not
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617035
Nope :)

Most browsers will attempt to parse your HTML, even when it's not right. When viewing your page, hit Ctrl+U, and it'll show you the generated HTML - makes it easier to spot these issues.

Also, double check your PHP - you're using variables called $hotel_name, which don't make a lot of sense. You may know what you mean now, but will you still know what you mean in 6 months time (or will another developer working on your code understand the relevance on $hotel_name)
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617042
it is working now i get  

array(2) { ["F1"]=> string(3) "915" ["Button1"]=> string(5) "Enter" }

Open in new window


I have also changed code to basic html and php for now.. new code is

<?php
// Connect database
error_reporting(E_ALL);
ini_set('display_errors', 1);
include("db.php");

$sql="SELECT raceId,ra.name FROM  races AS ra INNER JOIN seasons AS s ON ra.year = s.year WHERE (s.year = 2014) GROUP BY s.year, ra.round, ra.name, ra.date ORDER BY ra.round ";
$result=mysql_query($sql);

$options="";

while ($row=mysql_fetch_array($result)) {

    $id=$row["raceId"];
    $hotel_name=$row["name"];
    $options.="<OPTION VALUE=\"$id\">".$hotel_name." (".$hotel_name.")</option>";
}

?>


<form id="myform" name="myform" method="post" action="data.php">

<SELECT NAME="F1">
<OPTION VALUE=0>Choose Race
<?=$options.="<OPTION VALUE=\"$id\">".$hotel_name.'</option>';?>
<br /> </select><input name="Button1" type="submit" value="Enter"><form/>
				
<p>2015 results</p>

Track: "...name..." <p></p>
<img src="../images/circuits/1.jpg" />


<p><h1>Results</h1></p>
<table border="2" cellpadding="0" cellspacing="0" class="sortable" style="width: 95%">
	<tr><th><strong>Driver </strong> </th><th><strong>Team </strong> </th><th><strong>Position</strong></th></tr>

Open in new window


Ps. big thanks for explaining things: I find it very useful to learn than someone just do it and provide a bit of code that I do not understand... so big thanks and hopefully now with latest results we can get this closed
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617083
Excellent - heading in the right direction.

Now you know your data.php is receiving the information from the form, you need to edit data.php and use that value to lookup the info in your database. The value of the raceID is available in your script as $_POST['f1'].

I don't know how your database tables are set up, so I can't comment on the best way to query them, but now that you have the value, you can query them however you like. Connect to your database, run the queries and echo out the results that you need.

The best way to do this, ready for the next step (jQuery) is to output an array of data from your PHP script.

It may just be as simple as:

error_reporting(E_ALL);
ini_set('display_errors', 1);
include("db.php");
$sql = sprintf("SELECT * FROM someTable WHERE raceID = '%s';", $_POST['f1']);
$result = mysql_query($sql);
echo mysql_fetch_array($result));

Open in new window

0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617140
ok now with

<?php
//var_dump($_POST);
//print('<pre>'); print_r($_POST); print('</pre>');

error_reporting(E_ALL);
ini_set('display_errors', 1);

include("db.php");
$sql = sprintf("select * from races where raceID = '%s';", $_POST['f1']);
$result = mysql_query($sql);
echo mysql_fetch_array($result);

Open in new window


 i get errors
Notice: Undefined index: f1 in /var/www/F1/members_area/data.php on line 10

Open in new window


also you had two end brackets after $result i removed one
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617150
Right - my bad - these things are case sensitive. Your SELECT is called F1, so the case needs to match in PHP:

$_POST['F1']
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617166
LOL i just tried that as you posted.....

i now get

Notice: Array to string conversion in /var/www/F1/members_area/data.php on line 12
Array

Open in new window

0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617191
Sorry - it's been a while since I've used the mySQL extensions and I'm not testing any of this as I type it!!

For now, while we're debugging, change the echo line to:

var_dump( mysql_fetch_array($result) );

Open in new window


We'll come back and change that once we know we're getting the right data.

Ultimately, what you're after from your PHP script is an array of data that you can send back to your page when we make the AJAX request. Looking at your HTML page, that array will probably look something like:

array (
   trackName => "Some Track",
   trackImage => "track1.jpg",
   raceResults => array (
      array ( driver => "Driver 1", team => "Team 1", position => 1 ),
      array ( driver => "Driver 2", team => "Team 2", position => 2 ),
      array ( driver => "Driver 3", team => "Team 3", position => 3 ),
      ...
   )
)

Once your PHP script is generating the correct data, we can move on to the AJAX request to update your HTML page.
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617240
ok thanks for this..

i am now getting

array(6) { [0]=> string(7) "Rosberg" ["surname"]=> string(7) "Rosberg" [1]=> string(8) "Mercedes" ["Construtor"]=> string(8) "Mercedes" [2]=> string(1) "1" ["positionOrder"]=> string(1) "1" }

Open in new window

0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617295
Right - we're making progress :)

the mysql_fetch_array() method fetches the next record in your query result, and by default fetches the data as a numerical array and an associative array (which is why you get all the data twice).

We now need to start building the array of data, so remove the last var_dump() line, and add this instead:

$raceData = array();
while ($raceResult = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $raceData['results'][] = $raceResult;
}

var_dump( $raceData );

Open in new window


Basically, what we're doing here is creating a new array to store all your data, and then looping through your database query result, and adding each record into the 'results' key of the raceData array.

When you run this, you should see an output containing all the race results that were returned from your query.
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617302
ah that makes sense.... feel free to see results at http://cs97jjm3.ddns.net/F1/members_area/thankyou.php

i now get -

array(1) {
  ["results"]=>
  array(22) {
    [0]=>
    array(3) {
      ["surname"]=>
      string(7) "Rosberg"
      ["Construtor"]=>
      string(8) "Mercedes"
      ["positionOrder"]=>
      string(1) "1"
    }
    [1]=>
    array(3) {
      ["surname"]=>
      string(9) "Magnussen"
      ["Construtor"]=>
      string(7) "McLaren"
      ["positionOrder"]=>
      string(1) "2"
    }
    [2]=>
    array(3) {
      ["surname"]=>
      string(6) "Button"
      ["Construtor"]=>
      string(7) "McLaren"
      ["positionOrder"]=>
      string(1) "3"
    }

Open in new window

0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617340
Looking good :)

Couple of changes to make to your PHP script and we can move onto the jQuery part.

Remove the part of your script at the start that prints the $_POST array - we don't need that anymore and it'll break things if it's left in - you only want to be outputting the raceData array.

Change the last line of your script from var_dump, to echo json_encode():

echo json_encode( $raceData );

Open in new window


You now also need to edit your main html page - currently it's not even close to being valid - you've just dropped straight into a <form> tag, so you have no valid DOCTYPE and no HEAD section, which we're going to need.

You'll also need to include the jQuery library. The best-practice here is to include it directly from Google's CDN. Finally you'll also need to add a <script> tag to the head, ready for the next part, which is to add in the actual jQuery code.

Here's a valid HTML5 template that should get you going. Just add in your database bits for the <options>

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
include_once('db.php');
?>

<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="utf-8">
		<title>Chris Stanyon | EE | Q_28619165</title>
		<script src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
		<script type="text/javascript">
			// This is where we put the jQuery code.
		</script>	
	</head>
	<body>

	<form action="data.php" method="post">
		<div>
			<select name="F1">
				<option value="0">Choose Race...</option>
				<!-- do your database bits here -->
			</select>
			<input type="submit" value="View">
		</div>
	</form>


	<div id="response">
		<p>Track Name: <span id="trackName"></span></p>

		<div id="trackImage"></div>
		
		<h2>Results</h2>
		
		<table id="raceResults">
			<thead>
				<tr>
					<th>Driver</th>
					<th>Team</th>
					<th>Position</th>
				</tr>
			</thead>
			<tbody>
				
			</tbody>
		</table>
	</div>

	</body>
</html>

Open in new window


Once you've done all that, hit your submit button again, and you should see the same data as before, just formatted as a JSON string.

We're now ready to add in the AJAX request
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617413
Hooray going well... i get the json formatted data
{"results":[{"surname":"Rosberg","Construtor":"Mercedes","positionOrder":"1"},{"surname":"Magnussen","Construtor":"McLaren","positionOrder":"2"},{"surname":"Button","Construtor":"McLaren","positionOrder":"3"},{"surname":"Alonso","Construtor":"Ferrari","positionOrder":"4"},{"surname":"Bottas","Construtor":"Williams","positionOrder":"5"},{"surname":null,"Construtor":"Force India","positionOrder":"6"},{"surname":null,"Construtor":"Ferrari","positionOrder":"7"},{"surname":"Vergne","Construtor":"Toro Rosso","positionOrder":"8"},{"surname":"Kvyat","Construtor":"Toro Rosso","positionOrder":"9"},{"surname":null,"Construtor":"Force India","positionOrder":"10"},{"surname":"Sutil","Construtor":"Sauber","positionOrder":"11"},{"surname":null,"Construtor":"Sauber","positionOrder":"12"},{"surname":"Chilton","Construtor":"Marussia","positionOrder":"13"},{"surname":"Bianchi","Construtor":"Marussia","positionOrder":"14"},{"surname":"Grosjean","Construtor":"Lotus F1","positionOrder":"15"},{"surname":"Maldonado","Construtor":"Lotus F1","positionOrder":"16"},{"surname":"Ericsson","Construtor":"Caterham","positionOrder":"17"},{"surname":"Vettel","Construtor":"Red Bull","positionOrder":"18"},{"surname":"Hamilton","Construtor":"Mercedes","positionOrder":"19"},{"surname":"Massa","Construtor":"Williams","positionOrder":"20"},{"surname":"Kobayashi","Construtor":"Caterham","positionOrder":"21"},{"surname":"Ricciardo","Construtor":"Red Bull","positionOrder":"22"}]}

Open in new window

0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 2000 total points
ID: 40617447
Great - almost there. Looking at the JSON result, you're still including the <pre> </pre> tags in your PHP script - they'll need to be removed as well. JSON can be a fussy little thing and if the output isn't bang on, then it won't work.

Now for the fun part - the jQuery. Probably easier to post the code with comments and leave you to digest it. This needs pasting into the <script> tag in the HEAD of your doc:

$(document).ready(function() {
	// handle the change() event of the <select>
	$('select[name=F1]').change(function(){
		// if option 0 - Choose Race is selected, do nothing
		if ( $('option:selected', this).val() == 0 ) return;
		
		// make the AJAX request to your PHP script - POST the serialized form to data.php and return JSON
		$.ajax({
			url:        'data.php',
			method:     'post',
			dataType:   'json',
			data:       $(this).parents('form').serialize()
		})
		.done(function(data){
			// the AJAX request was successful so lets do something useful
			// the 'data' argument represents the JSON from your PHP script
			
			// clear out any old race results
			$('#raceResults tbody').empty();
			
			// loop through the race results - the 'results' key of the JSON data
			$.each(data.results, function(k, raceResult) {
				// create a new Table row, containing the info from the raceResult, and append it to the table body
				$('<tr>')
					.append( $('<td>').html(raceResult.surname ))
					.append( $('<td>').html(raceResult.Construtor ))
					.append( $('<td>').html(raceResult.positionOrder ))
					.appendTo('#raceResults tbody');
			}); 
		})
		.fail(function(x, status, error){
			// something went wrong with the AJAX request, so send out an alert.
			alert("Error: " + error);	
		});
	});
});

Open in new window


If all goes well, when you change the dropdown, the AJAX request will automatically be fired off and the results will be put into your table. Your PHP script seems a little slow, so it may look like it's not doing anything for a few seconds - give it time :)
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617459
PHP slow because running on original raspberrypi....

unsure but something is wrong...

1st page has dropdown <your html> when click view it goes to data.php which is json formatted... that is it... no table on
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617460
sorry ignore last comment: working: it helps to publish doh...
0
 
LVL 31

Author Closing Comment

by:James Murrell
ID: 40617466
This is a perfect example of how to answer and help people: not only have I learnt how to solve problem i had, i have more of a understanding of the how and why...

I cannot say thank you enough Chris....
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617469
No worries James - I prefer to help people understand what's going on than just offer a copy/paste solution. Hopefully you've now got a good base to move on with your project.

Good luck with it.

Chris
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617521
thanks now have to add new question about php and json... keep your eyes open
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40617575
If you post a link to it here I'll get notified :)
0
 
LVL 31

Author Comment

by:James Murrell
ID: 40617584
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to count occurrences of each item in an array.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question