Solved

Can i update mysql without re-querying

Posted on 2003-12-10
6
389 Views
Last Modified: 2012-05-05
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).
0
Comment
Question by:rfr1tz
  • 3
  • 2
6 Comments
 
LVL 13

Assisted Solution

by:lozloz
lozloz earned 75 total points
ID: 9912935
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
 
LVL 3

Author Comment

by:rfr1tz
ID: 9912977
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
 
LVL 13

Expert Comment

by:lozloz
ID: 9913311
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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 3

Author Comment

by:rfr1tz
ID: 9913681
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
 
LVL 13

Expert Comment

by:lozloz
ID: 9913790
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
 
LVL 11

Accepted Solution

by:
shmert earned 175 total points
ID: 9916012
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question