Query not running, switch statement(PHP/SQL)

Question

So I have a dropdown list that should change the information displayed within the section below the form. The switch statement checks which option is selected and then assigns the correct query to run to the variable $query. I'm not sure why the query isn't running, as I'm using a code editor on cPanel's file manager. I have a similar php code on another page of my website that works perfectly, so I'm even more puzzled.

<section class="content">
    <h2 class="heading">
        Fetal Development Navigation
    </h2>
    <div id="navigationButtons">
        <form action="" style="display:inline;" method="GET">
            <select name="week" style="width:50%; display:inline; height:5%;">
                <option title='Select A Week' value='select'>Please select a week! </option>
                <option title='Week 1' value='one'>Week 1</option>
                <option title='Week 2' value='two'>Week 2</option>
                <option title='Week 3' value='three'>Week 3</option>
                <option title='Week 4' value='four'>Week 4</option>
                <option title='Week 5' value='five'>Week 5</option>
                <option title='Week 6' value='six'>Week 6</option>
                <option title='Week 7' value='seven'>Week 7</option>
                <option title='Week 8' value='eight'>Week 8</option>
                <option title='Week 9' value='nine'>Week 9</option>
                <option title='Week 10' value='ten'>Week 10</option>
                <option title='Week 11' value='eleven'>Week 11</option>
                <option title='Week 12' value='twelve'>Week 12</option>
                <option title='Week 13' value='thirteen'>Week 13</option>
                <option title='Week 14' value='fourteen'>Week 14</option>
                <option title='Week 15' value='fifteen'>Week 15</option>
                <option title='Week 16' value='sixteen'>Week 16</option>
                <option title='Week 17' value='seventeen'>Week 17</option>
                <option title='Week 18' value='eightteen'>Week 18</option>
                <option title='Week 19' value='nineteen'>Week 19</option>
                <option title='Week 20' value='twenty'>Week 20</option>
                <option title='Week 21' value='twenty-one'>Week 21</option>
                <option title='Week 22' value='twenty-two'>Week 22</option>
                <option title='Week 23' value='twenty-three'>Week 23</option>
                <option title='Week 24' value='twenty-four'>Week 24</option>
                <option title='Week 25' value='twenty-five'>Week 25</option>
                <option title='Week 26' value='twenty-six'>Week 26</option>
                <option title='Week 27' value='twenty-seven'>Week 27</option>
                <option title='Week 28' value='twenty-eight'>Week 28</option>
                <option title='Week 29' value='twenty-nine'>Week 29</option>
                <option title='Week 30' value='thirty'>Week 30</option>
                <option title='Week 31' value='thirty-one'>Week 31</option>
                <option title='Week 32' value='thirty-two'>Week 32</option>
                <option title='Week 33' value='thirty-three'>Week 33</option>
                <option title='Week 34' value='thirty-four'>Week 34</option>
                <option title='Week 35' value='thirty-five'>Week 35</option>
                <option title='Week 36' value='thirty-six'>Week 36</option>
                <option title='Week 37' value='thirty-seven'>Week 37</option>
                <option title='Week 38' value='thirty-eight'>Week 38</option>
                <option title='Week 39' value='thirty-nine'>Week 39</option>
                <option title='Week 40' value='forty'>Week 40</option>
            </select>
            <input type="submit" value="Go" style="width:25%; display:inline; height:5%;"/>
        </form>
    </div>
