0

I´m a bloody beginner and I would be more than happy, if someone could help me with my code. I have an SQL-database with columns containing a variable number of strings and columns containing numbers, all separated by commas. Now, I want to read out data by using a html-form and php. Each user can decide himself, which information to read out, that means, there are no mandatory fields. The result should be the fruit that meets all requested criteria. Here is the code that works well. 

enter code here

    <?php
    include("../files/zugriff.inc.php");

    if (isset($_POST["submit"])) {

     $sent = $_POST['sent'];
     $daten = array();
     $fruits = $_POST["fruits "];
     $vegetables = $_POST["vegetables"];
     $country = $_POST["country"];
     $season = $_POST["season"];
     $diameter = $_POST["diameter"];
     $color = $_POST["color"];


     if(!empty($fruits)) {
        $daten['fruits'] = $fruits;
    }
     if(!empty($vegetables)) {
        $daten['vegetables'] = $vegetables;
    }
    if(!empty($country)) {
        $daten['country'] = $country;
    }
    if(!empty($season)) {
        $daten['season'] = $season;
    }
    if(!empty($diameter)) {
        $daten['diameter'] = $diameter;
    }
    if(!empty($color)) {
        $daten['color '] = $color;
    }


        foreach ($daten as $key => $value) {
       $spalten[] = $key;
       $werte[] = "'$value'";
       }

    echo "<b>Results:</b><br><br>";

    $sql = "SELECT * FROM fruitdatabase WHERE (" . implode(", ",   
    $spalten) . ") = (" . implode(", ", $werte) . ")";

    echo "<table>";
    echo "<tr><th>Names</th></tr>";

    $result = mysqli_query($db, $sql);

    while ($row = mysqli_fetch_assoc($result)) {

    echo "<tr><td>$row[name]</td></tr>";

     }
     echo "</table>";
    }

    mysqli_close($db);
    ?>

However, the problem is, when there are different values separeted by commas in one field, the requested value is not found. This only works, when there is only one value in a field. So I tried to Change my code as follows:

    foreach ($daten as $key => $value) {
       $spalten[] = $key;
       $werte[] = "'%$value%'";
       }

    echo "<b>Results:</b><br><br>";

    $sql = "SELECT * FROM fruitdatabase WHERE (" . implode(", ", 
    $spalten) . ") LIKE (" . implode(", ", $werte) . ")";

Now, single words, separated by commas, are found. However, the combination of different requests is no more possible, which means that I can only search for one criterion and not for more.

I think there must be a problem with the shortcut of the array-elements.

Many thanks! Gery

Columbus
  • 89
  • 1
  • 9
  • 1
    Since you're a beginner, this is the time to avoid learning bad habits. Do **not** create database fields containing comma-separated lists – Barmar Aug 15 '16 at 21:08
  • And your code is generating `%apple%,%orange%,etc...`. that's never going to match. you need `foo like '%apple%' or foo like '%orange%'` etc... You really should learn how to normalize a table, which makes the question somewhat moot. – Marc B Aug 15 '16 at 21:10
  • As suggested search on the terms of "database normalization", "relational tables" – teliaz Aug 15 '16 at 21:10
  • Thanks for the quick responses and I apoligize again for my question, which obviously is a simple problem for experts! – Columbus Aug 15 '16 at 21:23
  • However, could someone please show me how I could optimize my code? Thanks again!! – Columbus Aug 15 '16 at 21:24

0 Answers0