How to get records from databse in ascending order new record on page refresh each time

When records ended start from first record

Recommended Answers

All 34 Replies

Random Query In Ascending Order

Do you have a primary key or unique field in the table and what type it is? This is cruical for some control of which record to access.

primary key for field id

Use a session variable to keep track of the last viewed record between sessions.

Then use MySQL LIMIT clause to set an offset and limit the results to one record. MySQL Select (limit) reference

When you end up with an empty result set, reset back to record one.

Member Avatar for diafol

Something like this maybe. It's a little nasty - and perhaps only increment on success from DB.

session_start();

if(!isset($_SESSION['table_start'])){
    $_SESSION['table_start'] = 0;
}else{
    $_SESSION['table_start']++;
}
$this_start = $_SESSION['table_start'];


$sql = "SELECT ... FROM ... ORDER BY ... LIMIT $this_start, 1";

OK, you forgot to mention what type it is. So if it is an integer and autoincreased by the DB then the id fields probably increase as you add records and you can use this approach:

whenever you read and display the record save the id in the session. If there is no id in the session you are displaying the first record otherwise you just go for the next record. The rest is documended in the code below:

// start the session
session_start();

// if this is the first run of your script the id has not been stored in the session yet
if(!isset($_SESSION['current_id'])) {

    // this query will select the record with the lowest id, presumably the first record
    $query = 'SELECT * FROM table_name ORDER BY id ASC LIMIT 1';

} else {

    // this query will read the record just after the current id
    $query = 'SELECT * FROM table_name WHERE id > {$_SESSION['current_id']} ORDER BY id ASC LIMIT 1';
}

// read the row, display it and store the current id in the session
...
$_SESSION['current_id'] = $row['id']

More precise solution would be to have a field with timestamp so you could be always sure that the displayed records follow correctly each other.

Hopefully this is what you wanted to know.

I used ORDER BY RAND(), id ASC LIMIT 0,1

But it displays random records

I want serially like record_1 , record_2, record_3, record_4, record_5

one record at a time (displaying all records equally)

Member Avatar for diafol

Why are you ordering by RAND() if you want ordered results? The way I see it you have a choice with regard to modifying the WHERE or modifying the LIMIT clause.

As broj states - WHERE `id` > $var LIMIT 0,1
or - ORDER BY id ASC LIMIT $var,1

Not sure which would be the fastest. I think broj may have it - but you'd need to test.

without, RAND(), only one and same record is displayed. it is not going from record_1 to record_2

Only record_1 is displayed all the time

Member Avatar for diafol

Did you try my code? Or broj1's?

I used both codes, but same problem,
my database fields are,
id, userid, url, active
and query is,

$sql="SELECT * FROM websites WHERE userid=$id AND active=1 ORDER BY id ASC LIMIT 1"; 

I want to display one url at a time. my curl function converts that url into website.
thus I display external website on my site

I am doing this because framebreakers stops website displaying in iframe tag and redirect it from my site to original site

Help me to display one url at a time

Now I get what you want. You do not want to display a contents of one record on one page but a page with a URL which is stored in a record. Is that true? Well, if it is the best way I know would be using iframes which you do. But if the framebreakers on those pages redirect ot original page then as far as I am aware you loose control. I am not aware of a way how to avoid this (actually have no much experience in it). Maybe someone else on this forum knows more about that?

Member Avatar for diafol

Darn, we're back to this. A framebreaker code may have js check whether window.top is the same as window.self. If not then, a redirect ensues. So iframe content can't really be changed. Other sites may have X-Frame-Options: SAMEORIGIN as a response header. Again you're stuffed.

So cURL may be required in order to place the site into a div container. But as mentioned on a previous thread, 'domain-less' file references will try to access your server for the relevant files. The only way I can think of to circumvent this is to replace all file references to include the host domain:

e.g.

<img src="images/myimg.png" /> 

to

<img src="http://www.example.com/images/myimg.png" />

A preg replace may be able to make wholesale changes to the string before you echo it out.

Also as previously mentioned, the CSS and JS files, if you can get them resolved, may cause clashes with your own site resources. Additonally, if you have certain formatting, but the remote css file doesn't, you may find that your 'embedded' site looks totally different to the original. I can't think of a suitable workaround for this. DO these 'embedded sites' need to be functional? Could you not use images?

