need to print data using joins

[satyasampan]

two tables with columns
studentdetails
*rollnum
*name
*section
*mobile

studentmarks
*rollnum
*maths
*physics
*chemistry


roll num in both are same
now i need a php sql query such that if i submit rollnum,i need to get all the data of that roll num
and that query must be with sql joins

thanks in advance

[Rabbit]

We can't help you fix your code without seeing your code and you telling us what's wrong with it.

[Atli]

One question: Why are those two tables two tables? - What you've described is a 1:1 relationship; one that links two separate parts of the same table. It's usually only done for performance reasons, because it makes little sense from a design perspective.

Is there a reason why you're not storing the marks in the studentdetails table?


Also, your design is pretty static. You're using data as columns in your studentmarks table, which is always a red flag that a design needs an overhaul.

In a more flexible design, you'd be able to add and remove mark types - what you've created as "maths", "physics" and "chemistry" in your design - without having to alter the table. Typically, that would be done with a N:M (many-to-many) relationship. Like:

+-----------------+     +------------------+     +-------------+
| student_details |     | student_marks    |     | mark_types  |
+-----------------+     +------------------+     +-------------+
| rollnum (PK)    |>--->| rollnum (PK, FK) |  |-<| markid (PK) |
| name            |     | markid (PK, FK)  |<-|  | name        |
| section         |     | mark             |     +-------------+
| mobile          |     +------------------+
+-----------------+

There you can easily add more courses to be marked, and not all students would have to have a value for all mark types.

In this scenario, you'd query for marks like this:

SELECT sm.mark, mt.name AS `course`
FROM student_marks AS sm
JOIN mark_types AS mt
    ON sm.markid = mt.markid
WHERE sm.rollnum = :rollnum

Which would return only the marks and their type. Querying for the student info would be done in an entirely separate query.