Dynamic Html Table from MySQL syntax error from query

Question

I am dynamically creating a HTML from a selection made from a dropdown box on the event field in my databse. It works perfectly with a simple SQL query $selectStmt = $con->prepare('SELECT * FROM men WHERE event like :start');. I do however want to run a complex query but then get an

Parse error: syntax error, unexpected '',surname) AS '' (T_CONSTANT_ENCAPSED_STRING).

I am not sure if it is the query or the code after it, as the on the line number of the long query. The query works perfectly in other scripts that are not dynamically generated. Any help please.

My code:

<?php

    $dsn = 'mysql:host=localhost;dbname=****';
    $username = '****';
    $password = '****';
    try {
        $con = new PDO($dsn, $username, $password);
        $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (Exception $ex) {
        echo 'Not Connected '.$ex->getMessage();
    }
    $tableContent = '';
    $start = '';
    $selectStmt = $con->prepare('SELECT * FROM men');
    $selectStmt->execute();
    $users = $selectStmt->fetchAll();
    foreach ($users as $user) {
        $tableContent = $tableContent.'<tr>'.
                '<td>'.$user['id'].'</td>'
                .'<td>'.$user['name'].'</td>'
                .'<td>'.$user['surname'].'</td>'
                .'<td>'.$user['event'].'</td>'
                .'<td>'.$user['performance'].'</td>';
    }

    if(isset($_POST['search']))
    {
       $start = $_POST['start'];
       $tableContent = '';
       $selectStmt = $con->prepare('SELECT rank, performance, wind,athlete,dob,prov,pos,place,date FROM (SELECT performance, wind,name,surname,dob,prov,pos,place,date, @curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank, @incRank := @incRank + 1, @prevRank := performance, CONCAT(name,' ',surname) AS 'athlete' FROM men p, ( SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 ) r WHERE event like :start ORDER BY performance) s');
       $selectStmt->execute(array(
             ':start'=>$start.'%'
       ));
       $users = $selectStmt->fetchAll();
       foreach ($users as $user){
           $tableContent = $tableContent.'<tr>'.
                '<td>'.$user['id'].'</td>'
                .'<td>'.$user['name'].'</td>'
                .'<td>'.$user['surname'].'</td>'
                .'<td>'.$user['event'].'</td>'
                .'<td>'.$user['performance'].'</td>';
       }
    }
?>

You’re simply messing up the PHP syntax with the quote characters used inside the SQL string ... go read http://php.net/manual/en/language.types.string.php if you lack the basic knowledge in that area. — CBroe, Jun 28 '17 at 11:17
@Rushikumar it is on this line `$selectStmt = $con->prepare('SELECT rank, performance, wind,athlete,dob,prov,pos,place,date FROM (SELECT performance, wind,name,surname,dob,prov,pos,place,date, @curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank, @incRank := @incRank + 1, @prevRank := performance, CONCAT(name,' ',surname) AS 'athlete' FROM men p, ( SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 ) r WHERE event like :start ORDER BY performance) s');` — Seef, Jun 28 '17 at 11:27

score 1 · Accepted Answer · answered Jun 28 '17 at 11:17

You have combination of single quotes which giving error:

Change this:

$selectStmt = $con->prepare("SELECT rank, performance,
wind,athlete,dob,prov,pos,place,date FROM (SELECT performance, wind,name,surname,dob,prov,pos,place,date, @curRank := IF(@prevRank =
performance, @curRank, @incRank) AS rank, @incRank := @incRank + 1,
@prevRank := performance, CONCAT(name,' ',surname) AS 'athlete'
FROM men p, ( SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 ) r
WHERE event like :start ORDER BY performance) s");

score 0 · Answer 2 · answered Jun 28 '17 at 11:17

In CONCAT(name,' ',surname) AS 'athlete' remove the quotes from athlete alias and try the below,

$selectStmt = $con->prepare('SELECT rank, performance, wind,athlete,dob,prov,pos,place,date FROM 
 (SELECT performance, wind,name,surname,dob,prov,pos,place,date,
  @curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank, 
  @incRank := @incRank + 1, @prevRank := performance,
  CONCAT(name,' ',surname) AS athlete FROM men p,
  ( SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 ) r
  WHERE event like :start ORDER BY performance) s');

Dynamic Html Table from MySQL syntax error from query

2 Answers2