Page 1 of 2

Better way to do it without using IDs?

Posted: Fri Nov 04, 2005 5:59 pm
by Dm7
Right now I'm using ids to calucate the offsets (yes they works fine.. but a bit buggy... read on to see why), but I don't want to use ids for one reason... whenever I delete a file from the database, the IDs aren't right so therefore it get messed up. So I don't want to use IDs (autoin.), so I'd like to ask you to see if you have a better way to do that? How do you select a selected row and get row number from it so I can calucate LIMIT offset for prev, next and of course first and last pages.

Simple script sample(s) would be helpful too. Thanks.

Code: Select all

// Calucate offsets for prev and next links
			$total_results = mysql_result(mysql_query("SELECT COUNT(*) FROM $table"),0);
			$prev = $row['id']-1;
			$next = $row['id']+1;
			
			$sqloffsetp = "SELECT * FROM $table WHERE id='$prev'";
			$sqloffsetn = "SELECT * FROM $table WHERE id='$next'";
			$sqloffsetf = "SELECT * FROM $table ORDER BY date LIMIT 1";
			$sqloffsetl = "SELECT * FROM $table ORDER BY date DESC LIMIT 1";
			$resoffsetp = mysql_query($sqloffsetp);
			$resoffsetn = mysql_query($sqloffsetn);
			$resoffsetf = mysql_query($sqloffsetf);
			$resoffsetl = mysql_query($sqloffsetl);
			
			$testp = mysql_fetch_assoc($resoffsetp); //prev
			$testn = mysql_fetch_assoc($resoffsetn); //next
			$testf = mysql_fetch_assoc($resoffsetf); //first
			$testl = mysql_fetch_assoc($resoffsetl); //last
			
			//MYSQL ENDS
                        nextprev();

Code: Select all

function nextprev() // <<PREV 10 out of 20 images NEXT>>
{
			global $row, $table, $testp, $testf, $testn, $testl, $total_results;

			
			echo '<div align="center">';
			if ($row['id'] != 1) {
			echo "<a href='" . $_SERVER['PHP_SELF'] ."?pix=". $testf['name'] ."'><FIRST </a> ";
			}
			if (isset($testp['id'])) {
				echo "<a href='" . $_SERVER['PHP_SELF'] ."?pix=". $testp['name'] ."'><<PREV</a> ";
			}
			echo $row['id'] .' out of '. $total_results .' images ';
			if (isset($testn['id'])) {
				echo "<a href='". $_SERVER['PHP_SELF'] ."?pix=". $testn['name'] ."'>NEXT>></a>";
			}
			if ($row['id'] != $total_results) {
			echo "<a href='". $_SERVER['PHP_SELF'] ."?pix=". $testl['name'] ."'> LAST></a>";
			}
			echo '</div><br>';
}

Posted: Fri Nov 04, 2005 6:35 pm
by Luke
What is this for? previous and next links?

Posted: Fri Nov 04, 2005 6:36 pm
by TJ
You don't actually need to know the row numbers.

Recall that the SQL LIMIT statement takes two parameters: start, count

So given a recordset 'R' returned from "SELECT * FROM table";

"SELECT * FROM table LIMIT 1, 10" will give you records 1 through 10
"SELECT * from table LIMIT 15, 5" will give you records 15 through 19

All you need think about is what the values of start and count are and leave the internal numbering of the rows to the database.

Provided the result-set of the various queries produces the same unlimited result-set (no rows inserted/deleted in the interim) then your previous/next functions only have to update start. Usually inserting or deleting of records isn't an issue but its worth being aware of it.

Code: Select all

<?
$first = (isset($_GET['first']) && is_numeric($_GET['first'])) ? $_GET['first'] : 1;
$count = (isset($_GET['count']) && is_numeric($_GET['count']) && $_GET['count'] > 1) ? $_GET['count'] : 10;
$sqlQuery = sprintf("SELECT * FROM table LIMIT %d, %d", $first, $count);
$recordSet = mysql_query($sqlQuery, $connection) or die(mysql_error());
if (mysql_num_rows($recordSet)) {
  while($row = mysql_fetch_assoc($recordSet)) {
    print_r($row.'<br/>');
  }
}
else
  echo 'No records available<br/>';
?>

Posted: Fri Nov 04, 2005 6:42 pm
by Luke
Yes... mySQL doesn't care if you're missing the 4th id.

Posted: Fri Nov 04, 2005 7:31 pm
by timvw
And here is a hint for selecting the next / previous :

http://www.severnsolutions.co.uk/twblog ... andprevtwo


Btw, SELECT with LIMIT seems to make little sense if there isn't an ORDER BY clause..

Posted: Fri Nov 04, 2005 7:36 pm
by Dm7
Wait, it's for viewing pics...

http://www.dm7.net/gallery/view.php?pix=robotmonkey.jpg
go there so you get a better idea. :)

Posted: Fri Nov 04, 2005 10:04 pm
by Dm7
So you can see, I only want to show one image at the time... and... I'm not sure how I can do that without ID's...... the url I have given you earlier was a direct url to the viewing page of my gallery.

That's what I'm talking about... does anybody have an idea? I don't want it to show rows of stuff, I already do that with my gallery page... I'm talking about one image with "slide" feature.

Posted: Fri Nov 04, 2005 10:39 pm
by josh
LIMIT $x, 1

Where $x is the # of the pic you want to view, $x doesn't have to correspond to the id

Posted: Fri Nov 04, 2005 10:50 pm
by Dm7
jshpro2 wrote:LIMIT $x, 1

Where $x is the # of the pic you want to view, $x doesn't have to correspond to the id
Gotcha, but... how do I get $x? How do I get like $_GET['pix'] and get number for that pic row? Well how do I DETERMINE $x for LIMIT since I use pic name to call stuff.

Posted: Fri Nov 04, 2005 11:11 pm
by feyd
you don't use the picture's name anymore, or you would have to figure out how far into the data it is by traversing the results..

Posted: Sat Nov 05, 2005 3:03 pm
by Dm7
feyd wrote:you don't use the picture's name anymore, or you would have to figure out how far into the data it is by traversing the results..
traversing the results.. how? I'm stupid.. forgive me.. :oops:

Code: Select all

$first = (isset($_GET['first']) && is_numeric($_GET['first'])) ? $_GET['first'] : 1;
Geez that's what I have been looking for.. a shorter version of if/else thing... any good tutorial that explains that?

Posted: Sat Nov 05, 2005 3:14 pm
by John Cartwright
Its a comparison operator called ternary

Posted: Sat Nov 05, 2005 3:19 pm
by Dm7
Jcart wrote:Its a comparison operator called ternary
Thanks :D Now I understand how it works.

Posted: Sat Nov 05, 2005 3:19 pm
by feyd
traversing the results:

Code: Select all

$offset = 0;
while($row = mysql_fetch_assoc($result)) {
  if($offset == $currentPage) {
    break;
  } else {
    $offset++;
  }
}
if(is_array($row)) {
  // offset was found
  var_export($row);
}

Posted: Sat Nov 05, 2005 6:02 pm
by Dm7
feyd wrote:traversing the results:

Code: Select all

$offset = 0;
while($row = mysql_fetch_assoc($result)) {
  if($offset == $currentPage) {
    break;
  } else {
    $offset++;
  }
}
if(is_array($row)) {
  // offset was found
  var_export($row);
}
Geez I'm lost.. I guess I'll do the $n=1 instead of $pix = blah.jpg for my viewing page. :P Thanks for the help.