java - PHP:如何从同一 MYSQL 数据库的不同表中用 PHP 编写变量

Question

所以我在 Php 中使用 Slim 框架编写了一个 api 代码，以允许我从具有多个表的数据库中获取数据。然而，一些表包含相同的名称，尤其是 userid，因为 users 表连接到几乎所有其他表。

这是我在 DbConnect 中的代码，包括:

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; 
    }

我有一个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;
}

和我的 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();
}

我如何在 DbConnect.php 中正确编写我的变量和 SELECT MYSQL 代码，并在 User.java< 下正确实现 android studio 中的所有变量 和 SharedPrefManager.java。

任何帮助将不胜感激。提前致谢。

Answer 1

如果我理解你的问题是正确的，你需要在你的选择中对列进行别名化。见下文post for details