Select count from table column within last 3 days

Hi,

I have a database which stores listings in 2 main categories.

I am trying to select the total number of listings in each of the categories with term_taxonomy_id's of 109 and 110 that have been added within the last 3 days.

This is what I have so far, but it’s not showing anything when echoing. I also don’t know how to select them based on a time period of 3 days

$total_competitions = mysql_query("SELECT COUNT(term_taxonomy_id=109) FROM wp_term_taxonomy;");
echo $total_competitions;

Could anyone help me with the query?

This is a screenshot of the table in phpmyadmin:

Many thanks

There’s no date/time related data in your example.

MySQL has a lot of date/time related functions you can use:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

Use BETWEEN if you need some range.

the mysql_* functions are deprecate and do not work in any current version of PHP. Use PDO instead.

Thanks for the reply.

I have now put this together to just echo the number of rows for now, but I can’t get the number of rows to echo:

$connection= new mysqli("xxx", "xxx", "xxx", "xxx");
 
	$query = "SELECT count FROM wp_term_taxonomy WHERE term_taxonomy_id='109'";
 

    if (mysqli_connect_errno()) 
    { 
        echo "Database connection failed."; 
    } 
      
    $query = "SELECT Username, Password FROM geek"; 
      
 
    $result = mysqli_query($connection, $query); 
      
    if ($result) 
    { 
    
        $row = mysqli_num_rows($result); 
          
        printf("Number of row in the table : " . $row); 
    
 
        mysqli_free_result($result); 
    } 
  
 
    mysqli_close($connection);

And what happens instead? Remember: no one here sits in front of your screen, everything you do is a blank page for us, except for the things you tell us. you should at least throw exceptions on your testing system: mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Nothing happens - it just loads blank where it should be echoing.

I’ve added the exceptions to the code, but that doesn’t show anything.

This part works in phpmyadmin when making a query, but I can’t get it to echo on the frontend

$query = "SELECT count FROM wp_term_taxonomy WHERE term_taxonomy_id='109'";

You know you’ve got an extra query in your code?

$query = "SELECT Username, Password FROM geek"; 

Ah, thanks for point that out!

I now have:

	$query = "SELECT count FROM wp_term_taxonomy WHERE term_taxonomy_id='109'";
  
    if (mysqli_connect_errno()) 
    { 
        echo "Database connection failed."; 
    } 

    $result = mysqli_query($connection, $query); 
      
    if ($result) 
    {  
        $row = mysqli_num_rows($result); 
          
        printf("Number of row in the table : " . $row);  
        mysqli_free_result($result); 
    } 
    
    mysqli_close($connection); 
	
	mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

I’ve realised the query returns the number of rows rather than the value in the column called “count”.

I’ve tried changing this line to:
$row = $result; but it appears blank.

How do I return the value of an actual column:

Thanks

You need to look at the various fetch functions if you want to actually retrieve the data - $result is an object that contains those rows, or a reference to them at least. So you’d do something like

while ($row = mysqli_fetch_array($result) { 
   echo $row['taxonomy'];
}
1 Like