jdock1 Posted May 25, 2011 Share Posted May 25, 2011 What im trying to achieve is a user contact system, when the user types input in the textarea its saved in a database along with his userid on the site and the date, I have an admin side coded up where it retrieves the data, but there is no order. I want to have it display the most recent results on top. I have looked into this in the mysql manual, but you know how that is its so hard to make sense of anything in that damn manual. Im using a while loop to display the data. How can I achieve this? Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/ Share on other sites More sharing options...
jdock1 Posted May 25, 2011 Author Share Posted May 25, 2011 Also, how could I even delete single results? Ive tried multiple things, ive tried using an ID or deleting by matching the name of one of the table fields, but it deletes all results. Its near impossible to delete from a database without doing it in the mysql console or phpmyadmin itself! Im more worried about ordering, however. I will look into this more Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1219837 Share on other sites More sharing options...
Pikachu2000 Posted May 25, 2011 Share Posted May 25, 2011 SELECT `field` FROM `table` ORDER BY `your_timestamp_field` DESC DELETE FROM `table` WHERE `primary_key_field` = [integer] LIMIT 1 Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1219843 Share on other sites More sharing options...
jdock1 Posted May 26, 2011 Author Share Posted May 26, 2011 For the second part, deleting the entry, what do I put in the [integer]? I just dont see how this will work, im testing it now, will post results Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220433 Share on other sites More sharing options...
xyph Posted May 26, 2011 Share Posted May 26, 2011 http://www.tizag.com/mysqlTutorial/mysqltables.php Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220437 Share on other sites More sharing options...
Pikachu2000 Posted May 26, 2011 Share Posted May 26, 2011 What do you mean you don't see how it will work? That's how it's typically done. You have to figure out which record you want to delete, and use it's primary key, which will be an integer, as the parameter in the WHERE clause. Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220438 Share on other sites More sharing options...
jdock1 Posted May 26, 2011 Author Share Posted May 26, 2011 What do you mean you don't see how it will work? That's how it's typically done. You have to figure out which record you want to delete, and use it's primary key, which will be an integer, as the parameter in the WHERE clause. Long day of coding, idk. I got it, thanks. I got a really crappy problem now, which makes no sense to me. Im ordering the results by the date field descending as you stated, it is working, but it does not list the most recent result. When I enter another message into the database, it pushes the one before it to the top and echos it, but does not echo the most recent. I have no idea how to fix this? Here is my code $query="SELECT * FROM messages ORDER BY date DESC"; $result=mysql_query($query,$link2); $fetchdata = mysql_fetch_array($result); $email=$fetchdata['email']; while ($row = mysql_fetch_array($result)){ echo '<fieldset><legend><div class="mhead">Message from <em>'.$row['from'].'</em> on <em>'.$row['date'].'</em></div></legend><br> <div class="mbody">'.$row['input'].' </div><br> <div class="mreply"><u>Reply</div></u><br> <form action="" method="post"> <textarea name="msg" cols="30" rows="15" style="background-color:#6D6968" /></textarea><br> <input type="submit" name="submit" value="Send" /> </form> </fieldset>'; $msg = @$_POST['msg']; $to = $email; $subject = 'Reply from test'; $message2 = ' '.$msg.' '; $headers = 'From:test@localhost' . "\r\n"; if (@$_POST['submit'] == "Send") { mail($to, $subject, $message2, $headers); } I Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220444 Share on other sites More sharing options...
jdock1 Posted May 26, 2011 Author Share Posted May 26, 2011 I dont get it at all. Everything works fine, other than it not listing all results. When I change the query back to the originial with no order, every result is echoed. But when its ordered, it leaves the most recent result out. Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220445 Share on other sites More sharing options...
Pikachu2000 Posted May 26, 2011 Share Posted May 26, 2011 Your first call to mysql_fetch_array is taking the first result returned and moving the data pointer to the second result before entering the while loop and echoing data. You'll need to remove the line I commented in the code. $query="SELECT * FROM messages ORDER BY date DESC"; $result=mysql_query($query,$link2); $fetchdata = mysql_fetch_array($result); // <-- REMOVE THIS LINE $email=$fetchdata['email']; while ($row = mysql_fetch_array($result)){ Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220450 Share on other sites More sharing options...
Pikachu2000 Posted May 26, 2011 Share Posted May 26, 2011 Wait, I didn't notice it may actually have a purpose. Does it need to be there to get only the `email` field from the first record returned for some reason? Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220452 Share on other sites More sharing options...
jdock1 Posted May 26, 2011 Author Share Posted May 26, 2011 No, that worked great, thank you! I cant believe I didin't think of that. It did have a purpose, I was getting the users email from the table so it could be entered into a mail field. But, I just switched around the variable names, now it works fine. Thanks alot though, now I can move onto the next step! Ok, now im facing a problem with trying to delete a single result on the page. Im using the query $delquery="DELETE FROM `messages` WHERE `id` = '".$row['id']."' LIMIT 1"; I included a button on every result being echod, when clicked it executes that query. When I click it, it deletes every result on the page. That was my problem before. Since im echoing every result in the table, its deleting every result because $row['id'] is defined as the ID number in every result, therefore deleting them all, not just the one. I think this is impossible! Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220467 Share on other sites More sharing options...
Pikachu2000 Posted May 26, 2011 Share Posted May 26, 2011 Unless that query is running in a loop, there's no reason for it to delete more than one record. There are certainly ways to delete individual records via a form submission. Post the code you're using, and we'll see what's going on with it. Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220596 Share on other sites More sharing options...
Psycho Posted May 26, 2011 Share Posted May 26, 2011 $delquery="DELETE FROM `messages` WHERE `id` = '".$row['id']."' LIMIT 1"; Um, why are you referencing $row['id'] in your query?! You are using $row as the array variable for accessing the the DB results. You should be referencing a $_POST value. As Pikachu stated, show the code for creating the form fields and for processing the delete. Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220604 Share on other sites More sharing options...
jdock1 Posted May 26, 2011 Author Share Posted May 26, 2011 Ok heres my code, I know its messy, but thats just how I code, its the only way I know; <?php $query="SELECT * FROM messages ORDER BY date DESC"; $fetchquery="SELECT * FROM messages"; $result=mysql_query($query,$link2); $fetchresult=mysql_query($fetchquery,$link2); $fetchdata = mysql_fetch_array($fetchresult); $email=$fetchdata['email']; while ($row = mysql_fetch_array($result)){ $delquery="DELETE FROM `messages` WHERE `id` = '".$row['id']."' LIMIT 1"; echo '<fieldset><legend><div class="mhead">Message from <em>'.$row['from'].'</em> on <em>'.$row['date'].'</em></div></legend><br> <div class="mbody">'.$row['input'].' </div><br> <div class="mreply"><u>Reply</div></u><br> <form action="" method="post"> <textarea name="msg" cols="30" rows="15" style="background-color:#6D6968" /></textarea><br> <input type="submit" name="submit" value="Send" /> </form> <br> <br> <form action="" method="post"><input type="submit" name="delete" value="Delete message" /></form> </fieldset>'; if (@$_POST['delete'] == "Delete message") { mysql_query($delquery,$link2); echo "<font color='#00FF00'>Message deleted from database</font>"; } $msg = @$_POST['msg']; $to = $email; $subject = 'Reply from numbergame.com'; $message2 = ' '.$msg.' '; $headers = 'From:[email protected]' . "\r\n"; if (@$_POST['submit'] == "Send") { mail($to, $subject, $message2, $headers); } echo "<img src='imgs/hr.png' alt='HR' />"; } ?> I am using a lopp to display all of the results in the table. And im using $row variable, so I can pull the id number from the database and match it so it will delete only that result. Thanks for all the help guys.. I may just be an idiot. This is the only way I could think of it to work. So do Igot it all wrong? Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220773 Share on other sites More sharing options...
Psycho Posted May 26, 2011 Share Posted May 26, 2011 Ok heres my code, I know its messy, but thats just how I code, its the only way I know; That is a lame excuse. I used to poop my pants when I was a baby, but I learned how not to do that anymore (well, most of the time anyway). You really need to rethink the process you go through when coding. It is completely obvious from walking though that code why ALL the records would be deleted. 1. You run a query to get all the records to display. 2. You run a loop to display all the records and in that loop you also A. Create a Delete button with a value of "Delete message" B. You generate a delete query for each record and run it if ($_POST['delete'] == "Delete message") is passed. Think about that for a moment. If you attempt to delete ANY records then the $_POST value above will be true in every iteration of the loop - so all records are deleted. This isn't hard stuff, just basic logic. Pikachu already explained that you need to use the ID of the record when determining which one to delete. Also, you should NEVER run queries in loops. Lastly - ADD comments to your code. It will help you and others <?php //Check if there was a record selected for deletion if(isset($_POST['delete'])) { $deleteID = (int) $_POST['delete']; $query = "DELETE FROM `messages` WHERE `id` = '{$deleteID}' LIMIT 1"; $result = mysql_query($query, $link2) or die(mysql_error()); echo "<font color='#00FF00'>Message deleted from database</font>"; } //Send email if msg exists in POST data if (isset($_POST['msg'])) { $to = $_POST['email']; $subject = 'Reply from numbergame.com'; $message = "\n{$_POST['msg']}\n"; $headers = "From:[email protected]\r\n"; mail($to, $subject, $message, $headers); } //Get all records and display $query = "SELECT * FROM messages ORDER BY date DESC"; $result = mysql_query($query, $link2); while ($row = mysql_fetch_array($result)) { echo "<fieldset> <legend><div class=\"mhead\">Message from <em>{$row['from']}</em> on <em>{$row['date']}</em></div></legend><br> <div class=\"mbody\">{$row['input']}</div><br> <div class=\"mreply\"><u>Reply</div></u><br> <form action=\"\" method=\"post\"> <input type=\"hidden\" name=\"email\" value=\"{$row['email']}\" /> <textarea name=\"msg\" cols=\"30\" rows=\"15\" style=\"background-color:#6D6968\" /></textarea><br> <input type=\"submit\" name=\"submit\" value=\"Send\" /> </form> <br><br> <form action=\"\" method=\"post\"><input type=\"submit\" name=\"delete\" value=\"{$row['id']}\" /></form> </fieldset>"; echo "<img src='imgs/hr.png' alt='HR' />"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220820 Share on other sites More sharing options...
jdock1 Posted May 27, 2011 Author Share Posted May 27, 2011 Wow, thanks... thats so much neater and sensible code. I am a complete idiot. Idk why, but I just have trouble understanding things. I was never good at math, I never made it past pre-algebra in school, had f's till I dropped out when I was 16. But I love coding, it just takes me longer to understand things than others. Thanks for the help, it works perfectly.Ive been trying to achieve this for a month. Quote Link to comment https://forums.phpfreaks.com/topic/237383-how-to-order-mysql-results-from-most-recent-entry-in-while-loop/#findComment-1220998 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.