Jump to content

MySQL Queries


LaurenceTuck2

Recommended Posts

table structure :

listings > id, title, province

logs > id, data_id, datetime

 

when a listing gets viewed a log gets created and the id of the listing gets saved as a data_id.

What I want to show is a report of how many views a particular listing had by using the number of times the view got logged between dates.

 

Heres my code:

 

$query_log = "SELECT data_id FROM logs";

$result_log = mysql_query($query_log) or die("Query Failed");

while($line_logs = mysql_fetch_array($result_log)) {

$data_id = $line_logs['data_id'];

 

$query_data = "SELECT DISTINCT province FROM listings WHERE id = '$data_id'";

$result_data = mysql_query($query_data) or die("Query Failed");

$line_data = mysql_fetch_array($result_data);

 

$num = mysql_num_rows($result_data);

echo $line_data['province']."<br>";

 

};

 

This obviously just shows a list of provinces.

How do I get the number of rows from the logs that relate to each province.

The province can also be a static field and do something like this:

 

$query_data = "SELECT id FROM lisitngs WHERE province = 'province_01'";

$result_data = mysql_query($query_data) or die("Query Failed");

$line_data = mysql_fetch_array($result_data);

                     

foreach($line['id'] as $row_value) {

$query= "SELECT count(*) FROM logs WHERE data_id = '$row_value'";

$result= mysql_query($query) or die("Query Failed");

$line= mysql_fetch_array($result);

            echo $line['0'];

            };

 

I just cant get my head around this...

 

 

                     

 

 

Link to comment
https://forums.phpfreaks.com/topic/40501-mysql-queries/#findComment-197922
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.