php select dropdown values with selected

[ianhaney10]

I have been making a select dropdown that populates with values from a database table and got that working and on the page, it's a sales report page so I select the dropdown option value I want then put the dates in and click filter and it shows the results in a table below the form fields and it's working but when the page refreshes after clicking filter, the select dropdown resets and go back to the very first option in the select dropdown, I have tried to add selected to the option value but I am not doing it right as the vales don't have selected in the inspect element code but that may be because the page is refreshing

The current select dropdown code is below as thought that may be only part that's relevant to my post but can paste the whole code as a txt file if needed

 

<select name="lorry" id="lorry" class="custom-select select-2">
                                <!--<option value="all">All Lorries</option>-->
                                <?php
                                $mysqli = new mysqli('localhost', 'wwwbeechwoodsolu_collrystmusr', '********', 'wwwbeechwoodsolu_coal-lorry-system');
                                $sql = "SELECT id, lorry FROM lorries";
                                $result = $mysqli->query($sql);
                                if ($result->num_rows > 0) {
                                    while ($row = $result->fetch_assoc()) { ?>
                                <option value="<?php echo $row["lorry"]; ?>" <?php echo ($id == $row['id'])? 'selected="selected"':'' ?>><?php echo $row['id'] . ' ' . $row["lorry"]; ?></option>
                                <?php
                                    }
                                }
                                ?>
							</select>

 

[Barand]

Not your best idea to publish a password in a public forum. I've obscured it but you should consider changing it.

Try

    <select name="lorry" id="lorry" class="custom-select select-2">
        <option value="all">All Lorries</option>
        <?php
        $mysqli = new mysqli('localhost', 'wwwbeechwoodsolu_collrystmusr', '********', 'wwwbeechwoodsolu_coal-lorry-system');
        $sql = "SELECT id, lorry FROM lorries";
        $result = $mysqli->query($sql);
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $sel = ($row['lorry'] == $_GET['lorry']) ? 'selected' : '';
                echo "<option $sel >{$row['lorry']}</option\n";
            }
        }
        ?>
    </select>

 

[ianhaney10]

43 minutes ago, Barand said:

Not your best idea to publish a password in a public forum. I've obscured it but you should consider changing it.

Try

    <select name="lorry" id="lorry" class="custom-select select-2">
        <option value="all">All Lorries</option>
        <?php
        $mysqli = new mysqli('localhost', 'wwwbeechwoodsolu_collrystmusr', '********', 'wwwbeechwoodsolu_coal-lorry-system');
        $sql = "SELECT id, lorry FROM lorries";
        $result = $mysqli->query($sql);
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $sel = ($row['lorry'] == $_GET['lorry']) ? 'selected' : '';
                echo "<option $sel >{$row['lorry']}</option\n";
            }
        }
        ?>
    </select>

 

Dam it, I forgot to remove the db info I normally remove the db crendentials before posting but bit stressed today, I'll change the db password

I'll try that code though and post a update, thank you for the code. I'll post a update once tried in a few mins

[ianhaney10]

57 minutes ago, Barand said:

Not your best idea to publish a password in a public forum. I've obscured it but you should consider changing it.

Try

    <select name="lorry" id="lorry" class="custom-select select-2">
        <option value="all">All Lorries</option>
        <?php
        $mysqli = new mysqli('localhost', 'wwwbeechwoodsolu_collrystmusr', '********', 'wwwbeechwoodsolu_coal-lorry-system');
        $sql = "SELECT id, lorry FROM lorries";
        $result = $mysqli->query($sql);
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $sel = ($row['lorry'] == $_GET['lorry']) ? 'selected' : '';
                echo "<option $sel >{$row['lorry']}</option\n";
            }
        }
        ?>
    </select>

 

I have changed the db password

I have also tried the code and it's staying on the selected dropdown value but it's stopped bit of it from working, for example if I select All Lorries, no data is displayed but the dates are correct so thought it would do

Also the other bit that is happening is it's not showing the third dropdown value which should say Wickford Lorry, it stops after the Basildon Lorry

The basic html code without the PHP is outputted as the following

 

<select name="lorry" id="lorry" class="custom-select select-2">
                                <option value="all">All Lorries</option>
                                <option>Basildon Lorry</option>Wickford Lorry
						
					
					
						
							Date Start
							</select>

The code I have is below

 

