'Get' data from MSQL database using dropdown menu as 'keyword'

Beginners - Learning PHP
#1

Hi guys am very new to PHP and have bundled along for weeks using tutorials, sorting many ‘issues’ along the way, but am stuck, and would appreciate your help!

Here’s the page link in the comment for a visual in case I don’t make sense:
https://flighteducation.co.uk/Careers.php.

I have a search area (top right) that searches a mysql database for a ‘career’, and echos out the rest of the information in the database, below in the HTML using the PHP code:

<?php
session_start();
$output=NULL;

//Connect to the database
$mysqli = new mysqli("localhost", "", "", "");

//Query the database  
if((isset($_POST['submit'])) && (isset($_POST['search']) && ($_POST['search'] != NULL))) {
$search = $mysqli->real_escape_string($_POST['search']);
$result = $mysqli->query("SELECT * FROM careers WHERE jobTitle regexp '$search'");

    if($result->num_rows > 0)
    {
        while($rows=$result->fetch_assoc())
        {
            $jobTitle=$rows['jobTitle'];
            $jobDesription=$rows['jobDescription'];
                $salaryLow=$rows['salaryLow'];
                    $salaryHigh=$rows['salaryHigh'];
						$typicalHours=$rows['typicalHours'];
							$timeDay=$rows['timeDay'];
								$howBe=$rows['howBe'];
									$universityInfo=$rows['	universityInfo'];
										$universityRequirements=$rows['universityRequirements'];
											$collegeInfo=$rows['collegeInfo'];
												$collegeRequirements=$rows['collegeRequirements'];
													$otherRoutes=$rows['otherRoutes'];
														$directApplication=$rows['	directApplication'];
															$volunteeringExperience=$rows['volunteeringExperience'];
																$moreInformation=$rows['moreInformation'];
																	$skillsKnowledge=$rows['skillsKnowledge'];
																		$dayTasks=$rows['dayTasks'];
																			$workingEnvironment=$rows['	workingEnvironment'];
																				$careersProgression=$rows['careersProgression'];
            
			$output .="<b>Job Title:</b><br /> $jobTitle<br /><br />
                        <b>Job Description:</b><br /> $jobDesription<br /><br />
                        <b>Salary Low:</b><br /> $salaryLow<br /><br />
                        <b>Salary High:</b><br /> $salaryHigh<br /><br />
						<b>Typical Hours:</b><br /> $typicalHours<br /><br />
						<b>Typical Hours:</b><br /> $timeDay<br /><br />
						<b>How to become a:</b><br /> $howBe<br /><br />
						<b>University:</b><br /> $universityInfo<br /><br />
						<b>University requirements:</b><br /> $universityRequirements<br /><br />
						<b>College:</b><br /> $collegeInfo<br /><br />
						<b>College requirements:</b><br /> $collegeRequirements<br /><br />
						<b>Other routes:</b><br /> $otherRoutes<br /><br />
						<b>Direct application:</b><br /> $directApplication<br /><br />
						<b>Volunteering and Experience:</b><br /> $volunteeringExperience<br /><br />
						<b>More Information:</b><br /> $moreInformation<br /><br />
						<b>Skills & Knowledge:</b><br /> $skillsKnowledge<br /><br />
						<b>Day to Day tasks:</b><br /> $dayTasks<br /><br />
						<b>Working Environment:</b><br /> $workingEnvironment<br /><br />
						<b>Careers Progression:</b><br /> $careersProgression<br /><br />
                        <br /><br /><br />";
        }
    }
    else{
        $output="We can't seem to find any related Careers!<br />Try simplifying your search?";
    } 
}
?>

with the ‘output’ code after the search button section being:

<!--PHP OUTPUT-->
	<?php echo $output; ?>

My problem is that I also have a drop-down menu on the left which is ‘populated’ by a column in the database using the code:

<!--DROPDOWN SEARCH-->
<?PHP
$dropdown=$mysqli->query("SELECT jobTitle FROM careers");	
?>

	<!--Top Container -->
	<section class="careers-results">
	  <header class="showcase-career">
		  <label for="Careers">Choose a Career:</label>
		  	<img src="img/Careers-Logo.png" alt="LOGO"> <be>
<select class="careers-dropdown" name="Careers" id="Careers">
<?PHP
while ($rows=$dropdown->fetch_assoc())
{
$jobTitle=$rows['jobTitle'];
echo "<option value='jobTitle'>$jobTitle</option>";
}
?>

**I am struggling to get the ‘selection’ in the dropdown menu to trigger the same MySQL database, and echo the other information, as it does in the ‘search option’

I am supposing I need to input a $_GET somewhere? And is it possible to use the same <?php echo $output; ?> as used in the ‘search results’?

apologies in advance for my immaturity in knowledge and use of language (and length of this)

very much appreciated

Steven**

#2

You are chasing your tail figuratively speaking. First a few suggestions, try to keep most of your PHP and HTML/CSS separated as much as possible. Use prepared statements as well as MySQL when actually doing a query. The next is a personal choice use either mysqli or PDO. Personally I use PDO as you can use more databases with it and the prepared statements make a little more sense in my opinon.

