Learn VBA & Macros in 1 Week!

PHP - Small Pagination Problem

Full Excel VBA Course - Beginner to Expert

Small Pagination Problem	View Content

View Content

Hi guys,

well I'm trying to formulate pagination view of my results from a Mysql query in my database. The thing is that the query is not known beforehand, since the user of the database can enter several search terms in text fields of the webpage (text_search.php) and when he hits the "Submit" button, the new page (retrieve_text.php) looks about which text fields have been filled and appends the values in the mysql query like this:

Code: [Select]
$text_query = ...... WHERE [user-defined conditions]

Everything is OK until now. But I wanted to show the results more clear to the user, since it can have e.g 600 results so it's not so good to view them in a loooooooooong page. So I tried to paginate them.

Now comes the problem: the first page looks Ok, and brings only 25 results, which I want to be the limit of results to show per page. BUT, when I click on the "Next button", I see nothing. The reason? In pages 2,3...N, the $text_query is empty since there are no values submitted through the $_POST variable..., So, in the 1st page, if I print the SQL query I have something like that:

Code: [Select]
SELECT DISTINCT protein.protein_id, protein.protein_name FROM protein LEFT JOIN protein_reference ON protein_reference.prot_ref_protein_id = protein.protein_id LEFT JOIN reference ON reference.reference_id = protein_reference.prot_ref_reference_id LEFT JOIN families ON protein.protein_families_id = families.families_id WHERE protein.protein_name LIKE '%protein%' ORDER BY protein.protein_id LIMIT 0, 25

while in the 2nd page for example I get:

Code: [Select]
SELECT DISTINCT protein.protein_id, protein.protein_name, protein.protein_seq_len, protein.protein_organism, protein_ncbi FROM protein LEFT JOIN protein_reference ON protein_reference.prot_ref_protein_id = protein.protein_id LEFT JOIN reference ON reference.reference_id = protein_reference.prot_ref_reference_id LEFT JOIN families ON protein.protein_families_id = families.families_id WHERE ORDER BY protein.protein_id LIMIT 25, 25

The reason is, as I wrote before, that the where clause in the sql statement is written on-the-fly so the sql search is dynamic and not static. If it was static, like

Code: [Select]
SELECT * from protein WHERE name LIKE '%protein%';

things would be straight-forward.

What can I do? Should/Could I somehow "send" the sql query from page to page? I suppose this is BAD practice, but I don't really know how to proceed...

Similar Tutorials

View Content

been trying to get a code up and working for a update database form, couldnt really get any working i found from google and then after a bit of help from here i got a crazy idea and stopped dead and my tracks and switched gears. so i thought, why not just use the form i used to insert my database and change it around a little. same concept, how should this not be possible ?

for reference here are my tables and structure

Movies
ID(PK) Title Category(FK) URL
0 Name 1 http://www.

Categories
ID(PK) Category
1 Drama

note: i know i should change Category from movies to CategoryID for less confusion. to make long story short when i tried i got errors, to much time to go back over everything so i will just stick with what i got for now.

ok so my insert form inserts a movie title,category and url into my movies table. the dropdown for category is populated with a list of categories in my categories table, the way the code is writen it shows the actual category name instead of the id inside the dropdown. the form goes like:

Title: textfield1
Category:dropdown
URL:textfield2

so what i did was took the code from it, replaced title textfield1 with the same dropdown for category and just edited the select and echo to pull and show movie titles. i also added a WHERE to it so i can only select titles in a certian category.

so it should look like,

Title: dropdown
Category:dropdown

so here is the code below (ignore leftovers from URL or anything else i plan on taken them out pending the outcome)

Code: [Select]
<html>
<form id="form1" name="Update" method="post" action="ad3.php">

<label>

Title: <input type="text" name="Title" id="Title" />

</label>

<br />

<select name='dropdownt' id='dropdownt'>
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
{
die('Could not connect to DB: ' . mysql_error() );
}

mysql_select_db ("5", $con);
$query = "SELECT Title FROM movies WHERE Category='1'";
$result = mysql_query($query) OR DIE ("There was an error" .mysql_error());
while($row=mysql_fetch_array($result))
    {


echo " <option value=\"{$row['Title']}\">{$row['Title']}</option>";
    }

php?>

</select>

<select name='dropdown' id='dropdown'>
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
{
die('Could not connect to DB: ' . mysql_error() );
}

mysql_select_db ("5", $con);
$query = "SELECT ID, Category from categories";
$result = mysql_query($query) OR DIE ("There was an error" .mysql_error());
while($row=mysql_fetch_array($result))
    {


echo " <option value=\"{$row['ID']}\">{$row['Category']}</option>";
    }

php?>

</select>

<input name="" type="submit" value="send" />

</form>
</html>
one thing that i could not figure out (due to my lack of php knownledge) is that after my SELECT i have the Code: [Select]
echo " <option value=\"{$row['Title']}\">{$row['Title']}</option>"; before i had edited this code there was ID where the first 'Title' is. after trying to take one part of that out i got a error so i just left it.

so what i thought i would do for the process page (ad3.php) i would just turn the INSERT into a UPDATE.

Code: [Select]
<?php
$con = mysql_connect("localhost", "root", "");
if (!$con)
{
die('Could not connect to DB: ' . mysql_error() );
}

mysql_select_db ("5", $con);

$sql="UPDATE movies SET Category = '$_POST[dropdown]', Title = '$_POST[dropdownt]' LIMIT 1";

if (!mysql_query($sql,$con))
{
die ('Error: ' . mysql_error());
}

echo "<a href=\"form2.php\">Record added. Click here to make a new entry</a>";
the surprising thing (to me anyways) is that it actually worked! well.. ALMOST

it should work by letting me select a certian title thats in a certian category and change its current category into a different one and then submit. after a few hours i finaly got it to do what i need BUT the problem i am having is that when looking into my phpmyadmin, it just changes the name and category to the first record in my movies table which is id "0". one of the too. i almost done it on my own.. almost...

sorry for the extended post but i didnt want to leave anyone confused. so my question is, did i forget to add anything, take anything out, miss a change i needed to make ? maybe its the echos after my SELECT in my form >