MisterNubb MisterNubb - 1 month ago 9
MySQL Question

Execute Two query in the same script php

I have made one script which is suppose to update 2 different table at the same time. The problem is that only one table get updated and I cant get why?

Logic is:

The script should update the record of job.status and customer.status at the same time after I click on update button.

Only the table job get updated.

I tried to run only the second query and does not work.
I tried to run a query to another table on the same database but is not working.

Any suggestion?

here is my code:

<?php
include("connection.php");

if (isset($_POST['update'])) {
$results = $link->query("UPDATE job SET status='$_POST[status]', priority='$_POST[priority]' WHERE id='$_POST[hidden]'");
$results = $link->query("UPDATE customer SET status='$_POST[status]' WHERE id='$_POST[hidden]'");
}
$sql = "SELECT * from job";
if (!$result = $link->query($sql)) {
die('There was an error running the query [' . $link->error . ']');
}
echo "
<table class='table'>
<thead>
<tr>";
/* Get field information for all columns */
while ($finfo = $result->fetch_field()) {
echo " <th>" . $finfo->name . "</th>"; }
echo "
</tr> </thead> <tbody>";

while ($row = $result->fetch_assoc()) {
$job_id = $row['id'];

echo "<form action='' method=post>";
echo "<tr class='info'>

<input type=hidden name=hidden value=" . $row['id'] . ">
<td>" . $row['id'] . "</td>
<td>" . $row['device'] . "</td>
<td>" . $row['model'] . "</td>
<td>" . $row['problem'] . "</td>
<td>

<select class='form-control col-sm-10' id='status' name='status'>
<option value='new' ". ($row['status'] == 'new'? 'selected ': '') .">New</option>
<option value='progress' ". ($row['status'] == 'progress'? 'selected ': '') .">Progress</option>
<option value='wait' ". ($row['status'] == 'wait'? 'selected ': '') .">Wait</option>
<option value='done' ". ($row['status'] == 'done'? 'selected ': '') .">Done</option>
<option value='close' ". ($row['status'] == 'close'? 'selected ': '') .">Close</option>
</select>
</td>

<td>
<button type='submit' class='btn btn-primary btn-sm' name='update'>Update</button>
</td>
<td>
<a class='btn btn-primary btn-sm get_info' data-toggle='modal' data-target='#myModal' name='job_id' value= '[$job_id]'> Customer Info</a>
</form>
</td>

</tr>";
echo "</form>";


}
echo "
</tbody>

</table>";

?>


----UPDATE----

I notice that when i send this query is working.

$results = $link->query("UPDATE customer SET status='changethevalue' WHERE id='$_POST[hidden]'");


And when I send this one is working

$results = $link->query("UPDATE customer SET status='changevalue'");


----UPDATE 2----

I rewrote the query like this and is apparently working. But I dont know why.

if (isset($_POST['update'])) {

$results = $link->query("UPDATE customer SET status='$_POST[status]' WHERE id='$_POST[hidden]'");

$results = $link->query("UPDATE job SET status='$_POST[status]' WHERE id='$_POST[hidden]'");

}

Answer

there is no issue in the update queries but make sure you are using corect column names or try echo the 2nd query and run in php myadmin e-g echo $query="customer SET status='$_POST[status]' WHERE id='$_POST[hidden]'" then $results = $link->query($query);