sddouggy Posted December 10, 2010 Share Posted December 10, 2010 Hey all, I am fairly new in the php world and have set myself a challenge to create a display of a database. I have the database displaying what I want but now am trying to have a 'complete' field where if ticked it will delete that field from the db. The row ['complete'] line 30 is saved in the db as '0' when ticked I want this to change to a '1' and delete that line from the db. I have tried a few things but am getting a little bit lost. Any help would be much appreciate <?php $con = mysql_connect("Host","USER","PASS"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("TABLE", $con); $result = mysql_query("SELECT * FROM Priority"); echo "<table border='1' width='100%'> <tr> <td width='5%'>Ticket Number</td> <td width='5%'>Order #</td> <td width='65%'>Ticket Info</td> <td width='15%'>Assigned by</td> <td width='5%'>Priority</td> <td width='10%'>Complete</td> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>#" . $row['ID'] . "</td>"; echo "<td>" . $row['OrderNumber'] . "</td>"; echo "<td>" . $row['TicketInfo'] . "</td>"; echo "<td>" . $row['Assignedby'] . "</td>"; echo "<td>" . $row['Priority'] . "</td>"; echo "<td><input type='checkbox' name='Check' value='Check' /></td>"; //the last row, where the checkbox is is actually a row called ['complete'] } if(isset($Check) && $Check != "") { mysql_query("UPDATE Priority SET Complete = '1' WHERE $row = 'ID'"); } echo "</table>"; mysql_close($con); ?> <?php $con = mysql_connect("HOST","USER","PASS"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_query("DELETE FROM Priority WHERE Complete='1'"); mysql_close($con); ?> Quote Link to comment Share on other sites More sharing options...
MMDE Posted December 10, 2010 Share Posted December 10, 2010 not exactly sure what exactly you want, so not sure if this exactly helps you... you want to make checkboxes for each row? <input type="checkbox" name="" value="" /> if you want to be able to check many boxes at the same time and for example delete em or apply some action to them, then they can't have the same name or can they ? =P you kinda can send it all in one box, but that requires some action script and send it all in one hidden value, for example by making a comma seperated list that you explode in php. else you will need a new name for each checkbox, then loop check if they are set in php. if you only want to select one, then use on submit button at the end of each table row. that is the easiest, but you won't be able to do multiple at the same time. <form method="post" action=""> <input type="hidden" name="id" value="the id # in the db or row (but that would be more complicated for you)" /> <input type="checkbox" name="check" value="1" /> <input type="submit" value="submit" /> </form> to see the value from the checkbox in php: if(!empty($_POST['check'])){ if($_POST['check']=='1'){ if(!empty($_POST['id'])){ echo $_POST['id']; } } } (I like to use !empty instead of isset, works almost the same way, just !empty check if it is empty or not too.) also when you write input tags in html, you need to make the form tags too. You can choose between many methods, the most common is post and get. get will put the values and variables in the url. then it's just to use $_GET to get it. good for linking without sending post data. of course, I would also recommend checking the data you get before inserting it into the db, to avoid attacks. oh and the "id #" i'm talking about would be the unique number for that row in the db, something that makes it unique and there won't ever be two of. Quote Link to comment Share on other sites More sharing options...
sddouggy Posted December 10, 2010 Author Share Posted December 10, 2010 Thanks for your reply. Basically what I am after (apologies for not being clear) is to call a row from the database and once it is complete have a field in the database, linked to that row, change its value from 0 to 1. After the value is changed the DELETE will then remove any value from the DB in the column ['complete'] with the value 1. No? Thanks again Quote Link to comment Share on other sites More sharing options...
MMDE Posted December 10, 2010 Share Posted December 10, 2010 hmm, I still don't get it though, can you draw it? (or make some html code for it) and show me pic by pic, from start to end. I think you should with your code (if you know what you've done) and with what I told you be able to do pretty much everything you'd want. if you need help to update/delete rows in mysql then read about that. Quote Link to comment Share on other sites More sharing options...
sddouggy Posted December 10, 2010 Author Share Posted December 10, 2010 Want to do something like below: In a table show the fields from the mysql database which I can do. Once the table is displaying the fields I want to be able to tick a checkbox which will then mark the corresponding row as '1' and then using a mysql command I will then remove it. I can do the remove and update through MySQL, but its the checking that the field is ticked and making a value of 1 in the database. I hope this is more help Quote Link to comment Share on other sites More sharing options...
MMDE Posted December 10, 2010 Share Posted December 10, 2010 You will need to add a new column in the database, named Completed, type: tinyint default value: 0. Also, sorry if I've done a typo or two... I took me the liberty to add some stuff too... <?php $con = mysql_connect("Host","USER","PASS"); if(!$con){ die('Could not connect:'.mysql_error()); } mysql_select_db("TABLE",$con); if(!empty($_POST['check'])){ if(!empty($_POST['oid'])){ if(is_numeric($_POST['oid'])){ if($_POST['oid']==mysql_real_escape_string($_POST['oid'])){ mysql_query('UPDATE Priority SET Completed = 1 WHERE ID = '.$oid); } } } } $result=mysql_query('SELECT * FROM Priority WHERE Completed=0 ORDER BY Priority DESC'); echo '<table border="1" width="100%"> <tr> <td width="5%">Ticket Number</td> <td width="5%">Order #</td> <td width="65%">Ticket Info</td> <td width="15%">Assigned by</td> <td width="5%">Priority</td> <td width="5%">Complete</td> <td width="5%"> </td> </tr>' while($row = mysql_fetch_array($result)){ echo ' <form method="post" action=""> <tr> <input type="hidden" name="oid" value="'.$row['ID'].'" /> <td>#'.$row['ID'].'</td> <td>'.$row['OrderNumber'].'</td> <td>'.$row['TicketInfo'].'</td> <td>'.$row['Assignedby'].'</td> <td>'.$row['Priority'].'</td> <td><input type="checkbox" name="check" value="yes" /></td> <td><input type="submit" value="submit" /></td> </tr> </form>'; } echo '</table>'; ?> Quote Link to comment Share on other sites More sharing options...
sddouggy Posted December 10, 2010 Author Share Posted December 10, 2010 Thanks for that, i really appreciate it. Annoyingly though my table won't update to '1' I am sure I am doing something wrong Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted December 10, 2010 Share Posted December 10, 2010 make sure the query works. mysql_query('UPDATE Priority SET Completed = 1 WHERE ID = '.$oid) or die(mysql_error()); Quote Link to comment Share on other sites More sharing options...
sddouggy Posted December 10, 2010 Author Share Posted December 10, 2010 I get this error below You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 when using this code: <?php $con = mysql_connect("args"); if(!$con){ die('Could not connect:'.mysql_error()); } mysql_select_db("db70791_priority",$con); if(!empty($_POST['check'])){ if(!empty($_POST['oid'])){ if(is_numeric($_POST['oid'])){ if($_POST['oid']==mysql_real_escape_string($_POST['oid'])){ mysql_query('UPDATE Priority SET Completed = 1 WHERE ID = '.$oid) or die(mysql_error()); } } } } $result=mysql_query('SELECT * FROM Priority WHERE Completed=0 ORDER BY Priority DESC'); echo '<table border="1" width="100%"> <tr> <td width="5%">Ticket Number</td> <td width="5%">Order #</td> <td width="65%">Ticket Info</td> <td width="15%">Assigned by</td> <td width="5%">Priority</td> <td width="5%">Complete</td> <td width="5%"> </td> </tr>'; while($row = mysql_fetch_array($result)){ echo ' <form method="post" action=""> <tr> <input type="hidden" name="oid" value="'.$row['ID'].'" /> <td>#'.$row['ID'].'</td> <td>'.$row['OrderNumber'].'</td> <td>'.$row['TicketInfo'].'</td> <td>'.$row['Assignedby'].'</td> <td>'.$row['Priority'].'</td> <td><input type="checkbox" name="check" value="yes" /></td> <td><input type="submit" value="submit" /></td> </tr> </form>'; } echo '</table>'; ?> Quote Link to comment Share on other sites More sharing options...
sddouggy Posted December 10, 2010 Author Share Posted December 10, 2010 I get this error below You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 when using this code: <?php $con = mysql_connect("HOST","USER","PASS"); if(!$con){ die('Could not connect:'.mysql_error()); } mysql_select_db("db70791_priority",$con); if(!empty($_POST['check'])){ if(!empty($_POST['oid'])){ if(is_numeric($_POST['oid'])){ if($_POST['oid']==mysql_real_escape_string($_POST['oid'])){ mysql_query('UPDATE Priority SET Completed = 1 WHERE ID = '.$oid) or die(mysql_error()); } } } } $result=mysql_query('SELECT * FROM Priority WHERE Completed=0 ORDER BY Priority DESC'); echo '<table border="1" width="100%"> <tr> <td width="5%">Ticket Number</td> <td width="5%">Order #</td> <td width="65%">Ticket Info</td> <td width="15%">Assigned by</td> <td width="5%">Priority</td> <td width="5%">Complete</td> <td width="5%"> </td> </tr>'; while($row = mysql_fetch_array($result)){ echo ' <form method="post" action=""> <tr> <input type="hidden" name="oid" value="'.$row['ID'].'" /> <td>#'.$row['ID'].'</td> <td>'.$row['OrderNumber'].'</td> <td>'.$row['TicketInfo'].'</td> <td>'.$row['Assignedby'].'</td> <td>'.$row['Priority'].'</td> <td><input type="checkbox" name="check" value="yes" /></td> <td><input type="submit" value="submit" /></td> </tr> </form>'; } echo '</table>'; ?> Quote Link to comment Share on other sites More sharing options...
MMDE Posted December 10, 2010 Share Posted December 10, 2010 mysql_query('UPDATE Priority SET Completed = 1 WHERE ID = '.$_POST['oid']); lol, I didn't test it, so I was bound to do a typo. Always do haha You would have seen it if you tried to read the code though, $oid is never set. Quote Link to comment Share on other sites More sharing options...
sddouggy Posted December 10, 2010 Author Share Posted December 10, 2010 Lol! Even I should know that one! Works a treat! Thank you! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.