Results out of order

[davidcriniti]

Hi,

 

I have developed a website for a few mates who are organising a long distance running race (which is currently in progress).

 

I've got a search page and a display page, which pulls results from the database (the 'entrants' table and the 'results' table).

 

It works fine for results that I put in initially (previous years' results).

 

However, new records that I'm entering are going to the bottom of the results when it's not what I would have thought would happen.

 

Example - Our winner crossed the line in 26:01:40

 

If you do a search (and don't select a year), this should be at the top, as it is a race record, but his name (Jo Blake) ends up at the bottom.

 

htthttp://www.coast2kosci.com/results_sd_search.php

 

 

 

Any help or advice would be appreciated.

 

Thanks,

 

Dave

 

 

 

 

 

 

 

 

 

 

 

[cags]

If you don't provide an ORDER BY command in your MySQL statement they will come out of the database in the order they went in. If your used something along the lines of...

 

SELECT * FROM racers ORDER BY time ASC 

 

Then you should get them in the order they finished (dependent on data type used for the TIME column).

[davidcriniti]

Thanks cags. I tried to do this and at first glance it seemed i got it right first go. When I searched all results, there was no problem.

 

However, when I limited the search (by year or course or gender etc), I got the following error (this one is when I tried to select a specific year):

 

Error retrieving results from database!

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND year='2004'' at line 1

 

(note - I sorted by 'timeisnull' and then 'time' because I put a timeisnull column in so that dnfs would be listed under the results based on a time. ie: If a person dnf'd, their time would be 'dnf' which would appear above a the time if sorted by time, but if sorted by time is null with dnfs given a '1', and people who finished getting a '0', those who finished would be sorted first)

 

Here is the original code:

 

<?php

$dbcnx = @mysql_connect('localhost', 'coast2ko_me', 'monners1');
if (!$dbcnx) {
  exit('<p>Unable to connect to the ' .
      'database server at this time.</p>');
}

if (!@mysql_select_db('coast2ko_results')) {
  exit('<p>Unable to locate the results ' .
      'database at this time.</p>');
}

// The basic SELECT statement
$select = 'SELECT DISTINCT firstname, lastname, gender, time, year, course, position, gender_position, distance_completed';
$from   = ' FROM entrants, results ' ;
$where  = ' WHERE results.entrantid = entrants.entrantid ';

$year = $_POST['year'];
if ($year != '') { // A year is selected
  $where .= " AND year='$year'";
}

$course = $_POST['course'];
if ($course != '') { // A course is selected
  $where .= " AND course='$course'";
}


$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
  $where .= " AND lastname LIKE '%$searchtext%'";
}

$gender = $_POST['gender'];
if ($gender != '') { // Gender is selected
  $where .= " AND gender='$gender'";
}

?>

 

 

And here is the modified code. THe only difference between the two is on the end of line which begins with the $where variable.

 

 

  <?php

$dbcnx = @mysql_connect('localhost', 'myusername', 'mypassword');
if (!$dbcnx) {
  exit('<p>Unable to connect to the ' .
      'database server at this time.</p>');
}

if (!@mysql_select_db('coast2ko_results')) {
  exit('<p>Unable to locate the results ' .
      'database at this time.</p>');
}

// The basic SELECT statement
$select = 'SELECT DISTINCT firstname, lastname, gender, time, year, course, position, gender_position, distance_completed';
$from   = ' FROM entrants, results ' ;
$where  = ' WHERE results.entrantid = entrants.entrantid ORDER BY timeisnull, time ASC ';

$year = $_POST['year'];
if ($year != '') { // A year is selected
  $where .= " AND year='$year'";
}

$course = $_POST['course'];
if ($course != '') { // A course is selected
  $where .= " AND course='$course'";
}


$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
  $where .= " AND lastname LIKE '%$searchtext%'";
}

$gender = $_POST['gender'];
if ($gender != '') { // Gender is selected
  $where .= " AND gender='$gender'";
}

?>

 

 

 

 

 

 

 

 

T