CODEWITHSUNDEEP
phpmysql
orko
orko

Reputation: 127

Trying to create a function using mysql_fetch_array

My aim to send pushbullet notifications to users by using cron.

I have a mysql db and there are users table and issues table in it. I want to get all users from the users table after that create another query that bring issue count based on assigned user id from issues table. Finally make notifications.

Here are details and what i tried. Firstly; i tried to get user ids and it worked as expected.

   function some_function($sa){
   foreach($sa as $s)
      echo $s;

}

$sqlText['id']="SELECT users.id, 
                users.login
                FROM users
                GROUP BY users.login";

    $sqlQuery_MainMenu = mysql_db_query($db, $sqlText['id'], $connection) or die("Error");
    $file_names=array();
    while($mm_Content=mysql_fetch_array($sqlQuery_MainMenu)){
        $users[]=$mm_Content["id"];
    }   
    foreach ($users as $user) {
        $foo=array($user);
        some_function($foo);
    }

Since i have user ids i tried to create new query that brings issue count based on user id. But i could not do it. I know what i want to do but i do not know how to do it.

$sqlText['push']="SELECT COUNT(*)
FROM issues INNER JOIN users ON issues.assigned_to_id = users.id where assigned_to_id = $s";

    $sqlQuery_MainMenu = mysql_db_query($db, $sqlText['push'], $baglanti) or die("Error");
    $users=array();
    while($mm_Content=mysql_fetch_array($sqlQuery_MainMenu)){
        $users[]=$mm_Content;
    }   
    foreach($users as $index => $user){
        //$attachment .= $directory.'/'.$files[$index].', '."'$file_names[$index]'";
        echo $user[0] ;
         }

Basically what i am trying to do is create a function that runs following line for each value that came from mysql.

$pb->Device('user.id from db')->pushLink("Some text", "http://some link", "issue count");

Upvotes: 0

Views: 92

Answers (1)

Don't Panic
Don't Panic

Reputation: 41820

What I would suggest is that you probably don't need to do your initial query. I assume it would be the case that you would not need to send notifications to users with no issues, so you can get user IDs directly from your issue table without querying the user table first.

$sql = "SELECT assigned_to_id AS user_id, COUNT(*) AS issue_count
    FROM issues GROUP BY assigned_to_id";

(If you need to get additional information from the user table, you can still get it with one query by joining the two tables together.)

After you execute your query, you should be able to do whatever your notification method does as you fetch records from the results.

$users_with_issues = mysqli_query($db, $sql);
while($user = mysqli_fetch_assoc($users_with_issues)){
    $pb->Device($user['user_id'])->pushLink(
        "Some text", "http://some link", $user['issue_count']);
}

I used mysqli in this example, but the same general approach should work regardless of which database extension you are using.

Upvotes: 1

Related Questions