With that said I did a search in my PHP sandbox directory a long time ago just to see how search work. Doing a sandbox saves a lot of time coding as it helps to get the coding down and makes it easier to code the real project. That’s if you have the time. Anyways here’s a search I did:

<?php
include 'lib/includes/connect/connect.php';

$db_options = [
    /* important! use actual prepared statements (default: emulate prepared statements) */
    PDO::ATTR_EMULATE_PREPARES => false
    /* throw exceptions on errors (default: stay silent) */
    , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    /* fetch associative arrays (default: mixed arrays)    */
    , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
];
if (filter_input(INPUT_POST, submit)) {

    $pdo = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=world;charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);

    $query = 'SELECT Name, CountryCode, District, Population FROM city WHERE CountryCode=:CountryCode ORDER BY District'; // Set the Search Query:

    $stmt = $pdo->prepare($query); // Prepare the query:

    $result = $stmt->execute([':CountryCode' => filter_input(INPUT_POST, countryCode)]); // Execute the query with the supplied user's parameter(s):
}
?>
<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Database Search</title>
        <link rel="stylesheet" href="lib/css/reset.css">
        <link rel="stylesheet" href="lib/css/grids.css">
        <link rel="stylesheet" href="lib/css/searchstyle.css">
    </head>
    <body>
        <div class="container seachBackground">
            <form id="searchForm" action="search.php" method="post">
                <label for="searchStyle">search</label>
                <input id="searchStyle" type="text" name="countryCode" value="" placeholder="Enter Country Code (For Exampe : USA)..." tabindex="1" autofocus>
                <input type="submit" name="submit" value="submit" tabindex="2">
            </form>
        </div>
        <div>
            <table id="search" summary="Cities Around the World!">
                <thead>
                    <tr>
                        <th scope="col">City</th>
                        <th scope="col">Country Code</th>
                        <th scope="col">District / State</th>
                        <th scope="col">Population</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    if ($result) {
                        while ($record = $stmt->fetch()) {
                            echo "<tr>";
                            echo '<td>' . $record->Name . "</td>";
                            echo '<td>' . $record->CountryCode . "</td>";
                            echo '<td>' . $record->District . "</td>";
                            echo '<td>' . $record->Population . "</td>";
                            echo "</tr>";
                        }
                    } 
                    ?>
                </tbody>
            </table>
        </div>

    </body>
</html>

It’s not the tightest code, but I just wanted to show that most of the PHP and HTML/CSS is separated, sure there some code that is intermix though it isn’t a lot. That’s my coding philosophy as it makes it easier to code and debug for me, plus I can stylize the HTML with CSS before I even interject the PHP code. That is what I would do, but going with this style of coding I am sure someone here will give you hand in your coding problem. Though I have found following a bunch of tutorials can create more of a mess than helping you. Get the concept down by following a certain tutorial, use your own code when you get it down to where you are comfortable with it and the flow should be much easier. Just my .02 cents.

#3

You do realize this does absolutely nothing right?

You do not have any filters listed.

If omitted, FILTER_DEFAULT will be used, which is equivalent to FILTER_UNSAFE_RAW . This will result in NO FILTERING TAKING PLACE by default.

#4

Thanks for the reply Strider64…It’s very much appreciated!!

I know all too well how tutorials can mess things up, as it’s taken me 7 weeks just to get to where I am now (nightmare, and wont dull you with the details!!). Spent literally three days watching PHP tutorials for beginners, and whilst a some has sunk in, I am far from writing ‘big’ stuff of my own yet…hence here!

Have just checked out the PHP sandbox directory, and that will certainly help further!

Also, thanks for your advice on keeping things a little tidier…am finding my CSS file now a little tiresome! ZZzzzz

Thanks again

Steven

#5

Hi folks, and thank you for the help above! So, I have tried to do this another way now, which should be simpler?

I am going to create the drop-down box menu via HTML and not populated by the database which means I could run it from a HREF lookup.

Not quite there yet, but have a php of:

<?php
$id = $_GET['jobTitle'];


if( (int)$id == $id && (int)$id > 0 ) {
	
    $link = mysqli_connect('localhost','jj3wg2td_steven','PASSWORD','jj3wg2td_careerslist');      // Connect to Database
	
	if (!$link) {
    die('Could not connect: ' . mysqli_connect_error());
}
     
    $sql='SELECT * FROM careers WHERE jobTitle=' .$id;
     
    $result = mysqli_query($link,$sql);

    $row = mysqli_fetch_array($result);
   
     echo $row['jobTitle'];
	 echo $row['jobDescription']; 
}

else {

   echo "Record NOT FOUND";
}
?>

And then HMTL code of (example):

<a href="Careers Results.php?jobTitle=Animator">

Any pointers or advice, before I spend the next 4 weeks not sleeping are appreciated!

Steven

Sponsor our Newsletter | Privacy Policy | Terms of Service