How to get value from multiple SQL table and show that in html/php table?

Question

I have a SQL database named result having table res and sub. For example, The res table column and contents are:

sno regno        name       sub1  sub2  sub3  sub4  sub5  

1   1DU12CS100   student1   70    80    85    70    90     
2   1DU12CS101   student2   75    70    90    80    70
3   1DU12EE015   student3   80    85    70    50    65
4   1DU14CS123   student4   88    85    85    90    70
5   1DU13ME050   student5   85    90    70    60    55

The sub table column and contents are:

Sno   batname     sub1      sub2       sub3       sub4       sub5

1     1DU12CS     Maths     English    Hindi      Urdu       Social 
2     1DU12ME     Sanksrit  Chinese    Japanese   French     Dutch
3     1DU12EE     Circuit   Electrical Electronic Maths      Hindi
4     1DU14CS     Hindi     Maths      Urdu       Science    Maths
5     1DU13ME     Computer  Maths      Electrical Mechanical GK

I want to fetch some value from table res and some from table sub and display in php/html table.

1DU12CS100  -- 
                                   1DU ->college code
                                   12 ->Student admission year
                                   CS ->computer science
                                   100->roll no of student

When someone enters 1DU12CS100 in php form, the result should be displayed like this...

Subjects    Marks

Maths       70
English     80
Hindi       85
Urdu        70
Social      90

And when someone enters 1DU13ME050, then the display should be

Subjects      Marks

Computer      85
Maths         90
Electrical    70
Mechanical    60
GK            55

The php form code is

<!DOCTYPE HTML>
<html> 
<body>

<form action="result.php" method="post">
Enter your Reg No: <input type="text" name="regno"><br>
<input type="submit">
</form>

</body>
</html>

The result.php code is // What changes should be made in this php code ??

<?php
$servername = "localhost";
$username = "myresult";
$password = "abcdefg";
$dbname = "myresult";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$regno = mysqli_real_escape_string($conn, $_POST['regno']);

$sql = "SELECT * FROM myresult WHERE regno LIKE '$regno'"; // What changes should be here ??
$result = $conn->query($sql);
$columns = array();
$resultset = array();
while ($row = mysql_fetch_assoc($result)) {
    if (empty($columns)) {
        $columns = array_keys($row);
    }
    $resultset[] = $row;
}


if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "{$row['sub1']}{$row['sub2']}"; // What changes should be made here??



// Print the data
while($row = mysql_fetch_row($result)) {
    foreach($row as $_column) {
        echo "{$_column}";
    }
}
    }
} else {
    echo "Result Not Found";
}
$conn->close();

?>

Using substrings of id is generally frowned on. Instead store a batname id and a student id separately. Much easier in Sook many ways. — Peter Bowers
– Peter Bowers, Commented Mar 21, 2015 at 20:08
exactly what Peter Bowers just said. You can grab substrings, but you have to be absolutely certain that they are the ONLY place they exist. I strongly suggest expanding the database and using that instead of relying on your regno field; unless you are forced to (this looks like a remnant of old punch card style data management), I'd restructure your data. — nomistic
– nomistic, Commented Mar 21, 2015 at 20:33
"Normalization " is the term you should research on Google & find some articles or tutorials. Restructuring your database well is going to pay huge dividends in the future ... it only gets harder the longer you wait... — Peter Bowers
– Peter Bowers, Commented Mar 21, 2015 at 21:42
Thank you everyone for help. I am very new to PHP and SQL. What I have done is from searching on Google and Learning it from some websites... What I want is you can see from here results.vtu.ac.in/vitavi.php and check by entering 1DS13CS001, 1NH13CS052, 1OX13CS001 etc.. 1 is code for Bangalore region, Next 2 Letter is college code, Next 2 letter is year of admission, Next 2 is the branch/course code, Next 3 is Registration Number... You can check college code from here ... vtu.ac.in/affiliated-institutes/affiliated-institutes-bengaluru — alamirfan
– alamirfan, Commented Mar 22, 2015 at 9:08
I don't know the best method to arrange database for that and fetch results from it... Any help in this regard would be highly appreciated ! — alamirfan
– alamirfan, Commented Mar 22, 2015 at 9:14

Peter Bowers · Accepted Answer · 2015-03-22 12:33:00Z

1

