Solved

get results based on drop down from same database

Posted on 2015-02-18
24
453 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
  • 13
  • 11
24 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 31

Author Comment

by:James Murrell
Comment Utility
Apologies: i thought Value was working - it is not
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
sorry ignore last comment: working: it helps to publish doh...
0
 
LVL 31

Author Closing Comment

by:James Murrell
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
thanks now have to add new question about php and json... keep your eyes open
0
 
LVL 42

Expert Comment

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

Author Comment

by:James Murrell
Comment Utility
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now