I want to random show 6 news/reviews on my front page but it shows the same content 6 times random but I will not have duplication of content. Here is the SQL query:
SELECT
anmeldelser.billed_sti ,
anmeldelser.overskrift ,
anmeldelser.indhold ,
anmeldelser.id ,
anmeldelser.godkendt
FROM
anmeldelser
LIMIT 0,6
UNION ALL
SELECT
nyheder.id ,
nyheder.billed_sti ,
nyheder.overskrift ,
nyheder.indhold ,
nyheder.godkendt
FROM nyheder
ORDER BY rand() LIMIT 0,6
I am assuming that you need to join a Two table by this comment of yours. You didn't mention your foreign key so I am assuming that also.
It is also not clear that the column name of your tables are same or not.
So, I am posting an join query for your table in which I assume your foreign key and column name, so please correct that before using it.
Here is your query to join your table:
$query = $this->db
->select('an.billed_sti,an.overskrift,an.indhold,an.id,an.godkendt, ny.id as ny_id,ny.billed_sti as ny_billed_sti, ny.overskrift as ny_overskrift, ny.indhold as ny_indhold , ny.godkendt as ny_godkendt ')
->from('anmeldelser as an')
->join('nyheder as ny', 'ny.id_fk = an.id', 'left outer') // I am assuming here that the [id_fk] field is the foreign key
->limit(0, 6)
->order_by('puttablename.tablecolumn', 'asc') // Your you table name and column name by which you want to order, you can use [asc/desc] as your need
->get();
And If you want to UNION here is the solution for it.
//initialize query builder
$sql1=$sql2=$this->db;
$sql1->select('anmeldelser.billed_sti ,anmeldelser.overskrift ,anmeldelser.indhold ,anmeldelser.id ,anmeldelser.godkendt');
$sql1->from('anmeldelser');
$sql1->order_by('rand()');
$sql1->limit(3);
//get only sql string
$query1=$sql1->get_compiled_select();
$sql2->select('nyheder.id ,nyheder.billed_sti ,nyheder.overskrift ,nyheder.indhold ,nyheder.godkendt');
$sql2->from('nyheder');
$sql2->order_by('rand()');
$sql2->limit(3);
$query2=$sql2->get_compiled_select();
//combine two query
$query = $this->mydb->query("($query1) UNION ($query2)");
$result = $query->result();
showing my example with active record for simplicity, try randomizing your offset instead of the order, while still limiting to 6
// get the total number of rows
$total_rows = $this->db->count_all_results('my_table');
// offset random point within the total rows
$offset = rand( 0 , $total_rows - 6 );
$q = $this->db->offset( $offset )->limit( 6 )->get( 'my_table' );
print_r( $q->result_array() );
