0

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

When records ended start from first record

5
Contributors
34
Replies
41
Views
4 Years
Discussion Span
Last Post by broj1
0

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.

0

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.

0

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";
0

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.

Edited by broj1

0

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)

0

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.

Edited by diafol

0

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

0

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

0

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?

0

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.

0

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

0

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

0

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.

0

Thanks broj1

$_SESSION

Working fine now.

but body tag is getting disturbed.

other sites style is forcely working on body tag

0

Thanks broj1

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

0

@diafol

Thanks

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

0

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); 
}

Edited by vizz

0

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.

0

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

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

Sort of thing

Edited by diafol

0

@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.

0

@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;

0
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.

0

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; 
0

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";
}

Edited by broj1

This article has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.