SQL Database Structure

[mdb08]

I was wondering if anyone could help me get my head around this problem that I'm having, I have an excel spreadsheet with the following layout.

Area|ID |Location |Week1|Week2|Week3+
71 |1484|Rochdale|713 |728 |690
71 |1485|Doncaster|713 |728 |690

How would I best structure an SQL table to store these, that I could then easily retrieve and display in html?

Any help would be greatly appreciated

Thanks in advance

 

[tom_e]

If all you want to do is pull the data back out and you're not going to be putting together a big fancy database then just create a table which mirrors the excel file and import the data.

 

[AHarvey]

I'd probably split it into 2 tables.

1) unique Identifier Area Id Location
2) week information

 

[mdb08]

What I've tried is currently giving me this output;
https://ibb.co/ddYQ9k

With the following code

$query = "SELECT * FROM locations"; 
$result = $conn->query($query); 
$display = "<table border='1'><tr><th>Area</th><th>Location</th>th>Result</th>‌<th>Week</th>"; 
while($row = $result->fetch_assoc()) { 
$display = $display."<tr><td>".$row['area']."</td>"; 
$display = $display."<td>".$row['location']."</td>"; 
$display = $display."<td>".$row['result']."</td>"; 
$display = $display."<td>".$row['week']."</td></tr>"; 
} $display = $display."</tr></table>"

but I cannot figure out how to get it to display how I would like which is the following;
https://ibb.co/daWTUk

 

[AHarvey]

Surely the only difference there is the headers?

$display = "<table border='1'><tr><th>Area</th><th>Location</th>th>Result</th>‌<th>Week</th>";

to

$display = "<table border='1'><tr><th>Area</th><th>Location</th>th>Week 1</th>‌<th>Week 2</th>";

 

[mdb08]

The only difference is probably the headers, but I'm struggling with how to retrieve this information from the table and display it in the way so it shows one location and then goes across week 1's result, week 2's result etc how would I do that with the php?

I have

$result = $conn->query($query);

    $display = "<table border='1'><tr><th>Area</th><th>Location</th><th>Result</th><th>Week</th>";

    while($row = $result->fetch_assoc())
    {       
        $display = $display."<tr><td>".$row['area']."</td>";
        $display = $display."<td>".$row['location']."</td>";
        $display = $display."<td>".$row['result']."</td>"; 
        $display = $display."<td>".$row['week']."</td></tr>";
    }

    $display = $display."</tr></table>";

How would I change that to get my desired output?

 

[Dup]

Are you able to give us the output from the database?

echo('<pre>');
print_r($result->fetch_assoc());
echo('</pre>');

That way we can see the data. The array will probably want a quick re-arrangement then it should be easy to dynamically create the whole table so that it replicate the headings in the SQL and fills the columns where appropriate.

 

[mdb08]

Are you able to give us the output from the database?

echo('<pre>');
print_r($result->fetch_assoc());
echo('</pre>');

That way we can see the data. The array will probably want a quick re-arrangement then it should be easy to dynamically create the whole table so that it replicate the headings in the SQL and fills the columns where appropriate.

The results I get from inserting that is;

Array
(
    [area] => 72
    [location] => trent
    [result] => 123
    [week] => 20
)

 

[K1LaW8tt5]

You need to create week1, week2,week3 in your sql before you can extract them because the currently dont exist(see your array). As suggested above I would put the data into the sql in the same way you have it in the excel so you can easily import/export the table to/from excel.

 

[mdb08]

I've managed to get it to display how I want, thanks The next thing I can't figure out how to implement is having an average for '0','1','2','3','4','5' etc
https://ibb.co/jccSqQ

I have the following code

    if($_POST['week'])
    {
        $week = $_POST['week'];

        $query = "SELECT * FROM scores";

        $result = $conn->query($query);

        $display = "<table border='1'><tr><th>Area</th><th>Location</th>";

        $x = $y = 0;
        $regionytd = 0;
        $counter=0;
       
        while($x <= $week)
        {
            $display = $display."<th>".$x."</th>";
            $x++;
        }
       
        $display = $display."<th>YTD</th>";
       
        while($row = $result->fetch_assoc())
        {  
            $counter2 = 0;
            $yeartodate = 0;  
            $display = $display."<tr><td>".$row['Area']."</td>";
            $display = $display."<td>".$row['location']."</td>";
            while($y <= $week)
            {
                $display = $display."<td>".$row[$y]."</td>";
               
                if($row[$y]>0)
                {
                    $counter++;
                    $counter2++;
                    $regionytd = $regionytd + $row[$y];
                    $yeartodate = $yeartodate+$row[$y];
                }
                $y++;
            }
            $yeartodate = $yeartodate/$counter2;
            $yeartodate = round($yeartodate);
            $display = $display."<td>".$yeartodate."</td>";  
            $y =0;
        }
        $regionytd = $regionytd / $counter;
        $display = $display."</tr></table>";
    $conn->close();

    }

I assume I want to create a multidimensional array and add the values for 0 to it, but I can't figure out how I would do that. Any help would be greatly appreciated.