How to enter each post together in one field for the database via form?

[djfox]

Maybe I need to reword my question: What is LEFT JOIN doing?

[ChemicalBliss]

Take a look at this query after you've read below and try to understand it:

Don't rush read or skim, take it in and visualize.

// Uses Customer ID for Query to select all pet history (you can use a mysql flag to set which ones have been issued an invoice).
$query = "SELECT 
`owners`.`id` As ownerid,
`owners`.`firstname` As ownerfirstname,
`owners`.`lastname` As ownerlastname,
`owners`.`address` As owneraddress,
`owners`.`phone` As ownerphone,
`pets`.`id` As petid,
`pets`.`name` As petname,
`pets`.`breed` As petbreed,
`pets`.`status` As petstatus,
`pethistory`.`id` As eventid,
`pethistory`.`datetime` As eventdate,
`pethistory`.`duration` As eventtime,
`pethistory`.`cost` As eventcost,
`pethistory`.`whathappened` As eventwhathappened,
`pethistory`.`notes` As eventnotes,
`pethistory`.`datetime` As eventtime,
`vets`.`id` As eventvetid,
`vets`.`firstname` As eventvetfirstname,
`vets`.`lastname` As eventvetlastname
FROM `owners`
LEFT JOIN `pets` on `owners`.`id`=`pets`.`ownerid`
LEFT JOIN `pethistory` on `pets`.`id`=`pethistory`.`petid`
LEFT JOIN `vets` on `pethistory`.`vetid`=`vets`.`id`
WHERE `owners`.`id`='".$cid."'
ORDER BY eventdate";

 

In this example the LEFT JOIN is basically adding data to the result, grabbing data across multiple tables based on a common field such as an Owner ID.

 

Syntax

  JOIN `%Table%` on `%Table1%`.`%Field1%`=`%Table2%`.`%Field2%`

 

%Table%

The table you want to grab extra data from.

 

%Table1% and %Field1%

The table and field name, of the common data in your first table (The one you "SELECT FROM ..."). (Ex. a user id field).

 

%Table2% and %Field2%

The table and field name, of the common data in the table that you want to join with (grab extra data from). (Ex. a user id field).

 

---

 

Explanation

 

The "LEFT" part of the JOIN clause basically means that the first table rows takes priority even if there was no match. So for Ex;

 

Table Structure (for simplicity in this example no one receives the messages)

 

Table "Users"

id | name | password | email

1 |  bob  |    god      | some@email.com

2 |  joe  |    hacker  | another@email.com

3 |  fred  |    ziggy    | yetanother@email.com

 

Table "Messages"

id |  subject   |              message             | sender_id

1  |    Hello    |            Hello no one.            | 1

2  |  Goodbye |        Goodbye No one.        | 1

3  | fred loner | fred never sends messages. | 2

 

So by using this system you can see the first message is sent from user id 1 (bob).

So is the second, in fact fred is the only one who never sent a message.

 

Example Scenario

So now you want to display all the messages, from all users. You want to show their name, and you for some reason want their email too.

 

A simple SELECT * FROM Messages query won't get their name, or their email.

Nor will a simple SELECT * FROM Users query yield any message data.

 

That's where JOINS come in handy. ex.

SELECT                            # Define all the fields you want to select. This has more benefits than just being faster. (Aliases)

    Users.id As userid      # This "As userid" is called an Alias. It renames that field for only the query results.

    Users.name,              # This is handy because we have 2 "id" fields in both tables.

    Users.email,

    Messages.id As messageid,

    Messages.subject,

    Messages.message

FROM Users                    # This is Table 1, you "Start" from this table.

LEFT JOIN Messages on Users.id=Messages.sender_id  # Join the common fields from both tables

 

Results:

userid |  name  |            email                | messageid |    subject    |                message                | sender_id

1        |    bob    |    some@email.com      |        1        |      Hello    |              Hello no one.            | 1

1        |    bob    |    some@email.com      |        2        |  Goodbye  |          Goodbye No one.          | 1

2        |    joe    |    another@email.com  |        3        |  fred loner | fred never sends messages. | 1

3        |    fred    | yetanother@email.com |    NULL      |      NULL    |                  NULL                    | 1

* NULL basically means none-existent/empty.

 

Without the "LEFT" part, fred would not of been listed, because he had no messages that matched. And would have no priority.

If there was a message that was sent by No one (no userid, or none-matching userid), and we used the "RIGHT" clause instead, we would see that every message would be displayed, even though it could not find a user to match (instead of the other way around).

(Also fred would not be displayed since it had no matches and wasn't considered priority by the "RIGHT" clause as it did with the "LEFT" clause).

 

A Normal JOIN (no LEFT or RIGHT parts) would give only rows that had a match (Neither Fred nor any anonymous messages would be displayed).

 

---

 

Conclusion

So by separating unique data in this way you can more easily manage specific items with SQL queries rather than unnecessary PHP code. (For instance, if we kept all the messages inside a field on each User row, it would be very difficult to edit specific attributes (id/author/date etc) because we would first have to get the entire message details from the database, use PHP to split/reassemble the whole message data (or numerous messages), change for instance the author, then finally update the field  with the entire message contents by serializing it or something similar.

 

With JOINS and Normalized Database' - just a simple, single query could be used to modify, for instance - the author, using the Message' ID.

 

---

 

Hopefully this is a good enough little tutorial (will probably save this somewhere lol).

 

-cb-

[Catfish]

This LEFT JOIN is interesting stuff i've never seen before. From what I gather, what it is doing is pulling data from various (some arbitrary number) tables and creating a result set that shows unique rows based on ALL of the data from ALL of the tables.

 

My question is, what would the result set look like if we just called:

 

SELECT                            # Define all the fields you want to select. This has more benefits than just being faster. (Aliases)

    Users.id As userid      # This "As userid" is called an Alias. It renames that field for only the query results.

    Users.name,              # This is handy because we have 2 "id" fields in both tables.

    Users.email,

    Messages.id As messageid,

    Messages.subject,

    Messages.message

FROM Users                    # This is Table 1, you "Start" from this table.

 

without a LEFT JOIN ?

[ChemicalBliss]

@Catfish

I would reccommend posting question in new topics, i know it seems easy and related to this discussion but it can extend threads and make it harder to find relevant responses.

 

The answer is you CAN select form multiple tables, the WHERE part however doesn't join relevant data together, This way you can "Group" common data, such as "Everything to do with User ID x", or "Post ID x" etc, without having rediculously cmoplicated WHERE statements that doesn't really do the job anyway.

 

I suggest installing PHPMyAdmin, Insert some tables (Normalized like above), and play with the queries.

 

-cb-