RecipeDatabase/demo.php at master · Keydrain/RecipeDatabase · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
<?php

if (isset($_POST['submit'])) {
    print "<h1> Clint Cooper & Emily Rohrbough ~ Recipe Database </h1><br>";

    $link = mysqli_connect("localhost", "root", "<replace>", "Recipes");

    if ($link === false){
        die("Error: Could not connect. " . mysqli_connect_error());
    }

    $sql = isset($_POST['input']) ? $_POST['input'] : '';

    echo $query;

    $result = mysqli_query($link, $sql) or die(mysqli_error());
    print "Input Query:<br>";
    print "$sql<br>";
    print "<br>Results:<br>";
    print "<table border='1'>";
    while($row = mysqli_fetch_assoc($result)){
        print "<tr>";
        foreach($row as $cname => $cvalue){
            print "<td>$cname:<br>$cvalue</td>";
        }
        print "</tr>";
    }
    print "</table>";

} else {
    print "<h1> Clint Cooper & Emily Rohrbough ~ Recipe Database </h1><br>";

    $link = mysqli_connect("localhost", "root", "<replace>", "Recipes");

    if ($link === false){
        die("Error: Could not connect. " . mysqli_connect_error());
    }

    $sql = "SELECT R.Name
            FROM RECIPE R, AMOUNT_REQUIRED A
            WHERE R.Recipe_No = A.Recipe_No
            AND A.Ingredient_No IN (SELECT Ingredient_No
                                    FROM INGREDIENT
                                    WHERE NAME LIKE '%chicken%')";

    $result = mysqli_query($link, $sql) or die(mysqli_error());

    print "$sql<br>";
    print "<table border='1'>";
    while($row = mysqli_fetch_assoc($result)){
        print "<tr>";
        foreach($row as $cname => $cvalue){
            print "<td>$cname:<br>$cvalue</td>";
        }
        print "</tr>";
    }
    print "</table><br><br><br>";

    $sql = "SELECT R.Name
            FROM RECIPE R
            WHERE (R.Calories / R.Quantity) < 500
            LIMIT 5";

    $result = mysqli_query($link, $sql) or die(mysqli_error());

    print "$sql<br>";
    print "<table border='1'>";
    while($row = mysqli_fetch_assoc($result)){
        print "<tr>";
        foreach($row as $cname => $cvalue){
            print "<td>$cname:<br>$cvalue</td>";
        }
        print "</tr>";
    }
    print "</table><br><br><br>";

    $sql = "SELECT DISTINCT R.Name
            FROM RECIPE R, SOURCE S
            WHERE S.Reference LIKE '%allrecipes.com%'
            LIMIT 5";

    $result = mysqli_query($link, $sql) or die(mysqli_error());

    print "$sql<br>";
    print "<table border='1'>";
    while($row = mysqli_fetch_assoc($result)){
        print "<tr>";
        foreach($row as $cname => $cvalue){
            print "<td>$cname:<br>$cvalue</td>";
        }
        print "</tr>";
    }
    print "</table><br><br><br>";

    $sql = "SELECT GROUP_CONCAT(DISTINCT D.Directions SEPARATOR ', ') as 'Directions', GROUP_CONCAT(I.Name, ' : ', A.Amount, ' ', A.Unit SEPARATOR '<br>') as 'Ingredients'
            FROM INSTRUCTION_LIST D, INGREDIENT I JOIN AMOUNT_REQUIRED A ON I.Ingredient_No = A.Ingredient_No, RECIPE R
            WHERE R.Name LIKE 'Chicken Enchiladas%' AND D.Direction_No = R.Recipe_No AND A.Recipe_No = R.Recipe_No";

    $result = mysqli_query($link, $sql) or die(mysqli_error());

    print "$sql<br>";
    print "<table border='1'>";
    print "<col width = '50%'";
    print "<col width = '50%'";
    while($row = mysqli_fetch_assoc($result)){
        print "<tr>";
        foreach($row as $cname => $cvalue){
            print "<td>$cname:<br>$cvalue</td>";
        }
        print "</tr>";
    }
    print "</table><br><br><br>";

    $sql = "SELECT R.Name as 'Recipe Name', I.Name as 'Possible Gluten Ingredient'
            FROM RECIPE R,
                 (SELECT A.Recipe_No, SUM(I.Contains_Glutten) as Gluten
                  FROM INGREDIENT I JOIN AMOUNT_REQUIRED A on I.Ingredient_No = A.Ingredient_No
                  GROUP BY A.Recipe_No) T,
                 INGREDIENT I JOIN AMOUNT_REQUIRED A on I.Ingredient_No = A.Ingredient_No
            WHERE T.Gluten < '2'
            AND T.Recipe_No = R.Recipe_No
            AND A.Recipe_No = R.Recipe_No
            AND I.Contains_Glutten = 1
            LIMIT 25";

    $result = mysqli_query($link, $sql) or die(mysqli_error());

    print "$sql<br>";
    print "<table border='1'>";
    while($row = mysqli_fetch_assoc($result)){
        print "<tr>";
        foreach($row as $cname => $cvalue){
            print "<td>$cname:<br>$cvalue</td>";
        }
        print "</tr>";
    }
    print "</table><br><br><br>";
?>

<form action="<?php echo basename(__FILE__); ?>" method="post">
<h3><label for="input">Enter a query:</label></h3>
<input type="text" id="input" name="input" size="50" maxlength="2000" value="" />

</fieldset>
<p class="button"><input class="submit" type="submit" name="submit" value="Submit Query" /></p>
</form>

<?php
}
?>