Where are categories located in SQL? Can't find.

hi,

you can try to check table name wp_terms instead.

This may help too, part of an event category archive widget I am working on.

SELECT t.name,t.slug,date_format(event_start_date,'%Y-%m') AS event_start,count(e.event_id) AS event_count
  FROM " . $wpdb->prefix . "em_events e
  LEFT JOIN " . $wpdb->prefix . "term_relationships tr on (e.post_id = tr.object_id)
  LEFT JOIN " . $wpdb->prefix . "term_taxonomy tt on (tr.term_taxonomy_id=tt.term_taxonomy_id)
  LEFT JOIN " . $wpdb->prefix . "terms t on (tt.term_id=t.term_id)
 WHERE recurrence = 0
 GROUP BY t.name, event_start
 ORDER BY t.name ASC, event_start DESC

I’m a little lost by this, as I’m a new coder. To a more basic question, where do I find the event_category_id field that shows that a few different calendar items ARE included in the “specials” category? I know it’s got to be somewhere in the database, as when I go to those posts it shows that they are in that category. Yet nowhere in the event_category_id field does it show that. So where are they listed as such?

Thanks.

Angelo,

It is there:

INSERT INTO wp_terms (term_id, name, slug, term_group) VALUES
(1, ‘Uncategorized’, ‘uncategorized’, 0),
(2, ‘Specials’, ‘specials’, 0);

This is how I retrieve other data:

$sql = “SELECT event_start_date, recurrence_interval, event_name, post_content, event_start_time, event_slug, event_all_day, event_category_id FROM wp_em_events”;

But this is where the problem arises. When I try to find those posts that are in the “specials” category, the event_category_id shows NULL.

The code snippet from the previous post will route you to the category name given a post id. Using the ID from the post you join with the term relationship table to get the term_taxonomy_id which then can be joined to the term_taxonomy table to retrieve the term_id. The term id then gives you access to the terms table, where the category names (name) is found.

Okay, so like I said, I’m a newbie. I’m learning PHP, but not there yet – at least not where you’re at!

This is what I’ve got, and it works, except that I need it to see if a post is in the “specials” category. (If it is, I would echo that post. You can see my attempt.) What do I need to add to make this fly?

function todays_listings() {
	$servername = "localhost";
	$username = "angelsdb";
	$password = "R#start2013";
	$dbname = "angelsice";
	date_default_timezone_set("America/Belize");
	$fromToday = date("Y-m-d");

	/// Create connection
	$conn = new mysqli($servername, $username, $password, $dbname);
	// Check connection
	if ($conn->connect_error) {
    		die("Connection failed: " . $conn->connect_error);
	} 

	/// Read and parse data
	$sql = "SELECT event_start_date, recurrence_interval, event_name, post_content, event_start_time, event_slug, event_all_day, event_category_id FROM wp_em_events";
	$result = $conn->query($sql);

	if ($result->num_rows > 0) {
		// output data of each row
		while($row = $result->fetch_assoc()) {
			$fromCalendar = $row["event_start_date"];
			$dateEvent = new DateTime($fromCalendar);
			$dateToday = new DateTime($fromToday);
			$attributes = $row["event_attributes"];
			if((is_front_page()) && (strtotime($fromToday) == strtotime($fromCalendar)) && ($row["event_category_id"] == "specials") ) { 

				echo "<h2>" . $row["event_name"] ."</h2>";
				echo "<h3>" . $row["post_content"] . "</h3>";
				$timeEvent = $row["event_start_time"];
				if((int)$timeEvent > "00:00:00") {
					echo "<h4>All Day Long!</h4>";
				} else {
					echo "<h4>" . $time->format('h:i a') . "</h4>";
				}
				return;
			}
    		}

	} else {

  		echo "0 results";

	}

	mysqli_close($conn);
}

You could use this code, to determine if the post is associated with the specials category. Note, I am using slug, to avoid the case and spacing issues. Then check the result set for rows.

Alternatively, if you are only looking for specials, you should modify your $sql, to include the joins and where clauses, to allow the database to restrict the result set.

SELECT e.post_id, t.name,t.slug,date_format(event_start_date,'%Y-%m')
  FROM test_em_events e
    LEFT JOIN test_term_relationships tr on (e.post_id = tr.object_id)
    LEFT JOIN test_term_taxonomy tt on (tr.term_taxonomy_id=tt.term_taxonomy_id)
    LEFT JOIN test_terms t on (tt.term_id=t.term_id)
WHERE e.post_id = $row['post_id']
     AND e.recurrence = 0
     AND t.slug = 'specials'

Lastly, Event Manager has Placeholders that may satify your needs without the coding. From the documentation set, check:

Related Events

You can show lists of other events belonging to this category. The formatting of the list is the same as a normal events list.

#_CATEGORYPASTEVENTS
Will show a list of all past events with this category.
#_CATEGORYNEXTEVENTS
Will show a list of all future events with this category.
#_CATEGORYALLEVENTS
Will show a list of all events with this category.
#_CATEGORYNEXTEVENT
Will show the next event with this category.