How to limit/select database results shown on webpage?

MySQL database
1

Hi! :blush: On my webpage I’m posting database inputs related to the UN sustainability goals. I have a page dedicated to each goal, but I only want to show subsidiary goals from my database relating to a specific goal. Currently its posting ALL subsidiary goals(with numbers 1, 2, 3, 4 etc.) on my page for main goal 1.I only want subsidiary goals with the number 1 to be shown on the page for main goal 1 etc..
If I only use LIMIT:

billede
billede610×269 19.9 KB

The same four or so subsidiary goals are shown on all pages.
How do I further limit/select the database results posted so that only results with a specific number in my database are shown on the page? :pray:

2

I am not sure what you are asking help for, but, I will guess.

You have a table with goals listed. Normally, you would have a second table with the subsidiary goals in it.
Or better, just one big table with a structure like this:
goal_id
goal
sub_goad

And, then, when the user goes to the page, you run a query, simple to get all the unique goals, like this:
SELECT * FROM goal_table GROUP BY goal
This would return a list of all your goals which can be displayed to let the user pick the main goal.
You would keep that selection for listing the sub-goals for it. Then, when you need the sub-goal list,
you run a query something like:
SELECT * FROM goal_table WHERE goal=$selected_goal
If you keep track of the selected main goal, you can get all the sub goals using that in your query.

I hope the English explanation is clear to you. If you get stuck, post your code, not an image!

3

Thank you for replying! :blush: I’m very new to PHP, so I’m sorry if I didn’t articulate my problem very well: All the goals have a number 1, 2, 3 etc. and subgoals have a number relating to that number like 1.1, 1.2, 2.1, 2.2 etc. the numbers are linked to each other from two different tables(world_goal and world_subgoal) in my database. I just dont know how to only display subgoals with the first number as 1 on the page about goal 1, the ones starting with 2 on the page for main goal 2 and so on - currently all subgoals are displayed on every page for a main goal so subgoals 2.1 are also on the page for main goal 1 etc. Here is my code in english:
$query = “SELECT
world_subgoal.id,
world_subgoal.number,
world_subgoal.text,
world_goal.number AS goal_number
FROM world_subgoal, world_goal
WHERE world_subgoal.id_goal = world_goal.id
ORDER BY goal_number, number”;
I have been told that I need to make a SELECT clause that puts the the tables together to extract only the subgoals with correlating numbers to the main goal, I just can’t figure out how while actually making it work :grimacing:

4

You need another part in your WHERE clause, which will filter only subsidiaries linked to the correct goal. Something like so:

SELECT
world_subgoal.id,
world_subgoal.number,
world_subgoal.text,
world_goal.number AS goal_number

FROM world_subgoal, world_goal

/* You can use AND or OR to add other clauses to your WHERE statement. */
WHERE world_subgoal.id_goal = world_goal.id
AND world_goal.id = /* add your goal id here */

ORDER BY goal_number, number

Make sure you use prepared statements with parameter injection when using dynamic queries. The precise implementation will be different depending on which db connection method you’re using - mysqli or PDO - so it’s up to you to find out the best practice for their use. https://phpdelusions.net/ has a PDO and a mysqli page, and https://phptherightway.com/ is also a good resource.

5

Well, Ann, we can help you, but, perhaps I am not clear. To help you, we need to understand your database tables. You are combining two tables, but, not in the correct manner. In your first post you are using WITH and in the last post, you put two tables in the FROM section. You can not do either of these. At least not the way you are using them. I will suggest some things to help you decide how to handle this.

First, are you just trying to list all the sub-goals for EVERY main-goal? OR, are you trying to just list the sub-goals for one main-goal that has already been selected?

If you already know the one main-goal, you would just use a simple query to the world_subgoal table to get all the correct data from that one main-goal number.

If you need to list all of the main-goals and show the sub-goals for each, you need to either do one main-goal at a time, or create a result array that contains all of the possible data. If you do that, you need to understand how to group the data resulting from the query. One problem with this is you must order the data correctly.

The easiest way is to use two queries and two loops. But, if you want to JOIN the two tables, we can do it that way also. I will attempt to explain both ways. First, the easy way:

(Not real code, just examples…)
SELECT id FROM world_goal ( Gets a list of ALL goals ) (Gets all main-goals)
Loop thru the results goal-by-goal
Use the world_goal_id to get all sub-goals
SELECT * FROM world_subgoal WHERE id_goal=$row[‘id’] (Gets all sub-goals for this one goal in loop)
Loop thru this second results sub-goal-by-sub-goal and display each

This way is simple enough with two queries and two resulting sets of data. Small number of lines.

The next way would be to JOIN two tables and you will get an array of data combined from both of your tables. You would get a final array that would include all the main-goals and all of the sub-goals. But, this way is harder to display since you must go thru each row of the results and figure out when to break on the main-goal id number. If you join two tables, you would get results loosely like…
main-goal-id, sub-goal-number
So, displaying them you would have to parse thru them with them ordered by main-goal. Then, list all of the sub-goals for each one. How to handle this depends on how you want to display them. Either way works.

6

Thank you again! I think I understand what you mean, however, I’m still not really sure what to write in my actual code. As you write, I am trying to just list the sub-goals for one main-goal that has already been selected. The table for the main goal has an id column. The table for the sub-goals has its own id-column too, but it is related to the first table by having a column called “id_maingoal” and the id_maingoal corresponds to the equivalent id in the table for main goals. By clicking on a main goal on my webpage, you are suppossed to get redirected to another page showing the subgoals for only that specific main goal. But it displays all sub goals for all main goals, when I only want it to show the actual related sub goals the specific main goal on the page.
I have tried writing this instead:
“SELECT *
FROM world_subgoal, world_goal
WHERE world_subgoal.id_maingoal = main_goal.id
AND main_goal.id = ‘1’
ORDER BY main_goal”;
But then the sub goals that also function as links to read more about the sub goals, link you to random sub goal pages since that all have the same id_maingoal.
Is there a difference between using loops and JOIN? Can I write the two SELECT clauses you mention in the same $query?

7

Is there a difference between using loops and JOIN?

Yes. Loops are an imperative language (eg php) construct, JOINs are an SQL construct.

I think the confusion here is over what you’re trying to get back. To get a list of sub goals, you don’t need to select from two tables; just select from the sub goal table, where the main goal id matches the one you want:

SELECT id, number, text
FROM world_subgoal
WHERE id_goal = /* add your world goal id here */

If that doesn’t work, post your actual table structures to give us some more to go on.

1 Like
8

I used your example on a different project and it worked! Thank you :blush: