Query for a pivot table

[jej12]

I'm having trouble with the logic for my query. Printing $sql gives me the following:

 

SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BBQ' at line 1

 

"BBQ" is the first $val from the loop. What am I missing?

 

      $sql = "SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,";

            $dbQuery = mysql_result(mysql_query("SELECT description FROM events"), 0,0);            
        $size = count($dbQuery);            
        $i = 0;
            foreach ($dbQuery as $val)
            { //02
           if($val != '')
            { //02            
                       $sql .= "MAX(CASE e.description WHEN '". $val ."' THEN e.description END) AS `". $val ."`";                  
                  }
                  $i++;
          if($i != $size) $sql .= ",";
                  
            }//02
       
            if (!mysql_query($dbQuery)) {
                  die('Error: ' . mysql_error());
            }

     $sql .= "FROM requests r
                   LEFT OUTER JOIN (
                         registration_xref xref
                        INNER JOIN events e ON e.id_events = xref.event_id
                   ) ON r.id = xref.attendee_id
                   GROUP BY r.id";

[trq]

echo $sql to see what it actually looks like. Post it here if need be.

[jej12]

This is all I get when I echo $sql

 

SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BBQ' at line 1

[trq]

Obviously you need to stop the execution of the query so it doesn't generate an error.

 

Why on earth are you building a query from results found in the database anyway?

[jej12]

The rows in the events table can change. I was trying to make it more dynamic rather than trying to hard code everything.

 

The query works if it's hard coded (See below). Is there a better way to accomplish this?

 

      $sql = "SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,
 MAX(CASE e.description WHEN 'BBQ' THEN e.description END) AS `BBQ`,
 MAX(CASE e.description WHEN 'Tour' THEN e.description END) AS `Tour`                  
FROM requests r
LEFT OUTER JOIN (
registration_xref xref
INNER JOIN events e ON e.id_events = xref.event_id
) ON r.id = xref.attendee_id
GROUP BY r.id";

[DavidAM]

            $dbQuery = mysql_result(mysql_query("SELECT description FROM events"), 0,0);            
        $size = count($dbQuery);            
        $i = 0;
            foreach ($dbQuery as $val)
            { //02
           if($val != '')
            { //02            
                       $sql .= "MAX(CASE e.description WHEN '". $val ."' THEN e.description END) AS `". $val ."`";                  
                  }
                  $i++;
          if($i != $size) $sql .= ",";
                  
            }//02
       
            if (!mysql_query($dbQuery)) {
                  die('Error: ' . mysql_error());

 

Look at the first line and last 2 lines. $dbQuery contains the data you are retrieving from your SELECT on the events table. You are trying to execute that value (i.e. BBQ) in the next-to-last line here. I don't see the sense in that if statement being there at all. It would be more useful if you tested the result of mysql_query immediately after calling it (the first line). However, since you have nested it inside of a call to mysql_result, there is no way to test it.

 

I recommend breaking up that first line into several lines:

$innerSql = "SELECT ...";
$innerResult = mysql_query($innerSql);
if ($innerResult === false) // some error stuff here
for each ($innnerData = mysql_fetch_assoc($innerResult)) { // handle data here ...

 

 

[jej12]

Look at the first line and last 2 lines. $dbQuery contains the data you are retrieving from your SELECT on the events table. You are trying to execute that value (i.e. BBQ) in the next-to-last line here. I don't see the sense in that if statement being there at all. It would be more useful if you tested the result of mysql_query immediately after calling it (the first line). However, since you have nested it inside of a call to mysql_result, there is no way to test it.

 

I recommend breaking up that first line into several lines:

$innerSql = "SELECT ...";
$innerResult = mysql_query($innerSql);
if ($innerResult === false) // some error stuff here
for each ($innnerData = mysql_fetch_assoc($innerResult)) { // handle data here ...

Thank you for drawing my attention to those lines and your suggestion. I broke up the first line and removed the if statement. Everything seems to be working correctly now.