What you do here is called a JOIN [although you do it implicitly because you select from multiple tables]. This means, if you didn't put any conditions in your WHERE clause, you had all combinations of those tables. Only with your condition you restrict your join to those rows where the drink id matches.
But there are still X multiple rows in the result for every drink, if there are X photos with this particular drinks_id. Your statement doesn't restrict which photo[s] you want to have!
If you only want one row per drink, you have to tell SQL what you want to do if there are multiple rows with a particular drinks_id. For this you need grouping and an aggregate function. You tell SQL which entries you want to group together [for example all equal drinks_ids] and in the SELECT, you have to tell which of the distinct entries for each grouped result row should be taken. For numbers, this can be average, minimum, maximum [to name some].
In your case, I can't see the sense to query the photos for drinks if you only want one row. You probably thought you could have an array of photos in your result for each drink, but SQL can't do this. If you only want any photo and you don't care which you'll get, just group by the drinks_id [in order to get only one row per drink]:
SELECT name, price, photo
FROM drinks, drinks_photos
WHERE drinks.id = drinks_id
GROUP BY drinks_id
name price photo
fanta 5 ./images/fanta-1.jpg
dew 4 ./images/dew-1.jpg
In MySQL, we also have GROUP_CONCAT, if you want the file names to be concatenated to one single string:
SELECT name, price, GROUP_CONCAT[photo, ',']
FROM drinks, drinks_photos
WHERE drinks.id = drinks_id
GROUP BY drinks_id
name price photo
fanta 5 ./images/fanta-1.jpg,./images/fanta-2.jpg,./images/fanta-3.jpg
dew 4 ./images/dew-1.jpg,./images/dew-2.jpg
However, this can get dangerous if you have , within the field values, since most likely you want to split this again on the client side.
It is also not a standard SQL aggregate function.
In the previous installment of this MySQL series, I demonstrated how you can fetch data using MySQL clauses. In this part we will learn about the Joins function in MySQL, why they are used and how to use them. Let’s get started.
How Can I Link Two Tables in MySQL
MySQL Joins let you access data from multiple tables. A MySQL Join is performed whenever two or more tables are joined in an SQL statement. MySQL Joins include: the MySQL Inner Join [also known as the Simple Join], the MySQL Left Outer Join [also called the Left Join, it returns matching records from the left table], the Right Join [this returns the matching records from the right table], and the Full Join [this returns matching records from the all tables]. Using MySQL JOINs, you’ll be able to join more than two tables.
In MySQL, the Inner Join is the Default Join. On given keywords at that point, it chooses all rows from both tables, as long as there’s a coordinate between the columns in both tables.
Unlike SQL, MySQL does not consider the Outer Join as a separate Join type. To get the same results as the Outer Join, you need to join the Left Outer Join and Right Outer Join.
How Many Tables can be Joined in MySQL
According to the official documentation of MySQL 8.0, the maximum number of tables in a JOIN statement is 61. However, note that JOIN statements could require a lot of server resources as the number of tables increases. If this is the case with your query, I highly recommend breaking it into multiple queries to reduce the load on the server.
Let us start by adding a new table in our database which will contain the message along with the user ID which have sent this message, we will name it messages. The schema of our table is:
CREATE TABLE `messages` [
`id` int[11] NOT NULL,
`message` varchar[255] NOT NULL
]
We will be using the same selectdata function which we have created in our crud.php file. Now let us get started by joining these two tables. You can also fill your table so you can practice it.
Stop Wasting Time on Servers
Cloudways handle server management for you so you can focus on creating great apps and keeping your clients happy.
Inner Join
Inner Join joins table in such a way that it only shows those results which matches the condition that is given and hide others. The structure of Inner Join queries are:
SELECT column_name[s]
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
Inner Join and simple join both are same. You can also write your query like this:
SELECT column_name[s]
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
Now let us fetch the Name and the message from our database using Inner join. The query will be like this
$sql = "SELECT CONCAT[myguests.firstname,' ',myguests.lastname] AS name, myguests.email, messages.message From myguests INNER JOIN messages ON myguests.id = messages.id";
The CONCAT function is used to join two strings column in MySQL. Now open your index.php which we have created previously copy the following code in it.
| Name | Message |