Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Feb 2007
    Posts
    217
    Thanks
    25
    Thanked 1 Time in 1 Post

    mysql pagination

    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?

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    How is your data stored? Can you show us your table structure(s)?

  • #3
    Regular Coder
    Join Date
    Feb 2007
    Posts
    217
    Thanks
    25
    Thanked 1 Time in 1 Post
    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

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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% )

  • #5
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    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:


    Code:
    Table 1
    P_Id | Username | Aanuit | betaaldtm | Imagelocation
    
    Table 2
    P_Id | Pic
    P_Id is the link between tables.




    -------
    Leonard Whistler

  • #6
    New to the CF scene
    Join Date
    Jun 2010
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #7
    New Coder
    Join Date
    Jun 2010
    Location
    Enschede, Netherlands
    Posts
    34
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by alstonwillman View Post
    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.

  • #8
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    Quote Originally Posted by alstonwillman View Post
    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.


    Quote Originally Posted by alstonwillman View Post
    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?



    -------
    Leonard Whistler


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •