Hey Guys,
I've just started a new project in the last few days and after researching user management frameworks decided on userspice (over userfrosting <- too many requirements for hosting etc)... I simply cannot be bothered to readjust my admin framework for the nth time.
I must say Its great how simplistic US is and how active the community and devs are, it's great. As promised, from downloading the zip it was roughly 15 mins before it was installed, set up and I could have started coding. I have been reading through the forums, docs, watching youtube vids and googling to get some insight and answers on US but the below are still unanswered so thought i'd say Hi and list a few if I may? (I don't usually do forums so please move me or separate my post if i'm in the wrong place).
My main query regards stored procedures (or routines as they are called in phpmyadmin). I usually use PEAR to handle my db connections and emailing etc which had a handy way to call stored proc's. I was wondering if you have any function for this within US? I really don't like having any (my)sql in my php code and have not been able to find any documentation referencing to this whilst googling etc.
Also whilst i'm here I have a few questions, mostly regarding the correct US way to do the following:
1. I'm looking to allow for users to refer friends and track whom has referred who via a sign up url with a token/guid (ie ?ref={{referring user GUID}}). There may be some reward system to this at some point, which I can handle, but I wanted to know if there is anything already built in to US that could be a starting point or any existing functions I could use when creating this functionality? see question a). (I know i could simply look for the $_GET['ref'] guid and store it in the session until the referred user signed up)
1.a. I also need to collect extra information about the user during sign up, like say a users car. Am I able to just add columns to user table in DB (eg referral guid, referred by? / user inputted data - Your Car, address? etc) I would want this data to be included in $user-->data too.
I guess this is a two part question: Can I safely modify/add to the users table and what would be the correct way to hook into the signup submit function to store this data? (would this be via usersc files <- although i would prefer to add the data at time of adding basic info to table row)
2. final one, for now,  Not major but I noticed all user references are by ID={id}. Is there a way to change this to use another column? I like to create GUID's on signup and use these when publicly referencing a user account which can then get me the id for relational data table queries etc.
I also noticed a minor issue, when updating a bio then returning to view_all_users list shows the bios with the html tags as text, I realize this may be intentional to restrict html breaking the page. (just realized on 4.3 not 4.4!!)
Apologies for the wall of text, I'm a bit of a waffler. Looking forward to building this project on US.
Cheers
J
(01-18-2019, 05:05 PM)Developer_J Wrote: Hey Guys,
I've just started a new project in the last few days and after researching user management frameworks decided on userspice (over userfrosting <- too many requirements for hosting etc)... I simply cannot be bothered to readjust my admin framework for the nth time.
I must say Its great how simplistic US is and how active the community and devs are, it's great. As promised, from downloading the zip it was roughly 15 mins before it was installed, set up and I could have started coding. I have been reading through the forums, docs, watching youtube vids and googling to get some insight and answers on US but the below are still unanswered so thought i'd say Hi and list a few if I may? (I don't usually do forums so please move me or separate my post if i'm in the wrong place).
My main query regards stored procedures (or routines as they are called in phpmyadmin). I usually use PEAR to handle my db connections and emailing etc which had a handy way to call stored proc's. I was wondering if you have any function for this within US? I really don't like having any (my)sql in my php code and have not been able to find any documentation referencing to this whilst googling etc.
Also whilst i'm here I have a few questions, mostly regarding the correct US way to do the following:
1. I'm looking to allow for users to refer friends and track whom has referred who via a sign up url with a token/guid (ie ?ref={{referring user GUID}}). There may be some reward system to this at some point, which I can handle, but I wanted to know if there is anything already built in to US that could be a starting point or any existing functions I could use when creating this functionality? see question a). (I know i could simply look for the $_GET['ref'] guid and store it in the session until the referred user signed up)
1.a. I also need to collect extra information about the user during sign up, like say a users car. Am I able to just add columns to user table in DB (eg referral guid, referred by? / user inputted data - Your Car, address? etc) I would want this data to be included in $user-->data too.
I guess this is a two part question: Can I safely modify/add to the users table and what would be the correct way to hook into the signup submit function to store this data? (would this be via usersc files <- although i would prefer to add the data at time of adding basic info to table row)
2. final one, for now, Not major but I noticed all user references are by ID={id}. Is there a way to change this to use another column? I like to create GUID's on signup and use these when publicly referencing a user account which can then get me the id for relational data table queries etc.
I also noticed a minor issue, when updating a bio then returning to view_all_users list shows the bios with the html tags as text, I realize this may be intentional to restrict html breaking the page. (just realized on 4.3 not 4.4!!)
Apologies for the wall of text, I'm a bit of a waffler. Looking forward to building this project on US.
Cheers
J
Hi. Thanks for the compliments. Not being able to install UserFrosting was actually the thing that got me to write UserSpice in the first place.
Regarding stored procedures, you can call them just like any other DB query. The database class handles them and even lets you bind variables just like any other query. I have one called GET_ITEM_PRICE and I just do
$db->query("CALL GET_ITEM_PRICE(?,?,?,?,?)",array($company,$itemno,$cat,$mailing,'1'))->first();
1. We don't have a referral thing built in. I thought about writing a plugin for it, but it seems like everyone kind of has different things they want to do with a referral system. Let me think about that one. That might be a good demo for me to make a video on how to make a plugin.
1a. Yes. You can add columns. I generally suggest that people come up with slightly odd names to make sure that we don't use those columns in the future. Some people even prefix the column names like dev_car but yes. And yes, they will automatically be in $user->data() and available at all times.
1b. There is a place to put additional join form fields in usersc/scripts/additional_join_form_fields.php
2. Regarding profiles. We're not including those by default in 4.4. Nearly everyone modifies them anyway. I think you'd have a hard time changing the id on the front facing pages, but I think the profiles page is the only thing that exposes the ids of the users and like I said, that's not there anymore.
So yes, for all your pages, you can definitely have an id and a guid plus. Note that when you go to use the $db->update method it expects the identifying column to be id, so if you want to pass a different column there you can put an array like $db->update('users',['guid','=','blah'); Again. You're probably obscuring that for a reason, but I thought I should point that out. Hope this helps. Thanks for the great questions.
Cheers mudmin, Really helpful information.
I think I've been using pear for so long I forgot the basic call for stored proc's. Use it or lose it I suppose! may have some follow ups after trying your advice but that's definitely pointed me in the right direction. I think i'll also look into upgrading to 4.4 when I can as was in a rush to get started when I downloaded the source files.
Thanks again
Happy to help. I expect the final release of 4.4 to be in the next few days, so might as well wait for that.
(01-18-2019, 07:38 PM)mudmin Wrote: Happy to help. I expect the final release of 4.4 to be in the next few days, so might as well wait for that.
Hey Mudmin, et al,
I haven't had a chance to keep track of 4.4 release since the last message, hope it's going well. I have a follow up question regarding multiple result sets returned from a stored procedure please.
I've had a look at the DB file and tried a few pdo ways to get a second (or third) result set and could not get anything to work externally ($result->nextRowset() <- does not exists error) nor see any fn in the DB file to do this for me.
For the time being I have created the following in the DB file, but I know this is not great as its a core US file. Could you tell me if I've missed something please or if there is a fn for this in US4.4?
Code: public function nextRowset(){
if(!($this->_query->nextRowset())) return [];
return $this->_query->fetchALL(PDO::FETCH_OBJ);
}
Thanks
Can you not just return all the results and do a foreach through them?
(01-24-2019, 04:48 PM)mudmin Wrote: Can you not just return all the results and do a foreach through them?
Apologies, I'm not sure where you are referring to returning all results? Did you mean from the database or in the result set with php?
I did forget to mention a few details about my use case. In my SP, I'm adding SQL generated data to two tables. x amount of rows for table A and 1 row for table B based on table C data. These are then retrieved, with two separate "SELECT"S, and returned to php for the web apps JS. A join on this result set wouldn't work as the table columns aren't similar.
I can't run pdo's nextRowset() on $result->_query as it's private so had to add the above code as a workaround. If I var_dump the $result it only shows the first result set, although it does show that there are 2 results with `private '_queryCount' => int 2`.
The only thing I can think of, to avoid breaking any upgrades to US, is to add the above function in an extension class of DB and only include/call it if I need to access a secondary+ result set, prior to calling the SP.
This is my caller FN:
Code: private function my_fn($amount){
global $user, $db;
$result = $db->query("CALL MY_PROC(?,?,?)",array($user->data()->id,$amount,NULL));
var_dump($result);
$result_A = $result->results();
$result_B = $result->nextRowset();
var_dump($result_A, $result_B);
exit();
}
(01-24-2019, 06:57 PM)Developer_J Wrote: (01-24-2019, 04:48 PM)mudmin Wrote: Can you not just return all the results and do a foreach through them?
Apologies, I'm not sure where you are referring to returning all results? Did you mean from the database or in the result set with php?
I did forget to mention a few details about my use case. In my SP, I'm adding SQL generated data to two tables. x amount of rows for table A and 1 row for table B based on table C data. These are then retrieved, with two separate "SELECT"S, and returned to php for the web apps JS. A join on this result set wouldn't work as the table columns aren't similar.
I can't run pdo's nextRowset() on $result->_query as it's private so had to add the above code as a workaround. If I var_dump the $result it only shows the first result set, although it does show that there are 2 results with `private '_queryCount' => int 2`.
The only thing I can think of, to avoid breaking any upgrades to US, is to add the above function in an extension class of DB and only include/call it if I need to access a secondary+ result set, prior to calling the SP.
This is my caller FN:
Code: private function my_fn($amount){
global $user, $db;
$result = $db->query("CALL MY_PROC(?,?,?)",array($user->data()->id,$amount,NULL));
var_dump($result);
$result_A = $result->results();
$result_B = $result->nextRowset();
var_dump($result_A, $result_B);
exit();
}
We could DEFINITELY be speaking different languages and I'm going to have Brandin take a look at this.
I see no problem at all with extending the db class. I wouldn't advise it for most people, but we have really only done 2 updates to the DB class in the last 3 years. One to add error reporting and one that allows you to connect to multiple dbs.
If extending the class works, I would definitely say go for it because it won't cause any issues. If that doesn't work, modding it isn't the end of the world. Once you mod it, you would just have to keep an eye out for changes to the class. I usually would do something like copy the modded db class to DB.mine just in case an update rolls over it, you can do a quick diff.
I'll talk to Brandin and see if there's anything I'm missing.
oki, sounds good. For time sake I may just add the class extension to my todo list and stick with your suggestion regarding db.mine backup.
Thanks again for your time and thoughts, much appreciated
Hello,
I've tried to review this and unfortunately by the end of it I'm incredibly confused. The only thing I can recommend from the knowledge of the DB class is to call your proc by the query function, and retrieve its results and data through the class again as you've already done. What is the purpose of this additional function again?
Thank you,
Brandin.
|
