LaurenceTuck2 Posted February 28, 2007 Share Posted February 28, 2007 need help with a query: my database has a log table with a reference to the row id in a different table(listings). I need to query the db to find the number of logs created for that particular row id. i.e. how many logs created for a particular (listing) entry. any ideas? Link to comment https://forums.phpfreaks.com/topic/40501-mysql-queries/ Share on other sites More sharing options...
TRI0N Posted February 28, 2007 Share Posted February 28, 2007 Need a little bit of your code your are using to work with here. Link to comment https://forums.phpfreaks.com/topic/40501-mysql-queries/#findComment-195943 Share on other sites More sharing options...
LaurenceTuck2 Posted March 2, 2007 Author Share Posted March 2, 2007 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.