mercredi 22 juin 2016
Tabulate results of PHP MYSQL search using class-search.php
So I have a large database (120k rows) and I'm building a little web app to make it useful for the community (astronomy based stuff). I'm new to php and am looking for a little help.
I have a search.php file and a class-search.php file and at the moment the results look like this:
2 results found
Array
(
[count] => 2
[results] => Array
(
[0] => stdClass Object
(
[disc] => LOC 7
[fstdate] => 2016
[lstdate] => 2016
[lstpa] => 170
[lstsep] => 0.4
[stype] =>
[comp] => AB
[fstmag] => 13.80
[secmag] => 14.10
[dnum] =>
[rahour] => 05
[ramin] => 38
[rasec] => 48.04
[decdeg] => -02
[decmin] => 27
[decsec] => 14.2
)
[1] => stdClass Object
(
[disc] => LOC 7
[fstdate] => 2016
[lstdate] => 2016
[lstpa] => 284
[lstsep] => 7.5
[stype] =>
[comp] => AB,C
[fstmag] => 13.20
[secmag] => 16.60
[dnum] =>
[rahour] => 05
[ramin] => 38
[rasec] => 48.04
[decdeg] => -02
[decmin] => 27
[decsec] => 14.2
)
)
)
I'm looking to tabulate the results and I have got a rough idea what I'm doing but I'm not quite getting everything joined up - can anyone suggest anything please.
The key part of search.php looks like this: (Code may not be fully accurate as I've taken out some switches and ifs to reduce post length)
<?php
//Check if search data was submitted
if ( isset( $_GET['s'] ) ) {
// Include the search class
require_once( dirname( __FILE__ ) . '/class-search.php' );
// Instantiate a new instance of the search class
$search = new search();
// Store search term into a variable
$search_term = htmlspecialchars($_GET['s'], ENT_QUOTES);
// Send the search term to our search class and store the result
$search_results = $search->search($search_term);
}
?>
<!DOCTYPE html>
<html>
<head>
<title>WDSC search</title>
</head>
<body>
<h1>Search the WDSC</h1>
<div class="search-form">
<form action="" method="get">
<div class="form-field">
<label for="search-field">Search term</label>
<input type="search" name="s" placeholder="Enter your search term..." results="5" value="<?php echo $search_term; ?>">
<input type="submit" value="Search">
........
</form>
</div>
<?php if ( $search_results ) : ?>
<div class="results-count">
<p><?php echo $search_results['count']; ?> results found</p>
</div>
<div class="results-table">
<?php foreach ( $search_results['results'] as $search_result ) : ?>
<div class="result">
<p><?php echo $search_result->title; ?></p>
</div>
<?php endforeach; ?>
</div>
<div class="search-raw">
<pre><?php print_r($search_results); ?></pre>
</div>
<?php endif; ?>
</body>
</html>
and the key parts of class-search.php looks like this:
<?php
/**
* Performs a search
*
* This class is used to perform search functions in a MySQL database
*
*/
class search {
/**
* MySQLi connection
* @access private
* @var object
*/
private $mysqli;
/**
* Constructor
*
* This sets up the class
*/
public function __construct() {
// Connect to our database and store in $mysqli property
$this->connect();
}
/**
* Database connection
*
* This connects to our database
*/
private function connect() {
$this->mysqli = new mysqli( 'server', 'user', 'pass', 'db' );
}
/**
* Search routine
*
* Performs a search
*
* @param string $search_term The search term
*
* @return array/boolen $search_results Array of search results or false
*/
public function search($search_term) {
// Sanitize the search term to prevent injection attacks
$sanitized = $this->mysqli->real_escape_string($search_term);
// Define variable for search category from list selection
$cat = $_GET['category'];
// Run the query.
// Query for discoverer
$query = $this->mysqli->query("
SELECT disc, fstdate, lstdate, lstpa, lstsep, stype, comp, fstmag, secmag, dnum, rahour, ramin, rasec, decdeg, decmin, decsec
FROM WDS_CAT
WHERE $cat LIKE '{$sanitized}%'
");
}
// Check results
if ( ! $query->num_rows ) {
return false;
}
// Loop and fetch objects
while( $row = $query->fetch_object() ) {
$rows[] = $row;
}
// Build our return result
$search_results = array(
'count' => $query->num_rows,
'results' => $rows,
);
return $search_results;
}
}
endif;
?>
I've tried to link a table to the variable $search_results by means of
echo '<table border=1px>'; // opening table tag
echo'<th>Discoverer</th><th>First year observed</th><th>Last year observed</th><th>Last position angle</th><th>Last separation</th><th>Spectral type</th><th>Components</th><th>Primary Magnitude</th><th>Secondary magnitude</th><th>Durchmusterung number</th><th>Right ascension</th><th></th><th></th><th>Declination</th><th></th><th></th>'; //table headers
while($data = mysql_fetch_array($search_results))
{
// we are running a while loop to print all the rows in a table
echo'<tr>'; // printing table row
echo '<td>'.$data['disc'].'</td><td>'.$data['fstdate'].'</td><td>'.$data['lstdate'].'</td><td>'.$data['lstpa'].'</td><td>'.$data['lstsep'].'</td><td>'.$data['stype'].'</td><td>'.$data['comp'].'</td><td>'.$data['fstmag'].'</td><td>'.$data['secmag'].'</td><td>'.$data['dnum'].'</td><td>'.$data['ragiyr'].'</td><td>'.$data['ramin'].'</td><td>'.$data['rasec'].'</td><td>'.$data['decdeg'].'</td><td>'.$data['decmin'].'</td><td>'.$data['decsec'].'</td>'; // we are looping all data to be printed till last row in the table
echo'</tr>'; // closing table row
}
echo '</table>'; //closing table tag
?>
<?php endif; ?>
But no joy - can anyone suggest something please.
Thanks in advance
Inscription à :
Publier les commentaires (Atom)
Aucun commentaire:
Enregistrer un commentaire