Deleting Data each 24 hours in Database with PHP and MYSQLi

Question 1

I like to know how you do this:

I have made a sort of a chatbox that automatic updates it self with a script and it loads up the latest 50 chat messages from database.

Now i wonder, how to delete messages after each 24 hours but keep the first new 50 messages and delete the older ones after that.

Is this possible with MYSQLi and PHP or must i do it with a better solution?

UPDATE

Followed advise from Lund and a fast response from my provider: I am not able to do Cron Jobs or MYSQL Events with my current plan. Also i do not have the money for upgrading it, so erm.... if there is another solution, thank you for helping me out on this matter folks.

I have a table called: chatbox and have this in it:

C_ID
chattext
name
date

Question 2

Pseudo code (Won't actually work without some edits to fit your code, but its an idea) - I don't know how your data is stored, or its variable names. Or your table name. Or basically any information that I would need to actually get this to work.

$sql = ("SELECT * FROM ChatLog ORDER BY TimeAdded DESC"); //Order by descending should show the newest ones first.

$index = 1; //set an index to loop through

while($row = mysqli_query($con, $sql) { //use a while loop to go through the table

    if($index <= 50) { //for the first 50 records
        //do nothing
    } else { //for everything after 50 records
        $chatId = $row['chatLogID']; //ID or unique value for the actual message.
        $sql = ("DELETE FROM ChatLog WHERE chatLogID='$chatId'"); //Delete it from the table
        mysqli_query($con, $sql);//Execute query.
    }

}

You can set this up to execute everytime a message is stored, or check it against the current time of the server to remove it daily.

UPDATE: THIS CODE SHOULD BE WORKING WITH YOUR CODE

$sql = ("SELECT * FROM chatbox ORDER BY time DESC"); //Order by descending should show the newest ones first.

$index = 1; //set an index to loop through

while($row = mysqli_query($con, $sql) { //use a while loop to go through the table

    if($index <= 50) { //for the first 50 records
        //do nothing
    } else { //for everything after 50 records
        $chatId = $row['C_ID']; //ID or unique value for the actual message.
        $sql = ("DELETE FROM chatbox WHERE C_ID='$chatId'"); //Delete it from the table
        mysqli_query($con, $sql);//Execute query.
    }

}

Question 3

The mistake was done at ajax part document.getElementById("bookid").innerHTML and must be replaced with document.getElementById().value since I had to put data to Html Element that cotain value i.e Textbox(as textbox contain value attribute).

InnerHTML is used to manipulate the html elements that does not contain value, ** div,h1, ** etc. for details see below link.

http://www.verious.com/qa/what-39-s-the-difference-between-document-get-element-by-id-quot-test-quot-value-and-document-get-element-by-id-quot-tes/

ajax code

  function show_bookid(str)
{
    var xmlhttp;
    if(str.length==0)
    {
        document.getElementById("bookid").value="";
        return;
    }
    if(window.XMLHttpRequest)
    {
        xmlhttp= new XMLHttpRequest();
    }
    else
    {
        xmlhttp=new ActiveXOjbject("Microsoft.XMLHttpRequest"); 
    }
    xmlhttp.onreadystatechange=function()
    {
        if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
            document.getElementById("bookid").value=xmlhttp.responseText;
        }
    }
        xmlhttp.open("GET","getbook.php?q="+str,true);
        xmlhttp.send();

}

getbook.php

<?php
$b=$_GET['q'];
include('includes/security.php');
 include('includes/dbconnect.php'); 
$database=new MySQLDatabase();

$sql="select * from tbl_bkcat where book_id='".$b."'";
$result=mysql_query($sql);
$row=mysql_fetch_array($result);
echo $row['Book_id'];
?>

addbook.php

            <form name="bookadd" action="" class="jNice" method="post">
            <fieldset>

                <p><label>Subject</label>
                 <select name="subject" onChange="show_bookid(this.value);">

                 <?php

                    while($sel_rows=mysql_fetch_array($subresult))
                    {
                 ?>
                    <option value="<?php echo $sel_rows['book_id'];?>">
                    <?php echo $sel_rows['subject']?>
                    </option> 
                    <?php 
                        }
                    ?>
                </select>
                </p>

                <p>
                <label >Book ID</label>                 
               <input type="text" id="bookid" name="book"/>
                </p>

Question 4

If $getOrder has the order details (including company_id), and $getCompany has the company details (including company_id), then you can compare the two.

If they are equal, echo out selected as an attribute in the option, like so:

<select class="form-control" name="company_id" id="company_id">
<?php
    if($getCompany) {

        //Get company ID from Order
        $orderID = $getOrder["company_id"];

        while($company = mysqli_fetch_assoc($getCompany)) { ?>
            <option value="<?php echo $company['company_id']; ?>"
            <?php 
                //Compare and echo `selected` if they are equal
                if($orderId==$company["company_id"]) echo "selected";
            ?>>
                <?php echo $company['company_name']; ?>
            </option>
        <?php }
    } 
?>

That code can be cleaned up, however:

<select class="form-control" name="company_id" id="company_id">
<?php
    if($getCompany) {
        $orderID = $getOrder["company_id"];
        while($company = mysqli_fetch_assoc($getCompany)) {
            echo "<option value='{$company['company_id']}".($getOrder["company_id"]==$getCompany["company_id"] ? " selected" : null).">{$company['company_name']}</option>";
        }
    } 
?>

Question 5

Problem solved! I will post how it was done and someone may be able to use it.

function menu()
  {
    global $dbc;

  $result = $dbc->prepare('SELECT page, linktext, visable, parent FROM content WHERE visable > 0 ORDER BY parent,sort ASC');
  $result->execute();
  $result->bind_result($menu_page, $menu_linktext, $menu_visible, $menu_parent);

  while($result->fetch())
    {
        if($menu_parent == 0) $menu[$menu_page]=$menu_linktext;
        elseif(!empty($menu[$menu_parent])) $sub[$menu_parent][]=$menu_linktext;
    }

  $result->close();

  if(!empty($menu))
    {
        echo '<ul class="sf-menu" id="nav">';
        foreach($menu as $page=>$link)
          {
              echo "<li><a href='$link'>$link</a>";
              if(!empty($sub[$page]))
                {
                    echo '<ul>';
                    foreach($sub[$page] as $lnk) echo "<li><a href='$lnk'>$lnk</a></li>";
                    echo '</ul>';
                }
              echo '</li>';
          }
        echo '</ul>';
      }

}

Question 6

while ($row = mysqli_fetch_array($stmt)) {
    echo "<tr>";
    echo "<td>" . $row["aid"] . "</td>";
    echo "<td>" . $row["aname"] . "</td>";
    echo "</tr>";
}

Deleting Data each 24 hours in Database with PHP and MYSQLi

Answers

php,database,mysqli