Can i update mysql without re-querying

Linux, Apache, Mysql, PHP

I issue a query and then display the results.

Now the user checks a checkbox. The checkbox corresponds to a field in the mysql database. (The user is using the checkbox to change the database).

I could re-access the entire web page, and upate the single mysql field of a single record, then re-query the exact same information. But this seems pretty inefficient.

What I want to do CONCEPTUALLY is this: When the checkbox is checked, catch the event and run a javascript (or something) and upate the single record of the database using a mysql command from javascript. The web page would not change at all (except the checkbox would be filled in).
LVL 3
rfr1tzAsked:
Who is Participating?
 
shmertConnect With a Mentor Commented:
A good pattern to follow is:

// show the checkboxes, using an array input
// each checkbox has a hidden input before it, so unchecking a box that was checked will still send some data in the request.
<form action="process_checkboxes.php" method="post">
<?
$result = mysql_query("SELECT id, active FROM mytable");
while(list($id, $active) = mysql_fetch_row($result)) {
    $checked = $active=='yes' : 'checked' : '';
    echo '<input type="hidden" name="active[' . $id . ']" value="" />';
    echo '<input type="checkbox" name="active[' . $id . ']" value="1" ' . $checked . ' />' . $id . "<br />\n" ;
} ?>
<input type="submit" />
</form>

// now in the process page update each record based on the checkboxes.  This doesn't really check for spoofed data, you should take that into consideration
<?php
// process_checkboxes.php
foreach($_POST['active'] AS $id=>$checked) {
    $active = $checked ? 'yes' : 'no';
    $query = "UPDATE mytable SET active='$active' WHERE id=$id";
    $result = mysql_query($query) or die('query failed: ' . mysql_error());
}
?>
0
 
lozlozConnect With a Mentor Commented:
hi,

on the contrary, using php is a more efficient method. ok from the user's point of view the page wouldn't change with javascript (if this were possible, which i don't think it is easily) but from a server point of view there could be an infinite number of queries taking place compared to one (or a few if you're affecting multiple rows). you should just have a page with all your checkboxes and a single submit button. let the user make their changes then update everything in one go, returning to the original updated page when done

loz
0
 
rfr1tzAuthor Commented:
But how do I keep track of which checkboxes have changed so that I can update the mysql records? What if, out of 100 records, 10 checkboxes were checked, 7 others unchecked. I'd have to remember the initial state or update all 100 records.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
lozlozCommented:
i don't know how you system works - what do the checkboxes do? how many checkboxes per record etc.? if you give me some info i can give you some help with designing the system

loz
0
 
rfr1tzAuthor Commented:
The checkboxes tell mysql to change a field from yes to no.

Even if each record has only one checkbox, I still have to remember the value of each checkbox after the initial query. Then I have to pick through the list, comparing the current value with the initial value, updating each record as required.

This is too painful. I'd rather just re-submit the page.

0
 
lozlozCommented:
sorry i'm still not understanding - is it possible to see a demo at all? whenever i've had to do something like this, i've just used hidden fields to store the old value, compared to the checkbox value then updated if necessary

loz
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.