<select name="lorry" id="lorry" class="custom-select select-2">
                                <option value="all">All Lorries</option>
                                <?php
                                $mysqli = new mysqli('localhost', '', '', '');
                                $sql = "SELECT id, lorry FROM lorries";
                                $result = $mysqli->query($sql);
                                if ($result->num_rows > 0) {
                                    while ($row = $result->fetch_assoc()) {
                                $sel = ($row['lorry'] == $_GET['lorry']) ? 'selected' : '';
                                echo "<option $sel >{$row['lorry']}</option\n";
                                }
                                }
                                ?>
							</select>

[Barand]

mea culpa.

I missed the closing ">" on the "</option>"

echo "<option $sel >{$row['lorry']}</option\n";
                                           ^  

 

[ianhaney10]

1 hour ago, Barand said:

Not your best idea to publish a password in a public forum. I've obscured it but you should consider changing it.

Try

    <select name="lorry" id="lorry" class="custom-select select-2">
        <option value="all">All Lorries</option>
        <?php
        $mysqli = new mysqli('localhost', 'wwwbeechwoodsolu_collrystmusr', '********', 'wwwbeechwoodsolu_coal-lorry-system');
        $sql = "SELECT id, lorry FROM lorries";
        $result = $mysqli->query($sql);
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $sel = ($row['lorry'] == $_GET['lorry']) ? 'selected' : '';
                echo "<option $sel >{$row['lorry']}</option\n";
            }
        }
        ?>
    </select>

 

Sorry found the problem , I spotted > was missing at the end of option where it's got </option\n";

I added the > and it's displaying the Wickford Lorry option now and it's working just apart from the All Lorries, it's displaying no data but thought it would show the data for the dates chosen, I can paste the whole code if needed in a txt file

[Barand]

How are you processing the data when a lorry or "All" is selected?

[ianhaney10]

5 minutes ago, Barand said:

How are you processing the data when a lorry or "All" is selected?

I've attached the whole code in a txt file as thought might be easier to see all the code

CURRENT CODE FOR FORUM.txt

[Moorcam]

Try this:

<select name="lorry" id="lorry" class="custom-select select-2">
    <option value="all">All Lorries</option>
    <?php
    // Establishing a connection to the database
    $mysqli = new mysqli('localhost', 'username', 'password', 'database');

    // Checking for connection errors
    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }

    // Preparing the SQL query to fetch lorries
    $sql = "SELECT id, lorry FROM lorries";
    $result = $mysqli->query($sql);

    // Checking if the query returned any results
    if ($result && $result->num_rows > 0) {
        // Fetching and displaying each lorry
        while ($row = $result->fetch_assoc()) {
            $selected = (isset($_GET['lorry']) && $row['lorry'] == $_GET['lorry']) ? 'selected' : '';
            echo "<option value=\"{$row['lorry']}\" $selected>{$row['lorry']}</option>";
        }
    } else {
        echo "<option value=\"none\">No Lorries Available</option>";
    }

    // Closing the database connection
    $mysqli->close();
    ?>
</select>

 

[Barand]

If "all" is selected you search your data for "WHERE lorry = 'all' "

Do you have any data with that lorry name?

[ianhaney10]

3 minutes ago, Barand said:

If "all" is selected you search your data for "WHERE lorry = 'all' "

Do you have any data with that lorry name?

Ahh ok, I don't have a value that matches the lorry name called all or All Lorries but be good to show all the lorries within the dates but sounds like it's using all three fields to search for the data, would I possibly then need two sql queries so if select All Lorries then would show all the lorries no matter the dates chosen or if the dates could be left empty and if I choose Basildon Lorry or Wickford Lorry and choose a start and end date then would show the lorry data as it does currently

[Barand]

If the user selects "All", leave the "lorry = '$lorry'" out of the query. Only include that bit when a specific lorry is selected.

You need to normalize your data. The lorry name should be repeated in the sales table records - the id of the lorry should be used. Therefore your lorry options should be <option value='id'>lorry name </option>

[ianhaney10]

2 hours ago, Barand said:

If the user selects "All", leave the "lorry = '$lorry'" out of the query. Only include that bit when a specific lorry is selected.

You need to normalize your data. The lorry name should NOT (edited) be repeated in the sales table records - the id of the lorry should be used. Therefore your lorry options should be <option value='id'>lorry name </option>

That's where I would get bit stuck, I tried to google it and found code that would work but I'm unsure where in my current code it would go

The lorry name is repeated in the sales data on the sales page

Instead of this code 

echo "<option value=\"{$row['lorry']}\" $selected>{$row['lorry']}</option>";

