select from mysql table using array

Question

i am trying display a the rows in my database table using the array of ids gotten from another table. i want it to display the first row which is $rowsfriend. and display the second row which is rows .......... but it only displays $rowsfriend

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ochat";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM friends where friend1='".($_POST[id])."'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

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

     $rowsfriend = $row["friend2"];
         echo $rows;
         }
     }
$sqll = "SELECT * FROM users WHERE id IN ($rowssfriend)";
$resultt = $conn->query($sqll);
if ($resultt->num_rows > 0) {

     while($roww = $resultt->fetch_assoc()) {

     $rowsss = $row["username"];
         echo $rowss;
         }
     }
 else {
?>
    <h1>Register</h1> 
    <form action="selectfriends.php" method="post"> 
        id:<br /> 
        <input type="text" name="id" value="" /> 
        <br /><br /> 

        <input type="submit" value="enter" /> 
    </form>
    <?php
}

?>

What output you expect from echo $rows; and echo $rowss; -when there are no such PHP variables? — Ataboy Josef
– Ataboy Josef, Commented Jan 10, 2015 at 7:06

Manish Pant · Accepted Answer · 2015-01-10 07:05:42Z

1

Instead of two queries, you can write this nested query.

$sqll = "SELECT * FROM USERS WHERE ID IN (SELECT friend2 FROM friends WHERE friend1='".$_POST[$id]."')";
$resultt = $conn->query($sqll);
if ($resultt->num_rows > 0)
 {
   while($roww = $resultt->fetch_assoc()) 
     {
       $rowsss = $row["username"];
       echo $rowss;
     }
  }

Hope this solve your problem .

answered Jan 10, 2015 at 7:05

Manish Pant

111 silver badge9 bronze badges

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

Comments

score 1 · Accepted Answer · 2015-01-10 07:11:18Z

Please try this version of code instead, if you might:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ochat";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT users.* FROM users WHERE users.id IN (SELECT friend2 FROM friends where friend1='".($_POST[id])."')";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $rows = $row["username"];
        echo $rows;
    }
}
else {
?>
    <h1>Register</h1> 
    <form action="selectfriends.php" method="post"> 
        id:<br /> 
        <input type="text" name="id" value="" /> 
        <br /><br /> 

        <input type="submit" value="enter" /> 
    </form>
    <?php
}
?>

As far as I understood the code well, the problem was with the variable name typo as @Admieus wrote but also in the fact that in each iteration of the first loop variable $rowsfriend got overriden with a new value so after the end of the loop, $rowsfriend contained the last id from the result of the first query $sql. The above version makes only one query using subquery in it to get directly usernames who are friends of friend1 given in $_POST[$id].

I hope it helps.

Allmighty · Accepted Answer · 2015-01-10 07:04:05Z

There are typos in the second loop.

You assign the value to $rowsss and try to echo from $rowss notice the difference. Also, you assign the fetch_assoc result to $roww and then try to call it again with $row.

$sqll = "SELECT * FROM users WHERE id IN ($rowsfriend)";
$resultt = $conn->query($sqll);
if ($resultt->num_rows > 0) {

     while($roww = $resultt->fetch_assoc()) {

         $rowsss = $roww["username"];
         echo $rowsss;
         }
     }

Point of improvement is: check your variable names, make names that are easy to understand and hard to mix up. For instance, the variable containing the sql query should not be named $sql and to make it worse a second query shoul not be named sqll. Instead use names that imply what you are doing.

$querySelectFriendsFrom = "SELECT * FROM users WHERE id IN ($friendId)";

Don't take this as a hard rule, it's more of a tip to prevent silly mistakes.

Update: there was also a type in the query referring to rowssfriend instead of rowsfriend. Fixed above.

Collectives™ on Stack Overflow

select from mysql table using array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related