Jump to content

How to order mysql results from most recent entry in while loop


jdock1

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)){

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

$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.

Link to comment
Share on other sites

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:admin@numbergame.com' . "\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?

Link to comment
Share on other sites

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:admin@numbergame.com\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' />";
}

?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.