PHP - Can't split the String and save the data in to database

Question

I have a database with 3 tables (location,holidays and category)

This is holidays table

This is location table

This is category table

I can insert the data in to holidays table but locationID and catID are NULL how can I insert the other information in locationID, locationName, country in Location table and catID, catDesc in Category table

I guess that I need to split the String in the (option value='..') and save it in 2/3 different variables and then to insert them in the tables but i don't know how.

Thanks

<?php
        include 'database_conn.php';


        $holidayTitle=$_POST['holidayTitle'];
        $holidayDuration=$_POST['holidayDuration'];
        $holidayPrice=$_POST['holidayPrice'];
        $locationName=$_POST['locationName'];
        $catDesc=$_POST['catDesc'];


        if(isset($_POST['submit'])) {
            $sql = "INSERT INTO PCH_holidays(holidayTitle, holidayDuration, holidayPrice)
    VALUES ('$holidayTitle', '$holidayDuration', '$holidayPrice');";
            $sql .= "INSERT INTO PCH_location (locationID)
    VALUES ('$locationName');";
            $sql .= "INSERT INTO PCH_category (catID)
    VALUES ('$catDesc')";

            if ($dbConn->multi_query($sql) === TRUE) {
                echo "New records created successfully";
            } else {
                echo "Error: " . $sql . "<br>" . $dbConn->error;
            }
        }
        $dbConn->close();



        ?>

    <form action="admin.php" method="POST">
        <input type="text" name="holidayTitle" placeholder="Title">
        <br>
        <input type="text" name="holidayDuration" placeholder="Duration">
        <br>
        <input type="text" name="holidayPrice" placeholder="Price">
        <br>
        <select name="locationName">
        <option value='l1, Milaidhoo Island, Maldives'>Milaidhoo Island, Maldives</option>
        <option value='l2, Rangali Island, Maldives'>Rangali Island, Maldives</option>
        <option value='l3, Zanzibar, Tanzania'>Zanzibar, Tanzania</option>
        <option value='l4', Boston, USA>Boston, USA</option>
        <option value='l5, San Francisco, USA'>San Francisco, USA</option>
        <option value='l6, Nairobi, Kenya'>Nairobi, Kenya</option>
        <option value='l7, Algarve, Portugal'>Algarve, Portugal</option>
        <option value='l8, New York, USA'>New York, USA</option>
        <option value='l9, Sorrento,  Italy'>Sorrento, Italy</option>
        <option value='l10, Verona, Italy'>Verona, Italy</option>
        </select>


        <br>
        <select name="catDesc" >

        <option value='c1,Luxury'>>Luxury</option>

        <option value='c2,Tour'>Tour</option>

        <option value='c3,Safari'>Safari</option>

        <option value='c4,Golf'>Golf</option>

        <option value='c5,Weddings'>Weddings</option>

        <option value='c6,Walking'>Walking</option>

        <option value='c7,Opera'>Opera</option>
        </select>

        <br>

        <input type="submit" name="submit" value="send info">
    </form>

**Your code is vulnerable to SQL injection and will be hacked** even if [you are escaping inputs!](https://stackoverflow.com/a/5741264/2595450) Use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. Check: [How can I prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Spoody, Apr 15 '18 at 20:16
Check the types of columns "locationID" and "catID" are. ID's are generally `BigInt` and not `VarChar` (or any string type). AND YES!! you should use prepared statements to help prevent SQL injection — Barns, Apr 15 '18 at 20:21
Hi. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. Use edit functions to inline, not link, if you have the rep--make your post self-contained. And never give a diagram without a legend/key. Please read & act on [mcve]. — philipxy, Apr 15 '18 at 20:46
After your edit I see that you are using `VarChar` as `ID`columns. More specifically "c1,c2,c3..." for `catID` and "l1,l2,l3,..." for `locationID`. While technically this is not "wrong" it is generally expected that an SQL database will have a self-incrementing ID field of type `BigInt` (as I mentioned above). This ultimately helps maintain unique values, which serve to identify the data for that specific entry--and you do not need to maintain a pointer to the last valid "ID" value you are able to apply. Just saying... — Barns, Apr 15 '18 at 22:24

score 0 · Answer 1 · answered Apr 15 '18 at 20:40

I guess that I need to split the String in the (option value='..') and save it in 2/3 different variables and then to insert them in the tables but i don't know how.

That's pretty much it.

To split the string into 3 variables using your post data:

list($locationID, $locationName, $country) = explode(",", $_POST['locationName']);

Then change your SQL accordingly.

INSERT INTO PCH_location (locationID, locationName, country)
    VALUES ('$locationID', '$locationName', '$country');

Repeat same thing with category table.

Note the comments you'll receive plenty about SQL security i.e. Prepared statements. The above should address your error.

score 0 · Answer 2 · answered Apr 15 '18 at 21:01

If you are using drop downs for location and category, the values of the options should be the ID's of the respective options, not the description/name. IOWs, they should be numbers which you can then save directly to your holiday table. No parsing/manipulation required.

You have:

<option value='l1, Milaidhoo Island, Maldives'>Milaidhoo Island, Maldives</option>

But it should be:

<option value='1'>Milaidhoo Island, Maldives</option>

NOTE

Since you are storing the keys to the other two tables, you should not also store the text from these tables in the holiday table. If the name gets changed, you would have to do an update to the holiday table to update the descriptions. Not the best use of a relational database. Not a normalized structure.

PHP - Can't split the String and save the data in to database

2 Answers2