need to create a link to move through a table that automatically changes..

[Kane250]

Hi there,

 

This may be simple, and I might just be having a block, but hopefully someone can put perspective on this for me.

 

I have a table like this:

 

id | text | created_at | updated_at

 

1  text1    timestamp  timestamp (1 day ago)

2  text2  timestamp    timestamp (2 days ago)

 

In the table are a number of entries that are just text, and the created_at and updated_at are automatically stamped with DATETIME.  I'm trying to create a link on a page that upon clicking will find the entry that was updated least.  I'm basically doing this for that:

 

$order_by_desc = "SELECT text FROM para1 ORDER BY  `updated_at` DESC LIMIT 0 , 1";
$oldest_result = mysql_query($order_by_desc,$con);
$oldest_row = mysql_fetch_array($oldest_result);
$least_updated = ($oldest_row['text']);

 

I am using a link that basically just prints out $least_updated. In this example it would print out the bottom result, or ID# 2.  However, what I want this button to do is move up the table the next time it is hit, so that after it has displayed what would be ID#2, it looks for the next oldest entry that has been updated. In this case ID#1, but this would not always be the case since entries in the table will be updated sporadically.  Is there any way to set this up so that a link or a button of some sort will keep displaying this information each time?

 

I feel like it's got to be simple, but I just don't do enough php to know what function might do this.  I realize this might require something like javascript or ajax...so if anyone could just help me out with the php code that I need ajax to update the link with, that would be really helpful.

 

Thanks for any help in advance!

 

 

[drisate]

Yeah you could LIMIT 0 , 2 and load loop mysql_fetch_array so you get the stuff of ID#2 and ID#1 then use them like you need in your page

 

$i = 0;
$transaction = mysql_query("SELECT text FROM para1 ORDER BY  `updated_at` DESC LIMIT 0 , 1") or die (mysql_error());
while($trans = @mysql_fetch_array($transaction)){

if ($i = "0"){
	//oldest
}else{
	// newest
}

$i++;}

// Now that every vars are loaded do your page as normal and use the vars you initiated

[Kane250]

Thanks.

 

So is this code just reordering the table each time to find the oldest or the newest?  In actuality this table would contain many entries (possibly hundreds), so I'm wanting it to start at the oldest entry and then progressively climb towards the newest entry with each new click.

 

Would I use this same code but not limit the results?

[drisate]

The only thing you need to do is to load the time from the URL of where you are and insert it into the select querry

 

by the way i made an error its LIMIT 0 , 2

 

lol

 

and to load your stuff the code would look like

 

<?php

if ($_GET[$x]){

//bigger then
$operation = "x>'$_GET[$x]'";

// OR

//Smaller then
$operation = "x<'$_GET[$x]'";

// Choose what you prefer

}

$i = 0;
$transaction = mysql_query("SELECT * FROM para1 where $operation  ORDER BY `updated_at` DESC LIMIT 0 , 1") or die (mysql_error());
while($trans = @mysql_fetch_array($transaction)){

if ($i = "0"){
	//oldest
}else{
	// newest
}

$i++;}

// Now that every vars are loaded do your page as normal and use the vars you initiated
?>

[samshel]

initiate a variable to 0.

 

$i = 0;

 

fire query

 

SELECT * FROM para1 where  ORDER BY `updated_at` DESC LIMIT  $i , 1

 

 

whenever up button is pressed, increment $i by 1, you can refresh page and pass the $i by GET.

 

so when i = 1

 

query becomes

 

SELECT * FROM para1 where  ORDER BY `updated_at` DESC LIMIT  1 , 1 = > gives you second record updated latest

 

when button hit again,

 

i = 2

 

SELECT * FROM para1 where  ORDER BY `updated_at` DESC LIMIT  2 , 1 = > gives you third record updated latest

 

hope this is what u r looking for.

 

 

 

 

[drisate]

omg thats really intellegent samshel

A lot better. your a genious hehe

 

Simpler then what i was gething him into hehe

[Kane250]

Ahh that seems like exactly what I was looking for.  Will try this tonight.  Thanks!

[Kane250]

This seems to do exactly what I want, but I'm having issues with starting it at 0 and moving forward from there.  Here is how I have it running:

<?php

//IF THE NEXT BUTTON IS PRESSED

if (isset($_POST['newText'])) {
$direction=$_POST['newText']+1;}
else {
$direction=0;
}


//IF THE PREVIOUS BUTTON IS PRESSED

if (isset($_POST['previousText'])) {
$i--;
}


//CONNECT TO DB AND FIND OLDEST UPDATED TEXT AND DISPLAY IT
$con = mysql_connect('host', 'user', 'pw');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$db_selected = mysql_select_db("db",$con);
$order_by_desc = "SELECT text FROM para1 ORDER BY  `updated_at` DESC LIMIT '$direction' , 1";
$oldest_result = mysql_query($order_by_desc,$con);
$oldest_row = mysql_fetch_array($oldest_result);
$least_updated = ($oldest_row['text']);
mysql_close($con);
?>

<form name="firstForm" method="post" action="index.php">

<input type="submit" name="newText" value="New" /> 
<input type="submit" name="previousText" value="Previous" /> 

Paragraph 1: <textarea name="form1"><?php print $least_updated ?></textarea><br /></p>
<br />

 

It definitely works if I manually change the variable in the sql command but I don't know how to get this incrementing started.  If I add $direction=0; at the top, it just resets to 0 each refresh, and if I don't add anything, it's not a number...

[Kane250]

???

[drisate]

Try something like this

 

<?php

if ($_POST[direction]){
if ($_POST['newText']){
	$direction=$_POST[direction]+1;
}else{
	$direction=$_POST[direction]-1;
}
}else{
$direction = "0";
}

//CONNECT TO DB AND FIND OLDEST UPDATED TEXT AND DISPLAY IT
$con = mysql_connect('host', 'user', 'pw');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$db_selected = mysql_select_db("db",$con);
$order_by_desc = "SELECT text FROM para1 ORDER BY  `updated_at` DESC LIMIT '$direction' , 1";
$oldest_result = mysql_query($order_by_desc,$con);
$oldest_row = mysql_fetch_array($oldest_result);
$least_updated = ($oldest_row['text']);
mysql_close($con);
?>

<form name="firstForm" method="post" action="index.php">

<input type="hidden" name="num" value="<?=$direction?>" /> 
<input type="submit" name="newText" value="New" /> 
<input type="submit" name="previousText" value="Previous" /> 

Paragraph 1: <textarea name="form1"><?php print $least_updated ?></textarea><br /></p>
<br />