Professional Web Applications Themes

Toolbar to move in a MySQL table - PHP Development

Hello I can't find a sample script how to create a tollbar to navigate record by record. I have a form to display data with a small tollbar. I can add, del records, etc. I also can move from rec to rec but only with the ID. Sample for next: <a href="adresse_edit.php?id=<?= $id+1 ?>"> <img border="0" src="images/co_avant.png" width="32" height="24"></a> I like to move alphabetically. How can I do that. Do somebody have a sample script ? Many thanks in advance for your help. Otto...

  1. #1

    Default Toolbar to move in a MySQL table

    Hello

    I can't find a sample script how to create a tollbar to
    navigate record by record.
    I have a form to display data with a small tollbar. I can
    add, del records, etc.

    I also can move from rec to rec but only with the ID.

    Sample for next:
    <a href="adresse_edit.php?id=<?= $id+1 ?>">
    <img border="0" src="images/co_avant.png" width="32"
    height="24"></a>

    I like to move alphabetically.
    How can I do that. Do somebody have a sample script ?

    Many thanks in advance for your help.

    Otto

    Otto Guest

  2. #2

    Default Re: Toolbar to move in a MySQL table

    > I can't find a sample script how to create a tollbar to 


    I do not have a script but have an idea.
    You could pass current ID and navigation direction (previous
    or next). The script that gets this info (and knows used
    sort style) is able to determine what record should be
    displayed.

    <a href="adresse_edit.php?id=<?php echo $id; ?>&direction=next">next</a>
    <a href="adresse_edit.php?id=<?php echo $id; ?>&direction=previous">previous</a>

    This was easy part. The hard part is how to use that info to retrieve next or
    previous record. That strongly depends on DB engine you are using (eg. most MySQL
    versions do not support subquery and you use LIMIT clause to get range of records
    instead of popular - in other SQL engines - TOP clause).

    One way is to retrieve the whole current record (id = $id) and use the data
    to find the next or previous one. Another way is to do it in single SQL select.


    If the table (called eg. "recrds") looks like this:

    id - INT,
    t1 - VARCHAR
    t2 - VARCHAR
    t3 - VARCHAR

    and records are sorted by: t1 ascending, t2 ascending, t3 descending,
    then the first method would be something like:

    SELECT * FROM recrds WHERE id = $id

    store the results in $t1, $t2 and $t3 and do next select (replace TOP
    with LIMIT if you use MySQL) like this (when retrieving "next"):

    SELECT TOP 1 *
    FROM recrds
    WHERE (t1 > $t1)
    OR ((t1 = $t1) AND (t2 > $t2))
    OR ((t1 = $t1) AND (t2 = $t2) AND (t3 < $t3))
    OR ((t1 = $t1) AND (t2 = $t2) AND (t3 = $t3) AND (id > $id))
    ORDER BY t1 ASC, t2 ASC, t3 DESC, id ASC

    (you can ommit last OR statement if all (t1,t2,t3) sets are always unique).
    To retrieve "previous" you have to change "<" to ">" and ">" to "<" and
    reverse sort order:

    SELECT TOP 1 *
    FROM recrds
    WHERE (t1 < $t1)
    OR ((t1 = $t1) AND (t2 < $t2))
    OR ((t1 = $t1) AND (t2 = $t2) AND (t3 > $t3))
    OR ((t1 = $t1) AND (t2 = $t2) AND (t3 = $t3) AND (id < $id))
    ORDER BY t1 DESC, t2 DESC, t3 ASC, id DESC


    To do the retrieval in one step you'll probably have to use JOIN.
    Maybe something like this (to retrieve "previous"):

    SELECT TOP 1 r1.*
    FROM recrds AS r1 JOIN recrds AS r2
    ON (r1.t1 < r2.t1)
    OR ((r1.t1 = r2.t1) AND (r1.t2 < r2.t2))
    OR ((r1.t1 = r2.t1) AND (r1.t2 = r2.t2) AND (r1.t3 > r2.t3))
    OR ((r1.t1 = r2.t1) AND (r1.t2 = r2.t2) AND (r1.t3 = r2.t3) AND (id < r2.id))
    WHERE r1.id <> $id AND r2.id = $id
    ORDER BY r1.t1 DESC, r1.t2 DESC, r1.t3 ASC, r1.id DESC

    You can move whole WHERE clause to ON clause of JOIN or rather
    exchange WHERE clause with ON clause (some SQL engines will
    perform better with it).

    Hope this helps.


    Hilarion

    PS.: You could also check if there will be any next or previous for the
    record you finally retrieved (to check if you should show "next"
    and / or "previous" links), but this is something you could do
    by changing "TOP 1" to "TOP 2" and checking how many you got.
    Hilarion Guest

  3. #3

    Default Re: Toolbar to move in a MySQL table

    Hello Hilarion
     
    Many thanks four your quick answer. I will have a look about
    your idea.

    Otto

    Otto Guest

Similar Threads

  1. Status Toolbar moved to side of page & won't move back to bottom
    By Unregistered in forum Microsoft Windows
    Replies: 4
    Last Post: January 27th, 01:20 PM
  2. move row in table
    By sweetyp2005 in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: October 15th, 12:32 AM
  3. Can move my table
    By stusic in forum Macromedia Dynamic HTML
    Replies: 5
    Last Post: August 18th, 11:33 AM
  4. Toolbar choices disappear (File, Layout, Object, Table, View)
    By eaglesphan@adobeforums.com in forum Adobe Indesign Windows
    Replies: 2
    Last Post: July 13th, 07:47 PM
  5. move record from 1 table to another table
    By Jason Frazer in forum Microsoft Access
    Replies: 6
    Last Post: August 4th, 02:45 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139