Headache.

Thanks

My cURL code is working fine. It displays website inside DIV even framebreaker exists.

I'm having problem with mysql query.

$sql="SELECT * FROM websites WHERE userid=$id AND active=1 ORDER BY id ASC LIMIT 1";

I need help to display one record at a time and next one record on pagerefresh.

my query without rand() displays only one record all the time and with rand() displaying all records but not equally

I want to display all records equally one by one

if you want to display one by one in sequece like 1,2,3
then i will suggest you to find next

intitially pass 0 as id

$id=0;//this must be not part of fetch script, it may be passed in post or get

now search next maximum active id from pass id

$nextid="select max(id) from websites where active=1 and userid=$id";
$query="select * from websites where active=1 and userid=$nextid";

now run query and return result to your div, and also save nextid in some another div or hidden field

so next time page refreshed, pass that hidden field your fetch script as id

I hope i explained it well

This thread isn't solved yet?

My suggestion, as better explained/implemented by @diafol, should have worked with just a bit of tweaking. (You'll need to replace his placeholder sql with a query that retrieves the right fields, etc.)

@vizz, per your own description, you want new content to appear (per user), every time the page is refreshed (assuming the refresh button of the BROWSER, or a JavaScript refresh, or a META tag refresh.) So no form is being submitted and no link is being clicked on. Therefore, the ONLY way to persist information between reloads is with $_SESSION (How PHP Sessions Work and How to Use Them).

NOTE: Sessions require cookies be enabled so make sure you haven't disabled cookies in your web browser's settings.

Thanks broj1

$_SESSION

Working fine now.

but body tag is getting disturbed.

other sites style is forcely working on body tag

Member Avatar for diafol

other sites style is forcely working on body tag

said so.

Thanks broj1

No worries. But I think I have contributed the least in this thread :-).

@diafol

Thanks

Your code is also working fine. tested both codes
Thanks for the help

TABLE views record last viewed website. (id,user_id,url_id)
How to display next website after viewed last time when $_SESSION is reset?

When records end from table websites it gives error,

Notice: Undefined variable: url_id

Notice: Undefined variable: url

$user_id=$_REQUEST['id'];

if(!isset($_SESSION['current_id'])) 
{
// this query will select the record with the lowest id, presumably the first record
$query ="SELECT * FROM websites where userid !=$user_id ORDER BY id ASC LIMIT 0,1";
} else {
    // this query will read the record just after the current id
    $query = "SELECT * FROM websites WHERE id > {$_SESSION['current_id']} AND userid !=$user_id ORDER BY id ASC LIMIT 0,1";
}   

$result=mysql_query($query);                        

while ($row=mysql_fetch_assoc($result)) 
{ 
    $url_id=$row["id"];
    $uid=$row["userid"];
    $url=$row["url"];
}

//$_SESSION['current_id'] = $url_id;

$res = mysql_query("SELECT * FROM views where user_id =$user_id");
while ($row_view=mysql_fetch_assoc($res)) 
{
  $viewid = $row_view["id"];
  $view_user_id = $row_view["user_id"];
  $view_url_id = $row_view["url_id"];

}

if (empty($view_url_id)) 
{
    $_SESSION['current_id'] =  $url_id; 
}
else
{
    if($view_url_id < $url_id)
        {
            $_SESSION['current_id'] = $url_id;  
        }
    else 
        {
            $_SESSION['current_id'] = 0;    
        }
}

//display website

$fineurl="http://$url";                                 
$html = Get_Domain_Contents($fineurl);                          
$html = "<base href='{$fineurl}' />" . $html;                
echo $html; 

//update views table

$ft_update = mysql_query("SELECT * FROM views where user_id =$user_id");
$fetch_update = mysql_num_rows($ft_update);

if($fetch_update == 0)
{
    $insert_query="INSERT INTO views (user_id, url_id) VALUES ($user_id, $url_id)";
    $insert=mysql_query($insert_query); 
}
else    
{       
    $update_query="UPDATE views SET  url_id=$url_id WHERE user_id=$user_id";
    $update=mysql_query($update_query); 
}

How to display next website after viewed last time when $_SESSION is reset?

When the session is reset (i.e. browser is closed) you loose that information. If you want to keep it you have to store it somewhere. One option is on server (in a database or in a file) other option is on users machine (a cookie). It is up to you to decide which one is more convenient.

