Posted by Hilarion on 10/14/05 04:00

>> Look at the following discussion, we had that already, maybe it helps you.
>
> Yes I'm reminder about that. But until yet I was not able to resolve my
> problem.
> I know, that I'm asking about the same. I tried find a solution by myself.
> But I think if I will find a good sample, it would help me a lot.


I think we allready posted samples in previous discussion:

http://groups.google.com/group/alt.php.sql/browse_frm/thread/f86dacb1b139dfee/3f053fb2a6da0bd6

<quote>
previous:
SELECT *
FROM adresse
WHERE (nom < 'Dupont') OR (nom = 'Dupont' AND id < 123)
ORDER BY nom DESC, id DESC
LIMIT 1

next:
SELECT *
FROM adresse
WHERE (nom > 'Dupont') OR (nom = 'Dupont' AND id > 123)
ORDER BY nom ASC, id ASC
LIMIT 1

</quote>




PHP example (assuming you are using "ORDER BY nom ASC, id ASC"):

<?php
error_reporting( E_ALL );

// some connection code here

if (isset($_REQUEST['id']))
$id = intval( $_REQUEST['id'] );
else
$id = 0;

if (isset($_REQUEST['mode']))
$mode = $_REQUEST['mode'];
else
$mode = '';

// Possible modes:
// new - entering data for new record (emit <form>)
// insert - saving data for new record (after 'new')
// edit - edit data for existing (current) record (emit <form>)
// update - save modifications for existing (current) record (after 'edit')
// delete - delete existing (current) record
// first - display first record
// previous - display previous record
// last - display last record
// next - display next record
// - (empty or none from above) display selected record

function data_valid()
{
// Put data passed by $_REQUEST in global variables and validate
// it for inserting new record or updating existing one.
// Return TRUE if data is valid.
// When data are invalid echo error messages and return FALSE.
}

function load_data()
{
// Query current ID
$query =
'SELECT date_enr, no_adr, famille, politesse, nom, ' .
' adr1, adr2,rue,cd_pays, pays, np, canton, ' .
' lieu, tel, fax, tel_opt, tel_opt_no,info1, ' .
' info2, info3, groupe1, groupe2, groupe3, ' .
' groupe4,groupe5, groupe6, groupe7, groupe8, ' .
' email, web ' .
'FROM adresse ' .
'WHERE id = ' . $GLOBALS['id']
;
$req = mysql_query($query);
if (!$req)
{
die( mysql_error() );
}
// load data to global variables
}

// Function which is able to return ID of previous, next, first
// and last record according to "ORDER BY" and current record ID.
function get_other($mode)
{
$id = $GLOBALS['id'];
if (($mode == 'previous') || ($mode == 'next'))
{
// this only makes sense if we have current record
if ($id)
{
// get current record "nom"
$query = "SELECT nom FROM adresse WHERE id = $id";
$req = mysql_query($query);
if (!$req)
die( mysql_error() );
$result = mysql_fetch_row( $req );
}
else
$result = false;
if ($result)
$nom = $result[0];
else
{
// if there's no previous or next we go to first or last
if ($mode=='previous')
return get_other( 'first' );
else
return get_other( 'last' );
}
}
// choosing apropriate query
switch($mode)
{
case 'first':
$query = "
SELECT id
FROM adresse
ORDER BY nom ASC, id ASC
LIMIT 1
";
break;
case 'previous':
$query = "
SELECT id
FROM adresse
WHERE (nom < '$nom') OR (nom = '$nom' AND id < $id)
ORDER BY nom DESC, id DESC
LIMIT 1
";
break;
case 'next':
$query = "
SELECT id
FROM adresse
WHERE (nom > '$nom') OR (nom = '$nom' AND id > $id)
ORDER BY nom ASC, id ASC
LIMIT 1
";
break;
case 'last':
$query = "
SELECT id
FROM adresse
ORDER BY nom DESC, id DESC
LIMIT 1
";
break;
}
$req = mysql_query($query);
if (!$req)
die( mysql_error() );
$result = mysql_fetch_row( $req );
if ($result)
return $result[0];
// if there's no previous or next we go to first or last
if ($mode=='previous')
return get_other( 'first' );
if ($mode=='next')
return get_other( 'last' );
}