Should I have

echo "<option value=\"{$row['id']}\" $selected>{$row['lorry']}</option>";

The code I found online for the all data is below but was unsure to fit that into the current code, obviously it needed amending to be lorryall instead of username or something like that and lorry instead of username but though if can get the code right first then can change the names

$username_part = "";
if ($username != 'ALL')
{
    $username_part = "username = '$username' AND";
}

$q = "SELECT * FROM reports WHERE $username_part section_name = '$section_name' AND qeblah_status = '$qeblah_status' AND prayer_painting = '$prayer_painting' AND fatwa_status = '$fatwa_status'";

 

[ianhaney10]

I have just found this code which is look bit more easier to follow but again bit unsure where it would fit in to the current code

<?php

 $where ="1=1";

 if(isset($_POST['Station']) && !empty($_POST['Station']) && $_POST['Station'] !='All')
  {
      $where .=" and station = '$Station'";
  }

  if(isset($_POST['Section']) && !empty($_POST['Section']) && $_POST['Section'] !='All')
  {
      $where .=" and  section= '$section'";
  }

  "SELECT StationName, SectionName FROM profiles WHERE".$where

 

[mac_gyver]

here's some points about the attached code, most of which will simplify what it is doing -

1. if you put the closing </label> tag after the form field it corresponds to, you can leave out the for='...' and corresponding id='...' attributes.
2. if you put your database connection code in an a separate .php file and and require it one time on a page, you won't need to worry about posting it or your connection credentials when you ask for programming help.
3. any query that may match multiple rows of data (the lorries query) should have an ORDER BY ... term so that the rows will be in a specific order.
4. your code is conditioning and setting default values for the 3 inputs. you should use these conditioned inputs throughout the rest of the code. the current code using $_GET['lorry'] is producing php errors (visible in the 'view source' of the page) the first time your page is requested.
5. the $start_date and $end_date values are already strings in a "Y-m-d" format. Don't Repeat Yourself (DRY) and reformat them again when you use them.
6. any dynamic value you output in a html context needs to have htmlentities() applied to it to help prevent cross site scripting.
7. you should dynamically build the WHERE ... clause with only the terms that you want. if you put the terms into an array, with the lorry term being conditionally added when it is not equal to 'all', you can simply implode the contents of the array using the ' AND ' keyword to produce the WHERE clause.
8. you must use a prepared query to prevent any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished. if it seems like using the the mysqli extension with prepared queries is overly complicated, it is. this would be a good time to switch to the much simpler and better designed PDO extension.
9. the date_created column should be a DATE data type. if it is, you don't need to use sql's date() or sql's unix_timestamp() on it or php's date(...strtotime(...)) on it to get yor code to work.
10. if the sales query doesn't match any data, you should output a message stating so.
11. don't run queries inside of loops. you should use one query to get all the data that you want.

point #7 address how to leave the lorry term out of the WHERE clause when it is the 'all' value.

[mac_gyver]

here's an example showing how to dynamically build the WHERE clause, as a prepared query, using the PDO extension -

$where_terms = [];
$params = [];
					
// conditionally add the lorry term
if($lorry != 'all') // note: your html and your default value for this input is using 'all' and this comparison must match that value
{
	$where_terms[] = 'lorry = ?';
	$params[] = $lorry;
}
					
// add the date_created term
$where_terms[] = 'date_created BETWEEN ? and ?';
$params[] = $date_start;
$params[] = $date_end;
					
$where = implode(' AND ',$where_terms);
					
// build the (existing) query
$sql = "SELECT * from `sales` $where order by date_created desc";
					
// prepare, execute, and fetch the data using the PDO extension
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$sales_data = $stmt->fetchAll();
					
// at the point of producing the output, you can test if $sales_data is a boolean false value to output a message starting that there is no data to display (i would do this above the start of the html table)
// if there is data, you can loop over $sales_data to produce the output
				

 

[ianhaney10]

38 minutes ago, mac_gyver said:

here's an example showing how to dynamically build the WHERE clause, as a prepared query, using the PDO extension -

$where_terms = [];
$params = [];
					
// conditionally add the lorry term
if($lorry != 'all') // note: your html and your default value for this input is using 'all' and this comparison must match that value
{
	$where_terms[] = 'lorry = ?';
	$params[] = $lorry;
}
					
// add the date_created term
$where_terms[] = 'date_created BETWEEN ? and ?';
$params[] = $date_start;
$params[] = $date_end;
					