When records end from table websites it gives error,
Notice: Undefined variable: url_id
Notice: Undefined variable: url

Can you find out which line the notices are being issued? You use these variables in many places.

Member Avatar for diafol

When url comes to the end, reset it to the first record again.

if($num > count($whatever))$num = 0;

Sort of thing

@broj1
I have table in database views where last viewed website is updated. Fields of table views are,
id, user_id, url_id

ERROR
When records end from table websites it gives error,

  1. Notice: Undefined variable: url_id
  • line number 38 if($view_url_id < $url_id)
  1. Notice: Undefined variable: url

How to get url next of last viewed, or When url comes to the end, reset it to the first record again.

@diafol

I tried your code but little help needed
How to set session using this,

if($num > count($whatever))$num = 0;

$_SESSION['current_id'] = $url_id;

OR

$_SESSION['current_id'] = 0;

Member Avatar for diafol
if($_SESSION['current_id'] > count($whatever))$_SESSION['current_id'] = 0;

BUT, this will work only with the method I outlined (LIMIT method), as record #0 will not exist for the WHERE clause.

Thanks
But little problem

I am having problems to goto first record on end of records please help me.

How to get record last viewed website and display next website at next time???
There is table views to record last viewed website.

My simple code to redirect to first record on end of records

Database

CREATE TABLE IF NOT EXISTS `websites` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`userid` bigint(10) NOT NULL,
`url` varchar(250) NOT NULL,
`active` smallint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `views` (
`id` mediumint(10) NOT NULL AUTO_INCREMENT,
`user_id` mediumint(10) NOT NULL,
`url_id` mediumint(10) NOT NULL,
PRIMARY KEY (`id`)
)

Add more than 4-5 websites to database websites and test

Get website contents

function Get_Domain_Contents($url){
    $ch = curl_init();
    $timeout = 5;
    curl_setopt($ch,CURLOPT_URL,$url);
    curl_setopt($ch,CURLOPT_RETURNTRANSFER,1);
    curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,$timeout);
    curl_setopt($ch, CURLOPT_TIMEOUT, 10);
    $data = curl_exec($ch);
    curl_close($ch);
    return $data;   
}   

display website

$user_id=$_REQUEST['id'];       

if(!isset($_SESSION['current_id'])) 
{
// this query will select the record with the lowest id, presumably the first record
$query ="SELECT * FROM websites where userid !=$user_id ORDER BY id ASC LIMIT 0,1";
} else {
    // this query will read the record just after the current id
    $query = "SELECT * FROM websites WHERE id > {$_SESSION['current_id']} AND userid !=$user_id ORDER BY id ASC LIMIT 0,1";
}           

$result=mysql_query($query);                        

while ($row=mysql_fetch_assoc($result)) 
{ 
    $url_id=$row["id"];
    $uid=$row["userid"];
    $url=$row["url"];
}   

//$_SESSION['current_id']=$url_id;                      

 if($_SESSION['current_id'] >= count($url_id))
    {
        $_SESSION['current_id'] = 0;    
    }
else
    {
        $_SESSION['current_id']=$url_id;
    }                   

$fineurl="http://$url"  ;                   
$html = Get_Domain_Contents($fineurl);                              
$html = "<base href='{$fineurl}' />" . $html;

echo $html; 

Why do you have a condition userid !=$user_id in your query? I do not get that.

Anyway, just a guess since I am not sure whether I undertood the problem corectly:

// first get the last ID so you know when to switch for the first id again
// please note that DESC has been used
// LIMIT 1 is enough, no need for LIMT 0,1
$query ="SELECT id FROM websites where userid=$user_id ORDER BY id DESC LIMIT 1";
$last_id = ...

// if $_SESSION['current_id'] does not exist or if it is equal to last id
// then read the first record
if(!isset($_SESSION['current_id']) or $_SESSION['current_id'] == $last_id)
{
    // this query will select the record with the lowest id, presumably the first record
    $query ="SELECT * FROM websites where userid =$user_id ORDER BY id ASC LIMIT 1";

// in other cases read next record
} else {
    // this query will read the record just after the current id
    $query = "SELECT * FROM websites WHERE id > {$_SESSION['current_id']} AND userid =$user_id ORDER BY id ASC LIMIT 1";
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.