How to reiterate through an if statement after increasing a variable using PHP?

Question

I am relatively new to PHP and MYSQL and have been trying to write a program that will connect to a database and GET information from it to display on a webpage.

So far I have 2 tables: orderID table and reviews table.

In the orderID table the id column is my primary key and is set to AUTO-INCREMENT, whilst the oid is set as a foreign key in the reviews table.

I want to be able to display the oid, and then also the name and comments for each oid, in JSON format on my webpage, like so:

[
  {
    "objectID": "ABC1234X",
    "comments": [
        {
            "name": "Mary",
            "comment": "This is a comment"
        },
        {
            "name": "Greg",
            "comment": "Another comment"
        }
    ]
  },
  {
    "objectID": "DEF1234X",
    "comments": [
        {
            "name: "Greg",
            "comment: "A comment"
        },
        {
            "name": "Ben",
            "comment": "Oh look, a comment"
        }
    ]
  }
]

So far I have been able to do this for only the oid with a value of ABC1234X (corresponding to id value 1, in the orderID table), using the following code (my username, password, and dbname are all different in my actual code.):

<?php
    
$dbHostname = "localhost";
$username = "XXXXXXXXXX";
$dbPassword = "XXXXXXXXXX";
$dbName = "XXXXXXXXXXXXXXXXX";

$conn = new mysqli($dbHostname, $username, $dbPassword, $dbName);

if($conn->connect_errno > 0) {
    die('Unable to connect to database [' . $conn->connect_error . ']');
}

$oidList_array = array();
$objectID_array = array();
$review_array = array();

$id = 1;

$objectQuery = "SELECT * FROM orderID;";
$reviewQuery = "SELECT * FROM reviews WHERE oid IN (SELECT oid FROM orderID WHERE id=$id);";

$oidResult = mysqli_query($conn, $objectQuery);
$reviewResult = mysqli_query($conn, $reviewQuery);

$resultCheck = mysqli_num_rows($oidResult);

    if($resultCheck < 0) {
        die('There was an error running the query [' . mysqli_error($conn) . ']');
    } 
    else if ($resultCheck > 0) {
        while ($row_oid = mysqli_fetch_assoc($oidResult)) {
            $objectID_array['objectID'] = $row_oid['oid'];
            $objectID_array['comments'] = array();

        while ($row_review = mysqli_fetch_assoc($reviewResult)) {
            $review_array['name'] = $row_review['name'];
            $review_array['comment'] = $row_review['comment'];
            array_push($objectID_array['comments'], $review_array);
        }


            array_push($oidList_array, $objectID_array);
        }

        header('Content-Type: application/json');

        $jsonData = json_encode($oidList_array, JSON_PRETTY_PRINT);

        echo "<pre>" . $jsonData . "<pre>";

    }

This currently gives me this JSON output:

[
  {
    "objectID": "ABC1234X",
    "comments": [
        {
            "name": "Mary",
            "comment": "This is a comment"
        },
        {
            "name": "Greg",
            "comment": "Another comment"
        }
    ]
  },
  {
    "objectID": "DEF1234X",
    "comments": []
  }
]

How do I go about incrementing the $id value and then rerunning the if and while statements, so that the name and comment appears for "objectID": "DEF1234X"?

Is it as simple as just adding $id++ somewhere into the code? If so where would that go?

I thought about adding a for loop around the if and while statements, but was unsure if that would work and also what the conditions would be. If possible I would like to leave room to add more data in the future, so something like for ($id = 1; $id <= 2; $id++) {} wouldn't be viable (if it would even work in the first place).

Thank you in advance for any help that can be given.

Suggest your first step is to look up the JOIN syntax in SQL [See tutorial](https://www.mysqltutorial.org/mysql-join/) Then you can get the results in one query instead of 2. That in itself will simplfy all the other code — RiggsFolly, Aug 18 '20 at 16:17
I got it to work. Thank you so much! I was looking at it as a PHP problem, I hadn't even considered it would be an SQL solution. — G.Brown, Aug 18 '20 at 16:38
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Aug 18 '20 at 17:07

score 0 · Answer 1 · answered Aug 18 '20 at 16:27

0

You should use bind_param() function.

$reviewQuery = "SELECT * FROM reviews WHERE oid IN (SELECT oid FROM orderID WHERE id=?);";
$reviewQuery->bind_param('i', $id);

answered Aug 18 '20 at 16:27

Ali

15
1
8

And I think you should change that oid in the subquery to just id – Ali Aug 18 '20 at 16:31
If you want to iterate you should use an array to save the values with a loop. – Ali Aug 18 '20 at 16:38

How to reiterate through an if statement after increasing a variable using PHP?

1 Answers1