paginating results from LEFT JOIN

[turpentyne]

I need to set up pagination. I've seen a tutorial online, and in a book I have. But they're both for basic queries. 

 

I've got a LEFT JOIN. The basic script doesn't seem to work. Is there a different way it has to be done for joins?

 

 

[Adam]

Can we see the query?

[turpentyne]

With the code I followed in a book, the SELECT COUNT query was just searching one of the tables. It showed a bunch of page numbers at the bottom that didn't go anywhere ( I suspect the numbers matched the entire list of entries in that database)

 

Here's what it was: $data = "SELECT COUNT(*) FROM plantae ORDER BY scientific_name ASC";

 

So I tried mimicking the LEFT JOIN wording that is further down to see what might happen. Now I get this error: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data/21/2/40/160/2040975/user/2235577/htdocs/leafsearch2a.php on line 40

 

This is what's on line 40:

$row = mysql_fetch_array($result, MYSQL_NUM);

 

Here's the full code:

<?php // First connect to database
(database connection here) 

$display = 2;
// it's intentionally only 2 for the moment

if (isset($_GET['np'])) {
   $num_pages = $_GET['np'];
} else { 
$data = "SELECT COUNT
                        `descriptors`.*
                        ,`plantae`.*
                    FROM
                        `descriptors`
                    LEFT JOIN
                        `plantae` ON (`descriptors`.`plant_id` = `plantae`.`plant_name`)
                    WHERE
                        `leaf_shape` LIKE '%$select1%'
                        AND `leaf_venation` LIKE '%$select3%'
                        AND `leaf_margin` LIKE '%$select4%'";
$result = mysql_query ($data);
$row = mysql_fetch_array($result, MYSQL_NUM);
//row 40 above seems to be where a problem is 
$num_records = $row[0];

if ($num_records > $display) {
   $num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}
}
if (isset($_GET['s'])) {
   $start = $_GET['s'];
} else {
    $start = 0;
}

if(isset($_POST[submitted])) {
// Now collect all info into $item variable
$shape = $_POST['select1'];
$color = $_POST['select2'];
$vein = $_POST['select3'];
$margin = $_POST['select4'];




// This will take all info from database where row tutorial is $item and collects it into $data variable row 55

$data = mysql_query("SELECT
                        `descriptors`.*
                        ,`plantae`.*
                    FROM
                        `descriptors`
                    LEFT JOIN
                        `plantae` ON (`descriptors`.`plant_id` = `plantae`.`plant_name`)
                    WHERE
                        `leaf_shape` LIKE '%$select1%'
                        AND `leaf_venation` LIKE '%$select3%'
                        AND `leaf_margin` LIKE '%$select4%'
                    ORDER BY `plantae`.`scientific_name` ASC LIMIT $start, $display");



//chs added this in... row 72
echo '<table align="center" cellspacing="0" cellpading-"5">
<tr>
<td align="left"><b></b></td>
<td align="left"><b></b></td>
<td align="left"><b>Leaf margin</b></td>
<td align="left"><b>Leaf venation</b></td>
</tr>
';
//end something chs added in row 81

// This creates a loop which will repeat itself until there are no more rows to select from the database. We getting the field names and storing them in the $row variable. This makes it easier to echo each field.

while($row = mysql_fetch_array($data)){
echo '<tr>
<td align="left"> <a href="link.php">View plant</a>    </td>
<td align="left"> <a href="link.php">unknown link</a>   </td>
<td align="left">' . $row['scientific_name'] . '</td>
<td align="left">' . $row['common_name'] . '</td>
<td align="left">' . $row['leaf_shape'] . '</td>
</tr>';
}
echo '</table>';
// row 95
}
if ($num_pages > 1) {
echo '<br /><p>';
$current_page = ($start/$display) + 1;
// row 100
if ($current_page != 1) {
echo '<a href="leafsearch2a.php?s=' . ($start - $display) . '&np=;' . $num_pages . '">Previous</a> ';
}

for ($i = 1; $i <= $num_pages; $i++) {
if($i != $current_page) {
echo '<a href="leafsearch2a.php?s=' . (($display * ($i - 1))) . '$np=' . $num_pages . '">' . $i . '</a>';
} else {
echo $i . ' ';
}
}

// row 112
if ($current_page != $num_pages) {
echo '<a href="leafsearch2a.php?s=' . ($start + $display) . '$np=' . $num_pages . '"> Next</a>';
}
} 
//added curly
?>