function DeleteEnr()
{
// What is "DeleteEnr"? if you are removing "subdata", then no "famille" is needed.
// You need only ID (which you have as global).
}

function show_navigation_links()
{
$id = $GLOBALS['id'];
if ($id)
{
if (get_other('previous'))
{
$links[0] = array( 'fist', '?mode=first' );
$links[1] = array( 'previous', 'mode=previous&id=' . $id );
}
else
{
$links[0] = array( 'fist', '' );
$links[1] = array( 'previous', '' );
}
$links[2] = array( 'new', '?mode=new&id=' . $id );
if ($GLOBALS['mode'] != 'edit')
$links[3] = array( 'edit', '?mode=edit&id=' . $id );
else
$links[3] = array( 'edit', '' );
$links[4] = array( 'delete', '?mode=delete&id=' . $id );
if (get_other('next'))
{
$links[5] = array( 'next', 'mode=next&id=' . $id );
$links[6] = array( 'last', '?mode=last' );
}
else
{
$links[5] = array( 'next', '' );
$links[6] = array( 'last', '' );
}
}
else
{
if (get_other('first'))
$links[0] = array( 'fist', '?mode=first' );
else
$links[0] = array( 'fist', '' );
$links[1] = array( 'previous', '' );
$links[2] = array( 'new', '' );
$links[3] = array( 'edit', '' );
$links[4] = array( 'delete', '' );
$links[5] = array( 'next', '' );
if (get_other('last'))
$links[6] = array( 'last', '?mode=last' );
else
$links[6] = array( 'last', '' );
}
foreach( $link as $i => $links )
{
if ($link[1]!='')
$links[$i] = '<a href="' . htmlspecialchars( $_SERVER['PHP_SELF'] . $links[1] ) . '">' . $link[0] . '</a>';
else
$links[$i] = $link[0];
}
echo implode( ' | ', $links );
}
function show_data()
{
?>
<html>
<body>
Name: <?php htmlspecialchars( $GLOBALS['nom'] ); ?><br />
<!-- etcetera -->
<?php
show_navigation_links();
?>
</body>
</html>
<?php
}

function show_form()
{
?>
<html>
<body>
<form action="<?php htmlspecialchars( $_SERVER['PHP_SELF'] ); ?>">
<input type="hidden" name="id" value="<?php echo $GLOBALS['id']; ?>" />
<input type="hidden" name="mode" value="<?php echo $GLOBALS['mode']; ?>" />
Name: <input type="text" name="nom" value="<php echo htmlspecialchars( $nom ); ?>" /><br />
<!-- input fields for other data -->
<input type="submit" value="save" />
</form>
<?php
show_navigation_links();
?>
</body>
</html>
<?php
}

switch($mode)
{
case 'insert':
if (data_valid())
{
$query = "INSERT INTO ...";
if (mysql_query($query))
{
$id = mysql_insert_id();
header( 'Location: ' . $_SERVER['PHP_SELF'] . '?id=' . $id );
die();
}
else
echo mysql_error() . "<br />";
}
$mode = 'new';
show_form();
break;
case 'update':
if (data_valid())
{
$query = "UPDATE ....";
if (mysql_query($query))
{
header( 'Location: ' . $_SERVER['PHP_SELF'] . '?id=' . $id );
die();
}

else
echo mysql_error() . "<br />\n";
}
$mode = 'edit';
show_form();
break;
case 'new':
$mode = 'insert';
show_form();
break;
case 'edit':
load_data();
$mode = 'update';
show_form();
break;
case 'delete':
if (DeleteEnr())
{
$query = 'DELETE FROM adresse WHERE id=' . $id;
if (mysql_query($query))
{
$id = get_other('next');
if ($id)
header( 'Location: ' . $_SERVER['PHP_SELF'] . '?id=' . $id );
else
header( 'Location: main_page.html' );
die();
}
else
echo mysql_error() . "<br />\n";
}
$mode = '';
show_form();
break;
case 'first':
case 'previous':
case 'next':
case 'last':
$id = get_other($mode);
if ($id)
header( 'Location: ' . $_SERVER['PHP_SELF'] . '?id=' . $id );
else
header( 'Location: main_page.html' );
break;
default:
load_data();
show_data();
break;
}
?>



Hilarion

[Back to original message]