Mysqli select multiple queries

Question

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);

            ?>

you could probably just join those query instead, much simpler that way, is user_id set? — Kevin
– Kevin, Commented Sep 11, 2014 at 23:57

Deep · Accepted Answer · 2014-09-12 00:10:56Z

1

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 = ?

answered Sep 12, 2014 at 0:10

Deep

2,5442 gold badges17 silver badges27 bronze badges

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

Comments

Kevin · Accepted Answer · 2014-09-12 01:41:29Z

0

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>';
}

edited Sep 12, 2014 at 1:41

answered Sep 12, 2014 at 0:14

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

7 Comments

Deep Over a year ago

You SQL broken, be cause has return all categories and(for) some nulled question.category_id

Kevin Over a year ago

@user3272438 use the function: <?php get_classes($mysqli); ?>

Kevin Over a year ago

@user3272438 and always turn on error reporting so that you'll know whats going on. error_reporting(E_ALL); ini_set('display_errors', '1'); on top of php file

nviens Over a year ago

Ahhh, I set error reporting on and I got the following Fatal error: Call to undefined method mysqli_stmt::get_result()

Kevin Over a year ago

@user3272438 try my revision, get_result is only available on the native mysql driver mysqlnd

|

Collectives™ on Stack Overflow

Mysqli select multiple queries

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related