$sql = "SELECT sub.batname, sub.sub1 as subsub1, sub.sub2 as subsub2, ...,
        Res.sub1 as ressub1, ...
    FROM res 
    JOIN sub ON sub.batname = substr(res.regno, 1, 7)
    WHERE regno LIKE '$regno'";

(I haven't listed all the columns. Since you named the columns the same you have to specify aliases. Changing the names would be a good idea - subn isn't a great name. )

Then down below ...

while ($row = $result->fetch_assoc()) { 
    echo "{$row['ressub1']}{$row['subsub1']}";

edited Mar 22, 2015 at 12:33

answered Mar 21, 2015 at 20:12

Peter Bowers

3,1031 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

alamirfan Over a year ago

What I really wanted is fully understood by you and I was sure that your code will definitely fulfill my need but one problem arises is...The error I am getting from phpmyadmin SQL box is #1305 - FUNCTION akfashio_result.substr does not exist. Even from result.php page I am getting... Result Not Found

Peter Bowers Over a year ago

copy/paste the exact query you tried in phpmyadmin into a comment here.

alamirfan Over a year ago

Sorry there was some mistake... Earlier I had executed subm.batname = submstr(res.regno, 0, 7) because my table name was subm so I thought it should be submstr but later I got to know that substr is an inbuilt function...

alamirfan Over a year ago

I have created a separate table sub and res ...Now I have executed... SELECT sub.batname, sub.sub1 AS subsub1, res.sub1 AS ressub1 FROM res JOIN sub ON sub.batname = substr( res.regno, 0, 7 ) WHERE regno LIKE '1DU12CS100'; But the result is MySQL returned an empty result set (i.e. zero rows). (Query took 0.0010 sec)

alamirfan Over a year ago

Ya I got.... sub.batname = substr( res.regno, 1, 7 )... It should be 1,7 not 0,7... I didn't know about the term substr hence I was thinking how can I do this... But when I knew that it is a function then I searched on google and found dev.mysql.com/doc/refman/5.1/en/… and then I changed the code.... now it is working...

|

Roman Navrátil · Accepted Answer · 2015-03-21 20:14:08Z

0

I just that inner join in your sql query should do it. Somethink like:

SELECT * FROM res r INNER JOIN sub s ON r.id = s.id WHERE regno LIKE '1DU12CS100';

answered Mar 21, 2015 at 20:14

Roman Navrátil

522 bronze badges

1 Comment

alamirfan Over a year ago

You didn't understand my question... The SNo is just for deleting or editing the row... I tried your code and It is not working...what I want is when someone enters 1DU12CS100 then subjects are fetched from sub table where batname is 1DU12CS and results are fetched from res table where regno is 1DU12CS100...

nomistic · Accepted Answer · 2015-03-21 20:27:15Z

0

This only looks like the beginning (there's too much to do here) but for starters, you can modify your SQL query at the beginning to something like this, since batname appears to exist as the beginning of all regno variables

$sql = "SELECT regno as batname FROM myresult WHERE regno LIKE '{$regno}%'";

Then you could turn this into a variable such as $batname = $row['batname']

and then you can use $batname as a method of linking the data between the tables. However, you are going to need to do a bit more, as there are some exact strings which exist that mean something. so you will need to identify specific strings at certain places within $batname to identify their meaning. This will have to be broken apart itself. It be a good idea to create a new table that identifies the subject names to match the strings that created off of this.

In other words, your script is really just a beginning. Once you have a way of identifying each element within a string (again a lookup table would make your life a lot easier), you will be able to start working on your php scripting.

Here's an example of a lookup table you could use

    code    name
    ---     ----
    CS      Computer Science

etc (it is hard for me to tell what is going on with those codes, but I take you know what they all mean). You should probably pull this data for every piece that means something.

answered Mar 21, 2015 at 20:27

nomistic

2,9624 gold badges23 silver badges37 bronze badges

1 Comment

alamirfan Over a year ago

Thank you for your help and teaching what should be done in this regards... But I am very new to this so I can not do it by my own or I don't have idea how to implement this in database... My main aim now is, when someone enters 1DU12CS100 then subjects are fetched from sub table where batname is 1DU12CS and results are fetched from res table where regno is 1DU12CS100 and result should be displayed in php form... Please help me with the code ....

Collectives™ on Stack Overflow

How to get value from multiple SQL table and show that in html/php table?

3 Answers 3

9 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related