I'm stuck with trying to process multiple mySQL updates at the same time. I have 4 select/optiion boxes that pull entries from a db table. I want to be able to update the db onChange using JQuery. I have managed to get this working with one select module but as soon as I add more it spins out. I know that the main bad code is in db_submit.php but really not sure how else to write it. I know there has to be a cleaner way to do this.
FORM PAGE- INPUT.PHP
<html>
<head>
<script src="../assets/scripts/jquery-2.0.3.min.js"></script>
<script>
function updateDb() {
$.post("db_submit.php", $("#console").serialize());
}
</script>
<?php
include 'db_connect.php';
?>
</head>
<body>
<form id="console">
<select id="frame1" name="frame1" onChange="updateDb()">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
<select id="frame2" name="frame2" onChange="updateDb()">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
<select id="frame3" name="frame3" onChange="updateDb()">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
<select id="frame4" name="frame4" onChange="updateDb()">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
</form>
</body>
<?php
mysqli_close($con);
?>
</html>
PROCESSING PAGE- DB_SUBMIT.PHP
<?php
include 'db_connect.php';
$frame1= mysqli_escape_String($con,$_POST['frame1']);
$frame2= mysqli_escape_String($con,$_POST['frame2']);
$frame3= mysqli_escape_String($con,$_POST['frame3']);
$frame4= mysqli_escape_String($con,$_POST['frame4']);
$query = "UPDATE frameContent SET url='".$frame1."' WHERE name='frame1'";
$query = "UPDATE frameContent SET url='".$frame2."' WHERE name='frame2'";
$query = "UPDATE frameContent SET url='".$frame3."' WHERE name='frame3'";
$query = "UPDATE frameContent SET url='".$frame4."' WHERE name='frame4'";
mysqli_query($con,$query);
mysqli_close($con);
?>
I know that constantly setting the $query variable is causing problems but I'm not sure how else I can do this in the one page. Any help would be much appreciated.
Thanks!
First of all make sure the $queries are concatenated, then terminate each query with a semi-colon. After these you can use mysqli_multi_query to execute all four updates in one call from php.
$query = "UPDATE frameContent SET url='".$frame1."' WHERE name='frame1';";
$query .= "UPDATE frameContent SET url='".$frame2."' WHERE name='frame2';";
$query .= "UPDATE frameContent SET url='".$frame3."' WHERE name='frame3';";
$query .= "UPDATE frameContent SET url='".$frame4."' WHERE name='frame4';";
mysqli_multi_query($con,$query);
I think this might help :) but there's just a little changes within your codes:
<html>
<head>
<script src = "js/jquery-1.10.1.js"></script>
<script>
function updateDb()
{
// this var id will store all your 4 combobox values in an array
var id = [{val1: $("#frame1").val()},
{val1: $("#frame2").val()},
{val1: $("#frame3").val()},
{val1: $("#frame4").val()}];
//this .post will submit all data to db_submit.php
$.post("db_submit.php",{id:id}, function(data)
{
alert(data);
});
</script>
<?php
include 'db_connect.php';
?>
</head>
<body>
<select id="frame1" name="frame1">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
<select id="frame2" name="frame2">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
<select id="frame3" name="frame3">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
<select id="frame4" name="frame4">
<option value="">Select Channel</option>
<?php
$result = mysqli_query($con,"SELECT * FROM feedContent");
while ($row = mysqli_fetch_array($result)) {
echo "<option value='" . $row['url'] . "'>" . $row['name'] . "</option>";
}
?>
</select>
<input type="button" value="Submit" onClick="updateDb()"/>
</body>
<?php
mysqli_close($con);
?>
</html>
And in your DB_SUBMIT.PHP
<?php
include 'db_connect.php';
$frame1= mysqli_escape_String($_POST['id'][0]['val1']);
$frame2= mysqli_escape_String($_POST['id'][1]['val1']);
$frame3= mysqli_escape_String($_POST['id'][2]['val1']);
$frame4= mysqli_escape_String($_POST['id'][3]['val1']);
$query = mysqli_query("UPDATE frameContent SET url='$frame1' WHERE name='frame1'");
$query = mysqli_query("UPDATE frameContent SET url='$frame2' WHERE name='frame2'");
$query = mysqli_query("UPDATE frameContent SET url='$frame3' WHERE name='frame3'");
$query = mysqli_query("UPDATE frameContent SET url='$frame4' WHERE name='frame4'");
echo "Data was Successfully updated";
mysqli_close($con);
?>
I just add a button there for convenience, but if you dont want it just delete it and put back the onChange on every combocboxes that you have :)