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

[syntax=php]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;
}[/syntax]

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

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

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

[syntax=php]
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'];[/syntax]

I setup a function

[syntax=php]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;
}[/syntax]

then I added the foreach within my profile page

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

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 [syntax=php]while (($row = mysql_fetch_assoc($result)) !== false){[/syntax]

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

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

Any clue?

[Temor]

That error means your SQL query is failing.
Adding [syntax=php]echo mysql_error();[/syntax]
under the query that's failing usually tells you what's wrong.

In this query it's because you forgot quotes.
This:
[syntax=php]$result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = {$subscribedto} ");[/syntax]
Needs to look like this:
[syntax=php]$result = mysql_query("SELECT `id_user` AS `id`, `username` AS `username` FROM `fgusers3` WHERE `username` = '{$subscribedto}' ");[/syntax]


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.

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


?>[/syntax]

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.

[syntax=php]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;
}[/syntax]

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

[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 [syntax=php]<?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;
}
?>[/syntax]

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]

[syntax=php]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;
}[/syntax]

I think my problem is here

[syntax=php]$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'];[/syntax]

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.
[syntax=php]foreach ($rows as $row){
echo $row['username'];
}[/syntax]

[Thunderbob]

got it working

did this

[syntax=php]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;
}
[/syntax]

and

[syntax=php]<?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 }
} ?>[/syntax]

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