1

I'm trying to use PHP to pass HTML form data to a MYSQL db and return the data to the browser. By submitting checked permissions (M1,M2,MN1..) i want to display Names of instructors who have those permissions. Now, please tell me what is wrong with the code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head><body>
<form action="akcja.php" method="post">    
<br /><br /><br />
<table>
<tr><th><br/><input type="checkbox" id="check" name="M1" value="M1,">m1</th> 
<th><br/><center><input type="checkbox" id="check" name="M2" value="M2,">m2</center></th> 
<th><br/><center><input type="checkbox" id="check" name="MN1" value="MN1,"> mn1    </center></th> </tr></table>
<input type="submit" name="submit" value="Search Database" />
</form>
</body>
</html>



<?php
$query = mysql_query("SELECT * FROM permissions WHERE m LIKE '".$_POST['M1']."' OR m LIKE '".$_POST['M2']."' OR mn LIKE '".$_POST['MN1']."' ");  
if($query)
    while($permissions = mysql_fetch_assoc($query)){
        $query2 = mysql_query("SELECT name_surname FROM instruktorzy WHERE instruktor_id='".$permissions['instruktor_id']."'");  
        while($Mdwa = mysql_fetch_assoc($query2)){
            echo "<p style=\"font-size: 14px; font-family: Helvetica; background-color: #FFFFFF\"> ".$Mdwa['name_surname']."<br />" ; "</p>" ;
        }
    }
?>
animuson
  • 53,861
  • 28
  • 137
  • 147
Chrobry
  • 31
  • 1
  • 3
  • 11
  • Have you tried displaying `mysql_error()` on failure? – Waleed Khan Aug 20 '12 at 13:31
  • 3
    Aside from using insecure SQL query building and `mysql_` not sure without you saying whats not work. – Sammaye Aug 20 '12 at 13:31
  • 3
    Don't ever store $_POST variables without sanitizing your data. You are completely open to SQL Injection. – wesside Aug 20 '12 at 13:31
  • While im selecting only one checkbox i've recive: Notice: Undefined index: M2 in C:\wamp\www\kdp\instr\akcja.php – Chrobry Aug 20 '12 at 13:35
  • 2
    Yea you should use something like: `$m2 = isset($_POST['M2']) ? $_POST['M2'] : null` to stop `E_NOTICE` errors like that – Sammaye Aug 20 '12 at 13:36
  • 2
    Additional info on sanitizing: http://net.tutsplus.com/tutorials/php/sanitize-and-validate-data-with-php-filters/ if ($_POST['m1']) { $conditional .= 'm LIKE ' . sanitize($_POST['m1']); } You need to conditionally build that query. $query = 'SELECT * FROM permissions WHERE m LIKE ' . $conditional; – wesside Aug 20 '12 at 13:39
  • 2
    It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this article](http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/). – Matt Aug 20 '12 at 13:52

2 Answers2

2

This is how I see a cleaner way of orientating your code. I should note this was just quickly slapped together without any tools so don't copy and paste.

$connection = new PDO('mysql:host=localhost;dbname=db', 'awesome_user', 'love123',
array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''));

$query_obj = $connection->prepare("SELECT permissions.*, instruktorzy.name_surname 
    FROM permissions 
    LEFT JOIN instruktorzy ON instruktorzy.instruktor_id = permissions.instruktor_id  
    WHERE permissions.m IN (:m1, :m2) OR permissions.mn LIKE :mn1 LIMIT 100");
$query_obj->setFetchMode(PDO::FETCH_ASSOC);

// You will need something a little more complex here to deal with missing data, 
// I am just putting in what is required to get it working if the 
// entire $_POST is set
$query_obj->bindValue(':m1', isset($_POST['M1']) ? $_POST['M1'] : null);
$query_obj->bindValue(':m2', isset($_POST['M2']) ? $_POST['M2'] : null);
$query_obj->bindValue(':mn1', isset($_POST['MN1']) ? $_POST['MN1'] : null);

$query_obj->execute();

foreach($query_obj as $k => $row){
    echo '<p style="font-size: 14px; font-family: Helvetica; 
        background-color: #FFFFFF"> '.$row['name_surname'].'<br /></p>' ;
}

That should help you get on the right track of writing better code; hopefully.

Sammaye
  • 43,242
  • 7
  • 104
  • 146
  • Parse error: syntax error, unexpected '?', expecting ',' or ')' in C:\wamp\www\kdp\instr\pdoakcja.php on line 16 – Chrobry Aug 21 '12 at 08:16
  • @KL1M7R0И Whoops I forgot to close my brackets :P try again – Sammaye Aug 21 '12 at 08:26
  • It's finally working, thanks. And 1 more question - should i learn PDO or Doctrine2? – Chrobry Aug 21 '12 at 08:49
  • @KL1M7R0И It's upto you really, Doctrine2 is a lot heavier and resource intensive than PDO but it offers a standardised layer for a lot of stuff you'd have to write yourself. I would say look into both and pick which suites you :) – Sammaye Aug 21 '12 at 08:51
  • Maybe You know how to display the data on the same page as checkboxes, without reloading whole page?? AJAX? – Chrobry Aug 21 '12 at 09:17
  • @KL1M7R0И Indeed, AJAX would be required here. You would serialize the form (I use a library like JQuery to do this, it's easier) and then feed that as a post call to your PHP echoing the results back. – Sammaye Aug 21 '12 at 09:18
-1

The below code is a more clean way of doing things. Its been a long time since I used it in this way, but it might help you solve your problem since its probably because you use a queryloop in a queryloop. (not sure but wouldn't hurt if you do it like I did.

$permissions = Array();
$query = mysql_query("SELECT * FROM permissions WHERE m LIKE '" . $_POST['M1'] . "' OR m LIKE '" . $_POST['M2'] . "' OR mn LIKE '" . $_POST['MN1'] . "' ");
if ($query) {
  while ($row = mysql_fetch_assoc($query)) {
    array_push($permissions, $row);
  }
}
foreach ($permissions AS $key => $permission) {
  $query = mysql_query("SELECT name_surname FROM instruktorzy WHERE instruktor_id='" . $permission['instruktor_id'] . "'");
  if ($query) {
    while ($row = mysql_fetch_assoc($query2)) {
      echo "<p style=\"font-size: 14px; font-family: Helvetica; background-color: #FFFFFF\"> ".$Mdwa['name_surname']."<br />" ; "</p>" ;
    }
  }
}
Chris Visser
  • 1,607
  • 12
  • 24