[SOLVED] Cant get delete to work

[mike12255]

I have the following code:

 

$insertnew="INSERT INTO newitems (title,author,date,url) VALUES ('$subject','$name','$thedate','$url[0]')";
  mysql_query($insertnew) or die ("Could insert the new data!");
  
  
  $amount= mysql_query("SELECT * FROM newitems");
         $num_rows = mysql_num_rows($amount);



  
  if ($num_rows > 4){
  
  for ($counter=10; $num_rows > 4; $counter++){
  $getdate = "SELECT MAX(date) FROM `newitems`" ;
  $dateresult = mysql_query($getdate) or die (mysql_error());
  
  $delete = mysql_fetch_row($dateresult); 
  $todelete = "DELETE FROM newitems WHERE date = $delete";
  
  mysql_query($todelete);
  }
  
  }

 

anyway i cant get it to delete i know its passing for if statment because i echo'd my $num_rows and it was greater then four. Any ideas?

[gevans]

$todelete = "DELETE FROM newitems WHERE date = '{$delete['date']}'";

[mike12255]

It didnt work :S

[gevans]

sorry, missed the line before

 

change;

 

$delete = mysql_fetch_row($dateresult);

$todelete = "DELETE FROM newitems WHERE date = $delete";

 

to

 

$delete = mysql_fetch_assoc($dateresult);

$todelete = "DELETE FROM newitems WHERE date = {$delete['date']}";

[Maq]

And the reason why is because

 

$delete = mysql_fetch_assoc($dateresult);

 

returns an array ($delete) with the results.  $delete['date'] is encapsulated with { } so that it allows single quotes inside single quotes.

[mike12255]

@ gevans  Dont say sorry mate, your helping me.

 

P.S it didnt work

 

 

@Maq thanks for the explination mate

[Maq]

You need single quotes around the date comparator.  That's the whole  point of the curly brackets...

 

$todelete = "DELETE FROM newitems WHERE date = '{$delete['date']}'"; 

[mike12255]

yeah i added it in, already and it still dosnt work:

 

 
  if ($num_rows > 4){
  
  for ($counter=10; $num_rows > 4; $counter++){
  $getdate = "SELECT MAX(date) FROM `newitems`" ;
  $dateresult = mysql_query($getdate) or die (mysql_error());
  
$delete = mysql_fetch_assoc($dateresult);
$todelete = "DELETE FROM newitems WHERE date = {$delete['date']}";

[uniflare]

Ok the sql looks fine.

 

Echo the query, try it in phpmyadmin.

 

try this:

$todelete = "DELETE FROM `newitems` WHERE `date` = '".$delete['date']."'";

[gevans]

<?php
    if ($num_rows > 4){
    
     for ($counter=10; $num_rows > 4; $counter++){
     $getdate = "SELECT MAX(date) AS date FROM `newitems`";
     $dateresult = mysql_query($getdate) or die (mysql_error());
    
$delete = mysql_fetch_assoc($dateresult);
$todelete = "DELETE FROM newitems WHERE date = '{$delete['date']}'";

[mike12255]

This kind of worked:

 

if ($num_rows > 4){
    
     for ($counter=10; $num_rows > 4; $counter++){
     $getdate = "SELECT MAX(date) AS date FROM `newitems`";
     $dateresult = mysql_query($getdate) or die (mysql_error());
    
$delete = mysql_fetch_assoc($dateresult);
$todelete = "DELETE FROM newitems WHERE date = '{$delete['date']}'";

  
  mysql_query($todelete);

 

 

but it deleted everything instead of leaving me with the four newest

[uniflare]

it deleted every row set?

 

Try the query in phpmyadmin, echo the query,. you need to debug a little yourself or you may be on here a long time .

[mike12255]

yeah i know i saw your message about doing it in php my admin earlier but im getting ready for work, figured i'd do it when i got home

[Maq]

I don't understand this:

 

if ($num_rows > 4){

     for ($counter=10; $num_rows > 4; $counter++){

 

I mean I understand it but I don't get the point of it.  How is $num_rows being assigned?

 

Cause if it's running some kind of infinite loop it will delete everything since you have MAX(date).  Every time you delete a column the next MAX(date) will be deleted.

 

Add the echo statement in here:

 

$todelete = "DELETE FROM newitems WHERE date = '{$delete['date']}'";
echo $sql . "
";

[mike12255]

Ok, well i know a guy that is an amazing programmer and he helped me rewrite that code in two lines and save my database space:

 

 
 $newitems = mysql_query("SELECT * FROM forumtutorial_posts ORDER BY realtime DESC LIMIT 4"); // for the newest posts.
   while($row = mysql_fetch_array($newitems)) {

   print($row["postid"] . " " . $row["author"] . " " . $row["time"]);


}

 

Thanks to Precision(my friend - not on this forum)

[uniflare]

yes, its quicker if you use an instant messenger or talk to someone in person.

[Maq]

Ok, well i know a guy that is an amazing programmer and he helped me rewrite that code in two lines and save my database space:

 

 
 $newitems = mysql_query("SELECT * FROM forumtutorial_posts ORDER BY realtime DESC LIMIT 4"); // for the newest posts.
   while($row = mysql_fetch_array($newitems)) {

   print($row["postid"] . " " . $row["author"] . " " . $row["time"]);


}

 

Thanks to Precision(my friend - not on this forum)

 

Maybe I'm confused but, how does that solve your problem?  I thought you needed help deleting not displaying the 4 most recent posts...