Solved

Can i update mysql without re-querying

Posted on 2003-12-10
6
400 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
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…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 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