SQL/PHP: Joins with no matches on the other table (multiple tables)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I have four tables:

Personal_trainer(personaltrainerID, name, age, location)

Client(clientID, name, age, location)

Nutrition_Plan(NutritionplanID, personaltrainerID, clientID)

Training_Plan(TrainingplanID, personaltrainerID, clientID)

I'm trying to display that when a personal trainer creates a nutrition/training plan and assigns it to a client, only their specific clients appear as a result, but their is no foreign key in the client table to identify the coach (due to the criteria of the project)

I'm wondering what is the SQL for the neccessary join on both the training/nutrition plan. I have been trying for quite some time and here is my sample code.

Desired output is all the client data IF ONLY they have been assigned either a training or nutrition plan by that specific trainer

In the statement I'm having issues with the Bind parameter so that only users with clients can see their clients. If i use a specified ID i can get a return!

    <?php 

            //code to search for a item from the database

            // user can enter any character to search for a value from the db

           if (isset($_POST['search']))

           {

               $valueToSearch = $_POST['ValueToSearch'];

               $query = "select * from client WHERE concat(`clientID`, `name`, `age`, `sex`, `weight`, `height`, `yearsExperience`, `goal`, `injuries`, 'email')like'%".$valueToSearch."%'";

               $search_result = filterTable($query);



           }

           else {

           $query = "select *

            from client

            where clientID in (select clientID from nutrition_plan where personaltrainerID=?)

            or clientID in (select clientID from training_plan where personalTrainerID=?)";

           $query->bind_param("i", $_POST[""]);

               $search_result = filterTable($query);

           }

           //code to filter the db

           function filterTable($query)

           {

               $connect = mysqli_connect("localhost:3308","root","","fypdatabase");

               $filter_Result = mysqli_query($connect, $query);

               return $filter_Result;

           }

           ?>



           <?php

           while($row = mysqli_fetch_array($search_result))

          { //display the details from the db in the table with option to delete or update entry 

            ?>

                    <tr>

                    <td><?php echo $row["clientID"]; ?></td>

                    <td><?php echo $row["name"]; ?></td>

                    <td><?php echo $row["age"]; ?></td>

                    <td><?php echo $row["sex"]; ?></td>

                    <td><?php echo $row["weight"]; ?></td>

                    <td><?php echo $row["height"]; ?></td>

                    <td><?php echo $row["yearsExperience"]; ?></td>

                    <td><?php echo $row["goal"]; ?></td>

                    <td><?php echo $row["injuries"]; ?></td>

                    <td><?php echo $row["email"]; ?></td>

                    <td> 

                        <a href="?Delete=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Delete</a>

                    </td>

                    <td>

                        <a href="updateClient.php?Edit=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Update</a>

                    </td>

                  </tr>

                  <?php

edited Jan 3 at 11:02

asked Jan 3 at 10:00

Darán

207

"IF ONLY they have been assigned a training/nutrition plan" AND or OR?

– Quasimodo's clone
Jan 3 at 10:05

1

You might want a simple INNER JOIN chain with the USING(xxxID) clause. dev.mysql.com/doc/refman/8.0/en/join.html

– Quasimodo's clone
Jan 3 at 10:12

OR! sorry ill make the change!

– Darán
Jan 3 at 10:12

you have an error in your query SELECT * FROM then LEFT JOIN with full criteria and after filter using WHERE

– Artem Ilchenko
Jan 3 at 10:14

@Quasimodo'sclone thank you! how would i write that?

– Darán
Jan 3 at 10:15

|
show 5 more comments

I have four tables:

Personal_trainer(personaltrainerID, name, age, location)

Client(clientID, name, age, location)

Nutrition_Plan(NutritionplanID, personaltrainerID, clientID)

Training_Plan(TrainingplanID, personaltrainerID, clientID)

I'm wondering what is the SQL for the neccessary join on both the training/nutrition plan. I have been trying for quite some time and here is my sample code.

Desired output is all the client data IF ONLY they have been assigned either a training or nutrition plan by that specific trainer

In the statement I'm having issues with the Bind parameter so that only users with clients can see their clients. If i use a specified ID i can get a return!

    <?php 

            //code to search for a item from the database

            // user can enter any character to search for a value from the db

           if (isset($_POST['search']))

           {

               $valueToSearch = $_POST['ValueToSearch'];

               $query = "select * from client WHERE concat(`clientID`, `name`, `age`, `sex`, `weight`, `height`, `yearsExperience`, `goal`, `injuries`, 'email')like'%".$valueToSearch."%'";

               $search_result = filterTable($query);



           }

           else {

           $query = "select *

            from client

            where clientID in (select clientID from nutrition_plan where personaltrainerID=?)

            or clientID in (select clientID from training_plan where personalTrainerID=?)";

           $query->bind_param("i", $_POST[""]);

               $search_result = filterTable($query);

           }

           //code to filter the db

           function filterTable($query)

           {

               $connect = mysqli_connect("localhost:3308","root","","fypdatabase");

               $filter_Result = mysqli_query($connect, $query);

               return $filter_Result;

           }

           ?>



           <?php

           while($row = mysqli_fetch_array($search_result))

          { //display the details from the db in the table with option to delete or update entry 

            ?>

                    <tr>

                    <td><?php echo $row["clientID"]; ?></td>

                    <td><?php echo $row["name"]; ?></td>

                    <td><?php echo $row["age"]; ?></td>

                    <td><?php echo $row["sex"]; ?></td>

                    <td><?php echo $row["weight"]; ?></td>

                    <td><?php echo $row["height"]; ?></td>

                    <td><?php echo $row["yearsExperience"]; ?></td>

                    <td><?php echo $row["goal"]; ?></td>

                    <td><?php echo $row["injuries"]; ?></td>

                    <td><?php echo $row["email"]; ?></td>

                    <td> 

                        <a href="?Delete=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Delete</a>

                    </td>

                    <td>

                        <a href="updateClient.php?Edit=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Update</a>

                    </td>

                  </tr>

                  <?php

edited Jan 3 at 11:02

asked Jan 3 at 10:00

Darán

207

"IF ONLY they have been assigned a training/nutrition plan" AND or OR?

– Quasimodo's clone
Jan 3 at 10:05

1

You might want a simple INNER JOIN chain with the USING(xxxID) clause. dev.mysql.com/doc/refman/8.0/en/join.html

– Quasimodo's clone
Jan 3 at 10:12

OR! sorry ill make the change!

– Darán
Jan 3 at 10:12

you have an error in your query SELECT * FROM then LEFT JOIN with full criteria and after filter using WHERE

– Artem Ilchenko
Jan 3 at 10:14

@Quasimodo'sclone thank you! how would i write that?

– Darán
Jan 3 at 10:15

|
show 5 more comments

I have four tables:

Personal_trainer(personaltrainerID, name, age, location)

Client(clientID, name, age, location)

Nutrition_Plan(NutritionplanID, personaltrainerID, clientID)

Training_Plan(TrainingplanID, personaltrainerID, clientID)

I'm wondering what is the SQL for the neccessary join on both the training/nutrition plan. I have been trying for quite some time and here is my sample code.

Desired output is all the client data IF ONLY they have been assigned either a training or nutrition plan by that specific trainer

In the statement I'm having issues with the Bind parameter so that only users with clients can see their clients. If i use a specified ID i can get a return!

    <?php 

            //code to search for a item from the database

            // user can enter any character to search for a value from the db

           if (isset($_POST['search']))

           {

               $valueToSearch = $_POST['ValueToSearch'];

               $query = "select * from client WHERE concat(`clientID`, `name`, `age`, `sex`, `weight`, `height`, `yearsExperience`, `goal`, `injuries`, 'email')like'%".$valueToSearch."%'";

               $search_result = filterTable($query);



           }

           else {

           $query = "select *

            from client

            where clientID in (select clientID from nutrition_plan where personaltrainerID=?)

            or clientID in (select clientID from training_plan where personalTrainerID=?)";

           $query->bind_param("i", $_POST[""]);

               $search_result = filterTable($query);

           }

           //code to filter the db

           function filterTable($query)

           {

               $connect = mysqli_connect("localhost:3308","root","","fypdatabase");

               $filter_Result = mysqli_query($connect, $query);

               return $filter_Result;

           }

           ?>



           <?php

           while($row = mysqli_fetch_array($search_result))

          { //display the details from the db in the table with option to delete or update entry 

            ?>

                    <tr>

                    <td><?php echo $row["clientID"]; ?></td>

                    <td><?php echo $row["name"]; ?></td>

                    <td><?php echo $row["age"]; ?></td>

                    <td><?php echo $row["sex"]; ?></td>

                    <td><?php echo $row["weight"]; ?></td>

                    <td><?php echo $row["height"]; ?></td>

                    <td><?php echo $row["yearsExperience"]; ?></td>

                    <td><?php echo $row["goal"]; ?></td>

                    <td><?php echo $row["injuries"]; ?></td>

                    <td><?php echo $row["email"]; ?></td>

                    <td> 

                        <a href="?Delete=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Delete</a>

                    </td>

                    <td>

                        <a href="updateClient.php?Edit=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Update</a>

                    </td>

                  </tr>

                  <?php

edited Jan 3 at 11:02

asked Jan 3 at 10:00

Darán

207

I have four tables:

Personal_trainer(personaltrainerID, name, age, location)

Client(clientID, name, age, location)

Nutrition_Plan(NutritionplanID, personaltrainerID, clientID)

Training_Plan(TrainingplanID, personaltrainerID, clientID)

I'm wondering what is the SQL for the neccessary join on both the training/nutrition plan. I have been trying for quite some time and here is my sample code.

Desired output is all the client data IF ONLY they have been assigned either a training or nutrition plan by that specific trainer

In the statement I'm having issues with the Bind parameter so that only users with clients can see their clients. If i use a specified ID i can get a return!

    <?php 

            //code to search for a item from the database

            // user can enter any character to search for a value from the db

           if (isset($_POST['search']))

           {

               $valueToSearch = $_POST['ValueToSearch'];

               $query = "select * from client WHERE concat(`clientID`, `name`, `age`, `sex`, `weight`, `height`, `yearsExperience`, `goal`, `injuries`, 'email')like'%".$valueToSearch."%'";

               $search_result = filterTable($query);



           }

           else {

           $query = "select *

            from client

            where clientID in (select clientID from nutrition_plan where personaltrainerID=?)

            or clientID in (select clientID from training_plan where personalTrainerID=?)";

           $query->bind_param("i", $_POST[""]);

               $search_result = filterTable($query);

           }

           //code to filter the db

           function filterTable($query)

           {

               $connect = mysqli_connect("localhost:3308","root","","fypdatabase");

               $filter_Result = mysqli_query($connect, $query);

               return $filter_Result;

           }

           ?>



           <?php

           while($row = mysqli_fetch_array($search_result))

          { //display the details from the db in the table with option to delete or update entry 

            ?>

                    <tr>

                    <td><?php echo $row["clientID"]; ?></td>

                    <td><?php echo $row["name"]; ?></td>

                    <td><?php echo $row["age"]; ?></td>

                    <td><?php echo $row["sex"]; ?></td>

                    <td><?php echo $row["weight"]; ?></td>

                    <td><?php echo $row["height"]; ?></td>

                    <td><?php echo $row["yearsExperience"]; ?></td>

                    <td><?php echo $row["goal"]; ?></td>

                    <td><?php echo $row["injuries"]; ?></td>

                    <td><?php echo $row["email"]; ?></td>

                    <td> 

                        <a href="?Delete=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Delete</a>

                    </td>

                    <td>

                        <a href="updateClient.php?Edit=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Update</a>

                    </td>

                  </tr>

                  <?php

php mysql sql join outer-join

edited Jan 3 at 11:02

asked Jan 3 at 10:00

Darán

207

edited Jan 3 at 11:02

asked Jan 3 at 10:00

Darán

207

edited Jan 3 at 11:02

asked Jan 3 at 10:00

Darán

207

asked Jan 3 at 10:00

Darán

207

asked Jan 3 at 10:00

Darán

207

"IF ONLY they have been assigned a training/nutrition plan" AND or OR?

– Quasimodo's clone
Jan 3 at 10:05

1

You might want a simple INNER JOIN chain with the USING(xxxID) clause. dev.mysql.com/doc/refman/8.0/en/join.html

– Quasimodo's clone
Jan 3 at 10:12

OR! sorry ill make the change!

– Darán
Jan 3 at 10:12

you have an error in your query SELECT * FROM then LEFT JOIN with full criteria and after filter using WHERE

– Artem Ilchenko
Jan 3 at 10:14

@Quasimodo'sclone thank you! how would i write that?

– Darán
Jan 3 at 10:15

|
show 5 more comments

"IF ONLY they have been assigned a training/nutrition plan" AND or OR?

– Quasimodo's clone
Jan 3 at 10:05

1

You might want a simple INNER JOIN chain with the USING(xxxID) clause. dev.mysql.com/doc/refman/8.0/en/join.html

– Quasimodo's clone
Jan 3 at 10:12

OR! sorry ill make the change!

– Darán
Jan 3 at 10:12

you have an error in your query SELECT * FROM then LEFT JOIN with full criteria and after filter using WHERE

– Artem Ilchenko
Jan 3 at 10:14

@Quasimodo'sclone thank you! how would i write that?

– Darán
Jan 3 at 10:15

"IF ONLY they have been assigned a training/nutrition plan" AND or OR?

– Quasimodo's clone
Jan 3 at 10:05

You might want a simple INNER JOIN chain with the USING(xxxID) clause. dev.mysql.com/doc/refman/8.0/en/join.html

– Quasimodo's clone
Jan 3 at 10:12

OR! sorry ill make the change!

– Darán
Jan 3 at 10:12

you have an error in your query SELECT * FROM then LEFT JOIN with full criteria and after filter using WHERE

– Artem Ilchenko
Jan 3 at 10:14

@Quasimodo'sclone thank you! how would i write that?

– Darán
Jan 3 at 10:15

|
show 5 more comments

3 Answers
3

active

oldest

votes

SELECT name, age, location FROM Client

INNER JOIN

(

  SELECT personaltrainerID, clientID from Nutrion_Plan 

  UNION DISTINCT 

  SELECT personaltrainerID, clientID from Training_Plan

) u

USING(clientID)

WHERE u.personaltrainerID = ?;

edited Jan 3 at 11:06

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

Thank you thats great!! What would you replace "1" instead of so that different personaltrainerID's can be used when different trainers log in?

– Darán
Jan 3 at 11:04

Of course a parameter like ? for a prepared statement.

– Quasimodo's clone
Jan 3 at 11:05

When i include a ? mark on its own no results are returned, should there be another line for binding a parameter? See my edited example above please!

– Darán
Jan 3 at 11:07

Since you have tagged your question to belong to PHP, I expect you want to use mysqli's or DBO's prepare method. php.net/manual/en/mysqli.quickstart.prepared-statements.php / php.net/manual/en/pdo.prepared-statements.php That's highly recommended since interpolating client data directly into SQL would make your app vulnerable to SQL injection attacks.

– Quasimodo's clone
Jan 3 at 11:14

I'm using bind param but after the "i" for integer i dont know what value to pass into it? $query->bind_param("i", $_POST[""]); ----> not sure whats meant to be where post is

– Darán
Jan 3 at 11:18

|
show 3 more comments

Let's see if I understand your request correctly ...

Show only clients that have a nutrition or training plan by trainer 123:

select *

from client

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

Show these clients along with all their plans (regardless of the plans' trainers):

select *

from client

join

(

  select 'nutrition' as kind, nutritionplanid as id, personaltrainerid, clientid

  from nutrition_plan

  union all

  select 'training' as kind, trainingplanid as id, personaltrainerid, clientid

  from training_plan

) plan using (clientid)

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

That looks good thank you! when the pesonaltrainerID is coming from the database and could be any number, what would it be instead of '123' for instance? Would it be '?'

– Darán
Jan 3 at 10:33

It's coming from the database? What does it mean? From another table, e.g. select personaltrainerid from personal_trainer where name = 'John Smith'? Then you can simply replace 123 with this subquery in parantheses: ... where personaltrainerid = (select personaltrainerid from personal_trainer where name = 'John Smith'));

– Thorsten Kettner
Jan 3 at 10:36

Great thank you! so i should take your second example that should work?

– Darán
Jan 3 at 10:40

If this is what you want. Try it and check the results. The query gets you all clients with all their plans, provided at least one of the client's plans is by the desired trainer.

– Thorsten Kettner
Jan 3 at 10:42

I get you thank you, I tried the first query and no errors were present but the output of the client wasnt displayed in the table!

– Darán
Jan 3 at 10:45

|
show 5 more comments

This should work

SELECT * from client JOIN nutrition_plan ON client.clientid=nutritionplan.clientid JOIN Personaltrainer ON Personaltrainer.personaltrainerID=nutritionplan.personaltrainerID Where Personaltrainer.personaltrainerID="id"

answered Jan 3 at 10:14

Kashan

101111

Thanks Kashan its almost there! However what is the "id" at the end relating too? thats where my error is, personaltrainerID?

– Darán
Jan 3 at 10:21

this should actually display that through his personal id or name

– Kashan
Jan 3 at 10:28

I get you, it doesnt throw any errors and runs, however no data is displayed in the table!

– Darán
Jan 3 at 10:34

1

@Darán: This query gives you one personal trainer, their nutrition plans and the related clients. If you take the query as is, i.e. with "id" literally then you would look up trainer ID 0, because "id" means a column named id, and as this doesn't exist, MySQL defaults to a string 'id' instead, and as this isn't a number, it gets converted to 0.

– Thorsten Kettner
Jan 3 at 10:45

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019985%2fsql-php-joins-with-no-matches-on-the-other-table-multiple-tables%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

SELECT name, age, location FROM Client

INNER JOIN

(

  SELECT personaltrainerID, clientID from Nutrion_Plan 

  UNION DISTINCT 

  SELECT personaltrainerID, clientID from Training_Plan

) u

USING(clientID)

WHERE u.personaltrainerID = ?;

edited Jan 3 at 11:06

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

Thank you thats great!! What would you replace "1" instead of so that different personaltrainerID's can be used when different trainers log in?

– Darán
Jan 3 at 11:04

Of course a parameter like ? for a prepared statement.

– Quasimodo's clone
Jan 3 at 11:05

When i include a ? mark on its own no results are returned, should there be another line for binding a parameter? See my edited example above please!

– Darán
Jan 3 at 11:07

Since you have tagged your question to belong to PHP, I expect you want to use mysqli's or DBO's prepare method. php.net/manual/en/mysqli.quickstart.prepared-statements.php / php.net/manual/en/pdo.prepared-statements.php That's highly recommended since interpolating client data directly into SQL would make your app vulnerable to SQL injection attacks.

– Quasimodo's clone
Jan 3 at 11:14

I'm using bind param but after the "i" for integer i dont know what value to pass into it? $query->bind_param("i", $_POST[""]); ----> not sure whats meant to be where post is

– Darán
Jan 3 at 11:18

|
show 3 more comments

SELECT name, age, location FROM Client

INNER JOIN

(

  SELECT personaltrainerID, clientID from Nutrion_Plan 

  UNION DISTINCT 

  SELECT personaltrainerID, clientID from Training_Plan

) u

USING(clientID)

WHERE u.personaltrainerID = ?;

edited Jan 3 at 11:06

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

Thank you thats great!! What would you replace "1" instead of so that different personaltrainerID's can be used when different trainers log in?

– Darán
Jan 3 at 11:04

Of course a parameter like ? for a prepared statement.

– Quasimodo's clone
Jan 3 at 11:05

When i include a ? mark on its own no results are returned, should there be another line for binding a parameter? See my edited example above please!

– Darán
Jan 3 at 11:07

Since you have tagged your question to belong to PHP, I expect you want to use mysqli's or DBO's prepare method. php.net/manual/en/mysqli.quickstart.prepared-statements.php / php.net/manual/en/pdo.prepared-statements.php That's highly recommended since interpolating client data directly into SQL would make your app vulnerable to SQL injection attacks.

– Quasimodo's clone
Jan 3 at 11:14

I'm using bind param but after the "i" for integer i dont know what value to pass into it? $query->bind_param("i", $_POST[""]); ----> not sure whats meant to be where post is

– Darán
Jan 3 at 11:18

|
show 3 more comments

SELECT name, age, location FROM Client

INNER JOIN

(

  SELECT personaltrainerID, clientID from Nutrion_Plan 

  UNION DISTINCT 

  SELECT personaltrainerID, clientID from Training_Plan

) u

USING(clientID)

WHERE u.personaltrainerID = ?;

edited Jan 3 at 11:06

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

SELECT name, age, location FROM Client

INNER JOIN

(

  SELECT personaltrainerID, clientID from Nutrion_Plan 

  UNION DISTINCT 

  SELECT personaltrainerID, clientID from Training_Plan

) u

USING(clientID)

WHERE u.personaltrainerID = ?;

edited Jan 3 at 11:06

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

edited Jan 3 at 11:06

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

answered Jan 3 at 11:01

Quasimodo's clone

4,66221131

Thank you thats great!! What would you replace "1" instead of so that different personaltrainerID's can be used when different trainers log in?

– Darán
Jan 3 at 11:04

Of course a parameter like ? for a prepared statement.

– Quasimodo's clone
Jan 3 at 11:05

When i include a ? mark on its own no results are returned, should there be another line for binding a parameter? See my edited example above please!

– Darán
Jan 3 at 11:07

Since you have tagged your question to belong to PHP, I expect you want to use mysqli's or DBO's prepare method. php.net/manual/en/mysqli.quickstart.prepared-statements.php / php.net/manual/en/pdo.prepared-statements.php That's highly recommended since interpolating client data directly into SQL would make your app vulnerable to SQL injection attacks.

– Quasimodo's clone
Jan 3 at 11:14

I'm using bind param but after the "i" for integer i dont know what value to pass into it? $query->bind_param("i", $_POST[""]); ----> not sure whats meant to be where post is

– Darán
Jan 3 at 11:18

|
show 3 more comments

Thank you thats great!! What would you replace "1" instead of so that different personaltrainerID's can be used when different trainers log in?

– Darán
Jan 3 at 11:04

Of course a parameter like ? for a prepared statement.

– Quasimodo's clone
Jan 3 at 11:05

When i include a ? mark on its own no results are returned, should there be another line for binding a parameter? See my edited example above please!

– Darán
Jan 3 at 11:07

Since you have tagged your question to belong to PHP, I expect you want to use mysqli's or DBO's prepare method. php.net/manual/en/mysqli.quickstart.prepared-statements.php / php.net/manual/en/pdo.prepared-statements.php That's highly recommended since interpolating client data directly into SQL would make your app vulnerable to SQL injection attacks.

– Quasimodo's clone
Jan 3 at 11:14

I'm using bind param but after the "i" for integer i dont know what value to pass into it? $query->bind_param("i", $_POST[""]); ----> not sure whats meant to be where post is

– Darán
Jan 3 at 11:18

Thank you thats great!! What would you replace "1" instead of so that different personaltrainerID's can be used when different trainers log in?

– Darán
Jan 3 at 11:04

Of course a parameter like ? for a prepared statement.

– Quasimodo's clone
Jan 3 at 11:05

When i include a ? mark on its own no results are returned, should there be another line for binding a parameter? See my edited example above please!

– Darán
Jan 3 at 11:07

Since you have tagged your question to belong to PHP, I expect you want to use mysqli's or DBO's prepare method. php.net/manual/en/mysqli.quickstart.prepared-statements.php / php.net/manual/en/pdo.prepared-statements.php That's highly recommended since interpolating client data directly into SQL would make your app vulnerable to SQL injection attacks.

– Quasimodo's clone
Jan 3 at 11:14

I'm using bind param but after the "i" for integer i dont know what value to pass into it? $query->bind_param("i", $_POST[""]); ----> not sure whats meant to be where post is

– Darán
Jan 3 at 11:18

|
show 3 more comments

Let's see if I understand your request correctly ...

Show only clients that have a nutrition or training plan by trainer 123:

select *

from client

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

Show these clients along with all their plans (regardless of the plans' trainers):

select *

from client

join

(

  select 'nutrition' as kind, nutritionplanid as id, personaltrainerid, clientid

  from nutrition_plan

  union all

  select 'training' as kind, trainingplanid as id, personaltrainerid, clientid

  from training_plan

) plan using (clientid)

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

That looks good thank you! when the pesonaltrainerID is coming from the database and could be any number, what would it be instead of '123' for instance? Would it be '?'

– Darán
Jan 3 at 10:33

It's coming from the database? What does it mean? From another table, e.g. select personaltrainerid from personal_trainer where name = 'John Smith'? Then you can simply replace 123 with this subquery in parantheses: ... where personaltrainerid = (select personaltrainerid from personal_trainer where name = 'John Smith'));

– Thorsten Kettner
Jan 3 at 10:36

Great thank you! so i should take your second example that should work?

– Darán
Jan 3 at 10:40

If this is what you want. Try it and check the results. The query gets you all clients with all their plans, provided at least one of the client's plans is by the desired trainer.

– Thorsten Kettner
Jan 3 at 10:42

I get you thank you, I tried the first query and no errors were present but the output of the client wasnt displayed in the table!

– Darán
Jan 3 at 10:45

|
show 5 more comments

Let's see if I understand your request correctly ...

Show only clients that have a nutrition or training plan by trainer 123:

select *

from client

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

Show these clients along with all their plans (regardless of the plans' trainers):

select *

from client

join

(

  select 'nutrition' as kind, nutritionplanid as id, personaltrainerid, clientid

  from nutrition_plan

  union all

  select 'training' as kind, trainingplanid as id, personaltrainerid, clientid

  from training_plan

) plan using (clientid)

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

That looks good thank you! when the pesonaltrainerID is coming from the database and could be any number, what would it be instead of '123' for instance? Would it be '?'

– Darán
Jan 3 at 10:33

It's coming from the database? What does it mean? From another table, e.g. select personaltrainerid from personal_trainer where name = 'John Smith'? Then you can simply replace 123 with this subquery in parantheses: ... where personaltrainerid = (select personaltrainerid from personal_trainer where name = 'John Smith'));

– Thorsten Kettner
Jan 3 at 10:36

Great thank you! so i should take your second example that should work?

– Darán
Jan 3 at 10:40

If this is what you want. Try it and check the results. The query gets you all clients with all their plans, provided at least one of the client's plans is by the desired trainer.

– Thorsten Kettner
Jan 3 at 10:42

I get you thank you, I tried the first query and no errors were present but the output of the client wasnt displayed in the table!

– Darán
Jan 3 at 10:45

|
show 5 more comments

Let's see if I understand your request correctly ...

Show only clients that have a nutrition or training plan by trainer 123:

select *

from client

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

Show these clients along with all their plans (regardless of the plans' trainers):

select *

from client

join

(

  select 'nutrition' as kind, nutritionplanid as id, personaltrainerid, clientid

  from nutrition_plan

  union all

  select 'training' as kind, trainingplanid as id, personaltrainerid, clientid

  from training_plan

) plan using (clientid)

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

Let's see if I understand your request correctly ...

Show only clients that have a nutrition or training plan by trainer 123:

select *

from client

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

Show these clients along with all their plans (regardless of the plans' trainers):

select *

from client

join

(

  select 'nutrition' as kind, nutritionplanid as id, personaltrainerid, clientid

  from nutrition_plan

  union all

  select 'training' as kind, trainingplanid as id, personaltrainerid, clientid

  from training_plan

) plan using (clientid)

where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)

   or clientid in (select clientid from training_plan where personaltrainerid = 123);

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

answered Jan 3 at 10:26

Thorsten Kettner

53k32643

That looks good thank you! when the pesonaltrainerID is coming from the database and could be any number, what would it be instead of '123' for instance? Would it be '?'

– Darán
Jan 3 at 10:33

It's coming from the database? What does it mean? From another table, e.g. select personaltrainerid from personal_trainer where name = 'John Smith'? Then you can simply replace 123 with this subquery in parantheses: ... where personaltrainerid = (select personaltrainerid from personal_trainer where name = 'John Smith'));

– Thorsten Kettner
Jan 3 at 10:36

Great thank you! so i should take your second example that should work?

– Darán
Jan 3 at 10:40

If this is what you want. Try it and check the results. The query gets you all clients with all their plans, provided at least one of the client's plans is by the desired trainer.

– Thorsten Kettner
Jan 3 at 10:42

I get you thank you, I tried the first query and no errors were present but the output of the client wasnt displayed in the table!

– Darán
Jan 3 at 10:45

|
show 5 more comments

That looks good thank you! when the pesonaltrainerID is coming from the database and could be any number, what would it be instead of '123' for instance? Would it be '?'

– Darán
Jan 3 at 10:33

It's coming from the database? What does it mean? From another table, e.g. select personaltrainerid from personal_trainer where name = 'John Smith'? Then you can simply replace 123 with this subquery in parantheses: ... where personaltrainerid = (select personaltrainerid from personal_trainer where name = 'John Smith'));

– Thorsten Kettner
Jan 3 at 10:36

Great thank you! so i should take your second example that should work?

– Darán
Jan 3 at 10:40

If this is what you want. Try it and check the results. The query gets you all clients with all their plans, provided at least one of the client's plans is by the desired trainer.

– Thorsten Kettner
Jan 3 at 10:42

I get you thank you, I tried the first query and no errors were present but the output of the client wasnt displayed in the table!

– Darán
Jan 3 at 10:45

That looks good thank you! when the pesonaltrainerID is coming from the database and could be any number, what would it be instead of '123' for instance? Would it be '?'

– Darán
Jan 3 at 10:33

It's coming from the database? What does it mean? From another table, e.g. select personaltrainerid from personal_trainer where name = 'John Smith'? Then you can simply replace 123 with this subquery in parantheses: ... where personaltrainerid = (select personaltrainerid from personal_trainer where name = 'John Smith'));

– Thorsten Kettner
Jan 3 at 10:36

Great thank you! so i should take your second example that should work?

– Darán
Jan 3 at 10:40

If this is what you want. Try it and check the results. The query gets you all clients with all their plans, provided at least one of the client's plans is by the desired trainer.

– Thorsten Kettner
Jan 3 at 10:42

I get you thank you, I tried the first query and no errors were present but the output of the client wasnt displayed in the table!

– Darán
Jan 3 at 10:45

|
show 5 more comments

This should work

answered Jan 3 at 10:14

Kashan

101111

Thanks Kashan its almost there! However what is the "id" at the end relating too? thats where my error is, personaltrainerID?

– Darán
Jan 3 at 10:21

this should actually display that through his personal id or name

– Kashan
Jan 3 at 10:28

I get you, it doesnt throw any errors and runs, however no data is displayed in the table!

– Darán
Jan 3 at 10:34

1

@Darán: This query gives you one personal trainer, their nutrition plans and the related clients. If you take the query as is, i.e. with "id" literally then you would look up trainer ID 0, because "id" means a column named id, and as this doesn't exist, MySQL defaults to a string 'id' instead, and as this isn't a number, it gets converted to 0.

– Thorsten Kettner
Jan 3 at 10:45

add a comment |

This should work

answered Jan 3 at 10:14

Kashan

101111

Thanks Kashan its almost there! However what is the "id" at the end relating too? thats where my error is, personaltrainerID?

– Darán
Jan 3 at 10:21

this should actually display that through his personal id or name

– Kashan
Jan 3 at 10:28

I get you, it doesnt throw any errors and runs, however no data is displayed in the table!

– Darán
Jan 3 at 10:34

1

@Darán: This query gives you one personal trainer, their nutrition plans and the related clients. If you take the query as is, i.e. with "id" literally then you would look up trainer ID 0, because "id" means a column named id, and as this doesn't exist, MySQL defaults to a string 'id' instead, and as this isn't a number, it gets converted to 0.

– Thorsten Kettner
Jan 3 at 10:45

add a comment |

This should work

answered Jan 3 at 10:14

Kashan

101111

This should work

answered Jan 3 at 10:14

Kashan

101111

answered Jan 3 at 10:14

Kashan

101111

answered Jan 3 at 10:14

Kashan

101111

answered Jan 3 at 10:14

Kashan

101111

Thanks Kashan its almost there! However what is the "id" at the end relating too? thats where my error is, personaltrainerID?

– Darán
Jan 3 at 10:21

this should actually display that through his personal id or name

– Kashan
Jan 3 at 10:28

I get you, it doesnt throw any errors and runs, however no data is displayed in the table!

– Darán
Jan 3 at 10:34

1

@Darán: This query gives you one personal trainer, their nutrition plans and the related clients. If you take the query as is, i.e. with "id" literally then you would look up trainer ID 0, because "id" means a column named id, and as this doesn't exist, MySQL defaults to a string 'id' instead, and as this isn't a number, it gets converted to 0.

– Thorsten Kettner
Jan 3 at 10:45

add a comment |

Thanks Kashan its almost there! However what is the "id" at the end relating too? thats where my error is, personaltrainerID?

– Darán
Jan 3 at 10:21

this should actually display that through his personal id or name

– Kashan
Jan 3 at 10:28

I get you, it doesnt throw any errors and runs, however no data is displayed in the table!

– Darán
Jan 3 at 10:34

1

@Darán: This query gives you one personal trainer, their nutrition plans and the related clients. If you take the query as is, i.e. with "id" literally then you would look up trainer ID 0, because "id" means a column named id, and as this doesn't exist, MySQL defaults to a string 'id' instead, and as this isn't a number, it gets converted to 0.

– Thorsten Kettner
Jan 3 at 10:45

Thanks Kashan its almost there! However what is the "id" at the end relating too? thats where my error is, personaltrainerID?

– Darán
Jan 3 at 10:21

this should actually display that through his personal id or name

– Kashan
Jan 3 at 10:28

I get you, it doesnt throw any errors and runs, however no data is displayed in the table!

– Darán
Jan 3 at 10:34

@Darán: This query gives you one personal trainer, their nutrition plans and the related clients. If you take the query as is, i.e. with "id" literally then you would look up trainer ID 0, because "id" means a column named id, and as this doesn't exist, MySQL defaults to a string 'id' instead, and as this isn't a number, it gets converted to 0.

– Thorsten Kettner
Jan 3 at 10:45

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu