Mysqli Prepared Statement - Join Tables

[josephbupe]

Hi,

I want to restructure my database tables so that I can have one table (t_incidents) to hold foreign keys instead of holding foreign keys in the table "t_persons" because one person can commit more than one offense.

However, I need to know how the join should be implemented with MySQLi prepared statement.

I need some one to review the following statement for me and advise:

if ($stmt = $mysqli->prepare("Select t_persons.PersonID    
                                                                    
        ,t_persons.FamilyName
        ,t_persons.FirstName
        ,t_persons.OtherNames
        ,t_persons.Gender
        ,t_persons.CountryID
        ,t_persons.ImagePath    

        ,t_incidents.IncidentID
        ,t_incidents.Incident
        ,t_incidents.IncidentDate
        ,t_incidents.PersonID
        ,t_incidents.CountryID
        ,t_incidents.OffenceKeywordID
        ,t_incidents.StatusID
        ,t_incidents.AgencyID

        ,t_status.StatusID
        ,t_status.Status

        ,t_offencekeyword.KeywordID
        ,t_offencekeyword.Keyword

        ,t_countries.CountryID
        ,t_countries.Country

        ,t_agencies.AgencyID
        ,t_agencies.Agency

From t_persons
    Inner Join t_incidents On t_persons.PersonID = t_incidents.PersonID
    Inner Join t_incidents On t_countries.CountryID = t_incidents.CountryID
    Inner Join t_incidents On t_status.StatusID = t_incidents.StatusID
    Inner Join t_incidents On t_offenceskeyword.KeywordID = t_incidents.KeywordID
    Inner Join t_incidents On t_agencies.AgencyID = t_incidents.AgencyID

Where t_persons.PersonID = '$PersonID'")) {

Regards.

josephbupe

[Barand]

There is no need to select the PersonID from the person table and the incident table. As you are joining on that column the values will be the same, plus you can't have two columns returned with the same name. The same goes for all your other foreign keys.

 

You have a CountryId in person and incident tables. Is it possible they could be two different countries? If so you need to join twice to the country table, using a different table alias for each join.

 

You are using a prepared statement, the purpose of which is so you don't put values like "$PersonID" directly into the query but use "?" as a placeholder then use BindParam() to define the $PersonID value.

[maxxd]

Give this a shot and see if it gets you the information you're looking for. You may have to start with t_persons as your base table (as you have, I switched it to t_incidents because that seems to be the main data table but the join directions may cause some weirdness...)

$qry = "SELECT	 p.PersonID    
		,p.FamilyName
		,p.FirstName
		,p.OtherNames
		,p.Gender
		,p.ImagePath    
		,i.IncidentID
		,i.Incident
		,i.IncidentDate
		,s.StatusID
		,ok.KeywordID
		,c.Country
		,a.Agency
	FROM t_incidents i
	LEFT JOIN t_persons p
		ON i.PersonID = p.PersonID
	LEFT JOIN t_countries c
		ON i.CountryID = c.CountryID
	LEFT JOIN t_status s
		ON i.StatusID = s.StatusID
	LEFT JOIN t_offenceskeyword ok
		ON i.OffenseKeywordID = ok.KeywordID
	LEFT JOIN t_Agencies a
		ON i.AgencyID = a.AgencyID
	WHERE p.PersonID = :pid";

Basically, you don't need all the ID columns unless you plan on specifically doing something with them (sorting, filtering, etc.), and even if that is the case, you don't need to select them both. You're joining on the column value, so the values will be the same. Of course, as Barand pointed out, you're going to want to bind the $PersonID to the :pid parameter before you can actually run the query.

[josephbupe]

Yes, it's what I was looking for.

 

Thanx alot.