How to output all data from multiple tables using mysqli php

Question

Most of the variables will be in my language sorry about that. Also keep in mind i have very little knowledge of php/mysql as even my schools teachers have no idea what is it.

In my database there are 4 tables. The tables only have primary keys. The tables do not have any foreign key or references to other tables. This is because in my school project we were told to not add foreign key and references. This means I cant/dont know how to use JOIN. Currently i am attempting to output all of the data from the 4 tables and output them as 1 using html/css.

My main problem is i do not know how to output data from different rows.

I have tried using mysqli_data_seek to select each row with my counter($no) but it seems that either im using it wrong or its just not supposed to do that. I have also tried using mysqli_fetch_assoc but i still get the same output.

I have a connection to my database

This is the code mysqli_data_seek

<?php

    $row = mysqli_query($connection, "SELECT Kod_Barang, Kod_Rak, Harga, Tarikh_Jual from stok");
    $no = 0;

    if (mysqli_num_rows($row) > 0) {
    while ($stok = mysqli_fetch_assoc($row)) {

        $stok = mysqli_query($connection, "SELECT Kod_Barang, Kod_Rak, Harga, Tarikh_Jual from stok");
        $datastok = mysqli_data_seek($stok, $no);

        //get data from "barang' table
        $barang = mysqli_query($connection, "SELECT Nama_Barang, no_Barang from barang");
        $databarang = mysqli_data_seek($barang, $no);

        //get data from "lokasi"
        $lokasi = mysqli_query($connection, "SELECT Jenis_Barang from lokasi");
        $datalokasi = mysqli_data_seek($lokasi, $no);

        //get data from "tarikh" table
        $tarikh = mysqli_query($connection, "SELECT Tarikh_Beli from tarikh");
        $datatarikh = mysqli_data_seek($tarikh, $no);

        $no++;

then after this i will have a output which is connected to a table already made (this is still in the while loop)

    echo '<tr>';
    echo '<td>'.$no.' </td>';
    echo '<td>'.$datastok.'</td>';
    echo '<td>'.$databarang.'</td>';
    echo '<td>'.$datalokasi.'</td>';
    echo '<td>'.$datastok.'</td>';
    echo '<tr>';

This is the code with mysqli_fetch_assoc

<?php

    $rowstok = mysqli_query($connection, "SELECT Kod_Barang, Kod_Rak, Harga, Tarikh_Jual from stok");
    $no = 0;

    if (mysqli_num_rows($rowstok)> 0) {
        while ($stok = mysqli_fetch_assoc($rowstok)) {


            $stok = mysqli_query($connection, "SELECT Kod_Barang, Kod_Rak, Harga, Tarikh_Jual from stok");
            $datastok = mysqli_fetch_assoc($stok);

            //get data from "barang" table
            $barang = mysqli_query($connection, "SELECT Nama_Barang, Bil_Barang from barang");
            $databarang = mysqli_fetch_assoc($barang);

            //get data from "lokasi" table
            $lokasi = mysqli_query($connection, "SELECT Jenis_Barang from lokasi");
            $datalokasi = mysqli_fetch_assoc($lokasi);

            //get data from "tarikh" table
            $tarikh = mysqli_query($connection, "SELECT Tarikh_Beli from tarikh");
            $datatarikh = mysqli_fetch_assoc($tarikh);

            $no++;

Again here is my output table

    //output table
    echo '<tr>';
    echo '<td>'.$no.' </td>';
    echo '<td>'.$datastok['Kod_Barang'].'</td>';
    echo '<td>'.$databarang['Nama_Barang'].'</td>';
    echo '<td>'.$datalokasi['Jenis_Barang'].'</td>';
    echo '<td>'.$datastok['Harga'].'</td>';
    echo '<tr>';

Right now the output is just showing the first's rows data multiple times based on number of row. And also my counter($no) in increasing by 2 (1,3,5,7,....) not as expected 1 I am trying to get it to be each rows data.

you can still use JOIN without an explicit foreign key, as long as there are fields in each table whose values will match. What you're trying to do now is very inefficient. I have no idea why the school told you not to create foreign keys, that's pretty backwards. Maybe, as you say, they don't know what they're doing. — ADyson, Mar 28 '19 at 11:29
@ADyson from my attempts to use join i cant seem fit all my data as it says that there is not the same number of columns. could you please give me a example of this? — MichaelSWS, Mar 28 '19 at 11:39
first please a) show me your query, b) show the exact error message. I don't know exactly how you should write your join because I don't know which fields you want to join using. I can't see your table design, don't forget. — ADyson, Mar 28 '19 at 11:41
@ADyson my apologies but thanks to you i decided to retry "JOIN" with 2 tables and it worked but how does one join multiple tables? i am not sure on how to show u my database but if you dont mind u can use any database as an example — MichaelSWS, Mar 28 '19 at 11:53

score 1 · Answer 1 · answered Mar 28 '19 at 12:29

I got the answer thanks to @ADyson. I just changed my long line of code to JOIN

    $data = mysqli_query($connection , "SELECT *
FROM stok
INNER JOIN barang
      ON stok.Kod_Barang=barang.Kod_Barang
INNER JOIN lokasi
      ON stok.Kod_Rak=lokasi.Kod_Rak
INNER JOIN tarikh
      ON barang.Nama_Barang=tarikh.Nama_Barang");

again sorry for non English attributes.

here is where i learnt JOIN from another question SQL Inner join more than two tables

How to output all data from multiple tables using mysqli php

1 Answers1