-2

As the title states, I'm trying to send html form checkbox data (that is supposed to query a MySQL database) to a PHP script where the output should be a table of the queries' results. I have one document for the html and another for the php/sql. The query works fine in MySQL but when I try executing it through html/php, I get column names for the results table but no queried results. This is THE problem.

The database groups users into groups and groups into departments. The query finds user vacancies (where user_id=1) within departments. I imagine it's the php that's screwing me up. Here's what I have so far--

The html form:

<form name="dept_checkbox" method="post" action="dept.php">
<fieldset>
<legend>In which department would like to find open positions?</legend><br />
<input type="checkbox" name="dept_name" id="dept_name" value="First Department" />First Department<br />
<input type="checkbox" name="dept_name" id="dept_name" value="Second Department" />Second Department<br />
<input type="checkbox" name="dept_name" id="dept_name" value="Third Department" />Third Department<br />
<input type="checkbox" name="dept_name" id="dept_name" value="Fourth Department" />Fourth Department<br />
<input type="checkbox" name="dept_name" id="dept_name" value="Fifth Department" />Fifth Department<br />
<input type="checkbox" name="dept_name" id="dept_name" value="All Departments"/>All Departments<br /><br />
<input type="submit" value="Submit" />
</fieldset>

The above displays fine in the browser. The following is my failed attempt to run the sql query through php. At this point, i'm just trying to get it to work for one department ("First Department") and, once I get that to work, I figure I can go from there. Here's the sql/php minus the database login info at the beginning of the script:

if(isset($POST[dept_name])){
  $sql="select user_role,group_name
  from groups,users,groups_users_link,departments
  where groups.group_id=groups_users_link.groups_id
  and users.user_id=groups_users_link.user_id
  and groups.department_id=departments.department_id
  and league_name like \"First Department\"
  and groups_users_link.user_id=1";
  $result=mysql_query($sql);
}

Let me reiterate that the above query is working in MySQL. The following is the rest of the php script which is supposed to display the results in table form:

echo("<table border=\"10px\">");
echo("<tr><th>Role</th><th>Group</th></tr>");
while($myrow=mysql_fetch_array($result))
{
echo("<tr><td>".$myrow[user_role]."</td>");
echo("<td>".$myrow[group_name]."</td>");
}
echo("</table>");
?>
<h4><a href="dept.html">Back to form</a></h4>

I'm wanting this to display all the vacant user roles for such-and-such groups within the department "First Department." Instead it displays the column names but the query results are missing. Any help would be greatly appreciated.

Cosmophile
  • 3
  • 2
  • 1
    `$myrow[user_role]` fails because you have no string encapsulation, so `user_role` is treated as a constant, as opposed to a string (add quotes around user_role). Furthermore, enable error reporting. Lastly, migrate to `mysqli` or `pdo` – Ohgodwhy Apr 28 '15 at 02:02
  • 1
    Please quote your array keys when they are strings, ie `$POST['dept_name']` and `$myrow['group_name']`. These typically generate E_NOTICE's, not errors, but is the correct way. – John McMahon Apr 28 '15 at 02:04
  • Have you tried echoing the contents of `$POST['dept_name']`? I see in your query code you aren't using it, but maybe that was for testing purposes. – John McMahon Apr 28 '15 at 02:05
  • Also right after the query check for mysql errors: `if (!$result) echo mysql_error();` (And please, please switch to mysqli or pdo in the future.) – John McMahon Apr 28 '15 at 02:08
  • 1
    This `$POST[dept_name]` is incorrect for a few reasons. One of which has already been said, but didn't mention the fact that it's a superglobal http://php.net/manual/en/language.variables.superglobals.php - You also need to set your checkboxes as arrays. Plus, possibly a missing closing form tag. Your query will also need to be modified to accept the array. – Funk Forty Niner Apr 28 '15 at 02:10
  • Whoops! Such a dumb mistake. Fixed. Thanks for the quick feedback ya'll. – Cosmophile Apr 28 '15 at 02:17
  • beyond what is stated above, you are calling for data in an array but are not calling it as such. e.g. `name="dept_name"` should be `name="dept_name[]"`. As mentioned before, `$POST[dept_name]` should likely be `$_POST['dept_name']`. Your query has other issue, though you claim it works, I'm wondering how you are getting a `like` query without wildcards to work properly; it seems you are looking for something different such as `and league_name ='First Department' since you have already specified the value of your checkboxes. – nomistic Apr 28 '15 at 02:17
  • tells us what happens when you click more than 1 checkbox. – Funk Forty Niner Apr 28 '15 at 02:23
  • Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 28 '15 at 11:33

1 Answers1

0

In your HTML for the checkboxes, use array notation for the checkbox names:

name="dept_name[]"

Then when you are processing the posted form, you have an error where $POST should be $_POST.

Finally, switch to mysqli or pdo if possible. The mysqli functions can be used in a very similar fashion to mysql, it's very easy to switch if you aren't in a large legacy codebase.

John McMahon
  • 1,605
  • 1
  • 16
  • 21
  • Cool. Totally works now. And I'll see about changing to mysqli. Thanks again for the quick response. I'd upvote this but I got no reps :( – Cosmophile Apr 28 '15 at 02:20
  • On a side note, if you want the user to only choose one department at a time then you should switch to using radio buttons instead of checkboxes. If you are allowing multiple department selections then your query will need to be modified. As-is it may not work the way you expect if someone selects multiple checkboxes. – John McMahon Apr 28 '15 at 02:23
  • Hmmm. Yeah I am going for multiple selections. I'll tool around with it and see what happens – Cosmophile Apr 28 '15 at 02:34