php and mysql not showing data, not entering foreach loop

Question

Hello,

I am having trouble with modifying a php application to have pagination. My error seems to be with my logic, and I am not clear exactly what I am doing incorrectly. I have had before, but am not currently getting errors that mysql_num_rows() not valid result resource and that invalid arguments were supplied to foreach. I think there is a problem in my logic which is stopping the results from mysql from being returned.

All my "test" echos are output except testing while loop. A page is generated with the name of the query and the word auctions, and first and previous links, but not the next and last links. I would be grateful if a more efficient way of generating links for the rows in my table could be pointed out, instead of making a link per cell. Is it possible to have a continuous link for several items?

<?php
if (isset($_GET["cmd"]))
  $cmd = $_GET["cmd"]; else
die("You should have a 'cmd' parameter in your URL");
$query ='';
if (isset($_GET["query"])) {
    $query = $_GET["query"];
}
if (isset($_GET["pg"]))
{ 
 $pg = $_GET["pg"];
 }
  else $pg = 1;
$con = mysql_connect("localhost","user","password");
echo "test connection<p>";
if(!$con) {
    die('Connection failed because of' .mysql_error());
}
mysql_query('SET NAMES utf8');
mysql_select_db("database",$con);
if($cmd=="GetRecordSet"){
    echo "test in loop<p>"; 
    $table = 'SaleS';
    $page_rows = 10;
    $max = 'limit ' .($pg - 1) * $page_rows .',' .$page_rows;
    $rows = getRowsByProductSearch($query, $table, $max);
    echo "test after query<p>";
    $numRows = mysql_num_rows($rows);
    $last = ceil($rows/$page_rows);
    if ($pg < 1) {
     $pg = 1;
    } elseif ($pg > $last) {
     $pg = $last;
    }
    echo 'html stuff <p>';

    foreach ($rows as $row) {

echo "test foreach <p>";
     $pk = $row['Product_NO'];
     echo '<tr>' . "\n";
     echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['USERNAME'].'</a></td>' . "\n";
     echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['shortDate'].'</a></td>' . "\n";
     echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['Product_NAME'].'</a></td>' . "\n";
     echo '</tr>' . "\n";
    }
    if ($pg == 1) {
    } else {
     echo " <a href='{$_SERVER['PHP_SELF']}?pg=1'> <<-First</a> ";
     echo " ";
     $previous = $pg-1;
     echo " <a href='{$_SERVER['PHP_SELF']}?pg=$previous'> <-Previous</a> ";
    }
    echo "---------------------------";
    if ($pg == $last) {
    } else {
     $next = $pg+1;
     echo " <a href='{$_SERVER['PHP_SELF']}?pg=$next'>Next -></a> ";
     echo " ";
     echo " <a href='{$_SERVER['PHP_SELF']}?pg=$last'>Last ->></a> ";
    }
    echo "</table>\n";
}
echo "</div>";
function getRowsByProductSearch($searchString, $table, $max) {
    $searchString = mysql_real_escape_string($searchString);
    $result = mysql_query("SELECT Product_NO, USERNAME, ACCESSSTARTS, Product_NAME, date_format(mycolumn, '%d %m %Y') as shortDate FROM {$table} WHERE upper(Product_NAME) LIKE '%" . $searchString . "%'" . $max);
    if($result === false) {
     echo mysql_error();
    }
    $rows = array();
    while($row = mysql_fetch_assoc($result)) {
     echo "test while <p>";
     $rows[] = $row;
    }
    return $rows;
    mysql_free_result($result);
}

edit: I have printed out the mysql error of which there was none. However 8 "test whiles" are printed out, from a database with over 100 records. The foreach loop is never entereded, and I am unsure why.

Answer 1

This is wrong:

if($cmd=="GetRecordSet")
echo "test in loop\n"; {

It should be:

if($cmd=="GetRecordSet") {
    echo "test in loop\n";

Answer 2

if (!(isset($pg))) { $pg = 1; }

How is $pg going to get set? You don't appear to be reading it from $_GET. If you're relying on register_globals: don't do that! Try to read it from $_GET and parse it to a positive integer, falling back to '1' if that fails.

<a href='{$_SERVER['PHP_SELF']}?pg=$next'>Next -></a>

You appear to be losing the other parameters your page needs, 'query' and 'cmd'.

In general I'm finding it very difficult to read your code, especially the indentation-free use of echo(). Also you have untold HTML/script-injection vulnerabilities every time you "...$template..." or .concatenate a string into HTML without htmlspecialchars()ing it.

PHP is a templating language: use it, don't fight it! For example:

<?php
    // Define this to allow us to output HTML-escaped strings painlessly
    //
    function h($s) {
        echo(htmlspecialchars($s), ENT_QUOTES);
    }

    // Get path to self with parameters other than page number
    //
    $myurl= $_SERVER['PHP_SELF'].'?cmd='.urlencode($cmd).'&query='.urlencode($query);
?>

<div id="tableheader" class="tableheader">
    <h1><?php h($query) ?> Sales</h1>
</div>
<div id="tablecontent" class="tablecontent">
    <table border="0" width="100%"> <!-- width, border, cell width maybe better done in CSS -->
        <tr>
            <td width="15%">Seller ID</td>
            <td width="10%">Start Date</td>
            <td width="75%">Description</td>
        </tr>
        <?php foreach ($rows as $row) { ?>
            <tr id="row-<?php h($row['Product_NO']) ?>" onclick="updateByPk('Layer2', this.id.split('-')[1]);">
                <td><?php h($row['USERNAME']); ?></td>
                <td><?php h($row['shortDate']); ?></td>
                <td><?php h($row['Product_NAME']); ?></td>
            </tr>
        <?php } ?>
    </table>
</div>
<div class="pagercontrols">
    <?php if ($pg>1) ?>
        <a href="<?php h($myurl) ?>&amp;pg=1"> &lt;&lt;- First </a>
    <?php } ?>
    <?php if ($pg>2) ?>
        <a href="<?php h($myurl) ?>&amp;pg=<?php h($pg-1) ?>"> &lt;-- Previous </a>
    <?php } ?>
    <?php if ($pg<$last-1) ?>
        <a href="<?php h($myurl) ?>&amp;pg=<?php h($pg+1) ?>"> Next --> </a>
    <?php } ?>
    <?php if ($pg<$last) ?>
        <a href="<?php h($myurl) ?>&amp;pg=<?php h($last) ?>"> Last ->> </a>
    <?php } ?>
</div>

Is it possible to have a continuous link for several items?

Across cells, no. But you're not really using a link anyway - those '#' anchors don't go anywhere. The example above puts the onclick on the table row instead. What exactly is more appropriate for accessibility depends on what exactly your application is trying to do.

(Above also assumes that the PK is actually numeric, as other characters may not be valid to put in an 'id'. You might also want to consider remove the inline "onclick" and moving the code to a script below - see "unobtrusive scripting".)

Answer 3

In your getRowsByProductSearch function, you return the result of mysql_error if it occurs. In order to debug the code, maybe you can print it instead, so you can easily see what the problem is.

Answer 4

The problem (or at least one of them) is in the code that reads:

$rows = getRowsByProductSearch($query, $table, $max);
$numRows = mysql_num_rows($rows);

The $numRows variable is not a MySQL resultset, it is just a normal array returned by getRowsByProductSearch.

Change the code to read:

$rows = getRowsByProductSearch($query, $table, $max);
$numRows = count($rows);

Then it should at least find some results for you.

Good luck, James

Hi there,

The next problem is with the line that reads:

$last = ceil($rows/$page_rows);

It should be changed to read:

$last = ceil($numRows / $page_rows);

Would recommend adding the following lines to the start of you script at least while debugging:

ini_set('error_reporting', E_ALL | E_STRICT);
ini_set('display_errors', 'On');

As that would have thrown up a fatal error and saved you a whole lot of time.