PHP How to make a dropdown with database data

Question

So I have this database and it has column called selectedDates. In that column there is a row with the values

2018-08-22
2018-08-15
2018-08-20

Keep in mind this is 3 sets of dates, but in one row. How do I make a select tag/dropdown with these three values. Is this possible to do? If more information is needed, let me know and I will edit.

Down below is my code for having it do a dropdown of options within one column, but different rows. Such as

 Column 1
Row 1 -  Dog
Row 2 - Cat
Row 3 - Mouse

I need it to be like

Column 1 
Row 1 - Dog,Cat,Mouse

and then have the dropdown options as dog cat mouse

example code below of column 1, 3 different rows

 <?php
$con = new mysqli('localhost', 'root', '', 'races');

if($con->connect_errno) {
// error reporting here
}

$result = $con->query("SHOW TABLES");

if($result->num_rows > 0) {
echo '<select id="myTable" onchange="myFunction()">';
while($row = $result->fetch_array(MYSQLI_NUM)) {
    echo '<option value="Test" name="SelectedDate">' . $row[0] . '</option>';
}
echo '</select>';
} ?>

edit for replying to comment Example database

Id | Info | SelectedDates 
1    hi    2018-18,2018-19 
2    hi    2018-20,2018-21

In the code you provided, in my example it would only create a dropdown with id 1, but if I had another row like id 2, can i change $row[0] to $row[1] to have my dropdown display the 2nd row of selected dates?

We would love to see your coding attempt. This is a basic task with a few ways to satisfy. What is the delimiter? Think of code as cars. StackOverflow volunteers are "mechanics" not "manufacturers". Please show us your failed coding attempt. — mickmackusa, Aug 28 '18 at 20:25
This is one column in one row, I know how to do a dropdown if it were in different rows in the same column, but I need it to be the same row, same column. I separated them with a \n, but I can use a , if its easier to use in code. I will edit question with code attempt — , Aug 28 '18 at 20:27
https://stackoverflow.com/q/16985603/2943403 and https://stackoverflow.com/q/36832490/2943403 and https://stackoverflow.com/q/37289197/2943403 and https://stackoverflow.com/q/31911375/2943403 and https://stackoverflow.com/q/49289855/2943403 — mickmackusa, Aug 28 '18 at 21:17

score 1 · Accepted Answer · answered Aug 28 '18 at 22:23

1

If you want to check for a connection without explicitly checking the connect_errno property, you can simply check for a false $con value.
You should nominate the exact columns that you are going to access from your resultset. If you are only targeting the one row, only query for that one.
As a more brief syntax, you can drop > 0 and check for a non-falsey $result->num_rows value.
If you want to identify resultset columns by index, you can use $result->fetch_row() (again shorter syntax)
myTable is an inappropriate id value for your <select> tag -- because it is not a "table". I cannot suggest a better one because I don't know what your greater form structure is.
<option> tags don't take a name attribute, that goes in the parent <select> tag.
Setting a static value attribute value of Test will result in all options submitting the same value: Test (obviously that doesn't make sense). In fact, unless you need to reformatted/modify the date value, don't write a value attribute at all; just rely on the option's text to contain the value.

Untested Suggestion: (assuming you are processing just one row of data)

if (!$con = new mysqli('localhost', 'root', '', 'races')) {
    // connection error
} elseif (!$result = $con->query("SELECT selectedDates FROM `your_table` WHERE id = " . (int)$id)) {
    // query syntax error
} elseif (!$row = $result->fetch_row()) {
    // possible logic error, no row found
} else {
    echo '<select id="myTable" name="SelectedDate" onchange="myFunction()">';
    foreach (explode(",", $row[0]) as $date) {
        echo "<option>$date</option>";
    }
    echo "</select>";
}

answered Aug 28 '18 at 22:23

mickmackusa

43,625
12
83
136

This works, but if it's from a different row, can I just change $row[0] accordingly? – Aug 28 '18 at 23:16
Do you mean you have several rows that have comma-separated values? Do you want all of the dates from all rows as ` – mickmackusa Aug 28 '18 at 23:31
`[0]` of `$row[0]` is the column identifier. If you used associative keys, the comma-separated values would be accessed via `$row['selectedDates']`. – mickmackusa Aug 28 '18 at 23:41
If you want all `selectedDates` data from multiple rows, then I would recommend grouping in the query itself (joining with commas). If you want separate ` – mickmackusa Aug 28 '18 at 23:48
If I created a new row with comma-separated values, can I change my dropdown to show that rows values instead of the first one. – Aug 28 '18 at 23:50
That is more to do with query logic. You will want to write a `WHERE` clause in your query to isolate the "latest" using whatever logic is available in the table. If `WHERE` isn't suitable, you _could_ do `ORDER BY [some_column] DESC LIMIT 1` to isolate the "last" row. I can't be too concrete about my advice without more/better info. – mickmackusa Aug 28 '18 at 23:53
1

Never mind, I figured it out. Basically I just had to do 'WHERE Id = 17' and that was all – Aug 29 '18 at 00:38

score 0 · Answer 2 · answered Aug 28 '18 at 20:35

0

You say you must have all the dates in the same column, why?

Isn't this a database misconception? If you need multiple row for one value, you should have a table with "id | selectedDate" so that the issue you are speaking about does not happen.

If you must continue like this, you should try to look at this: https://secure.php.net/manual/fr/function.explode.php

Hope it helps.

answered Aug 28 '18 at 20:35

night-gold

2,202
2
20
31

I need all the dates in one row, not column. I wish i can add images to show what my database looks like. I have all the rows set up with an Id as my auto int, primary key. I can't use explode since there is no array that I am using. – Aug 28 '18 at 21:03
We don't need an image of your db, you can go to phpmyadmin and export the structure and row -- in fact that is preferred from a volunteer's perspective because then the details are 100% accurate and we can copy-paste. `explode()` doesn't require an array, it creates one. – mickmackusa Aug 28 '18 at 21:24

PHP How to make a dropdown with database data

2 Answers2