</section>
<section id="week" class="content">
    <?php
        $i = $_GET['week'];
        switch ($i) {
            case "one":
                $query = "SELECT * FROM fetaldev WHERE week = 1;";
                $week = "Week One";
                break;
            case "two":
                $query = "SELECT * FROM fetaldev WHERE week = 2;";
                $week = "Week Two";
                break;
            case "three":
                $query = "SELECT * FROM fetaldev WHERE week = 3;";
                $week = "Week Three";
                break;
                case "four":
                $query = "SELECT * FROM fetaldev WHERE week = 4;";
                $week = "Week Four";
                break;
            case "five":
                $query = "SELECT * FROM fetaldev WHERE week = 5;";
                $week = "Week Five";
                break;
        }

        /*if($i == 'one') {
        $query = "SELECT * FROM fetaldev WHERE week = 1;";
                $week = "Week One";
                echo $query . $week;
                }*/
        $run = mysqli_query($dbconn, $query);
        while ($row = mysqli_fetch_array($run)){
                $id = $row['id'];
                $tag = $row['tag'];
                $description = $row['description'];
                }
        //query isn't running

        ?>
    <?php if(isset($week)){?>
    <h2 class="heading"><?php echo $week; ?></h2>
    <h4>Size Comparisons</h4>
    <?php } else { ?>
    <h3>Please select a week from the dropdown list!</h3>
    <p>Each week will list out the size comparisons and notable milestones in the development of your baby!</p>
    <?php } ?>
    <p>
        <?php if($tag == 'size'){
            echo $description . ', ';
            }?>
    </p>
    <?php if(isset($week)){?>
    <h4>Approximate Weight and Length</h4>
    <?php } ?>
    <p><?php if($tag=='weight' || $tag =='length'){echo $description;}?></p>
</section>

If there is anything more from the code that you need, just let me know! Thanks in advance!

There's some unknowns - do you have a valid connection? After the query, does `mysqli_error($dbconn)` throw anything back? Is there any PHP errors (enable error-reporting and check your logs). And you should also check that the form was submitted before running the query. — Qirel, Jul 24 '17 at 16:44
If you go to www.mommy-info.com/pregnancy/fetal-development, you can see what's happening. The form is submitting because the switch statement is being executed (Say, week three is selected, then the $week variable is displayed as Week Three). No error is printing from the mysqli_error($dbconn). And there is no error-reporting or logs on this code editor. Thanks! — ethacker, Jul 24 '17 at 17:02
Do you have to use string cases for your switch? This is sort of unrelated but with PHP you could turn this large amount of code into a very short set if you don't have to use it the way you are currently - which would be easier to troubleshoot, read, and develop further — GrumpyCrouton, Jul 24 '17 at 17:06
@GrumpyCrouton I'm not sure what you mean? I just did strings because my option values are strings. — ethacker, Jul 24 '17 at 17:09
Quick suggestion - change the `value='one'` to `value='1'`, this will remove the switch as you can use the value directly. — Nigel Ren, Jul 24 '17 at 17:13
You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). — Qirel, Jul 24 '17 at 17:41
@Qirel I'm newish to php/sql, so what exactly do I need to type in for those and where in the code should I do it? — ethacker, Jul 24 '17 at 17:47

GrumpyCrouton · Accepted Answer · 2017-07-24T19:48:44.283

1

Try the following code, I was able to get rid of the switch completely, minimized your code using loops to prevent so much data repetition, and made your code not switch between PHP and HTML so much for more readability. I also switched you to prepared statements to avoid SQL injection.

Your problem was that you were setting variables and then displaying information after the database loop, you have to display the information DURING the loop to get info for each row in your database.

<section class="content">
    <h2 class="heading"> 
        Fetal Development Navigation 
    </h2>
    <div id="navigationButtons">
        <form action="" style="display:inline;" method="GET">
            <select name="week" style="width:50%; display:inline; height:5%;">
                <option title='Select A Week' value='select'>Please select a week! </option>
                <?php 
                    for ($i = 1; $i <= 40; $i++) { 
                        echo "<option title='Week {$i}' value='{$i}'>Week {$i}</option>"; 
                    } 
                    ?> 
            </select>
            <input type="submit" value="Go" style="width:25%; display:inline; height:5%;"/> 
        </form>
    </div>
