Using COUNT(*)

Hi,
I would like to create a function to count the number of rows in a table and then call this function in an HTML file.
Is this the right way to go about it?
func.inc.php

//declaring the function
function countrows($tablename){
$sql = 'SELECT id COUNT(*) AS numrows FROM $tablename;
$s = $pdo->prepare($sql);
$s=execute();
if($numrows>0){
        return TRUE;
    }
    else
    {
        return FALSE;
    }
}
}

Is the else part of the if statement necessary? Presumably if something is not TRUE then it is set to FALSE by default.
The next file calls the function. The table name is ‘courses’.
After including the above file in the next file,
file.html.php


<?php if(countrows('courses')){
        echo "Table is not empty";
    }
    else
    {
        echo "Table is empty";
    }
} ?>

Is it correct to use the single quotes for the tablename or should it just be,
if(countrows(courses))
Thanks,
Shane

Your code won’t work and I think it can be improved. But to answer your two questions first:

Is the else part of the if statement necessary?

Strictly speaking, no. But I’d still leave in the FALSE return value for good practice. If you’re going to have a function returning a value, then make sure it returns one (compiled langauges enforce you to do this at compile time).

Is it correct to use the single quotes for the tablename or should it just be,
if(countrows(courses))

‘courses’ is the name of the table being passed as an argument, and so you’re going to want to pass it as a string. If you did not enquote it, then it would be seen as a constant by PHP (which may or may not generate a warning depending on your error level settings).

Your code won’t work because of the following lines:


$sql = 'SELECT id COUNT(*) AS numrows FROM $tablename;
$s = $pdo->prepare($sql);
$s=execute();
if($numrows>0){

The first line needs to be delimited by double quotes so that the $tablename variable is interpolated and parsed - and your SQL is invalid. The second line won’t work because the $pdo variable isn’t in the scope of your function. Your third line won’t execute because (ignoring the first and second problem) you’re using an = sign instead of a ->. And your fourth line won’t work because $numrows is not defined anywhere.

Your code can be improved by firstly (assuming errors are fixed) validating the table name you’re passing in. This will prevent invalid table names being passed and executed (which will result in your code erroring rather than gracefully failing). I’d also be inclined to not use prepared statements since they don’t have much use in your case. I’d rewrite you function as follows:


<?php

function countRows($tablename, $pdo)
{
    $validTables = array('table1', 'table2');

    if(!in_array($tablename, $validTables, true)) {
        return -1;
    }

    $rowCountQuery = $pdo->query("SELECT COUNT(*) AS numrows FROM {$tablename}");
    $rowCount = $rowCountQuery->fetch(PDO::FETCH_ASSOC);

    return $rowCount['numrows'];
}

That way, -1 is returned if there was an error with an invalid table name (though you can throw an exception instead if you’d like). Otherwise, the row count is returned. You can test the above function like so:



$rowCount = countRows('tableName');

// you can use a switch statement here if you'd prefer
if($rowCount == -1)
  // error
elseif($rowCount == 0)
  // no rows
else
  // contains rows (number of rows = $rowCount)

Hi tpunt,
Thanks for your detailed response. Clearly there were many errors in my code.
I have amalgamated my code with yours and it works great now.
Here it is,

<?php
function countRows($tablename)
{
  include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';

  try
  {
    $sql = "SELECT COUNT(*) AS numrows FROM {$tablename}";
    $s = $pdo->prepare($sql);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error counting tablerows.';
    include 'error.html.php';
    exit();
  }

  $row = $s->fetch();

  if ($row['numrows'] > 0)
  {
    return TRUE;
  }
  else
  {
    return FALSE;
  }
}
<?php include_once $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/helpers.inc.php'; ?>
<?php include_once $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/func.inc.php'; ?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Manage Daily Notes</title>
  </head>
  <body>
    <h1>Manage Daily Notes</h1>
    <p><a href="?addcourses">Add course</a></p>
	<? if(countRows('courses')): ?>
 	<p><a href="?addrows">Add learner</a></p>
	<? endif; ?>
    <p><a href="..">Return to physCMS home</a></p>
    <?php include '../logout.inc.html.php'; ?>
  </body>
</html>

Thanks,
Shane

Hi,
I changed the function a little,

<?php
function countRows($tablename)
{
  include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';

  try
  {
    $sql = "SELECT COUNT(*) FROM {$tablename}";
    $s = $pdo->prepare($sql);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error counting tablerows.';
    include 'error.html.php';
    exit();
  }

  $row = $s->fetch();

  if ($row[0] > 0)
  {
    return TRUE;
  }
  else
  {
    return FALSE;
  }

Thanks,
Shane
}