MYSQL query with html input

Question

Hello I am trying to create a query for a table that shows the performances performed by the artist selected in the form's drop down menu. I am receiving an undefined index error but I used the same piece of code to reference the input earlier in my code so I am confused. any help is appreciated.

Query error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'INNER JOIN performance p ON p.artist_id = a.artist_id INNER JOIN recording r ON' at line 4 in C:\inetpub\wwwroot\courses\354\Students\raw51419\final exam\test.php:72 Stack trace: #0 C:\inetpub\wwwroot\courses\354\Students\raw51419\final exam\test.php(72): PDO->prepare('\nSELECT a.first...') #1 {main} thrown in C:\inetpub\wwwroot\courses\354\Students\raw51419\final exam\test.php on line 72

    $content ="";
require_once("db.php");
$query = "
SELECT a.first_name, a.last_name, s.genre, s.title, s.year,r.length, p.venue, p.date
FROM artist a
WHERE artist_id = ?
INNER JOIN performance p ON p.artist_id = a.artist_id 
INNER JOIN recording r ON p.recording_id = r.recording_id
INNER JOIN song s ON p.song_id = s.song_id";
$stmt = $conn->prepare($query);
$stmt->execute([$artid]);
foreach ($stmt as $row) {

$content .=
  "<tr>
    <td>{$row["first_name"]}</td>
    <td>{$row["last_name"]}</td>
    <td>{$row["title"]}</td>
    <td>{$row["year"]}</td>
    <td>{$row["genre"]}</td>
    <td>{$row["date"]}</td>
    <td>{$row["venue"]}</td>
    <td>{$row["length"]}</td>
  </tr>";
  }

$content =
  "<table>
  <h4>Performances:</h4>
  <table class='table table-bordered table-striped'>
    <tr>
        <th>First</th>
        <th>Last</th>
        <th>Title</th>
        <th>Year</th>
        <th>Genre</th>
        <th>Date</th>
        <th>Venue</th>
        <th>Length</th>
    </tr>
    {$content}
    </table>";

What is the exact error and which line does it occur on? That information will make it a lot easier for us to help you — ADyson
– ADyson, Commented May 7, 2021 at 21:57
Ok thanks. that's not an "undefined index" error, that's a SQL syntax error — ADyson
– ADyson, Commented May 7, 2021 at 22:06
You've got your WHERE clause before your INNER JOINS. That's not valid. Study basic SQL please - the WHERE clause always comes after the FROM and any JOINs — ADyson
– ADyson, Commented May 7, 2021 at 22:07
AND when you do move that WHERE statement, dont make it ambiguous - instead of WHERE artist_id = ? should probably be something like WHERE a.artist_id = ? — Kinglish
– Kinglish, Commented May 7, 2021 at 22:26

Rick James · Accepted Answer · 2021-05-08 05:30:46Z

1

The order of things in SELECT is strict. The common items are:

SELECT ...
FROM ...
JOIN ...   ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

Many of those clauses are optional.

answered May 8, 2021 at 5:30

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL query with html input

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related