PDO select limit placeholder[SOLVED]

Hi,
Is there anything obviously wrong here?

$test = 3;

try
{
$sql = “SELECT * FROM teachingpts ORDER BY id DESC LIMIT :test”;
$s = $pdo->prepare($sql);
$s->bindValue(‘:test’, $test, PDO::PARAM_IN);
$s->execute();
}

catch (PDOException $e)
{
$error = ‘Error fetching data from teachingpoints table.’;
include ‘error.html.php’;
exit();
}

Gives an error,

[05-Mar-2015 11:13:56 America/Denver] PHP Fatal error:  Undefined class constant 'PARAM_IN' in /home2/shanegib/public_html/artgibney/includes/buildteachingptsNumRows.inc.php on line 12

Thanks,
Shane

Shouldn’t it be PDO::PARAM_INT ?

Hi,
Yes that got rid of one error, thanks.
But I am still having problems.

A HTML form puts a variable in $_POST[‘rowNum’]

<div>
        <?php     
        echo($rowNum); 
        echo("swimming");
        ?>
        
        <form action="?" method="post">
            Number of rows to display: <input type="number" name="rowNum" value="rowNum">
            <input type="submit" value="rowNum">
        </form>
        </div>

looks like this,

The index.php file uses this $_POST[‘rowNum’] and sets it to $rowNum

if (isset($_POST['rowNum']))
{
    echo("this is the index file" . "<br>");
      $rowNum = $_POST['rowNum'];
      //header('Location: .');
      include 'teachingpoints.html.php';
      exit();
}

This directs back to teachingpts.html.php which starts with an

include $_SERVER['DOCUMENT_ROOT'] . '........./buildteachingptsNumRows.inc.php';?>

buildteachingptsNumRows.inc.php

<?php   
 include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';
    
   //echo("number is ". $rowNum);
   //$rowNum = $_POST['rowNum'];
   //$rowNum = 4;
   echo("<br>" . $rowNum . " is rowNum, ");
   echo("<br>" . $_POST['rowNum'] . " is _POST['rowNum']");
   
   if($rowNum == '')
   {
   echo("<br> I am empty <br>");
   $rowNum = 5;
   } else {
   echo("<br> I am not empty, I am " . $rowNum . "<br>");
   }
   
   try
  {
    $sql = "SELECT * FROM teachingpts ORDER BY id DESC LIMIT :rowNum";
    $s = $pdo->prepare($sql);
    $s->bindValue(':rowNum', $rowNum, PDO::PARAM_INT);
    $s->execute();
    }
    
  catch (PDOException $e)
  {
    $error = 'Error fetching data from teachingpoints table.';
    include 'error.html.php';
    exit();
  }

  $result = $s->fetchAll(PDO::FETCH_ASSOC);

  foreach ($result as $row)
  {
 $teachingpts[] = array('id' => $row['id'], 'unit' => $row['unit'], 'year' => $row['year'], 'exam' => $row['exam'], 'question' => $row['question'], 'part' => $row['part'], 'subpart' => $row['subpart'], 'topic' => $row['topic'], 'subtopic' => $row['subtopic'], 'tp' => $row['tp'], 'questiontype' => $row['questiontype']);
  }
?>

The error shows

[05-Mar-2015 14:07:10 America/Denver] PHP Notice:  Undefined variable: rowNum in /home2/shanegib/public_html/artgibney/includes/buildteachingptsNumRows.inc.php on line 7
[05-Mar-2015 14:07:10 America/Denver] PHP Notice:  Undefined index: rowNum in /home2/shanegib/public_html/artgibney/includes/buildteachingptsNumRows.inc.php on line 8
[05-Mar-2015 14:07:10 America/Denver] PHP Notice:  Undefined variable: rowNum in /home2/shanegib/public_html/artgibney/includes/buildteachingptsNumRows.inc.php on line 10

But why does it say that there is a problem with $rowNum and $_POST[‘rowNum’] when on the page it outputs those values as whatever i but in the form,

Thanks,
Shane

If you’re using PDO::PARAM_INT, do you have to use intval($rowNum) or not?

It’s confusing, at this point I’d be adding lots of echo() statements to see exactly which bit of code I’m running and what order it’s going through. Aren’t the error messages at lines 7, 8 and 10 because the first time through, you don’t have $rowNum or the $POST array, yet you’re using them for your debug display?

1 Like

You are spot on, that was the problem.

And also you are correct about,

Works wonderfully now. Here is the code,

<?php   
 include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';
    
   $rowNum = intval($rowNum);
   
   if($rowNum == '')
   {
   $rowNum = 5;//default row number
   } 
   
   try
  {
    $sql = "SELECT * FROM teachingpts ORDER BY id DESC LIMIT :rowNum";
    $s = $pdo->prepare($sql);
    $s->bindValue(':rowNum', $rowNum, PDO::PARAM_INT);
    $s->execute();
    }
    
  catch (PDOException $e)
  {
    $error = 'Error fetching data from teachingpoints table.';
    include 'error.html.php';
    exit();
  }

  $result = $s->fetchAll(PDO::FETCH_ASSOC);

  foreach ($result as $row)
  {
 $teachingpts[] = array('id' => $row['id'], 'unit'.........................

I think I might be able to do a little better and bypass the index.php altogether. will have a go at this.

Thanks,
Shane

Hi,
I just made it a little better,

 include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';

 if(isset($_POST['rowNum']))
        {
           $rowNum = intval($_POST['rowNum']);
       }
   
   if($rowNum == '')
   {
   $rowNum = 5;//default row number
   } 
   
   try
  {
    $sql = "SELECT * FROM teachingpts ORDER BY id DESC LIMIT :rowNum";
    $s = $pdo->prepare($sql);
    $s->bindValue(':rowNum', $rowNum, PDO::PARAM_INT);
    $s->execute();
    }

and the index.php file is still used but just to redirect,

if (isset($_POST['rowNum']))
{
      //header('Location: .');
      include 'teachingpoints.html.php';
      exit();
}

Thanks,
Shane

Excellent, glad it works now.

1 Like

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