Sorting data from mysql database
OK, obviously my first explanation of this was not clear... I wasn't exactly sure what to say. To simplify, what do I need to do to use the form below to successfully search in the mysql database I have? I want to have the user select from the three options in the dropdown menu and then type in the text to search and get the results that match from that category displayed.
html file:
<html>
<body>
<h2>Database Search</h2>
<form action="retrieve_data.php" method="POST">
<input type='hidden' name='submitted'>
Search for your information:
<select name="field" id="field">
<option value="id">id</option>
<option value="name">name</option>
<option value="username">username</option>
</select>
Search item:<input type="text" name='cond' size='20'><br><br>
<input type="submit">
</form>
</body>
</html>
php code I currently have that searches using checkboxes, which I don't want:
<?php
extract($_REQUEST);
$fields = 0;
$query = "select ";
$th = "";
for( $i = 0; $i < count($chk); $i++ ) {
if( $chk[$i] ) {
$query .= $chk[$i].',';
$th .= "<th>".$chk[$i]."</th>";
$fields++;
}
}
开发者_运维百科 $query = rtrim($query,',');
$query .= "user";
if(!empty($cond)) {
$query .= " where ".$cond;
}
$query .= " name ".$sorted;
$query .= " ".$name;
@ $db = new mysqli('localhost','root','','userData');
if (mysqli_connect_errno()) {
echo "Can't connect to Server. Errorcode: ", mysqli_connect_error();
exit;
}
else {
echo "Connected successfully<br />";
}
$result = $db->query(stripslashes($query));
$numrecs = $result->num_rows;
echo "Your results: <br />";
echo "<b>".$query."</b><br />";
echo "<table border='2'>";
echo $th;
for ($i = 0; $i < $numrecs; $i++ ) {
$row = $result->fetch_array();
echo "<tr>";
for($f = 0; $f < $fields; $f++ ) {
echo '<td>'.$row[$f].'</td>';
}
echo '</tr>';
}
echo "</table>";
?>
Finding the entry's, sorting them and choose them by category is a quest which the MySQL is suited for.
So what you'll want to have is a query that uses:
- A
LEFT JOINon your category-table - A
LIKE-statement to find the things "like" the users input - And an
ORDER BYto sort them - Also, you might need to
GROUP BYin order to sort the items (depends on how you want to sort them).
Your query preparation logic looks a little clumsy.
I suggest you separate your "table code" from the query preparation logic. Prepare the mysql query result object first and then code for the table part. That is the basic of separating the view from the controller logic. Your view code will then not need to be changed when you change the way of preparing the result (controller code).
And assume the database query part as one function (say getSortedResult()) which takes the user posted values as a parameter and returns the result object sorted in some order. Decide a format in which to pass the params. Say, you can select multiple values in the category select. Then lets take an array as a param.
Now, you just need to prepare the category param and call the getSortedResult(). Print the posted vals, and see what you get,
$postedCategory = $_POST['field']; //dropdown's posted values
print_r($postedCategory);
//you may just need to pass this param.
getSortedResult($postedCategory, $_POST['cond']);
Now, inside the getSortedResult func -
function getSortedResult($categories, $cond)
{
$db = new mysqli('localhost','root','','userData');
if (mysqli_connect_errno()) {
echo "Can't connect to Server. Errorcode: ", mysqli_connect_error();
exit;
}
else
{
//Assuming that you have some category field in the same table. If category is a separate table, you will need to do a join, refer Lukas Knuth's answer.
$query = "select * from table where category in (".implode(",",$categories)." and somefield = '".$cond."' order by <field you want>";
return $result = $db->query(stripslashes($query));
}
}
Finally in the display logic, loop over the result object and prepare the table -
?>
Your results: <br />
<table border="2">
<?php foreach($results as $each) : ?>
//
// row preparation
<?php endforeach; ?>
</table>
加载中,请稍侯......
精彩评论