• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
complex query or subquery to 'ignore' initial database records

#1
[eluser]jude[/eluser]
I have some standard code for getting data from a database:

function get_focus_news ($n1) {
$this->db->select('id, nid, date, title, intro');
$this->db->where('nid =', $n1);
$this->db->order_by('id desc');
$query = $this->db->get('focus', 15, 5);
return $query->result();
}

It gets 15 filtered records with the required 'nid', starting from the fifth filtered record.

How is the above routine implemented while ignoring the first 5 ACTUAL records of the database? How do we get the above routine to be implemented starting from the sixth ACTUAL record.

This, I suspect, requires a subquery to enable the routine to ignore the first 5 ACTUAL
records, as opposed to filtered records ?

#2
[eluser]TheFuzzy0ne[/eluser]
I'm sorry, but I don't understand. The query you've provided should ignore the first five entries, which is what you want, isn't it?

#3
[eluser]jude[/eluser]
Maybe an example will clarify. Suppose there's a list of stories on telecom, and I want to access only those stories on WiMax. BESIDES, I also want the five initial stories in the database(whatever they might be, WiMax or other) ignored, since I have already given them prominantly as lead stories.

If I run the code I gave earlier, the first five records ON WIMAX are identified and ignored, and the remaining WiMax stories delivered.

One needs a code that ignores the first five records in the database, and does the selection (of stories on WiMax) from the remaining (N minus 5) records.

Maybe a complex query or subquery is needed?

function get_focus_news ($n1) {
$this->db->select('id, nid, date, title, intro');

(N.B. from table 'focus' ignoring the first five records)

$this->db->where('nid =', $n1);
$this->db->order_by('id desc');
$query = $this->db->get('focus', 6, 0); (get first six items with the required 'nid')
return $query->result();
}

Thanks

#4
[eluser]TheFuzzy0ne[/eluser]
Unfortunately, I still don't understand the difference between:
Quote:If I run the code I gave earlier, the first five records ON WIMAX are identified and ignored, and the remaining WiMax stories delivered.

and
Quote:One needs a code that ignores the first five records in the database, and does the selection (of stories on WiMax) from the remaining (N minus 5) records.

But it's most likely just me being a bit dense.

#5
[eluser]Jondolar[/eluser]
Use the LIMIT clause

LIMIT 5,5 gets you the next 5 records (after the first 5 are skipped).

#6
[eluser]jude[/eluser]
no luck. The code below, which adds a limit clause (actually there are two limit clauses) gets me the seventh WiMax record onwards (the '$nid' is wimax).

function get_focus_news ($n1) {

$this->db->select('title, id');
$this->db->limit(10, 6);
$this->db->where('nid =', $n1);
$this->db->order_by('id desc');
$query = $this->db->get('news_posts', 6, 0);
return $query->result();
}

The database records are composed of wimax and non-wimax files. I needed the first six records (NOT first six wimax records) ignored in the query.

Thanks

#7
[eluser]Jondolar[/eluser]
Remove the limit()

Change this line:
$query = $this->db->get(‘news_posts’, 25, 6);

That will return you the next 25 records after the first 6.

#8
[eluser]jude[/eluser]
I removed the limit, and increased the records to 25.

Here is the revised code:

function get_focus_news ($n1) {

$this->db->select(‘title, id’);
$this->db->where(‘nid =’, $n1); [nid = wimax]
$this->db->order_by(‘id desc’);
$query = $this->db->get(‘focus’, 25, 6);
return $query->result();
}

Here I get 25 wimax stories after the db ignores the first six wimax stories.
What's needed is to get 25 wimax stories after the db ignores the first 6 db records (maybe perhaps 4 non-wimax stories and 2 wimax stories). In this instance I get the third wimax story onwards.

Terribly confusing, but that's my problem.

Thanks


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.