using php to display mysql table correctly on html

[lawnmowerman]

In real world, the ID column in the database may not necessary be in running order, due to unforseen database issue or other reasons. Another possibility could be someone might have deleted a particular row data intentionally, accidentally or for whatever reason. For example, try delete row 3 with ID 3 and you will see the effect.

One work around is to include the MySQL (if this is the database you are using) row_number() in your SQL query statement and use that sequential value. example:
SELECT row_number(), ID, data FROM mytable

I see, I am calling my row_number function based on ID, which is set as primary key. Does it still have the problem that you mentioned?

BTW my ID is very small. Less than 25 per set. I will assign multiple set of less than 25 data. How should I go about it assignment the set.?

That means, I have a table, let say 25 rows. I want to replicate the table structure, over and over again with different data sets. How should I go about it doing it in mysql. I have one table only now.

Let say, I want to have multiple tables based on day. How do I link this table based on date.? Everyday one table.

 

[davidktw]

I see, I am calling my row_number function based on ID, which is set as primary key. Does it still have the problem that you mentioned?

BTW my ID is very small. Less than 25 per set. I will assign multiple set of less than 25 data. How should I go about it assignment the set.?

That means, I have a table, let say 25 rows. I want to replicate the table structure, over and over again with different data sets. How should I go about it doing it in mysql. I have one table only now.

Let say, I want to have multiple tables based on day. How do I link this table based on date.? Everyday one table.

Your rows counting don't have to have anything to do with your table data/rows. You are just counting the rows you are displaying in your table. Just create a PHP variable and count the row that is echoing/printing in each loop/iteration.

$counter = 0;
while (...) {

    // print your data row

    $counter++;
    if ($counter % 3 == 0 && NOT_LAST_ROW_OF_DATA) {
        // print the separator
    }
}

or

$counter = 0;
while (...) {

   if (($counter > 0) && ($counter % 3 == 0)) {
        // print the separator
    }

    // print your data row

    $counter++;
}

There are different ways to deal with your edge cases.

 

[lawnmowerman]

Your rows counting don't have to have anything to do with your table data/rows. You are just counting the rows you are displaying in your table. Just create a PHP variable and count the row it is echoing/printing in each loop/iteration.

I already finish displaying the table on html page. I use a if statement with $row number as condition to construct the table on html. I show the code later. Now using phone. Not near pc.

 

[davidktw]

I already finish displaying the table on html page. I use a if statement with $row number as condition to construct the table on html. I show the code later. Now using phone. Not near pc.

Sure. I'm just explaining to you what are your options. Apply it in your future works.

 

[lawnmowerman]

Sure. I'm just explaining to you what are your options. Apply it in your future works.

