CODEWITHSUNDEEP
phpmysqlmysqli
nviens
nviens

Reputation: 447

Mysqli select multiple queries

I'm trying to select one query by using the user_id to get the categories specific to the user and then from there use the results from the first query to select data from a second table in a second query. The data from the second query I would like to display in a drop down menu. Is there any easy way to do this? I'm new to using mysqli so please bear with me.

I have the following function but it's not displaying anything in the dropdown menu, it is showing the drop down menu though.

function get_classes($mysqli) {
     if(isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) {
    $user_id = $_SESSION['user_id'];
    if ($stmt = $mysqli->prepare("SELECT category_id 
                            FROM questions WHERE user_id = ?")) {
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        $stmt->store_result();

        if($stmt->num_rows>0) {
            $stmt->bind_result($category_id);
            $stmt->fetch();
            if($result = $mysqli->prepare("SELECT category_id, category_name
                            FROM category WHERE category_id = ?")) {
                $result->bind_param('s', $category_id);
                $result->execute();
                $result->store_result();
            }
        }
        echo "<select id='classes' name='classes'>";
        while ($row = $result->fetch_assoc()) {
            unset($user_id, $category_name);
            $category_id = $row['category_id'];
            $category_name = $row['category_name'];
            echo '<option value="'.$category_id.'">'.$category_name.'</option>';
        }
        echo "</select>";
    }

}
}

I'm calling the function from another page with the following code:

<?php
            get_classes($mysqli);

            ?>

Upvotes: 1

Views: 955

Answers (2)

Kevin
Kevin

Reputation: 41885

Alternatively, you could join them instead, then the normal fetching. Example:

function get_classes($mysqli) {
    if(!isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) {
        return false;
    }

    $user_id = $_SESSION['user_id'];

    $sql = '
        SELECT
        category.category_id,
        category.category_name

        FROM category
        JOIN questions
        ON questions.category_id = category.category_id

        WHERE questions.user_id = ?
    ';
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $user_id);
    $stmt->execute();
    $result = $stmt->bind_result($category_id, $category_name);

    // printing
    echo '<select name="classes" name="classes">';
    while($row = $stmt->fetch()) {
        echo '<option value="'.$category_id.'">'.$category_name.'</option>';
    }
    echo '</select>';
}

Upvotes: 0

Deep
Deep

Reputation: 2512

You can make all via one sql query:

SELECT c.category_id, c.category_name
  FROM questions q
  INNER JOIN category c ON c.category_id = q.category_id
  WHERE q.user_id = ?

Upvotes: 1

Related Questions