</section>
<?
    if(!empty($_GET['week'])) { ?>
        <section id="week" class="content"> 
            <?php 
                $week = $_GET['week']; 

                if(!empty($week)){ 
                    echo "<h2 class='heading'>{$week}</h2>"; 
                    echo '<h4>Size Comparisons</h4>'; 
                } else { 
                    echo '<h3>Please select a week from the dropdown list!</h3>'; 
                    echo '<p>Each week will list out the size comparisons and notable milestones in the development of your baby!</p>'; 
                } 
                $stmt = $db_conn->prepare("SELECT id, tag, description FROM fetaldev WHERE week=? ORDER BY tag ASC"); 
                $stmt->bind_param('s', $week); 
                $stmt->execute(); 
                $stmt->bind_result($id, $tag, $description); 
                if(!empty($week)) { 
                    echo '<h4>Approximate Weight and Length</h4>'; 
                } 
                while ($stmt->fetch()) { 
                    echo "<p>".(($tag == "size") ? $description.', ' : $tag)."</p>"; 

                    if($tag == "weight") { 
                    echo "Weight: " . $description; 
                    } 
                    if($tag == "length") { 
                    echo "Length: " . $description; 
                    } 
                } 

                ?> 
        </section>
    <?php 
    }

If there are any issues, please let me know and I will update the answer to reflect a solution to them.

edited Jul 24 '17 at 19:48

answered Jul 24 '17 at 17:18

GrumpyCrouton

8,486
7
32
71

It just displays the "please select a week " part. – ethacker Jul 24 '17 at 17:34
In the code it doesn't even block that element from showing, did you add anything else? – GrumpyCrouton Jul 24 '17 at 17:38
I just copied and pasted exactly what you had. – ethacker Jul 24 '17 at 17:39
I see what I did. Change `if(isset($week))` to `if(isset($_GET['week'])` - Actually - I updated my code – GrumpyCrouton Jul 24 '17 at 17:39
I think I might have worded my previous comment wrong, what I meant is that no matter what week I choose it only displays "Please select a week from the dropdown list" and the
that goes with it.
– ethacker Jul 24 '17 at 17:40
1

It's because the variable didn't exist anymore. Try new code – GrumpyCrouton Jul 24 '17 at 17:41
It still isn't displaying any info from the database. I'm so confused. – ethacker Jul 24 '17 at 17:43
But that did fix the headings. – ethacker Jul 24 '17 at 17:44
@ethacker where is it supposed to display the information? – GrumpyCrouton Jul 24 '17 at 17:46
@LawrenceCherone I did not introduce it. It was already in the code. – GrumpyCrouton Jul 24 '17 at 17:46
@LawrenceCherone I guess technically it wasn't already in the code actually. You're right – GrumpyCrouton Jul 24 '17 at 17:48
echo "
".$tag == "size" ? $description.', ' : ''."
"; if(!empty($week)) { echo '
Approximate Weight and Length
'; } if($tag == "weight" || $tag == "length") { echo $description; – ethacker Jul 24 '17 at 17:49
@ethacker if `$tag` == "size" then it shows $description, else it is blank - what does `$tag` equal? – GrumpyCrouton Jul 24 '17 at 17:49
There are 6 or 7 records with the tag = size. – ethacker Jul 24 '17 at 17:56
Change that whole line with `echo "
".$tag == "size" ? $description.', ' : $tag."
";` and let me know what the output is – GrumpyCrouton Jul 24 '17 at 17:57
No output. http://www.mommy-info.com/pregnancy/fetal-development?week=3 – ethacker Jul 24 '17 at 17:59
Actually I see what the problem is. It's because all of that is being references outside of the loop. I'll fix it, one second – GrumpyCrouton Jul 24 '17 at 18:01
@LawrenceCherone I believe I fixed the injections. Please let me know if I missed something in regards to that. – GrumpyCrouton Jul 24 '17 at 18:03
@ethacker Do you expect more than one row per query? – Qirel Jul 24 '17 at 18:03
@ethacker fixed the loop issue, try now :) – GrumpyCrouton Jul 24 '17 at 18:04
Now nothing is displaying. http://www.mommy-info.com/pregnancy/fetal-development?week=1 – ethacker Jul 24 '17 at 18:04
Yes, there is more than one row per query. @Qirel – ethacker Jul 24 '17 at 18:04
@ethacker Do you have error reporting on? I may have messed p with the prepared statements. – GrumpyCrouton Jul 24 '17 at 18:05
@ethacker yeah small typo in prepared statements, try updated – GrumpyCrouton Jul 24 '17 at 18:05
There is no way to do error reporting on this code editor (cPanel file manager) – ethacker Jul 24 '17 at 18:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150019/discussion-between-grumpycrouton-and-ethacker). @ethacker – GrumpyCrouton Jul 24 '17 at 18:06

