1

hi i'm stuck with this code, check this out :

my table sql :

video table
id_video id_kategori content1 content2 ....
2             5                 ...
3             6                 ...

artikel table
id_artikel id_kategori content1 content2 ...
3             5                  ...
5             6                  ...

kategori table
id_kategori nama_kategori
4                 berita
5                 videophp
6                 videohtml

the source code : 

$query = mysqli_query(DB::connect(),"SELECT * FROM `kategori`");  
while($row = mysqli_fetch_row($query)){  
?>
<ul>
<?php
    $jml = mysqli_fetch_row(mysqli_query(DB::connect(),"SELECT (SELECT COUNT(\*) FROM `artikel` WHERE `id_kategori`='$row[0]') as first,(SELECT COUNT(\*) FROM `video` WHERE `id_kategori`='$row[0]') as second FROM dual"))[0];
?>
    <li><a href='<?php echo substr($row[1],0,5) == "video" ? "video" : "artikel"; ?>.php?kategori="<?php echo $row[0]; ?>"'><?php echo $row[1].'('.$jml.')'; ?></a></li>
</ul>
<?php
}
?>

it returns :
olahraga(17)
Teknologi(2)
Politik(6)
Berita(2)
video php(0)
video html(0)

everything good except from video table

simple count for just 1 table works just fine
SELECT () FROM video WHERE id_kategori = $row[0]
SELECT COUNT() FROM artikel WHERE id_kategori = $row[0]

so what's the code wrong from mine, anyone knows? please and thanks

faddi
  • 71
  • 1
  • 10
  • Try to remove [0] at last of this line "$jml = mysqli_fetch_row(mysqli_query(DB::connect(),"SELECT (SELECT COUNT(\*) FROM `artikel` WHERE `id_kategori`='$row[0]') as first,(SELECT COUNT(\*) FROM `video` WHERE `id_kategori`='$row[0]') as second FROM dual"))[0];" and when you want to access total value of video use [1] – Akkapong Kajornwongwattana Jan 31 '17 at 02:54
  • hi thanks for help. [1] returns only table of video but not the articles. i still don't get it whats wrong with the code i've search another question deeply from this forum http://stackoverflow.com/questions/1279569/sql-combine-select-count-from-multiple-tables and it works SELECT (SELECT COUNT(*) FROM `artikel` WHERE `id_kategori`='$row[0]') + (SELECT COUNT(*) FROM `video` WHERE `id_kategori`='$row[0]')"))[0] – faddi Jan 31 '17 at 03:12
  • @faddi, so this is working for you now? – bio_sprite Jan 31 '17 at 03:42
  • you want to sum total of video and article or you want to separate that – Akkapong Kajornwongwattana Jan 31 '17 at 03:56

0 Answers0