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, May 07 '21 at 21:57
Ok thanks. that's not an "undefined index" error, that's a SQL syntax error — ADyson, May 07 '21 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, May 07 '21 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, May 07 '21 at 22:26
You could use `using` for the `join`s as well. https://stackoverflow.com/questions/11366006/mysql-join-on-vs-using — user3783243, May 07 '21 at 22:56

score 1 · Answer 1 · answered May 08 '21 at 05:30

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 08 '21 at 05:30

Rick James

135,179
13
127
222

MYSQL query with html input

1 Answers1