mysql pagination

[docock]

I'm trying to make a pagination of goldmembers who uploaded pictures.
Problem though is that some people uploaded 2 pictures, others 8, others 12, and so on.

I was using this test script I wrote:

PHP Code:

<?php 
if (isset($_GET[“page”])) { $page  = $_GET[“page”]; } else { $page=1; }; 
$start_from = ($page-1) * 2; 

$string= "SELECT * FROM goliath.Gmembers WHERE Aanuit=1 order by Modified DESC LIMIT $start_from, 20";  
$con = mysql_connect("mysql3.***************",$user,$pass);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$result = mysql_query($string,$con);





$rs_result = mysql_query ($string,$con);
?> 
<?php 
while ($row = mysql_fetch_assoc($rs_result)) { 
?> 

<!-- display the images of the user here -->

<?php 
}; 
?> 

<?php 
$sql = "SELECT COUNT(Username) FROM a3718717_members.Gmembers WHERE Aanuit=1 AND Imgorder<>''"; 
$rs_result = mysql_query($sql,$con); 
$row = mysql_fetch_row($rs_result); 


$total_records = $row[0]; 
$total_pages = ceil($total_records / 2); 
 
for ($i=1; $i<=$total_pages; $i++) { 
if($page==$i){
echo "<b>$i</b>";
}else{
echo "<a href='pagination.php?page=$i>$i </a> "; 
}
}; 


mysql_close($con); 
?>

How can I alter the coding so that on every page 20 images are stored? In particular the coding for mysql is giving me headaches :S Right now the sql line retrieves 20 records, but each record can contain multiple images.

Hope you guys know what I'm after?

[Keleth]

How is your data stored? Can you show us your table structure(s)?

[docock]

Sure, here it is:

P_Id int(66) No auto_increment
Username varchar(255) latin1_general_ci Yes NULL
Aanuit varchar(1) latin1_general_ci Yes NULL
betaaldtm varchar(255) latin1_general_ci Yes NULL
Imagelocation varchar(255) latin1_general_ci Yes NULL
Pic1 varchar(155) latin1_general_ci Yes NULL
Pic2 varchar(155) latin1_general_ci Yes NULL
Pic3 varchar(155) latin1_general_ci Yes NULL
Pic4 varchar(155) latin1_general_ci Yes NULL
Pic5 varchar(155) latin1_general_ci Yes NULL
Pic6 varchar(155) latin1_general_ci Yes NULL
Pic7 varchar(155) latin1_general_ci Yes NULL
Pic8 varchar(155) latin1_general_ci Yes NULL
Pic9 varchar(155) latin1_general_ci Yes NULL
Pic10 varchar(155) latin1_general_ci Yes NULL
Pic11 varchar(155) latin1_general_ci Yes NULL
Pic12 varchar(155) latin1_general_ci Yes NULL
Pic13 varchar(155) latin1_general_ci Yes NULL
Pic14 varchar(155) latin1_general_ci Yes NULL
Pic15 varchar(155) latin1_general_ci Yes NULL
Random varchar(4) latin1_general_ci Yes NULL
Randomt varchar(4) latin1_general_ci Yes NULL
Randomtf varchar(4) latin1_general_ci Yes NULL
Imgorder varchar(100) latin1_general_ci Yes NULL
Modified datetime No
Paidtill datetime No

The picture filenames are stored in the Picxx fields, and the order of the image uploads is stored in the Imgorder field like this: Pic2, Pic1, Pic11, Pic4

[Keleth]

Why do you store all the pics in columns? First, very not normalized, second, if someone doesn't store 15 images, you're wasting space, and if you ever want more then 15 images, you have to go back and redo your db. Plus, with stuff like you're currently trying to do, its a hassle.

So first things first, you can't do what you're trying to do without PHP processing as far as I know. There might be a clever way to use COUNT and some other functions to get back 15 images, but I don't know how.

Second, you should normalize. Have one table that that contains the current table minus the pic1-15 and the imgorder. Given I don't know what the rest of your variables do, no way of giving further normalization advice. Create a second table with a p_id, picID, picOrder.

You can always get the pics and which order they are in by selecting the ones in reference to a perticular P_id, sorted by picOrder. You can find out how many a user has by counting rows. Its simpler, cleaner, and now you can get 20 images without any real issue.

Also, in before Old Pendant shouts about "No deliminated lists in a column!" (which I don't believe is a problem 100% of the time... just 99% )

[Len Whistler]

I would redo your DB structure with two tables, and each record one image. If they load 8 images that would be 8 rows.


Something like:

Table 1
P_Id | Username | Aanuit | betaaldtm | Imagelocation

Table 2
P_Id | Pic

P_Id is the link between tables.




-------

[alstonwillman]

hi
i am using a form having about 10 field to fill.when I submit it redirect to another page where it find results on the bases of form values.That all is ok.But problem comes when i implements pagination , when i implement pagination script , according to script it page loads again and the forms values are disappear can u tell me how can i maintain form values so I can use a nice pagination.

Thanks in advance

[Saeid]

Originally Posted by alstonwillman

hi
i am using a form having about 10 field to fill.when I submit it redirect to another page where it find results on the bases of form values.That all is ok.But problem comes when i implements pagination , when i implement pagination script , according to script it page loads again and the forms values are disappear can u tell me how can i maintain form values so I can use a nice pagination.

Thanks in advance

You have to use SESSION to store variables into it.

[Len Whistler]

Originally Posted by alstonwillman

hi
i am using a form having about 10 field to fill.when I submit it redirect to another page where it find results on the bases of form values.That all is ok.

Your DB structure should be fixed first. The 15 Pic# columns can be 1 column in a second table.

Originally Posted by alstonwillman

But problem comes when i implements pagination , when i implement pagination script , according to script it page loads again and the forms values are disappear can u tell me how can i maintain form values so I can use a nice pagination.

You need to LIMIT you DB query using the GET post method, the value is passed on with the page links. Default is page 1.

Can you post your pagination code?



-------