zer0uk Posted March 4, 2020 Share Posted March 4, 2020 Hello, must admit i'm very new to this and just a hobiest , I am creating a page which will be used for logging information. With a mysql database i have two tables "tbl_rides" & "tbl_courses" rides is the main table for storing information and has a foreign key associating column "course_id" with course_id in the tbl_courses What i want to do on a HTML/PHP page is display a drop down box that has the contents of "course" from the tbl_courses but when the user selects it and submits the record is written to the main form "tbl_rides" "course_id" so display the friendly name via the foreign key but the record is written to "tbl_rides" Can that be done .. am i going about this wrong ? Thanks for any help Andrew. Quote Link to comment Share on other sites More sharing options...
gw1500se Posted March 4, 2020 Share Posted March 4, 2020 It can be done and is a routine thing. Here is a simple example. Quote Link to comment Share on other sites More sharing options...
zer0uk Posted March 5, 2020 Author Share Posted March 5, 2020 Hi Thanks for the reply but I don't seem to be able to see where it tells me how to change the data I supply to the form from course_name to course_id, below is the code at the minute this will populate the list with course_id and post to the DB correctly. I need the List to Display the name (course_name) <?php // database connect include('../db_connect.php'); //-------- Check Data ----------- //Check if Data is sent & validate if(isset($_POST['name_submit'])){ if(empty($_POST['name_submit'])){ Echo 'No Course passed'; } else { $var_course_id = mysqli_real_escape_string($conn, $_POST['course_id']); //Create sql $sql = "INSERT INTO tbl_rides(course_id) VALUES ('$var_course_id')"; //Save to DB and check if(mysqli_query($conn, $sql)){ //success } else { echo 'query error: ' . mysqli_error($conn); } } } //end of checking ?> <!DOCTYPE html> <html> <form class="white" action="dropdown.php" method="POST"> <select name="course_id"> <?php // query to create course_id dropdown $resultset = $conn->query("SELECT course_id FROM tbl_rides"); while($rows = $resultset->fetch_assoc()) { $var_course_id = $rows['course_id']; echo "<option value='$var_course_id'>$var_course_id</option>"; } ?> </select> <div class="centre" > <input type="submit" name="name_submit" value="Submit my information !" /> </div> </form> </html> Quote Link to comment Share on other sites More sharing options...
gw1500se Posted March 5, 2020 Share Posted March 5, 2020 First please use the code icon (<>) and select PHP for your code. It makes it much easier to read. I'm not sure but I think you need to change your loop to this: $rows = $resultset->fetch_assoc(); foreach ($rows as $row) { $var_course_id = $row['course_id']; echo "<option value='$var_course_id'>$var_course_id</option>"; } Quote Link to comment Share on other sites More sharing options...
zer0uk Posted March 5, 2020 Author Share Posted March 5, 2020 Thanks for your reply that produced quite a strange drop down so the information in the courses table is (course_id) = 15 (course_name) =J2/1 my drop down with your new loop had a J and then a 1 so I think it was only using the 1st letter from both columns and displaying both !!! I changed my code slightly and it now displays the J2/1 in the dropdown which is correct but its still trying to post that information rather than the course_id back to the db so weighting a 0 (which is wrong should be 15) I will try and post the new code below (correctly ) <?php // database connect include('../db_connect.php'); //-------- Check Data ----------- //Check if Data is sent & validate if(isset($_POST['name_submit'])){ if(empty($_POST['name_submit'])){ Echo 'No Course passed'; } else { $var_course_id = mysqli_real_escape_string($conn, $_POST['course_id']); //Create sql $sql = "INSERT INTO tbl_rides(course_id) VALUES ('$var_course_id')"; //Save to DB and check if(mysqli_query($conn, $sql)){ //success echo ("$var_course_id"); } else { echo 'query error: ' . mysqli_error($conn); } } } //end of checking ?> <!DOCTYPE html> <html> <form class="white" action="dropdown.php" method="POST"> <select name="course_id"> <?php // query to create course_id dropdown $resultset = $conn->query("SELECT * FROM tbl_courses"); while($rows = $resultset->fetch_assoc()) { $var_course_name = $rows['course_name']; echo "<option value='$var_course_name'>$var_course_name</option>"; } ?> </select> <div class="centre" > <input type="submit" name="name_submit" value="Submit my information !" /> </div> </form> </html> Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 5, 2020 Share Posted March 5, 2020 I think what you are after is having the course ID be the VALUE of the option (which is what is sent to the server) and the Course ID as the LABEL for the option. Also, only SELECT the field(s) you want in a query - do not use '*' $resultset = $conn->query("SELECT course_id, course_name FROM tbl_courses"); while($row = $resultset->fetch_assoc()) { echo "<option value='{$row['course_id']}'>{$row['course_name']}</option>"; } Quote Link to comment Share on other sites More sharing options...
zer0uk Posted March 5, 2020 Author Share Posted March 5, 2020 Thanks for the help , I changed my code to yours but now my drop menu has nothing in it. What I want is the course_id be the value and the course_name as the label for the option. <?php // database connect include('../db_connect.php'); //-------- Check Data ----------- //Check if Data is sent & validate if(isset($_POST['name_submit'])){ if(empty($_POST['name_submit'])){ Echo 'No Course passed'; } else { $var_course_id = mysqli_real_escape_string($conn, $_POST['course_id']); //Create sql $sql = "INSERT INTO tbl_rides(course_id) VALUES ('$var_course_id')"; //Save to DB and check if(mysqli_query($conn, $sql)){ //success echo ("$var_course_id"); } else { echo 'query error: ' . mysqli_error($conn); } } } //end of checking ?> <!DOCTYPE html> <html> <form class="white" action="dropdown.php" method="POST"> <select name="course_id"> <?php // query to create course_id dropdown $resultset = $conn->query("SELECT course_id, course_name FROM tbl_courses"); while($rows = $resultset->fetch_assoc()) { echo "<option value='{$row[course_name]}'>{$row['course_name']}</option>"; } ?> </select> <div class="centre" > <input type="submit" name="name_submit" value="Submit my information !" /> </div> </form> </html> Quote Link to comment Share on other sites More sharing options...
Barand Posted March 5, 2020 Share Posted March 5, 2020 (edited) Variable names! while($rows = $resultset->fetch_assoc()) ^^^^ { echo "<option value='{$row[course_name]}'>{$row['course_name']}</option>"; ^^^ ^^^ } And the value should be the course_id Edited March 5, 2020 by Barand Quote Link to comment Share on other sites More sharing options...
zer0uk Posted March 5, 2020 Author Share Posted March 5, 2020 Whoops on the variable !!! Thanks all its been driving me mad , its working perfectly cheers. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.