Member Avatar for smms

Hello world,

1. I am building a website to list universities worldwide by majors and countries. To do that I have a page containing name of countries - say 50 countries.

2. Let's say, from the list you clicked, Australia, it will then take you to a page containing list of Engineering majors - say the number is 15.

3. Now say, you clicked "Aerospace Engineering" in the list. The link will take you to a page containing, say, 15 Australian universities that offer Aerospace Engineering.

Now, comes my question. My very little knowledge about web dev tells me, I have to create 50 x 15 x 15 pages and link them, means, creating 11250 pages! I am sure people came across to this sort of problems long time back and now it already has a very well known solution. So what are the solutions I am looking at?

Many thanks for your kind replies!

  • 4 Contributors
  • 4 Replies
  • 110 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by samsnov

Dani AI

Generated

— you do not need 11,250 static pages. Build a small set of templates and drive them from a normalized database. and were pointing in the right direction; below is a concrete, practical schema and a few implementation tips that fill the gaps in the replies.

CREATE TABLE countries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE majors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE universities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  country_id INT NOT NULL,
  FOREIGN KEY (country_id) REFERENCES countries(id)
);

CREATE TABLE university_major (
  university_id INT NOT NULL,
  major_id INT NOT NULL,
  PRIMARY KEY (university_id, major_id),
  FOREIGN KEY (university_id) REFERENCES universities(id),
  FOREIGN KEY (major_id) REFERENCES majors(id)
);

Example query (use prepared statements and pass slugs or IDs from the URL):

SELECT u.*
FROM universities u
JOIN university_major um ON um.university_id = u.id
JOIN majors m ON m.id = um.major_id
JOIN countries c ON c.id = u.country_id
WHERE c.slug = ? AND m.slug = ?
LIMIT 50;

Routing, UX and performance notes: use clean URLs like /country/australia/major/aerospace and build links in templates from slug values (this answers about linking). Index slug and foreign-key columns, paginate results, and cache expensive counts. Always use prepared statements to avoid SQL injection (see PHP PDO prepared statements) and enforce integrity with foreign keys (see MySQL foreign key docs). Plan for data cleanup (duplicate major names, synonyms) and consider a framework or CMS to speed development if needed.

Recommended Answers

All 4 Replies

Member Avatar for kaizerkiller

Hello world,

1. I am building a website to list universities worldwide by majors and countries. To do that I have a page containing name of countries - say 50 countries.

2. Let's say, from the list you clicked, Australia, it will then take you to a page containing list of Engineering majors - say the number is 15.

3. Now say, you clicked "Aerospace Engineering" in the list. The link will take you to a page containing, say, 15 Australian universities that offer Aerospace Engineering.

Now, comes my question. My very little knowledge about web dev tells me, I have to create 50 x 15 x 15 pages and link them, means, creating 11250 pages! I am sure people came across to this sort of problems long time back and now it already has a very well known solution. So what are the solutions I am looking at?

Many thanks for your kind replies!

Although I'm not yet good in making large databases, I want to share my idea on your post. :| You don't have to create 11250 PAGES, you talking about a static website. Try making a dynamic website. You need to create a dynamic page that list all 50 countries, 1 result page that list the major courses that is offered on that country, 1 result page for the list of universities of that offers that particular course. My idea is just sort of a draft for you.

Member Avatar for rch1231

Hello,

More like 3 page by dynamic pages that are created on the fly by php querying the database. I would set up a table for each of the selection criteria. Table 1 Countries, Table 2 Majors by country, Table 3 Universities including country and majors.

The code is not that difficult to write however there are a million example out there. See if you can find a copy of the PHP cookbook. It give your excellent code examples that you can simply adjust for your needs.

Member Avatar for smms

Thanks for the right direction. After few google and wiki, I understand, the solution is learning Mysql for database and PHP for dynamic website. Am I on the right track?

Member Avatar for samsnov

Although I'm not yet good in making large databases, I want to share my idea on your post. :| You don't have to create 11250 PAGES, you talking about a static website. Try making a dynamic website. You need to create a dynamic page that list all 50 countries, 1 result page that list the major courses that is offered on that country, 1 result page for the list of universities of that offers that particular course. My idea is just sort of a draft for you.

hi if i understod you well , you want to find out how to link those sites togather ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.