Solved

Can i update mysql without re-querying

Posted on 2003-12-10
6
408 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 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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

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!

Question has a verified solution.

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

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…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

729 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