Page 1 of 1
is my query correct?
Posted: Tue Jan 15, 2013 6:22 pm
by Porkypie
Hey all,
I'm trying to do an assignment for school.
Our website should be able to search in our database.
I think this could be done easier but we have to do it like this.
Anyways I could use some help and I was hoping to find it here.
I made the following form:
<form action="" method="get">
<label>Zoek op: </label>
<select name="filter">
<option value="*">alles</option>
<option value="achternaam">achternaam</option>
<option value="studentnummer">studentnummer</option>
<option value="voornaam">voornaam</option>
<option value="geboortedatum">geboortedatum</option>
<option value="slb">slb</option>
</select>
<input type="text" name="zoeken">
<select name="order">
<option value="achternaam">achternaam</option>
<option value="studentnummer">studentnummer</option>
</select>
<input type="submit" value="zoek">
</form>
I've got the following php code to go with it:
$search = mysql_query("select
* from student
where {$_GET['filter']}= {$_GET['zoeken']}
order by {$_GET['order']} asc");
I hope it's clear what I am trying to do here
If my sql query is wrong, could someone tell me how it should look otherwise.
Thanks in advance!
Re: is my query correct?
Posted: Tue Jan 15, 2013 7:03 pm
by ExtremeGaming
Well. For starters you have unsecure variables, which will allow sql injection. Second, use
echo mysql_error();
to find the error with your query.
Re: is my query correct?
Posted: Tue Jan 15, 2013 8:02 pm
by Porkypie
I know it's unsecured but it's only local and won't come online
these are the errors I get:
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 'tar') ORDER by ('studentnummer') asc' at line 1
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Users\B_Kortekaas\Desktop\Studentpackage\WOS Portable\www\opdracht\test.php on line 31
and this is the full code:
<html>
<head>
<title>Opdracht</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<?php
//connect
mysql_connect('localhost','root','*****');
mysql_select_db('hogeschool');
?>
<div id="search">
<table border="0">
<tr>
<td>
<a href="student.php">Student Toevoegen</a>
<a href="gegevens.php">Gegevens Aanpassen</a>
</td>
<td class="rechts">
<form action="test.php" method="get">
<label>Zoek op: </label>
<select name="filter">
<option value="*">alles</option>
<option value="achternaam">achternaam</option>
<option value="studentnummer">studentnummer</option>
<option value="voornaam">voornaam</option>
<option value="geboortedatum">geboortedatum</option>
<option value="slb">slb</option>
</select>
<input type="text" name="zoeken">
<select name="order">
<option value="achternaam">achternaam</option>
<option value="studentnummer">studentnummer</option>
</select>
<input type="submit" value="zoek">
</form>
</td>
</tr>
</table>
</div>
<div id="container">
<?php
$result = mysql_query("SELECT `studentnummer`, `achternaam`, `voornaam`, `geboortedatum`, `slb` FROM student");
echo mysql_error();
echo "<table>
<tr>
<th>studentnummer</th>
<th>voornaam</th>
<th>achternaam</th>
<th>geboortedatum</th>
<th>SLB</th>
</tr>";
while ($row = mysql_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['studentnummer'] . "</td>";
echo "<td>" . $row['achternaam'] . "</td>";
echo "<td>" . $row['voornaam'] . "</td>";
echo "<td>" . $row['geboortedatum'] . "</td>";
echo "<td>" . $row['slb'] . "</td>";
echo "</tr>";
}
echo "</table>";
die();
?>
</div>
<div id="toevoegen">
</div>
</body>
</html>
Re: is my query correct?
Posted: Tue Jan 15, 2013 8:13 pm
by Porkypie
found the problem
query was wrong
Re: is my query correct?
Posted: Tue Jan 15, 2013 8:25 pm
by Porkypie
Ok it's not over yet.
I also want the form to search in all columns.
can i put a certain value in the form option or should i do it in php?
and how should I do it?
Also how can I display all results if i leave my textfield empty in the form?
Re: is my query correct?
Posted: Tue Jan 15, 2013 11:36 pm
by ExtremeGaming
I personally would do it something like this:
<?php
// to select all columns in a table, do SELECT * FROM table (with an asterisk)
if(isset($_POST['filter']) && $_POST['filter'] != "All") {
// Do a query with a WHERE clause to handle the filter
} else {
// Do a query to select all
}
?>
<!-- Of course you would include all the proper tags but for this sample I won't -->
<form method="post">
<select name="filter">
<option>All</option>
<option>category 1</option>
<option>category 2</option>
<!-- more categories if you want -->
</select>
</form>
Just a heads up. Teachers usually scan assignments like this with something that searches the web for pages with similar information on it. So don't copy this word for word or it's plagiarism.