?
Solved

dynamic list with checkbox mysql php

Posted on 2009-04-06
10
Medium Priority
?
648 Views
Last Modified: 2013-12-12
Hi - I'm very new to both php and mysql so will need detailed explanation - sorry!
I have created an update page which lists projects from a table in my database. The table has a field called 'live' which i'm trying to use to determine whether the project is to be included on the site or not. I'm using a checkbox nect to each project to allow the user to toggle live between the values 'y' and'n'. I have the list working just fine and i've managed to get the checkbox to show the current value in the database but i'm stumped as to how I then update the database with the chosen value. the field 'live' needs to be sent both 'y' and 'n' values for each project_id. I've tried various ways and think maybe foreach is the way to go but at present have deleted all attemps and am close to giving up. I'd appreciate any help I can get. thanks I've included the code for my form....
0
Comment
Question by:heather-allen
[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
  • 6
  • 4
10 Comments
 
LVL 1

Author Comment

by:heather-allen
ID: 24092503
Hi - i've upped the points - can anybody help me? Not sure if the code got added so here it is again just in case...

<form method="POST" action="">


<ul id="project_list">

 
   
      <?php // query projects table and print out each item

$result = mysql_query("SELECT project_id, project_name, client, thumbnail, live, project_order FROM projects ORDER BY project_order");



// print the list items

while ($row = mysql_fetch_assoc($result)) {

if($row['live']=="y") {$live="checked=\"checked\"";} else {$live="";}

echo '<li>

<input type="hidden" name="form_order[]" value="'. $row['project_id'] .'" />
<table width="900" border="0" id="projectform">
  <tr>
    <td width="200">'. $row['project_name'] .'</td>
    <td width="200">'. $row['client'] .'</td>
    <td width="200">'. $row['thumbnail'] .'</td>
    <td width="54" align="center"><input type="checkbox" name="live_check[]"'. $live.'value="y"/></td>
    <td width="82"><img src="../images/thumb/'. $row['thumbnail'] .'" alt="'. $row['project_name'] .'" width="82" height="50" /></td>
    <td width="82" align="center"><a href="project_update.php?project_id='. $row['project_id'] .'">EDIT</a></td>
    <td width="82" align="center"><a href="project_delete.php?project_id='. $row['project_id'] .'">DELETE</a></td>
</tr>
</table>
</li>';

}

?>
     
</ul>


<div class="holder">
  <input type="submit" name="reorder" value="update projects" />
</div>

</form>



I just need to know how to post the result to my database for each item whether checked or not.

Thank you
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24092873
I would recommend using a radio group per project.

An unchecked checkbox sends no data back to the server, so you you will have to do a lot more work to determine what you need to set.

With a radio group per project, you get the appropriate value.

Something like this ...


// Get SQL to provide is_live and is_not_live ...

$result = mysql_query
      (
      "SELECT
            project_id,
            project_name,
            client,
            thumbnail,
            live,
            project_order,
            CASE WHEN live = 'y' THEN ' checked' ELSE '' END AS is_live,
            CASE WHEN live <> 'y' THEN ' checked' ELSE '' END AS is_not_live
      FROM
            projects
      ORDER BY
            project_order"
      );

// Use the radio groups like this.
<input type="radio" name="live_check[{$row['project_id']}]" value="1"{$row['is_live']} />
<input type="radio" name="live_check[{$row['project_id']}]" value="0"{$row['is_not_live']} />



At this stage, I would prove to myself that I understand what I'm getting out of the form.

Using ...

<?php
echo '<pre>';
print_r($_POST);
echo '</pre>';



as the form handler, will allow you to see the values returned from the form.

Then it should be just a matter of iterating $_POST['live_check'] ...



foreach($_POST['live_check'] as $i_ProjectID => $i_ProjectState)
 {
 $s_SQL = 'UPDATE table SET live=' . intval($i_ProjectState) . ' WHERE project_ID = ' . intval($i_ProjectID);

 // Run the query to update the live state.
 }



sort of thing (untested code snippets).
0
 
LVL 1

Author Comment

by:heather-allen
ID: 24096672
Thanks RQuadling but I'd really like to use a checkbox - I don't mind changing the values to '1' and '0' if that helps - has anyone got any ideas?

Have upped the point value again as this seems to be harder than I thought

thanks
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 1

Author Comment

by:heather-allen
ID: 24097525
Don't worry I have worked out a way to do it myself.

Basically when 'update' is clicked I update all checkboxes to value 'n' and then run this code

if(isset($_POST['update'])) {

$live = $_POST['live_check'];

foreach ($live as $live_id) {

$result = mysql_query("UPDATE projects SET live='y' WHERE project_id=". mysql_real_escape_string($live_id));

}
}


seems to do the trick

:)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24097991
Like I said ...

" you will have to do a lot more work to determine what you need to set."

How about this instead ...

$s_Lives = implode(', ', $_POST['live_check']);
$result = mysql_query("UPDATE projects SET live='y' WHERE project_id in (" . mysql_real_escape_string($s_Lives) . ")");

1 SQL statement rather than x number.

Or ...

$s_Lives = implode(', ', $_POST['live_check']);
$result = mysql_query("UPDATE projects SET live= CASE WHEN project_id IN (" . mysql_real_escape_string($s_Lives) . ") THEN 'y' ELSE 'n' END");


No need to turn them all to no first. 1 SQL command sets ALL the projects in 1 go, either to 'y' or 'n'.


0
 
LVL 1

Author Comment

by:heather-allen
ID: 24098569
That makes more sense but I tried it (version 2 as need to update to 'n' as well) and if there are no projects checked it throws up the error

Notice: Undefined index: live_check in /blah/blah
Warning: implode() [function.implode]: Invalid arguments passed in /blah/blah

Any ideas?

:)
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 2000 total points
ID: 24102756
Yes. Oops.

$_POST['live_check'][] = -1;
$s_Lives = implode(', ', $_POST['live_check']);
$result = mysql_query("UPDATE projects SET live= CASE WHEN project_id IN (" . mysql_real_escape_string($s_Lives) . ") THEN 'y' ELSE 'n' END");

I assume that your id are automatically created and positive.
0
 
LVL 1

Author Comment

by:heather-allen
ID: 24105287
yes

fantastic - works like a dream!

thanks for all your help...

:)
0
 
LVL 1

Author Closing Comment

by:heather-allen
ID: 31567246
although I got it working your way was far better! thanks so much :)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 24105329
Refactoring and improving the code is often much more fun than the original development.

Just make sure you add adequate comments so that this level of shortcutting doesn't catch you out in years to come.

And thank you for the points.
0

Featured Post

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

764 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