SQL statement

ayeboy

Hi,

I'm doing a college project and am trying to query the database using a drop down list in html. Any clues much appreciated.

I think I have the html bit set up ok:

<form method="POST" action="http://localhost/test/roomlocator.php&quot; data-ajax="false">


<p>

Choose Your Module:

<select name="choices" id="choices">
<option value="">Select .....</option>
<option value="1">Design Practice</option>
<option value="2">Design Principles</option>
<option value="3">Digital Asset Management</option>
<option value="4">Digital Audio</option>
<option value="5">Digital Signal Processing</option>
</select>
<button type="submit">Submit</button>
</form>

I am having a problem when I get to the php file. How should it be written to take the values (e.g. "1">Design Practice) from the dropdown html form and use them in the sql statement. I just dont know how to set the variable for $moduleName. I think this is were my problem lies.

<?php

$conn = mysql_connect('localhost', 'root');
mysql_select_db('project', $conn);

$moduleName = $_POST;

$moduleName = ????????


$result = mysql_query("SELECT moduleName, location
FROM moduletable
WHERE moduleName =$moduleName", $conn);
while ($row =mysql_fetch_array($moduleName))
{
echo "<tr><td> $row[moduleName]</td><td> $row[location] </td> </
tr>";
}
mysql_close($conn);

?>

arleitiss

Would you not rather use GET method for this case? I mean not that it's a confidential information is being parsed?
In my experience using _GET for this type of thing you want to make is much easier.

Giblet

You need to sanitise any inputs being processed, especially using inline SQL like you are.
I can stuff variables into a post quite easily.

$moduleName = $_POST['moduleName'] // I stuff "1; drop table moduleTable; --" 

Query is now

SELECT moduleName, location
FROM moduletable 
WHERE moduleName =1; drop table moduleTable; --", $conn);

zzap64

Security / sanitisation is very important when doing database access but the major problem here is the line:

$moduleName = $_POST;

There's no tag in the HTML with that name, so it'll always be blank.

Looking at your select: "<select name="choices" id="choices">".

You want to get the "choices" into your PHP so you have to do:

$moduleName = $_POST;

which should get you on your way.

BTW:

$moduleName will contain "" or 1-5 so don't be expecting text in your input. It's the text that you set the value to in the <option> tag that is sent with the form

ayeboy

Hi Guys,

Thanks for replying. But still no good. This stuff is all new to me and most of it is going over my head.

Zzap - yeah I had html labelled incorrectly but even when I changed it (id="name"), it still doesnt do anything. Im getting an error message:

Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in C:\wamp\www\test\roomlocator4php.php on line 16

<select name="name" id="name" >
                    <option value="">Select .....</option>
                    <option value="1">Design Practice</option>
                    <option value="2">Design Principles</option>
                    <option value="3">Digital Asset Management</option>
                     <option value="4">Digital Audio</option>
                       <option value="5">Digital Signal Processing</option>
         </select> 
         <button type="submit">Submit</button>
</form> 

<?php

$conn = mysql_connect('localhost', 'root');
mysql_select_db('project', $conn);

$moduleName = $_POST["name"];


$result = mysql_query("SELECT moduleName, location
FROM moduletable 
WHERE moduleName =$moduleName", $conn);
while ($row =mysql_fetch_array($moduleName)) [I][B]<!-- this is line 16 -->[/B][/I]
{
echo "<tr><td> $row[moduleName]</td><td> $row[location] </td> </
tr>";
}
mysql_close($conn);

?>

Giblet, I dont quite follow exactly what changes you make to the variable. Can you please explain more?

Cheers!!

Niall09

[PHP]while ($row =mysql_fetch_array($moduleName))[/PHP]

You need to replace $moduleName with $result because $moduleName isn't a mysql query, whereas $result is

[PHP]while ($row =mysql_fetch_array($result))[/PHP]

I also presume the moduleName column in your database are 1, 2, 3, 4, and 5, rather than the name. Or else your query won't work.

Another thing, you should put or die(mysql_error()) after any mysql queries as it gives you much better error messages!

[PHP]$result = mysql_query("SELECT moduleName, location
FROM moduletable
WHERE moduleName = $moduleName", $conn) or die(mysql_error());[/PHP]

ayeboy

Niall, you're a star.

I think i must have changed $result by accident when I was hitting and hoping by changing bits here and there.

Also, numbers werent corresponding with the database either but thats sorted now as well.

Simple enough stuff I presume but a total head wreck when you dont know exactly what youre doing and cant get it working.

Anyhow, thanks guys for helping!!