Stored Procedures / Routines + general US startup questions

[mudmin]

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.