Multiple SQL Queries Into Multiple Arrays

Question

I have this page thats a quick over view of the logged on users profile, along with some editing functions, so to avoid confusion I'm going to post all of the code, just ignore the html.

In the start of the code I'm retrieving the users information, and then storing it into an array called $row. Later on down the code, I'm retrieving rows from a seperate table, and I want to store those into an array aswell ($postRow). But I just figured out through reading that you can only preform one SQL query per page without running code that I, quite frankily don't understand, and the only tidbits of code that I found regarding that issue printed the information directly onto the page, I couldn't find any on how to store them both into arrays. Can someone help me regarding this issue? I'm really between a rock and a hard place.

<?php
    include('header.php');
    if (isset($_SESSION['username'])){
        require 'connect.php';

        $user = $_SESSION['username'];

        $query = mysqli_query($connect, "SELECT * FROM users WHERE username='$user';", MYSQLI_USE_RESULT);
        $row = mysqli_fetch_assoc($query);
        ?>
        <h4>BlogHub > Profile > <?php echo $row['fname']." ".$row['lname']; ?></h4>
        <div id="profileAva">
            <img src="<?php echo $row['avatar']; ?>" />
        </div>
        <h5 id="infoDisp" style="margin:3px;"><?php echo "ID #".$row['ID']." - ".$row['fname']." ".$row['lname']." - ".$row['username']." - ".$row['email']." - ".$row['posts']." Posts"; ?></h5>
        <?php
        $id = $row['ID'];
        $recentPost = mysqli_query($connect, "
        SELECT * 
        FROM  `blog_posts` 
        WHERE poster_id='$id'
        LIMIT 1
        ", MYSQLI_USE_RESULT);
        $postRow = mysqli_fetch_assoc($recentPost);
        ?>
        <p></p>
        <div id="changeAva">
            <button class="cancelQuery">x</button>
            <center>
            <form style="padding-top:20px;" class="boxI" enctype="multipart/form-data" action="changeAva.php" method="POST">
                <input style="padding-bottom:15px;" type="file" value="Choose a Avatar" name="file"/><br />
                <button type="Submit">Submit</button>
            </form>
            </center>
        </div>
        <div id="changeInfoBox">
            <button class="cancelQuery">x</button>
            <center>
                <form action="changeInfo.php" method="POST">
                    First Name: <input type="text" name="fname" value="<?php echo $row['fname']; ?>" /><br />
                    Last Name: <input type="text" name="lname" value="<?php echo $row['lname']; ?>" /><br />
                    Email: <input type="text" name="email" value="<?php echo $row['email']; ?>" /><br />
                    <button type="submit">Submit</button>
                </form>
            </center>
        </div>
        <?php
    }
    else {
        echo "<center><p>You need to be logged in to view this page.</p></center>";
    }
    include('footer.php');
?>

Mike Brant · Accepted Answer · 2013-08-26T22:21:41.957

0

There is no restriction on number of queries executed during a script execution. There is however no reason that you couldn't get this information in a single query. You would use a JOIN to do this.

This might look like this:

SELECT bp.*
FROM users AS u
INNER JOIN blog_posts AS bp
  ON u.id = bp.poster_id
WHERE u.username = '?'

The particular problem you mention in comment to other answer is because you need to call mysql_free_result() before making your next query.

edited Aug 26 '13 at 22:21

answered Aug 26 '13 at 22:16

Mike Brant

70,514
10
99
103

But if I only want the most recent post from that user (1 post) do I still need to use INNER JOIN? – George Coleman Aug 26 '13 at 22:18
Oh shit your MYSQL_USE_RESULT was keeping me back, thanks Mike. Appreciate it. – George Coleman Aug 26 '13 at 22:20
@GeorgeColeman If your input is username (on user table) but the data you are looking for in in `blog_posts` then you would need to do a join if you want to get this data with a single query, rather than doing it in two queries like you are currently doing. – Mike Brant Aug 26 '13 at 22:23

Multiple SQL Queries Into Multiple Arrays

1 Answers1