displaying info from database using foreach- SOLVED

[Thunderbob]

I'm working on a subscription system that works similar to youtube

so far I am able to manualy subsribe to users by inserting data into the database.
Now I want to display the array of users that I am subscribed to on my profile page.

However, I am a bit confused on the foreach function and I'm running into parameter errors.
I've used foreach before on my page which is found here yourtechview.com/source/user_list.php
which shows all of the registered users on my site and each one is linked to their profile page.
I want to do the same thing but with the people I am subscribed to.

To make the user list I made a function

function fetch_users(){
        $result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` ");
               $users = array();
               while (($row = mysql_fetch_assoc($result)) !== false){
                $users[] = $row;
        }echo mysql_error();
               return $users;
}

Then I called the function on the user_list.php as follows

<?php
foreach (fetch_users() as $user){
?>
<p>
    <a href="profile.php?uid=<?php echo $user['id'];?>"><?php echo $user['username']; ?> </a>
</p>
<?php
}
?>

This works beautifully.

Now I want to connect to the same table fgusers3
but I only want to fetch the usernames and ids of the usernames that are = to $subscribedto
When I echo $subscribedto I only get 1 username without links of course which is what I want.

This is how $subscribed to is born

require_once 'dbconnect.php';
$sqlCommand = "SELECT `username` FROM fgusers3  WHERE id_user = '$subscribers'";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$rows = mysqli_fetch_assoc($query);
$subscribedto = $rows['username'];

I setup a function

function fetch_subscribedto()
{ $result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = {$subscribedto} ");
               $users = array();
               while (($row = mysql_fetch_assoc($result)) !== false){
                $users[] = $row;
        }echo mysql_error();
               return $users;
}

then I added the foreach within my profile page

<?php
foreach (fetch_subscribedto() as $user){
?>
<p>
    <a href="profile.php?uid=<?php echo $user['id'];?>"><?php echo $user['username']; ?> </a>
</p>
<?php
}
?>

I get this over 1 million times

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/content/66/9481266/html/source/subscribers.php on line 38

line 38 is

while (($row = mysql_fetch_assoc($result)) !== false){

common sense tells me it is what I am making `username equal` to

$result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = {$subscribedto} ");

Any clue?

[Temor]

That error means your SQL query is failing.
Adding

echo mysql_error();

under the query that's failing usually tells you what's wrong.

In this query it's because you forgot quotes.
This:

$result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = {$subscribedto} ");

Needs to look like this:

$result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = '{$subscribedto}' ");

You always need to put quotes around your variable if it contains a string, unlike when it contains an integer.

[Thunderbob]

cool. Now there are no errors.
However, it's not printing any of the data.

<?php
foreach (fetch_subscribedto() as $user){
?>
<p>
    <a href="profile.php?uid=<?php echo $user['id'];?>"><?php echo $user['username']; ?> </a>
</p>
<?php
}


?>

when I echo $subscribedto I get a result
somehow the query is not responding to it.

[Temor]

Well, $subscribedto is not set inside the function, so it will remain blank. I would suggest passing the variable to the function.

function fetch_subscribedto($subscribedto)
{ $result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = {$subscribedto} ");
               $users = array();
               while (($row = mysql_fetch_assoc($result)) !== false){
                $users[] = $row;
        }echo mysql_error();
               return $users;
}

<?php
foreach (fetch_subscribedto($subscribedto) as $user){
?>
<p>
    <a href="profile.php?uid=<?php echo $user['id'];?>"><?php echo $user['username']; ?> </a>
</p>
<?php
}
?>

[Thunderbob]

good progress

now I can see 1 our of the 2 users I am subscribed to.

I made somechanges

I made to separate tables

subscribers
contains id, myid, subtomeid, date
subscribedto
contains id, myid, subtoid, date

changed the queries

<?php 
session_start();
require_once 'dbconnect.php';
$sqlCommand = "SELECT id_user, username FROM fgusers3 WHERE username='" . $_SESSION['username'] . "'";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
while ($row = mysqli_fetch_array($query)) {
$pid = $row["id_user"];
$username = $row["username"];
}
mysqli_free_result($query);

// check for new messages
$sqlCommand = "SELECT COUNT(id) AS numbers FROM subscribers WHERE myid = '$user_id'";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$result = mysqli_fetch_assoc($query);
$sub_count= $result['numbers'];


// check for new messages
$sqlCommand = "SELECT COUNT(id) AS numbers FROM subscribedto WHERE myid = '$user_id'"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$result = mysqli_fetch_assoc($query);
$sub_to= $result['numbers'];

$sqlCommand = "SELECT `subtomeid` FROM subscribers  WHERE myid = '$user_id'";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$rows = mysqli_fetch_assoc($query);
$subscribers = $rows['subtomeid'];

$sqlCommand = "SELECT `subtoid` FROM subscribedto  WHERE myid = '$user_id'";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$rows = mysqli_fetch_array($query);
$subscribedtoid = $rows['subtoid'];

$sqlCommand = "SELECT `username` FROM fgusers3  WHERE id_user = '$subscribedtoid'";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$rows = mysqli_fetch_array($query);
$subscribedto = $rows['username'];

function fetch_subscribedto($subscribedto)
{ $result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = '{$subscribedto}' ");
               echo mysql_error();
               $users = array();
               while (($row = mysql_fetch_assoc($result)) !== false){
                $users[] = $row;
        }echo mysql_error();
               return $users;
}
?>

My subscriber count is successfully showing 3 which is correct based on the info inserted into db
My subscribe to count is successfully showing 2 which is correct based on the info inserted into db
All that is left is showing the list.

so close.

[Temor]

So, which query is the one fetching the list?

[Thunderbob]

function fetch_subscribedto($subscribedto)
{ $result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = {$subscribedto} ");
               $users = array();
               while (($row = mysql_fetch_assoc($result)) !== false){
                $users[] = $row;
        }echo mysql_error();
               return $users;
}

I think my problem is here

$sqlCommand = "SELECT `subtoid` FROM subscribedto  WHERE `myid` = '$user_id' ORDER by `id` DESC";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$rows = mysqli_fetch_array($query);
$subscribedtoid = $rows['subtoid'];

$sqlCommand = "SELECT `username` FROM `fgusers3`  WHERE `id_user` = '$subscribedtoid'";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$rows = mysqli_fetch_array($query);
$subscribedto = $rows['username'];

when I echo $rows['username'] it is only giving me 1 username when it should be 2.
should I be using array() ?

[Temor]

Since $rows is an array, you'll need to loop trough it.

foreach ($rows as $row){
echo $row['username'];
}

[Thunderbob]

got it working

did this

function fetch_subscribedto()
{ $users = array();

$result = mysql_query("SELECT `subtoid` AS `id` , `username` AS `username` FROM `subscribedto` WHERE `myid` = '{$_SESSION['uid']}' ");
                while (($row = mysql_fetch_assoc($result)) !== false){
                $users[] = $row;
}
return $users;
}
        

and

<?php
require_once"subscribers.php";

$users = fetch_subscribedto();
if (count($users) ==0)
{ echo 'Your are not subscribed to anyone at this time.';
}
else {
   foreach($users as $user){ ?>
   <a href="profile.php?uid=<?php echo $user['id']?>"><?php echo $user['username'] ?>
   <INPUT TYPE="submit" height="30px" width="100px" id="unsubscribe" position="absolute" 
   name="unsubscribe" value="Unsubscribe" style="background:#980000; color:white;margin-left:600px; 
    position:absolute; padding-left:10px; padding-right:10px; border-radius: 10px; font-family:calibri; font-weight:bold;"><br><br>
<?php } 
 }  ?>

Now I just have to put this in a table and I'll be good.
Thanks for your time Temor