Select count from table column within last 3 days

PHP
1

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:

db
db935×560 65.9 KB

Many thanks

2

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.

3

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);
4

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);

5

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'";

6

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

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

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:

db2
db2961×160 17.4 KB

Thanks

8

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
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.