score 0 · Answer 2 · answered Jul 24 '17 at 18:22

Your logic is somewhat divided. You shouldn't use variables that are only defined after a query if the query is never run. Then you shouldn't run the query unless there's a value set by $_GET['week'].

I've modified your code with the following

Creating your options with a for-loop, as they're the same each time (just incrementing) - you can shorten a few lines with it.
Using value="1" instead of value="one" for your options. This allows you to use that value directly in a query, so that you don't need a switch to convert it back.
Checking that isset($_GET['week']) before running the query, or before using the variables that are being defined in the query.
Putting the output from your query inside a loop (while ($stmt->fetch())) so that you can get all the records, and not just the last one.
Security improvement: Using prepared statements with bounded parameters, to prevent SQL injection. This is because we now use $_GET['week'] directly in the query.
Security improvement: Using htmlspecialchars($_GET['week']) when echoing - this will at the very least prevent invalid HTML (bugs), but it will prevent XSS-attacks if malicious HTML/JavaScript enters your database.
Debugging: Adding error_reporting(E_ALL); ini_set('display_errors', 1); will allow you to find any PHP errors, and using $stmt->error and/or $dbconn->error will give you any MySQL errors.

Note: You should never display errors to a live environment - they should only be logged! While in development, displaying errors to the browser is fine.

With those changes implemented, your code would look something like this.

<?php
// Enabling error-reporting
error_reporting(E_ALL); 
ini_set('display_errors', 1);
?>
<section class="content">
    <h2 class="heading">
        Fetal Development Navigation
    </h2>
    <div id="navigationButtons">
        <form style="display:inline;" method="GET">
            <select name="week" style="width:50%; display:inline; height:5%;">
                <option title='Select A Week' value='select'>Please select a week! </option>
                <?php 
                for ($i = 1; $i <= 40; $i++) {
                    echo '<option title=Week '.$i.'" value="'.$i.'">Week '.$i.'</option>';
                }
                ?>
            </select>
            <input type="submit" value="Go" style="width:25%; display:inline; height:5%;"/>
        </form>
    </div>
</section>
<section id="week" class="content">
    <?php
    if (isset($_GET['week'])) {
        ?>
        <h2 class="heading"><?= htmlspecialchars($_GET['week']); ?></h2>
        <?php 
        if ($stmt = $dbconn->prepare("SELECT id, tag, description FROM fetaldev WHERE week = ?")) {
            $stmt->bind_param("s", $_GET['week']);
            if (!$stmt->execute()) // If the query failed to execute
                die("Error executing query: ".$stmt->error);
            $stmt->bind_result($id, $tag, $description);
            while ($stmt->fetch()) {
                if ($tag == 'size') {
                    ?><h4>Size Comparisons</h4>
                    <p><?= htmlspecialchars($description); ?>, </p>
                    <?php 
                }

                if ($tag == 'weight' || $tag == 'length') {
                    ?><h4>Approximate Weight and Length</h4>
                    <p><?= htmlspecialchars($description); ?></p>
                    <?php 
                }
            }
            $stmt->close();
        } else {
            die("Error preparing query: ".$dbconn->error);
        }
    } else { ?>
        <h3>Please select a week from the dropdown list!</h3>
        <p>Each week will list out the size comparisons and notable milestones in the development of your baby!</p>
        <?php 
    } ?>
</section>

References

Even if this might not fix everything, it should be a start to figuring out what's wrong, as we've now implemented some error-checking to your code.

Query not running, switch statement(PHP/SQL)

2 Answers2

Approximate Weight and Length

References