Passing an 'id' to query WHERE in a table

Hi,
Using a pull down menu I pass an ‘id’ to a function which selects rows of a table where only that id is present. From this selection I create an array ‘newNotes’. But I can’t display this array.
The pull down menu form,
search.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/helpers.inc.php';  
      include_once $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/func.inc.php'; 
      include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/buildcourses.inc.php';
      include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/buildlearners.inc.php';
      include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/buildnotes.inc.php'; ?>
<!DOCTYPE html>
<html lang="en">
  <body>
      <p><a href="..">physCMS home</a> &#8658; <a href="/artgibney/admin/notes/">Manage Daily Notes</a></p>
      <?php include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/logout.inc.html.php'; ?>
 <form action="" method="get">
      <div>
        <label for="student">By student:</label>
        <select name="student" id="student">
          <?php foreach ($learners as $learner): ?>
            <option value="<?php htmlout($learner['id']); ?>"><?php
                htmlout($learner['learner']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div>
        <input type="hidden" name="action" value="searchview">
        <input type="submit" value="Search">
      </div>
    </form>
  </body>
</html>

This passes an ‘id’ to a function in index.php

if (isset($_GET['action']) and $_GET['action'] == 'searchview')
{ 
  include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';
  // Build the new list of notes rows where id's matches
  try
  {
    //$result = $pdo->query('SELECT id, absence, late, equip, effort, comment, date, time FROM notes);
    $sql = 'SELECT id, effort FROM notes WHERE 
    id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
    }
    
  catch (PDOException $e)
  {
    $error = 'Error fetching list of notes.';
    include 'error.html.php';
    exit();
  }

  foreach ($s as $row)
  {
    $newNotes[] = array('id' => $row['id'], 'effort' => $row['effort']);
  }
  include 'searchview.html.php';
  exit();
}

Then I should be able to display this array $newNotes in searchview.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .
    '/artgibney/includes/helpers.inc.php'; ?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Manage Topics: Search Results</title>
  </head>
  <body>
     <p><a href="..">physCMS home</a> &#8658; <a href="/artgibney/admin/notes/">Manage Daily Notes</a></p>
   <?php include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/logout.inc.html.php'; ?>
    <h1>Search Results</h1>
    <?php   
    echo "<pre>";
    print_r($newNotes);
    echo "</pre>";
    ?>
  </body>
</html>

I think the problem may be with this line in index.php

$s->bindValue(':id', $_POST['id']);

Perhaps I can’t use $_POST here.

Any help would be greatly appreciated,
Thanks,
Shane

Your form is using GET but you are trying to use an id that was sent as POST.

If the intention is to perform updates then change the method=“GET” in the form to method=“POST”.

If the form is not supposed to be updating anything then change the $_POST reference to $_GET.

Note that you should be validating the value passed before using it in the bindValue - as otherwise the id can be set to be anything at all by the person sending the request (regardless of whether you use GET or POST).

Hi,
Thanks for your reply.
I changed all to ‘post’

 <form action="" method="post">
      <div>
        <label for="student">By student:</label>
        <select name="student" id="student">
          <?php foreach ($learners as $learner): ?>
            <option value="<?php htmlout($learner['id']); ?>"><?php
                htmlout($learner['learner']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div>
        <input type="hidden" name="action" value="searchview">
        <input type="submit" value="Search">
      </div>
    </form>

and in the controller index.php

if (isset($_POST['action']) and $_POST['action'] == 'searchview')
{ 
  include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';

  // Build the new list of notes rows where id's matches
  try
  {
    //$result = $pdo->query('SELECT id, absence, late, equip, effort, comment, date, time FROM notes);
    $sql = 'SELECT id, effort FROM notes WHERE 
    id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
    }

When I hit the submit button on the pulldown menu, it just returns to this menu.

How do I ‘validate’ it? Or more to the point what is meant by that? Make sure it isn’t a mysql injection?

Thanks,
Shane

I saw that you’re using:

And then you loop on it:

foreach ($s as $row)
  {
    $newNotes[] = array('id' => $row['id'], 'effort' => $row['effort']);
  }

But, in the official doc here: http://php.net/manual/en/pdostatement.execute.php

They say that the value returned by execute is either TRUE or FALSE.
You should probably check fetch, fetchall and fetch column.

Edit: Also, if you use a SELECT on an ID, I assume that you will get a unique record from the database? If so, you don’t need to use a loop to get the data. :smile:

Test that it has a value that it is allowed to have and does not contain something that could not possibly be an id.

Hi,
Thanks for both of your replies.
felgall I will validate it later, for now I am just trying to pass the array. I do have a function for validating things which I will be able to use.

I think I am correctly using fetchAll(),

if (isset($_POST['action']) and $_POST['action'] == 'searchview')
{ 
  include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';

  // Build the new list of notes rows where id's matches
  try
  {
    //$result = $pdo->query('SELECT id, absence, late, equip, effort, comment, date, time FROM notes);
    $sql = 'SELECT id, effort FROM notes WHERE 
    id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
    }
    
  catch (PDOException $e)
  {
    $error = 'Error fetching list of notes.';
    include 'error.html.php';
    exit();
  }
  
  $result = $s->fetchAll();

  foreach ($result as $row)
  {
    $newNotes[] = array('id' => $row['id'], 'effort' => $row['effort']);
  }
  include 'searchview.html.php';
  exit();
}

But still when i hit subnmit on the options or pull down menu I still get returned to the same menu.
I should get sent to searchview.html.php

Thanks,
Shane

Doesn’t this imply it will just reload the same page?

<form action="" method="post">

I’m a little confused about which bit of code is in which file, but surely the above will reload the same page so you can deal with the form post in there?

Hi,
I added to the form a query string (?searchview) and now I am sent to searchview.html.php after submitting the pull down menu.

But the array newNotes is not displaying when I use

$print_r($newNotes);

I use this is serachview.html.php like this,

 <body>
     <p><a href="..">physCMS home</a> &#8658; <a href="/artgibney/admin/notes/">Manage Daily Notes</a></p>
   <?php include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/logout.inc.html.php'; ?>
    <h1>Search Results</h1>
    <?php    
    echo "<pre>";
    print_r($newNotes);
    echo "</pre>";
    ?>
  </body>

Any ideas,
Thanks,
Shane

Hi,
In my error_log.php file,

[30-Nov-2014 11:49:59 America/Denver] PHP Notice: Undefined variable: newNotes in /home2/shanegib/public_html/artgibney/admin/notes/searchview.html.php on line 15

So there is an problem with line 15 of searchview.html.php
That is the line which reads,

print_r($newNotes);

So the array $newNotes is not being created properly.

Shane

You have this code in the form

<select name="student" id="student">

but in your query, you use $_POST[‘id’] to select the data. Is it that it’s just not finding any data, and that’s why it doesn’t create the array?

Yes you are right. I have changed this to,

 <form action="?searchview" method="post">
      <div>
        <label for="student">By student:</label>
        <select name="id" id="id">
          <?php foreach ($learners as $learner): ?>
            <option value="<?php htmlout($learner['id']); ?>"><?php
                htmlout($learner['learner']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div>
        <input type="hidden" name="action" value="searchview">
        <input type="submit" value="Search">
      </div>
    </form>

I also had the wrong name for the column in the database. It is not called ‘id’ but ‘userid’.
So the pdo statement now reads,

if (isset($_POST['action']) and $_POST['action'] == 'searchview')
{ 
  include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';

  // Build the new list of notes rows where id's matches
  try
  {
    //$result = $pdo->query('SELECT id, absence, late, equip, effort, comment, date, time FROM notes);
    $sql = 'SELECT userid, effort FROM notes WHERE 
    userid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
    }
    
  catch (PDOException $e)
  {
    $error = 'Error fetching list of notes.';
    include 'error.html.php';
    exit();
  }
 
  $result = $s->fetchAll();// see p.196
 
  foreach ($result as $row)// see p.196
  {
    $newNotes[] = array('userid' => $row['userid'], 'effort' => $row['effort']);
  }
  include 'searchview.html.php';
  exit();
}

And it is working,
Thanks,
Shane

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.