is my query correct?

Porkypie · Post by **Porkypie** » Tue Jan 15, 2013 6:22 pm

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!

ExtremeGaming · Post by **ExtremeGaming** » Tue Jan 15, 2013 7:03 pm

Well. For starters you have unsecure variables, which will allow sql injection. Second, use

echo mysql_error();

to find the error with your query.

Porkypie · Post by **Porkypie** » Tue Jan 15, 2013 8:02 pm

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>

Porkypie · Post by **Porkypie** » Tue Jan 15, 2013 8:13 pm

found the problem

query was wrong

Porkypie · Post by **Porkypie** » Tue Jan 15, 2013 8:25 pm

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?

ExtremeGaming · Post by **ExtremeGaming** » Tue Jan 15, 2013 11:36 pm

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.

BetterPHP

is my query correct?

is my query correct?

Re: is my query correct?

Re: is my query correct?

Re: is my query correct?

Re: is my query correct?

Re: is my query correct?