From 38bfa49958eca679166231f46e767f229d2561a6 Mon Sep 17 00:00:00 2001
From: Harald Eilertsen <haraldei@anduin.net>
Date: Sat, 4 Sep 2021 20:41:01 +0200
Subject: Fix so concert id is not overwritten by venue id.

We originally had a more specified query, but simplified it to:

   SELECT * FROM wpg_concerts LEFT JOIN wpg_venues ON ...;

But since both the concerts table and the venues table has a column id,
the concert id would be overwritten with the venue id. MySQL/MariaDB
does not allow columns with the same name in multiple tables when using
unqualified column names in the query.

So we need to be more explicit again.

I was hoping that the following would work:

    SELECT wpg_concerts.*, wpg_venues.* FROM .... ;

I think MySQL/MariaDB would handle that, but now since php turns the
result into an array, where each key must be unique, this again
overwrites the concert id with the venue id.

So thus a more verbose specification of the columns was necessary.
---
 includes/concert.php | 13 ++++++++-----
 1 file changed, 8 insertions(+), 5 deletions(-)

diff --git a/includes/concert.php b/includes/concert.php
index c0c13ee..a533679 100644
--- a/includes/concert.php
+++ b/includes/concert.php
@@ -30,6 +30,12 @@ if ( !class_exists('GiglogAdmin_Concert') ) {
         public const STATUS_ALL_APPROVED = 4;
         public const STATUS_REJECTED = 5;
 
+        private const BASE_QUERY =
+            'SELECT wpg_concerts.*, wpg_venues.id as venue_id, wpg_venues.wpgvenue_name wpg_venues_wpgvenue_city '
+            . 'FROM wpg_concerts '
+            . 'LEFT JOIN wpg_venues ON wpg_concerts.venue = wpg_venues.id ';
+
+
         /*
          * Constructs a new concert object from an array of attributes.
          * The attributes are expected to be named as in the database,
@@ -67,7 +73,6 @@ if ( !class_exists('GiglogAdmin_Concert') ) {
             }
         }
 
-
         /**
          * Get concert with given id.
          *
@@ -78,8 +83,7 @@ if ( !class_exists('GiglogAdmin_Concert') ) {
         {
             global $wpdb;
 
-            $query = 'SELECT * FROM wpg_concerts '
-                . 'LEFT JOIN wpg_venues ON wpg_concerts.venue = wpg_venues.id '
+            $query = self::BASE_QUERY
                 . 'WHERE ' . $wpdb->prepare('wpg_concerts.id = %d', $id);
 
             $results  = $wpdb->get_results($query);
@@ -200,8 +204,7 @@ if ( !class_exists('GiglogAdmin_Concert') ) {
         {
             global $wpdb;
 
-            $query = 'SELECT * FROM wpg_concerts '
-                . 'INNER JOIN wpg_venues ON wpg_concerts.venue = wpg_venues.id ';
+            $query = self::BASE_QUERY;
 
             $where = [];
 
-- 
cgit v1.2.3