Pagination Help

[ArizonaJohn]

Hello,

 

I am trying to implement pagination, and I can't even get the code to accept the user-submitted table name, which is the variable $find.  When I enter in "miami," I get an error message that says:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%miami%'' at line 1.

 

The first part of my code is listed below.  I assume this is a reference to the line below that has this:

 

$presult = mysql_query("SELECT COUNT(*) FROM '%$find%'") or die(mysql_error());

 

Why doesn't the code look up the table with the name "miami"?

 

 

Thanks in advance,

 

John

 

 

<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{

//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
unset($_SESSION['find']);	

}

// Otherwise we connect to our Database
mysql_connect("mysqlv3", "username", "password") or die(mysql_error());
mysql_select_db("sand2") or die(mysql_error());

// We preform a bit of filtering

$find = strip_tags($find);
$find = trim ($find);
$find = strtolower($find);

$presult = mysql_query("SELECT COUNT(*) FROM '%$find%'") or die(mysql_error());

$rr = mysql_fetch_row($presult);  
$numrows = $rr[0]; 
$rowsperpage = 50; 
$totalpages = ceil($numrows / $rowsperpage);

[CarbonCopy]

I do not believe wild cards are accepted when stating a table name. Correct code would be

 

$presult = mysql_query("SELECT COUNT(*) FROM '$find'") or die(mysql_error());

 

And OH DEAR LORD!!!

 

run mysql_real_escape_string on $find please! This is a security issue, and your existing filtering leaves in unwanted code such as a quote.

[gnawz]

plus your query...

 

Must be sth like

 

$presult = mysql_query("SELECT COUNT(*) FROM tablename where searchterm =  '%$find%'") or die(mysql_error());

[gnawz]

Actually better:

 

$presult = mysql_query("SELECT COUNT(*) FROM tablename where searchterm LIKE  '%$find%'") or die(mysql_error());

[.josh]

Actually better:

 

$presult = mysql_query("SELECT COUNT(*) FROM tablename where searchterm LIKE  '%$find%'") or die(mysql_error());

 

umm, no... according to the OP $find is supposed to be a table name, not some random piece of data in some column.

[ArizonaJohn]

Hi Crayon Violet,

 

That's correct.  $find is the table name.  Elsewhere in my code, I have this and it totally works:

 

$result=mysql_query("SHOW TABLES FROM sand2 LIKE '%$find%'")
or die(mysql_error());

 

I'm not sure why I can't get the '%$find%' to work at the beginning of my pagination code.  By the way, thanks for your tutorial.  I'm using it.

 

-John

 

[.josh]

As CarbonCopy mentioned, I do not believe you can use wildcards on a tablename when selecting like that.  My advice would be to first do a SHOW TABLES ... and then loop through the results, with your SELECT inside the loop.

[roopurt18]

SELECT COUNT(*) FROM '%$find%'

 

If you are indeed using $find as the table name, you can not enclose it in single quotes either.  IIRC the proper identifier quote character in MySQL is the backtick: `

 

SELECT COUNT(*) FROM `$find`

[ArizonaJohn]

Hi Crayon Violet,

 

Your advice seems to have worked.  The SELECT seems to be working within the loop.  Right now, the code displays all of the entries and then it shows the little pagination hyperlinks at the bottom of the page.  So far so good.

 

Now my problem is figuring out where to put the

LIMIT $offset, $rows

that you use in your tutorial.  When I try to stick it into the $r at the bottom of my code below, I get an error

for the loop that follows,

while($row=mysql_fetch_array($r))

.

 

Where should I put the

LIMIT $offset, $rows

?

 

Thanks,

 

John

 

mysql_connect("mysqlv3", "username", "database") or die(mysql_error());
mysql_select_db("sand2") or die(mysql_error());

$find = strip_tags($find);
$find = trim ($find);
$find = strtolower($find);

$result=mysql_query("SHOW TABLES FROM sand2 LIKE '%$find%'")
or die(mysql_error());

if(mysql_num_rows($result)>0){
while($table=mysql_fetch_row($result)){

$presult = mysql_query("SELECT COUNT(*) FROM `$table[0]`") or die(mysql_error());

$rr = mysql_fetch_row($presult);  
$numrows = $rr[0]; 
$rowsperpage = 50; 
$totalpages = ceil($numrows / $rowsperpage);

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {  
   $currentpage = (int) $_GET['currentpage'];  
} else {  
   $currentpage = 1;  
} 
  
if ($currentpage > $totalpages) {   
   $currentpage = $totalpages;  
} // end if  
// if current page is less than first page...  
if ($currentpage < 1) {  
   // set current page to first page  
   $currentpage = 1;  
} // end if  

// the offset of the list, based on current page   
$offset = ($currentpage - 1) * $rowsperpage; 


print "<p class=\"topic\">$table[0]</p>\n";
$r=mysql_query("SELECT * , votes_up - votes_down AS effective_vote FROM `$table[0]` ORDER BY effective_vote DESC");

print "<table class=\"navbar\">\n";
while($row=mysql_fetch_array($r)){

[.josh]

ah okay, I guess I didn't think ahead to how that would tie into the pagination itself.  Okay, slightly modified advice:

 

Run the show tables query.  Then what you're gonna wanna do is loop through the result to build the query for your pagination.  But you're going to incorporate UNION into the pagination query.  So an example would be:

 

$result = "(select * from table1) union (select * from table2) union (select * from table3) orderby somecolumn desc";

 

so what you're gonna have to do is loop through the results from the show table query and dynamically add (select * from tablename) for each table returned form the show table query.  Then add on your order by column or desc or whatever.

[ArizonaJohn]

Hi Crayon Violet,

 

When I run this code, the query only returns one table, which is what I want.  So I don't think I need to be joining tables with UNION.

 

The code works great as is; it's just not breaking the rows up into pages.  My problem is that I just don't know where I should add LIMIT to the code.  It seems to me in your tutorial that is what breaks the results up into the desired number of rows per page.

 

-John

[.josh]

I thought the point of the initial "show tables from db like ..." was to gather data from multiple tables and paginate it? 

 

If your code is currently doing exactly what you want, minus the limit, you would put it in your data selection query, where you select the actual data and order it, etc...

[ArizonaJohn]

OK, thanks.  I got the limit part to work, but funny story... the reason it wasn't working is because I thought your tutorial said "LIMIT $offset, $rows" when after scrolling to the right, I saw that it says "LIMIT $offset, $rowsperpage."

 

It's almost working; it displays only the number of rows I pick on the first page.  But now I have a new problem: when I click on the 2nd or 3rd page hyperlink, the display is blank.  I'm thinking maybe I'm using the wrong destination for the hyperlink.  I'm just using "$_SERVER['PHP_SELF']", but am I supposed to use the name of my MySQL database or something?

 

Thanks,

 

John