How to limit the number of records in a table and update records in database from xml on the page via Ajax?

Good day!

The task consists in the following:
1) you Need to limit the parsing of the news in the N-number(latest records) to the database.
2) don't know how to make "switchCase" to replace the reference to xml, when you click on the page RU/ENG for example. To change the language of the tape.
3) refresh Button articles from the database using Ajax to not refresh the page.

That's what nakalyakal himself, because of his knowledge. Any help would be appreciated, even code optimization.

PHP
<?
//.... The dB connection 

$xmlURL = "https://bash.im/rss/"; //left link for example

$sxml = simplexml_load_file($xmlURL);

foreach($sxml->channel->item as $item) {
 $id = stripcslashes($item);
 $title = stripslashes($item->title);
 $link = stripslashes($item->link);
 $image = stripslashes($item->image);
 $description = stripslashes($item->description);
 $date = stripcslashes($item->pubDate);

 $query = mysqli_query($db, "SELECT COUNT(*) FROM news WHERE id='$title'");
 $item = mysqli_fetch_row($query);
 $total = $item[0];

 if ("$total" == 0) {
 $sql = mysqli_query($db, "INSERT INTO news (id, title, link, image, description, created_at) VALUES('$id', '$title', '$link', '$image', '$description', '$date')");
 } else if ("$total" > 0) {
 $sql2 = mysqli_query($db, "UPDATE news (id, title, link, image, description, created_at) VALUES('$id', '$title', '$link', '$image', '$description', '$date')");
}
}

?>

HTML
<div class="rss-feed">
<?php
 $sql = mysqli_query($db, 'SELECT `title`, `link`, `description`, `created_at` FROM `news` ORDER BY `created_at` desc LIMIT 5');
 while ($result = mysqli_fetch_array($sql)) {
 echo '<div class="item">';
 echo '<h2><a href='.$result['link'].'>'.$result['title'].'</a></h2>';
 echo '<div class="description">'.$result['description'].'</div>';
 echo '<p class="datetime">'.$result['created_at'].'</p>';
 echo '</div>';
}
?>
</div>
March 23rd 20 at 19:18
1 answer
March 23rd 20 at 19:20
Solution
Well for starters: $id = stripslashes($item) - it's all neither here nor there.
Then it $id = mysqli_real_escape_string($db, stripslashes($item->title)), at least.
And the best $id = mysqli_real_escape_string($db, stripslashes($item->guid)). And change the length of the column `id` in the database.
And all inputs pass through mysqi_real_escape_string($db, ...);

Next, change to $query = mysqli_query($db, "SELECT COUNT(*) FROM news WHERE id='{$id}'");
And yet, why do any UPDATE record that is already in the database, and the logic - completely identical to the new data?

But when accessed from the AJAX (which here is not shown) to give only the last XX entries, that's all, no problem.
The problem is that in real no RSS link tag , there's no id or attribute in which you can do the checking for uniqueness, only title and link. All articles differ only titles and URLs. - torey40 commented on March 23rd 20 at 19:23
@torey40, then $id = mysqli_real_escape_string($db, stripslashes($item->title)), at least. - mac.Okuneva commented on March 23rd 20 at 19:26
@mac.Okuneva
Next, change to $query = mysqli_query($db, "SELECT COUNT(*) FROM news WHERE id='{$id}'");


After this procedure, everything breaks, and in the basis there are duplicates of all records with each update of the page. ID in my case it is not a unique identifier of the article, but simply the sequence number in the database. - torey40 commented on March 23rd 20 at 19:29
@mac.Okuneva, thank you for pointed out the error about the shielding had not thought of. Excess removed. - torey40 commented on March 23rd 20 at 19:32
@mac.Okuneva, how is it possible to limit the adding to the database, only the 10 latest articles. Not to replace the entire table, but only 1-2 lines depending on the number of new records? - torey40 commented on March 23rd 20 at 19:35
@torey40, well, sort things out, you have COUNT(*) checks for records in such `id`, or not to check?
If not check, and the serial number - then change to "SELECT COUNT(*) FROM news WHERE title='{$title}'". Of INSERT and remove `id`, it should generate the base itself. And that would store only 10 records delete one record with the lowest `id`: "DELETE FROM `news` ORDER BY `id` ASC LIMIT 1". - mac.Okuneva commented on March 23rd 20 at 19:38
@torey40, $total - that you have a number of SQL records `title`='$title', right? Then change:
$query = mysqli_query($db, "SELECT COUNT(*) FROM news WHERE title='{$title}'");
 $item = mysqli_fetch_row($query);
 if ($item[0] == 0) {
 mysqli_query($db, "INSERT INTO news (title, link, image, description, created_at) VALUES('{$title}', '{$link}', '{$image}', '{$description}', '{$date}')");
}

 $query = mysqli_query($db, "SELECT COUNT(*) FROM news");
 $item = mysqli_fetch_row($query);
 if ($item[0] > 10) {
 mysqli_query($db, "DELETE FROM news ORDER BY `id` ASC LIMIT ".($item[0] - 10));
 }
- mac.Okuneva commented on March 23rd 20 at 19:41
@mac.Okuneva, Yes. Thanks for the help, just what we need. KST, it is impossible to do that when the update data id kept the primary numbering from 0 to 10? Now with every update id is growing. Or anything terrible? Just as in the future work with these data if we have for example visit print 1 article with ID 10 for example? - torey40 commented on March 23rd 20 at 19:44
@torey40, if you make `id` BIGINT UNSIGNED (far beyond 18 with 18 zeroes), it is sooooo not going to end soon.
If `id`=10, then:
SELECT * FROM `news` WHERE `id`=10
And if only the 10th record (one) available:
SELECT * FROM `news` ORDER BY `id` ASC LIMIT 10, 1 - mac.Okuneva commented on March 23rd 20 at 19:47
@mac.Okuneva, not how to make a selection by ID I know. The issue is that in the database itself, the numbering did not grow, but remained always from 0 to 10? - torey40 commented on March 23rd 20 at 19:50
if you do `id` BIGINT UNSIGNED (far beyond 18 with 18 zeroes), it is sooooo not going to end soon.

First thought about it, and did. But it is somehow not on Feng Shui or something, if the database only 10 records. - torey40 commented on March 23rd 20 at 19:53
@torey40, I think it should work:
mysqli_query($db, "SET @pos := 0");
mysqli_query($db, "UPDATE `news` SET `id`=(SELECT @pos:=@pos+1) ORDER BY `id` ASC");

To do after all additions/deletions in the table. - mac.Okuneva commented on March 23rd 20 at 19:56
@mac.Okuneva, thank you worked. - torey40 commented on March 23rd 20 at 19:59
@torey40, January AJAX data: {update: 'yes'} and you will write, they know how. ;-)
Of course success: function(response) {$('div.rss feed').innerHTML = response;}

And backend - easy. In the same script after initialization of the MySQL connection:
if (isset($_REQUEST['update']))
{
 $sql = mysqli_query($db, 'SELECT `title`, `link`, `description`, `created_at` FROM `news` ORDER BY `created_at` desc LIMIT 10');
 while ($result = mysqli_fetch_array($sql)) {
 echo '<div class="item">';
 echo '<h2><a href='.$result['link'].'>'.$result['title'].'</a></h2>';
 echo '<div class="description">'.$result['description'].'</div>';
 echo '<p class="datetime">'.$result['created_at'].'</p>';
 echo '</div>';
}
exit();
}
- mac.Okuneva commented on March 23rd 20 at 20:02

Find more questions by tags MySQLPHPAJAX