Create list from mysql query

Question

I have a feeling this is really simple. Here's the deal: I have a table with three columns. I want to take all the values in one of the columns and turn that into a list. I want to do this so I can transverse through the list. Each value in the list corresponds to a username. I want to take that username to access info about a user. Using this info, I can check which faculty the user is in and sort accordingly. This is what I've come up with:

    function get_users_by_faculty($faculty) { 
    global $connection;
    $query = "SELECT * FROM owner";
    $user_set = mysql_query($query); // ERROR could not establish link to server
    confirm_query($user_set);  
    foreach($user_set as $user) { //ERROR invalid argument
        $userFaculty = get_info_by_id($user["ownerId"], "ou"); 
        if($faculty == $userFaculty){
            return $user["name"];
        } else {
            return NULL; 
        }
    }

I've been quite stuck on this for a few hours.

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — tereško
– tereško, Commented May 31, 2012 at 14:17
You should also read up about WHERE clauses. Your code is equivalent of going to a grocery store, buying up the entire store's inventory, driving home, then throwing it all out because you only wanted a chocolate bar. — Marc B
– Marc B, Commented May 31, 2012 at 14:18
+1 the question -- N Vidovic is making a fine effort there. Tereško, THANKS for your answer -- I think you should post it as an full answer. — Smandoli
– Smandoli, Commented May 31, 2012 at 14:22
@MarcB Well, not quite. It would be like going to the grocery store, buying all the chocolate bars, and throwing out only the ones that aren't made by Hershey. It's not a big database. And I don't know a WHERE statement that allows me to retrieve just one column. — nv39
– nv39, Commented May 31, 2012 at 14:30
How are you connecting to the database, it seems that is your first and biggest problem? — jeroen
– jeroen, Commented May 31, 2012 at 14:32

smaloron · Accepted Answer · 2012-05-31 14:36:46Z

2

I don't know your fields names, but I think you could do that with an sql query.

something like that :

SELECT user.id,user.name, faculty.name 
FROM user inner join faculty on faculty.id = user.faculty_id
WHERE faculty.id=?

You should replace ? with your faculty id.

If you want a list of user names, you can use group concat :

SELECT GROUP_CONCAT(user.name SEPARATOR ';') 
FROM user inner join faculty on faculty.id = user.faculty_id
WHERE faculty.id=?
GROUP BY faculty.id

edited May 31, 2012 at 14:36

answered May 31, 2012 at 14:30

smaloron

362 bronze badges

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

1 Comment

nv39 Over a year ago

Hey, that is a good suggestion but my problem is actually a bit more complicated. I only have the usernames in the database. I then want to insert each of these usernames in another function. The said function has access to a command-line program that stores the information (like the faculty name, for example) on that user

Collectives™ on Stack Overflow

Create list from mysql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related