Using PHP to update database, when dropdown list changed

[jxfish2]

I've built some pretty sophisticated PHP / HTML / MYSQL scripts at work, but in each case, the action recalls, and reloads the page.

 

So far, I have not needed to use JavaScript, and the pages work exactly as intended.

 

For my purposes, so far, this has worked quite nicely.

 

But, I have a new requirement, that I can not seem to get to work.

 

I am trying to initialize a form, but the form includes a select dropdown box.

 

I would really like to have this update the database entry, and the display, whenever the value in this dropdown box is changed.

 

I am including some snippets of what I have in place so far, and bear in mind, that I have tried many different things, but nothing has worked so far...

 

Note that the webpage itself changes the value, but no SQL code is going back to the database itself:

 

<form action="Get_All_Assets.php" method="POST">

<input type="hidden" name="alt" value="<?php echo $row['alt_code'];?>" />

<input type="hidden" name="pri" value="<?php echo $row['prio'];?>" />

<input type="hidden" name="loc" value="<?php echo $row['location'];?" />

<center>

<select onchange="Get_All_Assets.php" name="NEWPRIO">

<option value="?php echo $row['prio'];?><?php echo $row['prio'];?></option>

<option value="1">1</option>

<option value="2">2</option>

<option value="3">3</option>

</select>

</center>

</form>

 

Remember, that the goal is that we can change the priority of the asset, on the fly, both on the current html display, as well as in the database itself, so that other users will see the updated priority, whenever they access the record.

 

At the beginning of the PHP file, I am setting the NEWPRIO environment variable:

 

$NEWPRIO="";

$NEWPRIO=$_POST['NEWPRIO'];

 

Next, I check to see if the $NEWPRIO variable is set to a value, other than NULL or blank:

 

if ("$NEWPRIO" != "")

{

mysqli_query($con, "UPDATE table SET prio='$NEWPRIO' WHERE location='$LOC' and alt_code='$alt_code';");

}

 

Again, when I click on the dropdown box and change the value, it changes on the webpage, but not inside of the database.

 

Is the onchange function in the right location?

 

Is the onchange function correct in this situation?

 

I was not able to find any examples that actually used these same conditions, so I was just trying to wing it...

 

Any help, or "specific" suggestions would be greatly appreciated.

 

Thanks in advance, and have a great day.

 

JCF

 

[Ingolme]

That onchange attribute on the <select> element isn't doing anything because it expects Javascript, not a filename. Your form should have a submit button (<input type="submit">) so that it can submit the data to the server.

 

 

 

The quotes around the variable in this line are unnecessary:

if ("$NEWPRIO" != "")

It's just casting $NEWPRIO to a string, except that it was already a string to begin with.

[justsomeguy]

The onchange attribute, like all other event handler attributes, is supposed to be Javascript code, not a filename. You need to specify a Javascript function to run when the event fires. You can have that function submit the form if you want to, and then the page will refresh with the submitted data.

[jxfish2]

Note that all of the environment variables are initialized / read in, at the beginning of the script.

 

I just did not include all of them in the above example.

 

Also note that I am using this same form syntax for multiple other queries and database updates, within this same script.

 

The only difference, is that this new form requires a dropdown select function, and I have not been able to find the correct format / syntax so far.

 

JCF

Edited September 23, 2014 by jxfish2

[jxfish2]

Hi Ingolme,

 

Note that I set the variable to "", prior to reading in the new data from the POST submission.

 

In theory, the only time this condition would be true, is if the new value was read in by the page refresh.

 

And I thought that was the purpose of the onchange function, so that I would not need a submit button.

 

So, if I want the onchange to execute the filename php script, what should I put in the <select> statement, and what should I put in the <form> statement?

 

I CAN add a submit button if necessary, but I really did not want to go that route.

 

But, I've already tried using a submit button, but something is still wrong in the <form> and <select> statements.

 

