Page 1 of 1

Select Function

Posted: Sun Jan 11, 2009 12:41 pm
by bro_ton
I'm new to PHP and I'm trying to figure out how to use a SELECT option in a function.

The function is set up with the following parameters

Code: Select all

functionName ($table, $fields="*", $where="", $order="", $group="")
The current code uses this function and select * from the users table:

Code: Select all

$user =  functionName ("users", "*")
In my users table I also have a field for groupID and I would like to change the function so that it selects all users from the groupID based on the the groupID the current user is associated with.

Re: Select Function

Posted: Sun Jan 11, 2009 1:08 pm
by jaoudestudios
If you want help with query post what you have so far including your database schema.

Or if you want to modify your function, you will need to post the full function.

Re: Select Function

Posted: Sun Jan 11, 2009 1:09 pm
by jaoudestudios
And you only need to post your question ONCE!

You are less likely to be helped if you keep re-posting the same question under a different title - just some friendly advice :wink:

Re: Select Function

Posted: Sun Jan 11, 2009 1:18 pm
by bro_ton
Sorry about that. I hope this is enough information.

Here is the function I'm working with.

Code: Select all

function eF_getTableData($table, $fields="*", $where="", $order="", $group="")
 
{
 
    global $db;
 
    $thisQuery = microtime(true);
 
 
    $sql = "SELECT ".$fields." FROM ".$table;
 
    if($where != "") {
 
        $sql .= " WHERE ".$where;
 
    }
 
    if($order != "") {
 
        $sql .= " ORDER BY ".$order;
 
    }
 
    if($group != "") {
 
        $sql .= " GROUP BY ".$group;
 
    }
 
 
 
    $result = $db -> GetAll($sql);
 
//echo $sql."<BR>";
 
    if ($db->debug == true) {
 
        echo '<span style = "color:red">Time spent on this query: '.(microtime(true) - $thisQuery).'</span>';
 
    }
 
    $GLOBALS['databaseTime'] = $GLOBALS['databaseTime'] + microtime(true) - $thisQuery;
 
    $GLOBALS['databaseQueries']++;
 
 
 
    if ($result == false) {
 
        return array();
 
    } else {
 
        return $result;
 
    }
 
}
Here is the database table for users
Image

Here is where the function is called that I would like to create an argument the SELECTS * from users based on the userGroupName of the current user logged in.

Code: Select all

$users        = eF_getTableData("users", "*");

Re: Select Function

Posted: Sun Jan 11, 2009 1:22 pm
by bro_ton
jaoudestudios wrote:And you only need to post your question ONCE!

You are less likely to be helped if you keep re-posting the same question under a different title - just some friendly advice :wink:
Advice well taken. Thanks jaoudestudios. After making the original post I started looking around the forum and realized that databases was probably a better place to make the post.

Re: Select Function

Posted: Sun Jan 11, 2009 1:39 pm
by jaoudestudios
function eF_getTableData($table, $fields="*", $where="", $order="", $group="")
A note on your function, you have no database protection against sql injection, I suggestion doing some research into this - otherwise you might find yourself with an empty database.

To filter the results, in your $where, add...

Code: Select all

"userGroupName = '".$userGroupName."'"

Re: Select Function

Posted: Sun Jan 11, 2009 1:59 pm
by bro_ton
Thanks, I will look into the sql injection today.

I placed the code in the $Where argument as you recommended.

Code: Select all

$users        = eF_getTableData("users", "*", "userGroupName = '".$userGroupName."'");
However, when I logged in with a member of one of the groups and ran the query it still shows users from all of the groups. I currently have 4 groups. Is there anyway to place an argument in this code that says to only return users from the userGroupName the current logged in user is part of.

I'm sure I'm not explaining myself very well, this is all very new for me.

Re: Select Function

Posted: Sun Jan 11, 2009 2:05 pm
by jaoudestudios
Make sure that $userGroupName is not empty - you might have named it something else previously in your code!?!?

I have modified your function slightly, I have put my modification in red. This will display the query, can you run it again and post the query back?

Code: Select all

function eF_getTableData($table, $fields="*", $where="", $order="", $group="")
 
{
 
    global $db;
 
    $thisQuery = microtime(true);
 
 
    $sql = "SELECT ".$fields." FROM ".$table;
 
    if($where != "") {
 
        $sql .= " WHERE ".$where;
 
    }
 
    if($order != "") {
 
        $sql .= " ORDER BY ".$order;
 
    }
 
    if($group != "") {
 
        $sql .= " GROUP BY ".$group;
 
    }
 
    [color=#FF0000]echo $sql;[/color]
 
    $result = $db -> GetAll($sql);
 
//echo $sql."<BR>";
 
    if ($db->debug == true) {
 
        echo '<span style = "color:red">Time spent on this query: '.(microtime(true) - $thisQuery).'</span>';
 
    }
 
    $GLOBALS['databaseTime'] = $GLOBALS['databaseTime'] + microtime(true) - $thisQuery;
 
    $GLOBALS['databaseQueries']++;
 
 
 
    if ($result == false) {
 
        return array();
 
    } else {
 
        return $result;
 
    }
 
}
EDIT: I just realised you have the same line of code commented out 2 lines down

Re: Select Function

Posted: Sun Jan 11, 2009 2:46 pm
by bro_ton
Here is the code I got when I ran it with the echo.

Code: Select all

  SELECT * FROM configurationSELECT * FROM users WHERE login='ca-manager1'SELECT * FROM user_types WHERE id=4SELECT * FROM modules WHERE active = 1
I did realize that the not every row in the field userGroupName had a value so I went into phpMyAdmin and manually a value for each user. When I tried to login back in I got a blank screen so I'm not sure what happened. I had saved all of the original files, but when I replaced them I'm still getting it to work.

I guess I have a full day of work ahead of me :)

Re: Select Function

Posted: Sun Jan 11, 2009 2:56 pm
by jaoudestudios
I did not see the query we are working on there. Are you sure you are calling the function to generate the query?

Re: Select Function

Posted: Sun Jan 11, 2009 3:53 pm
by bro_ton
I'm not sure what happened, but I got it working again.

Question. I added the field userGroupName to the original table "users" to try and get a unique field whereby I could select all of the users for a particular group. There are 2 other tables called "groups" and "users_to_group" but I didn't know how to join them so I thought it would be easier to replicate the data in the "users" table. However, I noticed that the sql echo you had me do showed the user_type field from the "users" table. I'm still not sure how this argument

Code: Select all

$users        = eF_getTableData("users", "*", "userGroupName = '".$userGroupName."'");
ended up retrieving the user_type field, but maybe I should just use that field to group users under a specific administrator. The user_type field was designed for creating accounts with different levels of administration, but I can create a unique user_type for each group administrator and achieve what I'm looking for maybe?

Re: Select Function

Posted: Mon Jan 12, 2009 1:53 am
by jaoudestudios
If you have 2 other tables to do this, then I would not replicate the data in the users table, this will cause problems in the future! i.e if you update it in one place but not the other, which information is dominant, the user can only belong to one group - where as when using another table a user can belong to multiple groups.

Post the structure for the other 2 tables.