simple search tutorial, results in a table?

[nearenough]

im a complete novice.

 

im using one of the tutorials on here to make a simple search form. the bit of script that i think outputs the results is

 

        $results = array(); // the result array

        $i = 1;

        while ($row = mysql_fetch_assoc($searchResult)) {

            $results[] = "{$i}: {$row['placing']}, &nbsp  {$row['racedate']}, &nbsp  {$row['horseid']}<br /><br />";

            $i++;

 

how do i make these results appear in a table, and how would i make it so there was a link on the date that clicked would show all the races on that day?

 

sorry i advance as this is probably the most dumb of questions!

[DWilliams]

You can use standard HTML techniques to do what you desire. The table part is easy, but I'm not sure how your site is set up so it's a bit hard for me to say exactly what your link needs to look like but here's my modification to your code to get you started:

 

$i = 1;
echo '<table border="1"><tr><th>Num</th><th>Placing</th><th>Race Date</th><th>Horse ID</th></tr>';
while ($row = mysql_fetch_assoc($searchResult)) 
{
   echo "<tr><td>$i</td>
                    <td>{$row['placing']}</td>
                    <td><a href=\"viewrace.php?date={$row['racedate']}\">{$row['racedate']}</a></td>
                    <td>{$row['horseid']}</td></tr>";
   $i++;
}
echo '</table>';

 

That should display your results in a table. Modify it to look how you want. As it is now, if the user clicks on the race date it will take them to a "viewrace.php" page with the date passed in as a $_GET variable so that page could take that date and query for races on that date. Obviously since I don't know your site structure you'll have to adjust that, I'm just showing how it could work.

[nearenough]

thats excellent, thank you very much. ive been faffing about for a week trying to learn php and databases and all that. last week i knew nothing about them, now i know how to use a mysql database and link it to webpages and have a flimsy grasp of very basic php.

 

hopefully at the end of this i will have a site with harness racing results from the UK that people can search for  race results and horses form on.

 

so the next question is what query do i use to take this variable "date passed in as a $_GET variable" and output races on that date in raceview.php?

[DWilliams]

thats excellent, thank you very much. ive been faffing about for a week trying to learn php and databases and all that. last week i knew nothing about them, now i know how to use a mysql database and link it to webpages and have a flimsy grasp of very basic php.

 

hopefully at the end of this i will have a site with harness racing results from the UK that people can search for  race results and horses form on.

 

so the next question is what query do i use to take this variable "date passed in as a $_GET variable" and output races on that date in raceview.php?

 

Well it's not quite that simple. You'll have to make another page, assuming you don't have one. In my example, viewrace.php would be what you create.

 

GET variables are the extra stuff you see in the URL after your page name like viewrace.php?id=1&hello=world . Everything past the ? is a GET variable. They can be put there by a form with it's method set to GET, or manually like I did in my example. In viewrace.php, you could now access the variables set in the URL like so:

 

echo $_GET['id']; // Outputs "1"
echo $_GET['hello']; // Outputs "world"

 

Knowing that, you can make a viewrace.php which takes the 'date' GET variable and queries the database for all races with the same date as $_GET['date'] then display them like you've already done.

 

Also I made a slight omission in my example. The line:

 

<td><a href=\"viewrace.php?date={$row['racedate']}\">{$row['racedate']}</a></td>

 

should be changed to

 

<td><a href=\"viewrace.php?date=" . urlencode($row['racedate']) . "\">{$row['racedate']}</a></td>

 

That adds a call to urlencode() which will make it possible to pass special characters through the URL (like date separators).

 

That's just a basic overview of the concept. There's still a lot more to learn to make a "better" system (like user input cleanup/security primarily) but that's the basic idea.

[nearenough]

sooo i had a stab at writing racedate.php and came up with:

 

// Set up our error check and result check array

$error = array();

$results = array();

 

// First check if a form was submitted.

// Since this is a search we will use $_GET

if (isset($_GET['search'])) {

  $searchTerms = trim($_GET['search']);

  $searchTerms = strip_tags($searchTerms); // remove any html/javascript.

 

  if (strlen($searchTerms) < 3) {

      $error[] = "Search terms must be longer than 3 characters.";

  }else {

      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.

  }

 

  // If there are no errors, lets get the search going.

  if (count($error) < 1) {

      $searchSQL = "SELECT id, placing, racedate, horseid FROM race WHERE ";

     

      // grab the search types.

      $types = array();

      $types[] = isset($_GET['racedate'])?"`racedate` ":'';

     

      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

     

          $andOr = isset($_GET['matchall'])?'AND':'OR';

      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `placing`"; // order by title.

 

      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

     

      if (mysql_num_rows($searchResult) < 1) {

        $error[] = "The search term provided {$searchTerms} yielded no results.";

      }else {

        $results = array(); // the result array

        $i = 1;

echo '<table border="1"><tr><th>Num</th><th>Placing</th><th>Race Date</th><th>Horse ID</th></tr>';

while ($row = mysql_fetch_assoc($searchResult))

{

  echo "<tr><td>$i</td>

                    <td>{$row['placing']}</td>

                    <td>{$row['racedate']}</a></td>

                    <td><a href=\"viewhorse.php?horseid=" . urlencode($row['horseid']) . "\">{$row['horseid']}</td></tr>";

  $i++;

}

echo '</table>';

          }

      }

  }

 

 

function removeEmpty($var) {

  return (!empty($var));

}

?>

<html>

  <title>My Simple Search Form</title>

  <style type="text/css">

      #error {

        color: red;

      }

  </style>

  <body>

      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>

      <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">

        Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br />

        Search In:<br />

        Form Line: <input type="checkbox" name="placing" value="on" <?php echo isset($_GET['placing'])?"checked":''; ?> /> |

        racedate: <input type="checkbox" name="racedate" value="on" <?php echo isset($_GET['racedate'])?"checked":''; ?> /> |

        Horse Name: <input type="checkbox" name="horseid" value="on" <?php echo isset($_GET['horseid'])?"checked":''; ?> /><br />

                Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />

        <input type="submit" name="submit" value="Search!" />

      </form>

      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>

  </body>

</html>

 

which had a 0.1% chance of working, and doesnt!

 

i presume that

 

$types[] = isset($_GET['racedate'])?"`racedate` ":'';

 

is the wrong bit?

 

im glad backing winners at the races is easier than php!!

[nearenough]

phew, think ive got it hammered into my head at last

 

 

if (isset($_GET['date'])) {

  $searchTerms = trim($_GET['date']);

  $searchTerms = strip_tags($searchTerms); // remove any html/javascript.

 

  if (strlen($searchTerms) < 3) {

      $error[] = "Search terms must be longer than 3 characters.";

  }else {

      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.

  }

 

  // If there are no errors, lets get the search going.

  if (count($error) < 1) {

      $searchSQL = "SELECT id, placing, racedate, horseid FROM race WHERE ";

     

      // grab the search types.

      $types = array();

      $types[] = isset($_GET['date'])?"`racedate` LIKE '%{$searchTermDB}%'":'';

 

 

 

works!

 

thank you for all your help, VERY much appreciated