$where = implode(' AND ',$where_terms);
					
// build the (existing) query
$sql = "SELECT * from `sales` $where order by date_created desc";
					
// prepare, execute, and fetch the data using the PDO extension
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$sales_data = $stmt->fetchAll();
					
// at the point of producing the output, you can test if $sales_data is a boolean false value to output a message starting that there is no data to display (i would do this above the start of the html table)
// if there is data, you can loop over $sales_data to produce the output
				

 

Thank you for the code and the other points, I'm not 100% on PDO and would need to re do the whole system in PDO as it's all done in mysqli but for now I'm not sure what the code would be in mysqli to do the dynamically built where clause

[mac_gyver]

that your current code is open to sql injection (i could inject sql that will add a UNION query to your SELECT query to output the entire contents of your user(s) database table) means that you must go through the database specific code and secure it. using the much simpler and better designed PDO extension results in the least amount of work to accomplish this.

here's how you can convert any sql query that has php variables being put directly into it into a prepared query, using the PDO extension -

1. remove, and keep for later, any php variables that are inside the sql query statement. note: any wild-card characters in a LIKE comparison are supplied as part of the data value not as part of the sql query statement.
2. remove any quotes or {} that were around the php variable and any concatenation dots/extra quotes that were used to get the php variable into the sql query statement.
3. put a simple ? prepared query place-holder into the sql query statement for each value.
4. call the PDO prepare() method for the sql query statement. this returns a PDOStatement object.
5. call the PDOStatement execute([...]) method with an array of the variables you removed in step #1.
6. for a query that returns a result set, fetch the data from the query. see the PDOStatement fetch() method when fetching a single row of data. the  PDOStatement fetchAll() method when fetching all the rows of data at once. and occasionally the PDOStatement fetchColum() method when fetching a single column from a single row of data. forget about any num rows function/method/property. just fetch then test if/how many rows of data there are.
7. for a query that doesn't return a result set, you can use the PDO lastInsertId() method and the PDOStatement rowCount() method with an insert/update/delete query to get the last insert id and the number of affected rows.

if you build the sql query statement in a php variable, it makes debugging easier since you can echo the sql query statement to see what it is. this also helps reduce mistakes since it separates the sql query syntax, as much as possible, from the php syntax.

the sql query syntax for a PDO and mysqli prepared query is identical. the difference is in the php code needed to prepare, bind input parameters, execute the query, and fetch the data. mysqli has added a couple of methods in later versions of php that tries to make it more like the PDO extension, but these methods have a limitation in that they treat all data values as strings, so they are not useable for values that must be numerical (a limit clause) or are boolean or null values.

[Moorcam]

7 hours ago, ianhaney10 said:

Thank you for the code and the other points, I'm not 100% on PDO and would need to re do the whole system in PDO as it's all done in mysqli but for now I'm not sure what the code would be in mysqli to do the dynamically built where clause

Here is mac_gyver's code converted to mysqli with prepared statements:

<?php
// Initialize the MySQLi connection
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check for connection errors
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$where_terms = [];
$params = [];
$types = '';

// Conditionally add the lorry term
if ($lorry != 'all') {
    $where_terms[] = 'lorry = ?';
    $params[] = $lorry;
    $types .= 's'; // Assuming lorry is a string
}

// Add the date_created term
$where_terms[] = 'date_created BETWEEN ? AND ?';
$params[] = $date_start;
$params[] = $date_end;
$types .= 'ss'; // Assuming date_start and date_end are strings

$where = implode(' AND ', $where_terms);

// Build the query
$sql = "SELECT * FROM `sales` WHERE $where ORDER BY date_created DESC";

// Prepare the statement
$stmt = $mysqli->prepare($sql);

// Check for preparation errors
if ($stmt === false) {
    die("MySQLi prepare error: " . $mysqli->error);
}

// Bind parameters
$stmt->bind_param($types, ...$params);

// Execute the statement
if (!$stmt->execute()) {
    die("MySQLi execute error: " . $stmt->error);
}

// Fetch the data
$result = $stmt->get_result();
$sales_data = $result->fetch_all(MYSQLI_ASSOC);

// Check if there is data to display
if (empty($sales_data)) {
    echo "No data to display.";
} else {
    foreach ($sales_data as $sale) {
        // Output the data (customize as needed)
        echo "Sale ID: " . $sale['id'] . "<br>";
        // Add more fields as necessary
    }
}
?>

Hope this helps.