Multiple Queries Help

[SteveCatbert]

Hi All,

 

I am designing a web page that needs to call up a database for multiple queries. The problem is that the current way I am running the script is that it requires me to run approximately 100+ queries on a single page and want to try to reduce this substantially, if not entirely. 

 

I have a database that holds four tables. One table is a list of ranks that can be assigned to a user. There are approximately 100-150 of these ranks. The second table consists of a list of groups that users can join. The third table is the user table. The user table consists of a field that contains the rank_id associated with the user and only one rank can be assigned to a user at a time. The fourth and final table is a joining table. This table connects the user table and the groups table together. 

 

The page I am designing is supposed to call up each rank, in a specific order, the order not correlating in any alphabetical or numerical sequence, and list all associated users with that rank. It then moves onto the next rank and repeats the query over and over until all ranks are used up. Currently I am manually creating the code to call up each rank and display all members with said rank.

 

Is there a way to run one query or fewer queries for this task? An example of what I am doing is below:

 

*Search the joining table for all users associated with group number 34*

$q_user_rank_search = mysql_query("select * from sv1user_group where group_id='34'") or die(mysql_error()); 
while($r_user_rank_search = mysql_fetch_assoc($q_user_rank_search))

 

*Search the users table for all users that are part of the above group AND have the rank with ID number 34*

$q_user_search = mysql_query("select username,user_id,user_rank from sv1users where user_id='$g_user_id' AND user_rank='34'") or die(mysql_error()); 
while($r_user_search = mysql_fetch_assoc($q_user_search)) 
{
echo $r_user_search['username']."<br>";


}

 

The second set of code above is repeated for each rank and this is where I would like to reduce the load on the database and limit the queries if possible.

Edited January 5, 2016 by SteveCatbert

[benanamen]

You need to learn about JOINS. You are also using deprecated code that will not work at all in the latest version of Php. You need to use PDO with prepared statements.

 

I believe your database design may be wrong as well. Post an SQL dump of your database.

Edited January 5, 2016 by benanamen

[SteveCatbert]

Unfortunately I have no control of the database design nor the PHP version. The version of the PHP on this server is 5.4.36. 

[Barand]

If you want help we still need to know the structure of your db tables.

 

The deprecated code you are using is the mysql_ library. You need to change to mysqli_ or PDO functions, both of which should be available in your version of PHP

[SteveCatbert]

Unfortunately I have not been able to get any mysqli_ commands to work with the current server version of php, which is why I am using mysql_.

 

For the sake of ease, I am only including,the necessary fields as there are many more that are not related to this issue.

 

'Join' table', user_group

group_id, user_id

 

Users Table, users

user_id, username, user_rank

 

Group Table, groups

group_id, group_name

 

Ranks Table, user_ranks

rank_id, rank_name

[Barand]

given the structure is

       +-------------+           +-------------+                                       +--------------+                   
       | rank        |           | user        |                                       | group        |                   
       +-------------+           +-------------+                                       +--------------+                   
       | rank_id(PK) |-----+     | user_id(PK) |------+                        +-------| group_id(PK) |                   
       | rankname    |     |     | username    |      |                        |       | groupname    |                   
       +-------------+     +----<| user_rank   |      |     +--------------+   |       +--------------+                   
                                 +-------------+      |     | user_group   |   |                                          
                                                      |     +--------------+   |                                          
                                                      +----<| user_id(PK)  |   |                                          
                                                            | group_id(PK) |>--+                                          
                                                            +--------------+                

then you could

SELECT 
    g.groupname
  , r.rank_id  
  , r.rankname
  , u.username
FROM user_group ug
    INNER JOIN group g USING (group_id)
    INNER JOIN user u USING (user_id)
    INNER JOIN rank r ON u.user_rank = r.rank_id
WHERE ug.group_id = 34
ORDER BY u.rank_id, u.username

[SteveCatbert]

Hi,

 

Thank you for your help. I put what you had above into my script (changed the names to the exact names of the tables) and I am getting an error saying the column name does not exist in the field list. Any idea why? I have confirmed that all the field and column names are correct. 

[Barand]

Sorry, I have no psychic powers, and, as I cannot see your screen from here, you'll have to post the query and the exact error message. Also, post your table structures with exact column names. Use this type of query for each table

SHOW CREATE TABLE users;

[SteveCatbert]

I managed to get this to work, thank you all