This is an extension of my 2014 High Ed Web presentation: WordPress and Beer

Sometimes you need to store a bit of data that won’t fix in a post type. For our calculator: each recipe is a post, but we also have to store the list of ingredients for the recipe. This is a many-to many relationship. Luckily WP plays nicely with additional tables in its DB.

Using phpMyAdmin, I built the table ‘agb_recipemeta’ with the same prefix as the rest of the WP tables:

Each row has three id’s

• meta_id: unique, auto-incremented value for this table
• recipe_id: matches the id for the recipe post that this gets hooked to. There may be many rows for each recipe.
• ingredient_id: matches the id for the grain, hop, yeast, or water ingredient that this is hooked to.

We can also add this table name to the $wpdb variable for easy access:

[php]

// allow easy access the recipe_meta table
$wpdb->agb_recipemeta = $wpdb->prefix . ‘agb_recipemeta’; // this needs to be somewhere global for other pages

[/php]

Now a little basic SQL will get the values into the new table:

[php]
// and insert!        
$query = "
    INSERT INTO $wpdb->agb_recipemeta(recipe_id,ingredient_id,hop_alpha,hop_oz,hop_mins,yeast_att,grain_lb,grain_ppg,grain_srm,water_ca,water_mg,water_hco3,water_cl,water_na,water_so4)
    VALUES $sql_ingredients
";
$result = mysql_query($query);
[/php]