Better way to do it without using IDs?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Dm7
Forum Commoner
Posts: 67
Joined: Sat Oct 08, 2005 9:16 pm
Location: USA

Better way to do it without using IDs?

Post 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>';
}
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

What is this for? previous and next links?
TJ
Forum Newbie
Posts: 20
Joined: Thu Nov 03, 2005 10:22 pm
Location: Nottingham, UK

Post 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/>';
?>
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Yes... mySQL doesn't care if you're missing the 4th id.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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..
Dm7
Forum Commoner
Posts: 67
Joined: Sat Oct 08, 2005 9:16 pm
Location: USA

Post 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. :)
Dm7
Forum Commoner
Posts: 67
Joined: Sat Oct 08, 2005 9:16 pm
Location: USA

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Dm7
Forum Commoner
Posts: 67
Joined: Sat Oct 08, 2005 9:16 pm
Location: USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
Dm7
Forum Commoner
Posts: 67
Joined: Sat Oct 08, 2005 9:16 pm
Location: USA

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Its a comparison operator called ternary
Dm7
Forum Commoner
Posts: 67
Joined: Sat Oct 08, 2005 9:16 pm
Location: USA

Post by Dm7 »

Jcart wrote:Its a comparison operator called ternary
Thanks :D Now I understand how it works.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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);
}
Dm7
Forum Commoner
Posts: 67
Joined: Sat Oct 08, 2005 9:16 pm
Location: USA

Post 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.
Post Reply