here is my code, looks tedious, but it serve the purpose.

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        if ($row["id"] == 1){
        echo "<tr>";
        echo "<td>CLASS　A</td>";
        echo "<td>" . $row["number"] . "</td>";   
        echo "</tr>";       
        }
        if ($row["id"] == 2){
            echo "<tr>";
            echo "<td>CLASS B</td>";
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
        if ($row["id"] == 3){
            echo "<tr>";
            echo "<td>CLASS C</td>";
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
            echo "<tr colspan=2><td>BLANK</td></tr>";
        }
                                
        if ($row["id"] == 4){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 5){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 6){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 7){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 8){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }

        if ($row["id"] == 9){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 10){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 11){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 12){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 13){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
                                        
        if ($row["id"] == 13){
            echo "<tr colspan=2><td>BLANK</td></tr>";
        }

        if ($row["id"] == 14){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 15){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 16){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 17){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 18){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
        
        if ($row["id"] == 19){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 20){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 21){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 22){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 23){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }

    }
}
else {
    echo "0 results";
}

 

[lawnmowerman]

Your rows counting don't have to have anything to do with your table data/rows. You are just counting the rows you are displaying in your table. Just create a PHP variable and count the row that is echoing/printing in each loop/iteration.

$counter = 0;
while (...) {

    // print your data row

    $counter++;
    if ($counter % 3 == 0 && NOT_LAST_ROW_OF_DATA) {
        // print the separator
    }
}

or

$counter = 0;
while (...) {

   if (($counter > 0) && ($counter % 3 == 0)) {
        // print the separator
    }

    // print your data row

    $counter++;
}

There are different ways to deal with your edge cases.

my row spacing is not uniform, that means is not every 3 row to be exact. my code is below, it is very mechanical way of doing it, i try doing a nested while loop, but can't get it work, so I use multiple if statement. lol

 

[davidktw]

here is my code, looks tedious, but it serve the purpose.

Spoiler

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        if ($row["id"] == 1){
        echo "<tr>";
        echo "<td>CLASS　A</td>";
        echo "<td>" . $row["number"] . "</td>";  
        echo "</tr>";      
        }
        if ($row["id"] == 2){
            echo "<tr>";
            echo "<td>CLASS B</td>";
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
        if ($row["id"] == 3){
            echo "<tr>";
            echo "<td>CLASS C</td>";
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
            echo "<tr colspan=2><td>BLANK</td></tr>";
        }
                               
        if ($row["id"] == 4){
            echo "<tr>";                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 5){                          
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 6){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 7){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 8){                          
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }

        if ($row["id"] == 9){
            echo "<tr>";                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 10){                          
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 11){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 12){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 13){                          
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
                                       
        if ($row["id"] == 13){
            echo "<tr colspan=2><td>BLANK</td></tr>";
        }

        if ($row["id"] == 14){
            echo "<tr>";                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 15){                          
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 16){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 17){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 18){                          
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
       
        if ($row["id"] == 19){
            echo "<tr>";                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 20){                          
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 21){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 22){                      
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 23){                          
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }

    }
}
else {
    echo "0 results";
}

Not a smart way of doing software development

 

[lawnmowerman]

Not a smart way of doing software development

i know, I have been trial and error until late last night, if I have the time to do it again, will troubleshoot and optimize it, if I do nested loop (while and if), my output table always out of alignment. lol, spending too much time fixing it but failed.

 

[davidktw]

i know, I have been trial and error until late last night, if I have the time to do it again, will troubleshoot and optimize it, if I do nested loop (while and if), my output table always out of alignment. lol, spending too much time fixing it but failed.

Try harder on your work, I believe is your course work. What you need to work on is your looping and condition. The method you are doing will not suffice.

In any case, just to open up your mind further. In web technologies, CSS and JS are powerful tools at your disposal too. In modern web design, we often leave it to frontend styling. Of course that doesn't mean backend styling is obsolete, there are still very good use cases for them. I'm just opening up possibilities for your imagination. The whole idea is balance computation between frontend and backend, let the technologies at each end share responsibilities to serve the end-users.

 

[lawnmowerman]

Try harder on your work, I believe is your course work. What you need to work on is your looping and condition. The method you are doing will not suffice.

In any case, just to open up your mind further. In web technologies, CSS and JS are powerful tools at your disposal too. In modern web design, we often leave it to frontend styling. Of course that doesn't mean backend styling is obsolete, there are still very good use cases for them. I'm just opening up possibilities for your imagination. The whole idea is balance computation between frontend and backend, let the technologies at each end share responsibilities to serve the end-users.

this is sort of assignment that given to me with minimal help, have to ask online oldbird for tips and the right directions. hope I am able to think thorough the logic of this nested if and while loop, the stylesheet is also something that I need to learn and tweak a bit.

I will study the stepthrough line by line, it is a good resource to understand the flow.
last night made silly mistake when I just put td, without tr, wasted a lot of my time. thanks again.
have to start studying the coding now. CSS i know a bit, JS is zero knowledge.

 

[lilycll]

here is my code, looks tedious, but it serve the purpose.

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        if ($row["id"] == 1){
        echo "<tr>";
        echo "<td>CLASS　A</td>";
        echo "<td>" . $row["number"] . "</td>";   
        echo "</tr>";       
        }
        if ($row["id"] == 2){
            echo "<tr>";
            echo "<td>CLASS B</td>";
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
        if ($row["id"] == 3){
            echo "<tr>";
            echo "<td>CLASS C</td>";
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
            echo "<tr colspan=2><td>BLANK</td></tr>";
        }
                                
        if ($row["id"] == 4){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 5){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 6){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 7){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 8){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }

        if ($row["id"] == 9){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 10){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 11){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 12){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 13){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
                                        
        if ($row["id"] == 13){
            echo "<tr colspan=2><td>BLANK</td></tr>";
        }

        if ($row["id"] == 14){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 15){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 16){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 17){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 18){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }
        
        if ($row["id"] == 19){
            echo "<tr>";                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 20){                           
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 21){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 22){                       
            echo "<td>" . $row["number"] . "</td>";
        }
        if ($row["id"] == 23){                           
            echo "<td>" . $row["number"] . "</td>";
            echo "</tr>";
        }

    }
}
else {
    echo "0 results";
}

In your line for row id 4, you have a start of table row using <tr> but you did not close it with a </tr> at the end of the block. This could result in incorrect table formatting.

The way I see your codes is not optimised.

Your <tr> and </tr> (<td> and </td> too) could code before and after if block while leaving only the <td> data </td> parts or just data part inside the if block. This way you codes will be even shorter.

The only reason why you need so many if conditions for different id is to produce different result for different id. Otherwise you can try to group them if their outcome is the same.

 

[lawnmowerman]

In your line for row id 4, you have a start of table row using <tr> but you did not close it with a </tr> at the end of the block. This could result in incorrect table formatting.

The way I see your codes is not optimised.

Your <tr> and </tr> (<td> and </td> too) could code before and after if block while leaving only the <td> data </td> parts or just data part inside the if block. This way you codes will be even shorter.

The only reason why you need so many if conditions for different id is to produce different result for different id. Otherwise you can try to group them if their outcome is the same.

you have got eagle vision, to spot on that mistake. I already corrected it, but not yet change the code to optimize the repeating if loops, I willl change it later and submit the newer code here.

every single ID is map to a different result. that is why I use this method to implement the table. but I guess there is still room to optimize coding here. thanks for your feedback! cheers

 

[lilycll]

I see, I am calling my row_number function based on ID, which is set as primary key. Does it still have the problem that you mentioned?

BTW my ID is very small. Less than 25 per set. I will assign multiple set of less than 25 data. How should I go about it assignment the set.?

That means, I have a table, let say 25 rows. I want to replicate the table structure, over and over again with different data sets. How should I go about it doing it in mysql. I have one table only now.

Let say, I want to have multiple tables based on day. How do I link this table based on date.? Everyday one table.

As long as you are sure the ID which is the Primary key does not change then is fine.

You can add a new column in the same table (let's call it Table1) and name it GroupID. Create a 2nd table (call it Table2) with at least 2 columns GroupID and GroupDate. Populate the existing 25 rows in Table1.GroupID with value 1. Add a new first row in Table2 with value 1 and a Date value. You can replicate the same set of 25 rows with GroupID=2 or as many sets as you like.

Your new query will be

SELECT t1.GroupID, t1.GroupDate, t2.ID, t2.Number
FROM Table1 t1, Table2 t2
WHERE t1.GroupID = t2.GroupID

 

[lawnmowerman]

As long as you are sure the ID which is the Primary key does not change then is fine.

You can add a new column in the same table (let's call it Table1) and name it GroupID. Create a 2nd table (call it Table2) with at least 2 columns GroupID and GroupDate. Populate the existing 25 rows in Table1.GroupID with value 1. Add a new first row in Table2 with value 1 and a Date value. You can replicate the same set of 25 rows with GroupID=2 or as many sets as you like.

Your new query will be

SELECT t1.GroupID, t1.GroupDate, t2.ID, t2.Number
FROM Table1 t1, Table2 t2
WHERE t1.GroupID = t2.GroupID

Got it, I am trying it and will get feedback this method later. greenhorn in database stuff, but have to start learning this stuff. can't do without database in web technology.