John Davidson

Message:

I would like some help to write some SQL logic.

I currently have a scrapy bot collecting news on 10 sites.

News is updated daily on these sites.

I download them into an internal system, I need the news not to be shown by repeated sources.

It needs to work the same as Google Discover, randomizing the display of these news across news source sites.

It currently looks like this:

NEWS 1 : SITE A

NEWS 2 : SITE A

NEWS 3 : SITE A



NEWS 4: SITE B

NEWS 5: SITE B



NEWS 6: SITE C

NEWS 7: SITE C

I need this order to be more random, more fluid.

My system is in PHP / MYSQL.

Could you help me work out this logic? I'm lost on that, the dates are all the same day, and there's no hourly variation, just the entire date.

---

My Answer:
One way to achieve randomization of news sources in your system is to add a new column in your database table to store a random number for each news item. You can then use this random number to sort the news items when fetching them from the database.

Here's an example of how you can implement this logic in PHP and MySQL:

1. Add a new column called "random_number" to your news table:

sql
ALTER TABLE news ADD random_number INT;


2. Update the random_number column with a random value for each news item:

php
// Connect to your MySQL database
$connection = mysqli_connect("localhost", "username", "password", "database");

// Fetch all news items from the database
$query = "SELECT * FROM news";
$result = mysqli_query($connection, $query);

// Update the random_number column with a random value for each news item
while ($row = mysqli_fetch_assoc($result)) {
$randomNumber = rand(1, 1000); // Generate a random number between 1 and 1000
$updateQuery = "UPDATE news SET random_number = $randomNumber WHERE id = " . $row['id'];
mysqli_query($connection, $updateQuery);
}

// Close the database connection
mysqli_close($connection);


3. Fetch news items from the database and order them by the random_number column:

php
// Connect to your MySQL database
$connection = mysqli_connect("localhost", "username", "password", "database");

// Fetch news items from the database and order them by the random_number column
$query = "SELECT * FROM news ORDER BY random_number";
$result = mysqli_query($connection, $query);

// Display the news items
while ($row = mysqli_fetch_assoc($result)) {
echo "NEWS " . $row['id'] . ": " . $row['source'] . "
";
}

// Close the database connection
mysqli_close($connection);


By following these steps, you should be able to randomize the display of news items across news source sites in your system. Feel free to adjust the random number range and sorting logic to better suit your needs.