So i am writing an api code using Slim framework in Php to allow me get data from a database with multiple tables. However the some of the tables contain same names especially userid given the users table is connected to almost all the other tables.
Here is my code in DbConnect under includes:
public function getAllUsers(){
$stmt = $this->con->prepare("SELECT tblclients.id, tblclients.email, tblclients.firstname, tblclients.lastname, tblclients.companyname, tblclients.address1, tblclients.city, tblclients.state, tblclients.postcode, tblclients.country, tblclients.phonenumber, tblclients.status, tblclients.currency, tblclients.credit, tblclients.language, tblaccounts.gateway, tblaccounts.date, tblaccounts.amountin, tblaccounts.fees, tblaccounts.amountout, tblactivitylog.date, tblactivitylog.description, tblactivitylog.user, tblannouncements.id, tblannouncements.date,tblannouncements.title, tblannouncements.created_at, tblannouncements.updated_at, tblclientsfiles.title, tblclientsfiles.filename, tblclientsfiles.dateadded, tblcredit.date, tblcredit.description, tblcredit.amount, tblcurrencies.code, tblcurrencies.prefix, tblcurrencies.suffix, tbldomains.orderid, tbldomains.type, tbldomains.registrationdate, tbldomains.domain, tbldomains.firstpaymentamount, tbldomains.recurringamount, tbldomains.registrationperiod, tbldomains.expirydate, tbldomains.status, tbldomains.nextduedate, tbldomains.nextinvoicedate, tbldomains.paymentmethod, tbldomains.created_at, tbldomains.updated_at, tbldownloads.id, tbldownloads.type, tbldownloads.title, tbldownloads.description, tbldownloads.downloads, tblemails.subject, tblemails.message, tblemails.date, tblemails.to, tblhosting.regdate, tblhosting.domain, tblhosting.paymentmethod, tblhosting.firstpaymentamount, tblhosting.amount, tblhosting.billingcycle, tblhosting.nextduedate, tblhosting.nextinvoicedate, tblhosting.termination_date, tblhosting.completed_date, tblhosting.domainstatus, tblhosting.diskusage, tblhosting.disklimit, tblhosting.bwusage, tblhosting.bwlimit, tblhosting.lastupdate, tblinvoiceitems.invoiceid, tblinvoiceitems.type, tblinvoiceitems.description, tblinvoiceitems.amount, tblinvoiceitems.taxed, tblinvoiceitems.duedate, tblinvoiceitems.paymentmethod, tblinvoices.date, tblinvoices.duedate, tblinvoices.datepaid, tblinvoices.subtotal, tblinvoices.credit, tblinvoices.status, tblinvoices.paymentmethod, tblknowledgebase.id, tblknowledgebase.title, tblknowledgebase.article, tblknowledgebase.views, tblknowledgebase.votes, tblticketdepartments.id, tblticketdepartments.name, tblticketdepartments.description, tbltickets.tid, tbltickets.date, tbltickets.title, tbltickets.message, tbltickets.status, tbltickets.urgency, tbltickets.lastreply, tblticketreplies.tid, tblticketreplies.date, tblticketreplies.message, tblticketreplies.admin
FROM tblclients, tblcurrencies, tblaccounts, tblactivitylog, tblannouncements, tblclientsfiles, tblcredit, tbldomains, tbldownloads, tblemails, tblhosting, tblinvoiceitems, tblinvoices, tblknowledgebase, tblticketdepartments, tbltickets, tblticketreplies
WHERE tblclients.currency = tblcurrencies.id
AND tblclients.id = tblaccounts.userid = tblactivitylog.userid = tblclientsfiles.userid = tblcredit.clientid = tblcredit.clientid = tbldomains.userid = tblemails.userid = tblhosting.userid = tblinvoiceitems.userid = tblinvoices.userid = tbltickets.userid = tblticketreplies.userid;");
$stmt->execute();
$stmt->bind_result($id, $email, $firstname, $lastname, $companyname, $address1, $city, $state, $postcode, $country, $phonenumber, $status, $currency, $credit, $language, $accgateway, $accdate, $accamountin, $accfees, $accamountout, $actdate, $actdescription, $actuser, $anid, $andate, $antitle, $ancreated_at, $anupdated_at, $filetitle, $filefilename, $filedateadded, $credate, $credescription, $creamount, $curcode, $curprefix, $cursuffix, $doorderid, $dotype, $doregistrationdate, $dodomain, $dofirstpaymentamount, $dorecurringamount, $doregistrationperiod, $doexpirydate, $dostatus, $donextduedate, $donextinvoicedate, $dopaymentmethod, $docreated_at, $doupdated_at, $dwnid, $dwntype, $dwntitle, $dwndescription, $dwndownloads, $esubject, $emessage, $edate, $eto, $horegdate, $hodomain, $hopaymentmethod, $hofirstpaymentamount, $hoamount, $hobillingcycle, $honextduedate, $honextinvoicedate, $hotermination_date, $hocompleted_date, $hodomainstatus, $hodiskusage, $hodisklimit, $hobwusage, $hobwlimit, $holastupdate, $initinvoiceid, $inittype, $initdescription, $initamount, $inittaxed, $initduedate, $initpaymentmethod, $indate, $induedate, $indatepaid, $insubtotal, $incredit, $instatus, $inpaymentmethod, $tdid, $tdname, $tddescription, $titid, $tidate, $timessage, $tiadmin);
$users = array();
while($stmt->fetch()){
$user = array();
$user['id'] = $id;
$user['email'] = $email;
$user['firstname'] = $firstname;
$user['lastname'] = $lastname;
$user['companyname'] = $companyname;
$user['address1'] = $address1;
$user['city'] = $city;
$user['state'] = $state;
$user['postcode'] = $postcode;
$user['country'] = $country;
$user['phonenumber'] = $phonenumber;
$user['status'] = $status;
$user['currency'] = $currency;
$user['credit'] = $credit;
$user['language'] = $language;
$user['gateway'] = $accgateway;
$user['date'] = $accdate;
$user['amountin'] = $accamountin;
$user['fees'] = $accfees;
$user['amountout'] = $accamountout;
$user['date'] = $actdate;
$user['description'] = $actdescription;
$user['user'] = $actuser;
$user['id'] = $anid;
$user['date'] = $andate;
$user['title'] = $antitle;
$user['created_at'] = $ancreated_at;
$user['updated_at'] = $anupdated_at;
$user['title'] = $filetitle;
$user['filename'] = $filefilename;
$user['dateadded'] = $filedateadded;
$user['date'] = $credate;
$user['description'] = $credescription;
$user['amount'] = $creamount;
$user['code'] = $curcode;
$user['prefix'] = $curprefix;
$user['suffix'] = $cursuffix;
$user['id'] = $doorderid;
$user['type'] = $dotype;
$user['registrationdate'] = $doregistrationdate;
$user['domain'] = $dodomain;
$user['firstpaymentamount'] = $dofirstpaymentamount;
$user['recurringamount'] = $dorecurringamount;
$user['registrationperiod'] = $doregistrationperiod;
$user['expirydate'] = $doexpirydate;
$user['status'] = $dostatus;
$user['nextduedate'] = $donextduedate;
$user['nextinvoicedate'] = $donextinvoicedate;
$user['paymentmethod'] = $dopaymentmethod;
$user['created_at'] = $docreated_at;
$user['updated_at'] = $doupdated_at;
$user['id'] = $dwnid;
$user['type'] = $dwntype;
$user['title'] = $dwntitle;
$user['description'] = $dwndescription;
$user['downloads'] = $dwndownloads;
$user['subject'] = $esubject;
$user['message'] = $emessage;
$user['date'] = $edate;
$user['to'] = $eto;
$user['regdate'] = $horegdate;
$user['domain'] = $hodomain;
$user['paymentmethod'] = $hopaymentmethod;
$user['firstpaymentamount'] = $hofirstpaymentamount;
$user['amount'] = $hoamount;
$user['billingcycle'] = $hobillingcycle;
$user['nextduedate'] = $honextduedate;
$user['nextinvoicedate'] = $honextinvoicedate;
$user['termination_date'] = $hotermination_date;
$user['completed_date'] = $hocompleted_date;
$user['domainstatus'] = $hodomainstatus;
$user['diskusage'] = $hodiskusage;
$user['disklimit'] = $hodisklimit;
$user['bwusage'] = $hobwusage;
$user['bwlimit'] = $hobwlimit;
$user['lastupdate'] = $holastupdate;
$user['invoiceid'] = $initinvoiceid;
$user['type'] = $inittype;
$user['description'] = $initdescription;
$user['amount'] = $initamount;
$user['taxed'] = $inittaxed;
$user['duedate'] = $initduedate;
$user['paymentmethod'] = $initpaymentmethod;
$user['date'] = $indate;
$user['duedate'] = $induedate;
$user['datepaid'] = $indatepaid;
$user['subtotal'] = $insubtotal;
$user['credit'] = $incredit;
$user['status'] = $instatus;
$user['paymentmethod'] = $inpaymentmethod;
$user['id'] = $tdid;
$user['name'] = $tdname;
$user['description'] = $tddescription;
$user['id'] = $titid;
$user['date'] = $tidate;
$user['message'] = $timessage;
$user['admin'] = $tiadmin;
array_push($users, $user);
}
return $users;
}
I have a User.java
public class User {
private int id;
private String email;
private String firstname;
private String lastname;
private String companyname;
private String postcode;
private String city;
private String state;
private String phonenumber;
private String address1;
private String country;
private String status;
private int currency;
private String credit;
private String language;
private String code;
private String prefix;
private String suffix;
public User(int id, String email, String firstname, String lastname, String companyname, String address1, String city,
String state, String postcode, String country, String phonenumber, String status, int currency, String credit,
String language, String code, String prefix, String suffix) {
this.id = id;
this.email = email;
this.firstname = firstname;
this.lastname = lastname;
this.companyname = companyname;
this.address1 = address1;
this.city = city;
this.state = state;
this.postcode = postcode;
this.country = country;
this.phonenumber = phonenumber;
this.status = status;
this.currency = currency;
this.credit = credit;
this.language = language;
this.code = code;
this.prefix = prefix;
this.suffix = suffix;
}
public int getId() {
return id;
}
public String getEmail() {
return email;
}
public String getFirstName() {
return firstname;
}
public String getLastName() {
return lastname;
}
public String getCompanyName() { return companyname; }
public String getAddress1() {
return address1;
}
public String getCity() {
return city;
}
public String getState() { return state; }
public String getPostcode() {
return postcode;
}
public String getCountry() {
return country;
}
public String getPhonenumber() {
return phonenumber;
}
public String getStatus() { return status; }
public int getCurrency() { return currency; }
public String getCredit() {
return credit;
}
public String getLanguage() {
return language;
}
public String getCode() {
return code;
}
public String getPrefix() {
return prefix;
}
public String getSuffix() {
return suffix;
}
}
and my sharedprefManager.java
public class SharedPrefManager {
private static final String SHARED_PREF_NAME = "my_shared_preff";
private static SharedPrefManager mInstance;
private Context mCtx;
private SharedPrefManager(Context mCtx) {
this.mCtx = mCtx;
}
public static synchronized SharedPrefManager getInstance(Context mCtx) {
if (mInstance == null) {
mInstance = new SharedPrefManager(mCtx);
}
return mInstance;
}
public void saveUser(User user) {
SharedPreferences sharedPreferences = mCtx.getSharedPreferences(SHARED_PREF_NAME, Context.MODE_PRIVATE);
SharedPreferences.Editor editor = sharedPreferences.edit();
editor.putInt("id", user.getId());
editor.putString("email", user.getEmail());
editor.putString("firstname", user.getFirstName());
editor.putString("lastname", user.getLastName());
editor.putString("companyname", user.getCompanyName());
editor.putString("address1", user.getAddress1());
editor.putString("city", user.getCity());
editor.putString("state", user.getState());
editor.putString("postcode", user.getPostcode());
editor.putString("country", user.getCountry());
editor.putString("phonenumber", user.getPhonenumber());
editor.putString("status", user.getStatus());
editor.putInt("currency", user.getCurrency());
editor.putString("credit", user.getCredit());
editor.putString("language", user.getLanguage());
editor.putString("code", user.getCode());
editor.putString("prefix", user.getPrefix());
editor.putString("suffix", user.getSuffix());
editor.apply();
}
public boolean isLoggedIn() {
SharedPreferences sharedPreferences = mCtx.getSharedPreferences(SHARED_PREF_NAME, Context.MODE_PRIVATE);
return sharedPreferences.getInt("id", -1) != -1;
}
public User getUser() {
SharedPreferences sharedPreferences = mCtx.getSharedPreferences(SHARED_PREF_NAME, Context.MODE_PRIVATE);
return new User(
sharedPreferences.getInt("id", -1),
sharedPreferences.getString("email", null),
sharedPreferences.getString("firstname", null),
sharedPreferences.getString("lastname", null),
sharedPreferences.getString("companyname", null),
sharedPreferences.getString("address1", null),
sharedPreferences.getString("city", null),
sharedPreferences.getString("state", null),
sharedPreferences.getString("postcode", null),
sharedPreferences.getString("country", null),
sharedPreferences.getString("phonenumber", null),
sharedPreferences.getString("status", null),
sharedPreferences.getInt("currency", -1),
sharedPreferences.getString("credit", null),
sharedPreferences.getString("language", null),
sharedPreferences.getString("code", null),
sharedPreferences.getString("prefix", null),
sharedPreferences.getString("suffix", null)
);
}
public void clear() {
SharedPreferences sharedPreferences = mCtx.getSharedPreferences(SHARED_PREF_NAME, Context.MODE_PRIVATE);
SharedPreferences.Editor editor = sharedPreferences.edit();
editor.clear();
editor.apply();
}
How do i properly write my variables and SELECT MYSQL code inDbConnect.php and also properly implement all the variables in android studio under User.java and SharedPrefManager.java.
Any help would be deeply appreciated. Thanks in advance.
