Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can i update mysql without re-querying

Posted on 2003-12-10
6
Medium Priority
?
415 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 13

Assisted Solution

by:lozloz
lozloz earned 300 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 700 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to implement server side field validation and display customized error messages to the client.
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…
The viewer will learn how to dynamically set the form action using jQuery.

688 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