0

My problem is:

I am joining tables together, and some tables have multiple data which I should fetch, but my JSON object only retrieves one. I think the reason for this is because the while loop loops through my main table aswell, fetches everything and stops looping, considering there is nothing left to loop through (I fetch my data based on my id in my database and these are auto_incremented etc).

So my question would be:

How can I loop through my main table (once), but keep on looping through the joined tables and insert these values in their 'own' array?

this is what I have right now:

$sqli = "SELECT event.*, typex.id, typex.type 
        FROM event 
            JOIN typex ON event.id = typex.id 
        WHERE event.id = ?";

mysqli_stmt_bind_param($stmt, 's', $editID); //fetch data based on ID
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$resultCheck = mysqli_num_rows($result);

if ($resultCheck > 0) {
    $data = array();

    while ($row = mysqli_fetch_assoc($result)) {
        $data['all'] = array($row); 
        $data['type'] = array($row['type']);  
    }
    //Echo data as JSON
    echo json_encode($data);

So to give you an idea:

$data['all'] is the array which will hold all $row (this is so I can easily access columns from my main table)

$data['type'] however should only hold data from the table: typex (typex is a joined table).

an example:

You have a main table called: stack and a table you joined called: typex.

You want to insert all the rows with id number 5 from the table stack in the array $data['all'], considering the main table has an auto_increment on the id and unique, it will loop once... but now comes the part I fail at and need your help: In your joined table: typex you have 2 rows with id number 5. these 2 rows should be fetched and inserted in the array $data['type']. How could this be achieved?

Improve this question
5
  • I would think you'd need to get the data from typex and join on event so it goes through all of the typex results. Or do two separate queries, one for event, and one for all of the typex events. Commented Mar 20, 2019 at 13:20
  • and how would you fetch that via ajax? That would mean I have to make multiple ajax calls. Commented Mar 20, 2019 at 13:21
  • No, you can do it in one call, just add the multiple queries to the code above. Commented Mar 20, 2019 at 13:23
  • I think you might just need to PREPARE that query before attempting to bind values and execute it Commented Mar 20, 2019 at 13:30
  • @RiggsFolly with all due respect, but I am not that retarded, I took that part out considering it would be pretty useless to show it to you guys. since it had no value whatsoever. I understand the misunderstanding tho.\ Commented Mar 20, 2019 at 13:33

2 Answers 2

Reset to default
2 
$data['all'] = array($row); 
$data['type'] = array($row['type']);

This will always set the last $row's result, if you need all of the rows, you will need to do it like this:

$data[] = array('all'=>$row,'type'=>$row['type']);

Now each item of $data is an array with 2 keys, all and type

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

but do I still need the variable $data = array(); outside the while loop? or can I take that part away?
keep it out of the loop, if you bring it inside, you will again get just the last row since it will be re-initialized.
it works! but one last question tho: How would you do this if you had for example: $data[] = array('all'=>$row,'type'=>$row['type'], 'problem'=>$row['problem']); in which 'type' has 2 rows to fetch and problem has 3 rows to fetch? how can I check the length of type (which would be 2) and the length of problem (which would be 3?)
each key will have array as value so you get the value using key and then can get count like this count($data[0]['type']) count of type array which is on first index\
yes, correct, but how would I check for the length of the array? for example: What if I wanted to say for (var i = 0; i < $data[?]['type'].length)?
0

A few things to change here:

First in the query, as both the event and typex tables have an id column you will need an alias on one of them so you can distinguish between them, so I did that on typex.event also just in case.

$sqli = "SELECT event.*, typex.id as typex_id, typex.type as typex_type
        FROM event 
            JOIN typex ON event.id = typex.id 
        WHERE event.id = ?";

Here I have amended the code to use the OO mysqli as its a whole lot easier to read

$stmt->bind_param('s', $editID); 
$stmt->execute();

$result = $stmt->get_result();
$resultCheck = mysqli_num_rows($result);

if ($resultCheck > 0) {
    $data = array();
    $last_event = 0;

    while ($row = $result->fetch_assoc()) {

        if ( $last_event != $row['id'] ) {
            $data['all'] = array($row); 
            $last_event = $row['id'];
        }

        $data['type'] = array('type' => $row['typex_type'], 'id' => $row['typex_id'] );
    }
    //Echo data as JSON
    echo json_encode($data);
}
Improve this answer

3 Comments

and this will fetch all the rows from table typex, and all the rows ONCE from the main table? if so, what did I do differently?
Ahh one minute, I will amend it
There you go. Give that a try

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.