-1

I wish to input a number into a database based of a drop down menu consisting of data from another table.

Links table:

enter image description here

Category table:

enter image description here

So basically my drop down will consist of the category.cat written information. But when I submit the form it will input category.id into the links.catID column in the database.

The code I have so far is:

<?php
// since this form is used multiple times in this file, I have made it a 
function that is easily reusable
function renderForm($links, $url, $catID, $type, $error){
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html>

<head>

<title>New Record</title>

</head>

<body>

<?php

// if there are any errors, display them

if ($error != ''){

    echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';

}

?>



<form action="" method="post">
<div>
    <strong>Link Title: *<br></strong> <input type="text" name="links" size="40" value="<?php echo $links; ?>" /><br><br/>
    <strong>URL: *<br></strong> <input type="text" name="url" size="40" value="<?php echo $url; ?>" /><br><br/>
<?php
require 'db/connect.php';
echo" <strong>Category: *<br></strong>";
echo "<select name='catID' id='catID'>";
$sql = "SELECT * FROM links";
$results = $db->query($sql);
    if($results->num_rows){
        while($row = $results->fetch_object()){
            echo "<option>";
            echo "{$row->catID}";
            echo "</option>";
        }
} echo "</select><br>";
?>
<br>
<strong>Type: *<br></strong> <input type="text" name="type" size="40" value="<?php echo $type; ?>" /><br><br/>

<p>* Required</p><br>

<input type="submit" name="submit" value="Submit">

</div>

</form>

</body>

</html>

<?php

}

// connect to the database
include('connect-db.php');

// check if the form has been submitted. If it has, start to process the form and save it to the database

if (isset($_POST['submit'])){
    // get form data, making sure it is valid
    $links = mysql_real_escape_string(htmlspecialchars($_POST['links']));
    $url = mysql_real_escape_string(htmlspecialchars($_POST['url']));
    $catID = mysql_real_escape_string(htmlspecialchars($_POST['catID']));
    $type = mysql_real_escape_string(htmlspecialchars($_POST['type']));

// check to make sure all fields are entered
if ($links == '' || $url == '' || $catID == '' || $type == ''){

    // generate error message
    $error = 'ERROR: Please fill in all required fields!';

    // if either field is blank, display the form again
    renderForm($links, $url, $catID, $type, $error);
} else {
    // save the data to the database
    mysql_query("INSERT links SET links='$links', url='$url', catID='$catID', type='$type'")
    or die(mysql_error());

    // once saved, redirect back to the view page
    header("Location: view.php");
    }
} else {

// if the form hasn't been submitted, display the form
renderForm('','','','','');

}

?>

Which gives me the following:

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
Conal Smith
  • 31
  • 1
  • 6
  • @halfer, thank you for that :) – Conal Smith Jul 28 '18 at 17:10
  • How are both of the tables linked? Can you please explain more? – Parth Manaktala Jul 28 '18 at 17:38
  • @Parth, link.catID is a indexed to caregory.id. In the links table under catID you only see 1, however there are about 50 or so links in the table and the catID is so far anywhere from 1 to 7 as shown in the id column of the category table. So I'm looking to read the category.cat in a drop down. each with a value of category id but INSERT it into links.catID. I hope this makes sense :) – Conal Smith Jul 28 '18 at 17:45
  • I posted a answer, check if that helps you – Parth Manaktala Jul 28 '18 at 17:48

1 Answers1

0

May be try this? (Considering the links.links columns and category.cat columns are common)

Store the value of dropdown in a variable say $dropdown_selected_option

Getting the id from the category table using sql:

$sql = "Select id from category where cat = '$dropdown_selected_option'";
$result = mysqli_query($conn, $sql);

Later run a query again to update the given fields in second table;

Update links set 

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result))
   {
   // Run update query here where $row['id'] has the ID from the category table required.
   }
}
Parth Manaktala
  • 1,112
  • 9
  • 27
  • Thanks for your time with this. This is going to sound stupid and I apologize I'm trying to teach myself this and the logic somethings catches me out. I understand you getting the ID from category database. I understand your getting the information from the database under the column cat, however do you not need to create the variable $dropdown_selected_option elsewhere before you can use it in a select statement? Or will it pull the information that I click on in this instance? I'm really new to Php and I'm struggling at the minute, sorry :'(. – Conal Smith Jul 28 '18 at 18:05
  • You need to pull it on submit button pressed, via $_POST(considering you are using post here). Refer here if you want to know how : https://stackoverflow.com/questions/17139501/using-post-to-get-select-option-value-from-html/17139538 – Parth Manaktala Jul 29 '18 at 02:06
  • Also don’t forget to upvote/accept the correct answer so as other’s who face the similar problem might know what the correct solution is. – Parth Manaktala Jul 29 '18 at 02:08
  • Thank you, I will look into trying this after work today, and hopefully I can figure it out. I will update here and tick your answer as right if/when I figure it out using your method :) Thanks a lot. – Conal Smith Jul 29 '18 at 05:56
  • I was trying to work on this all last night and couldn't get it working :'( I will upload the code I currently have when I get home. – Conal Smith Jul 30 '18 at 05:57
  • Okay. Will look into it – Parth Manaktala Jul 30 '18 at 07:29