Trying to create a function using mysql_fetch_array

Question

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");

Stop using `mysql_*` : http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — w3spi, May 29 '15 at 18:33
Ok i won`t but do you have any suggestions? How can i achieve my goal. — orko, May 29 '15 at 18:39
**WARNING**: If you're just learning PHP, please, do not learn the obsolete `mysql_query` interface. It's awful and is being removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. Always be absolutely **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will have severe [SQL injection bugs](http://bobby-tables.com/). — tadman, May 29 '15 at 18:40
Since you're using an exceptionally quirky database interface, I've almost never seen `mysql_db_query` used before, you probably want to be sure your user passwords are [properly hashed](http://www.phptherightway.com/#password_hashing). Read up on [PHP the Right Way](http://www.phptherightway.com/) to be sure you're doing things correctly. — tadman, May 29 '15 at 18:42
@tadman I am using mysqli, would you suggest the same thing? — Zapp, May 29 '15 at 18:42
@Zapp PDO is preferable to `mysqli`, and a [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) like [Laravel](http://laravel.com/) is preferable to that. PDO has named placeholders which is extremely handy. — tadman, May 29 '15 at 18:43
@tadman thanks for sharing, i have glanced the link that you have sent and i saw my errors on creating db and db connections. I added the link to my favs and i will spent a lot of time with it. I am going to change all db connections. I am a beginner and correct orientation is so important for me. So thank again. — orko, May 29 '15 at 18:52

score 1 · Accepted Answer · answered May 29 '15 at 18:55

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.

I am going o get "Some text" field also from db records that is related with user id. And i want it to make it for each user record. I have not tried this yet but i will tomorrow and let you know. Thanks for your help @dont-panic — orko, Jun 05 '15 at 19:54
This solved my problem now i need to improve it in order to make more secure and contemporary. Thank you — orko, Jun 15 '15 at 09:35

Trying to create a function using mysql_fetch_array

1 Answers1