I just wanna ask if that's possible, and if it is, how ? because I'm running out of ideas. my query only get 1 record at the time so the limit didn't work for me. I'm making a searchbar function, with 4 tables using union . here's the code
if (isset($_POST['searchquery']) && $_POST['searchquery'] !=""){
$searchquery = preg_replace('#[^a-z 0-9?!]#i', '' , $_POST['searchquery']);
$radio = $_POST['radio'];
$sqlcommand = "(SELECT id_fakultas as id_search
FROM tabel_fakultas
WHERE kode_fakultas LIKE '%$searchquery%' OR ket_fakultas LIKE '%$searchquery%')
UNION
(SELECT id_jenis AS id_search
FROM tabel_jenis
WHERE jenis_surat LIKE '%$searchquery%' OR ket_jenis LIKE '%$searchquery%')
UNION
(SELECT id_kodearea as id_search
FROM tabel_kodearea
WHERE kode_area LIKE '%$searchquery%' OR ket_kodearea LIKE '%$searchquery%')
UNION
(SELECT id_lembaga as id_search
FROM tabel_lembaga
WHERE kode_lembaga LIKE '%$searchquery%' OR ket_lembaga LIKE '%$searchquery%')
UNION
(SELECT id_prodi as id_search
FROM tabel_prodi
WHERE kode_prodi LIKE '%$searchquery%' OR ket_prodi LIKE '%$searchquery%')
UNION
(SELECT id_surat AS id_search
FROM tabel_surat
WHERE
subyeksurat LIKE '%$searchquery%'
OR tanggal_srtmasuk LIKE '%$searchquery%'
OR tanggal_srtkeluar LIKE '%$searchquery%'
OR keterangan LIKE '%$searchquery%'
OR no_surat LIKE '%$searchquery%'
OR status_surat LIKE '%$searchquery%'
AND tipe_surat LIKE '%$searchquery%'
)
";
$runquery = mysql_query($sqlcommand)or die(mysql_error());
$query_jumlah = mysql_num_rows ($runquery);
so whatever I'm typing , it'll check the id which match with those 4 tables. after I get the id , I'll use it to get the record I wanted. it's on 4th table .
if ( $query_jumlah > 0 )
{
while ($data = mysql_fetch_array($runquery)){
$cari = $data['id_search'];
echo $posisi, $batas;
$commandcari = mysql_query("SELECT
tfak.kode_fakultas, tfak.ket_fakultas,
tj.jenis_surat, tj.ket_jenis,
tk.kode_area , tk.ket_kodearea,
tlem.kode_lembaga, tlem.ket_lembaga,
tprodi.kode_prodi, tprodi.ket_prodi,
ts.tipe_surat, ts.subyeksurat, ts.tanggal_srtmasuk, ts.tanggal_srtkeluar, ts.keterangan, ts.no_surat, ts.status_surat,
ts.id_surat, ts.id_fakultas, ts.id_jenis, ts.id_lembaga, ts.id_prodi, ts.tipe_surat, ts.file
FROM tabel_surat ts
LEFT JOIN tabel_jenis tj
ON ts.id_jenis=tj.id_jenis
LEFT JOIN tabel_kodearea tk
ON ts.id_kodearea=tk.id_kodearea
LEFT JOIN tabel_lembaga tlem
ON ts.id_lembaga=tlem.id_lembaga
LEFT JOIN tabel_prodi tprodi
ON ts.id_prodi=tprodi.id_prodi
LEFT JOIN tabel_fakultas tfak
ON ts.id_fakultas=tfak.id_fakultas
WHERE
ts.id_surat LIKE '%$cari%'
OR ts.id_lembaga LIKE '%$cari%'
OR ts.id_fakultas LIKE '%$cari%'
OR ts.id_prodi LIKE '%$cari%'
AND ts.tipe_surat LIKE '%$radio%'
LIMIT $posisi,$batas
") or die(mysql_error());
if(mysql_num_rows($commandcari) > 0 ) {
$no1 =$posisi+1;
while ($data2 = mysql_fetch_array($commandcari)) {
$no++;
$pembuat_fakultas=$data2['id_fakultas'];
$pembuat_lembaga=$data2['id_lembaga'];
$pembuat_prodi=$data2['id_prodi'];
echo '
<tr>
<td align="center"> '.$no.'</td>
<td align="center"> '.$data2['no_surat'].'</td>
<td align="center"> '.$data2['jenis_surat'].'</td>
<td align="center"> '.$data2['subyeksurat'].'</td>
<td align="center"> '.$data2['ket_jenis'].'</td>
<td align="center"> '.$data2['kode_area'].'</td>';
if ($pembuat_fakultas!=''){
echo '<td align="center"> '.$data2['ket_fakultas'].'</td>' ; }
elseif ($pembuat_lembaga !=''){
echo '<td align="center"> '.$data2['ket_lembaga'].'</td>' ; }
elseif ($pembuat_prodi != ''){
echo '<td align="center"> '.$data2['ket_prodi'].'</td>' ; }
if ($radio!='suratkeluar'){
echo '<td align="center"> '.$data2['tanggal_srtmasuk'].'</td>';}
elseif ($radio!='suratmasuk'){
echo '<td align="center"> '.$data2['tanggal_srtkeluar'].'</td>';}
echo'
<td align="center"> '.$data2['keterangan'].'</td>
<td align="center"><a href="'.$data2['file'].'" target = "_blank"> Preview</a></td>
</tr>
';
}
}
}
}
if I use the usual paging, with LIMIT in my mysql_query , my row only returned 1. It's because I only get the id from the union and see which record use the id I got from the union on the 4th table . so if I got 3 ids from the union, like ID1, ID2, ID3, when I use it on $commandcari, it'll return 1 record num row on each id.
select * from tabel_surat where id_surat = id1
select * from tabel_surat where id_surat = id2
select * from tabel_surat where id_surat = id3
is it possible to do the pagination without using limit ?
NB : I know my english is not good enough to do the explanation (it's not my primary language), but I hope you understand my question.
If you need pagination on a search in multiple tables, then you need to get all result records from the 4 tables and do the pagination on the client side. Ie.
Print pagination links as normal