Note that I am calling this same php script in numerous other <form> entries within this same code, and it is working everywhere else.

 

I just do not have any other embedded <select> statements within any of my other <form> entries.

 

JCF

Edited September 23, 2014 by jxfish2

[jxfish2]

Hi Justsomeguy,

 

I am calling this same script from within many other <form> entries in this code, and it is working everywhere else, just as intended.

 

But, none of my other form entries contain an embedded <select> clause.

 

Is there no way to make this form, with the embedded <select> clause, call this same php script?

 

It's been awhile, but you've helped me in the past, and I trust your judgement.

 

But, I have never really used JavaScript before, and I've managed to get my PHP code to do what I want so far.

 

If there is any way to make the form call this same php script, while there's an embedded <select> statement, this is what I would prefer to do...

 

If there is no way to make this work the way I want, then I will be forced to use JavaScript.

 

But, I don't really understand JavaScript, so I might need some real help there.

 

JCF

Edited September 23, 2014 by jxfish2

[Ingolme]

What you're doing with $NEWPRIO is superfluous, as soon as you make an assignment, any previous assignments are like if they never happened.

 

This:

$NEWPRIO="";$NEWPRIO=$_POST['NEWPRIO'];

Is completely equivalent to this:

$NEWPRIO=$_POST['NEWPRIO'];

Like I mentioned before, wrapping the variable in quotes for the comparison is useless as well.

This:

if ("$NEWPRIO" != "")

Is the same as this:

if ($NEWPRIO != "")

Now the core problem is that your form was not submitting. onchange is a Javascript thing and cannot be used for PHP. Your form must have a submit button if you want it to send the data, unless you want to learn Javascript.

 

When the form gets submitted if there's still a problem then you should check what values the variables have. It's impossible to diagnose a problem without looking at the data that the program is working with.

if ("$NEWPRIO" != ""){     mysqli_query($con, "UPDATE table SET prio='$NEWPRIO' WHERE location='$LOC' and alt_code='$alt_code';");}

First print out all the values you're using:

$NEWPRIO, $LOC, $alt_code and others. Make sure they have the values you expected them to. If they don't then that's a clue to where the problem is occurring.

 

Your code is susceptible to two major problems:

1. If $_POST['NEWPRIO'] was not actually sent to the server then PHP is issuing a warning. Your program might be supressing the warning but it's still there and is probably the cause of some problems. An undefined variable might not be equal to an empty string.
2. You're not sanitizing any of your data, people could hack your database by injecting SQL into the $_POST variables. They could possibly send the string "'WHERE 1 -- " and make the database empty the field for all the records.

[jxfish2]

Thanks Ingolme for your comments and help.

 

I managed to fix the issue, and the code is now working as intended.

 

Just an FYI... I have a lot of error checking, and data validation going on inside of these scripts... A lot...

 

And, my query, and update commands are very specific.

 

No user can execute any of the update commands, unless logged in, and the ID is validated each time a submit button is selected.

 

Additionally, I did not post any specific table names here, and a number of other fields necessary to make each of the updates work, were never posted here either.

 

And finally, this code sits behind the firewall, and is only accessible via the company intranet. There is no external connectivity to these servers, or to my webpages.

 

Again, thank you for your input, and have a great week.

 

JCF

[justsomeguy]

Is there no way to make this form, with the embedded <select> clause, call this same php script?

Yes, but not just by putting a filename in the event handler attribute. Putting a PHP filename in there is the same as doing this:

<script type="text/javascript">Get_All_Assets.php</script>

It expects Javascript code, but you are just writing a filename. The text "Get_All_Assets.php" is not valid Javascript code. Write a Javascript function to submit the form, and then call the function.

<select onchange="submit_assets_form()" ...

Then you write a Javascript function called submit_assets_form, which targets that form element and submits it. Then it will be like you manually pressed the submit button.https://developer.mozilla.org/en-US/docs/Web/API/HTMLFormElement.submit