Mysqli multiple prepared statements with temp table and stored function. Last prepared statement not binding

Question

   # I am using a combination of php prepared statements with mysql. I have created stored functions which work fine. I also am creating a temp table. However when I hardcode the last prepared query my result table is sorted correctly but when I try to use prepared bind parameters it doesn't sort correctly. Here is the flow of what is going on #


    $createTempTable = '
     CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (

    SELECT st.Songs_ID, st.Num_Of_Votes, st.Song_Title, 
           st.Date_Released, st.MusicLink, st.ytVideoID,
           "                                                            " AS artists, 
           "                                                            " AS genres
    FROM Songs_Table st
        INNER JOIN Songs_Genres_Crossover sgc ON sgc.Song_ID = st.Songs_ID
    INNER JOIN Song_Genres sg ON 
                                  sgc.Genre_ID = sg.Genre_ID 
                                  AND sg.Genre_Label = ?
    WHERE 
      st.Date_Released >= ? && st.Date_Released <= ?
    );
    ';


    $stmt1 = $mysqli -> prepare("$createTempTable");

    $stmt1->bind_param('sss', $genreLabelLowercase, $startTime, $endTime);

    $stmt1->execute();
    $stmt1->close();


    //this calls stored function and requires no params
    $updateTempTable = '
     SELECT populateSongIdsWithArtistsAndGenres();
    ';

    $stmt2 = $mysqli -> prepare("$updateTempTable");

    $stmt2->execute();
    $stmt2->close();


    //this is the function that doesn't seem to bind correctly
    //notice if i hardcode this and do not use ? in it the query is executed fine
    $getTempTableResult = '
    SELECT Songs_ID, Num_Of_Votes, Song_Title, Date_Released, 
           MusicLink, ytVideoID, artists, genres 
    FROM table2 
    ORDER BY ? 
    LIMIT ? , ? ;
    ';

    $stmt3 = $mysqli -> prepare("$getTempTableResult");

    $topVotedOrderBy = 'Num_Of_Votes DESC, artists ASC, Song_Title ASC';
    $firstSong = 0;
    $songsToUse = 100;

    $stmt3->bind_param('sii', $topVotedOrderBy, $firstSong, $songsToUse);

    $stmt3->execute();


    /* Bind results */
    $stmt3 -> bind_result($songId, $numOfVotes, $songTitle, $dateReleased, 
                          $musicLink, $ytVidId, $artists, $genres);

    while ($stmt3 -> fetch()) {
    //use results
    }

    $stmt3->close();

I hope this is all the info needed to help. I need to figure out why stmt3 is not executing correctly. Thanks! I was looking int mysqli_multi_query but I would like to make the query work this way which protects with the prepared statements before i venture to multi_query.

 Thanks guys I am using the query in this way as a workaround. It is messier but it works fine                #



$createTempTable = '
 CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (

SELECT st.Songs_ID, st.Num_Of_Votes, st.Song_Title, 
        st.Date_Released, st.MusicLink, st.ytVideoID, "                                                            " AS artists, 
        "                                                            " AS genres
            FROM Songs_Table st
            INNER JOIN Songs_Genres_Crossover sgc
            ON sgc.Song_ID = st.Songs_ID
            INNER JOIN Song_Genres sg
            ON sgc.Genre_ID = sg.Genre_ID AND sg.Genre_Label = ?

            WHERE 
            st.Date_Released >= ? && st.Date_Released <= ?


);

';

$stmt1 = $mysqli -> prepare("$createTempTable");

$stmt1->bind_param('sss', $genreLabelLowercase, $startTime, $endTime);

$stmt1->execute();
$stmt1->close();



$updateTempTable = '
 SELECT populateSongIdsWithArtistsAndGenres();
';

$stmt2 = $mysqli -> prepare("$updateTempTable");

$stmt2->execute();
$stmt2->close();



if($sort === 'Date Released(Newest First)') {
    //newest released
    $dateReleasedNewestFirstOrderBy = 'Date_Released DESC, artists ASC, Song_Title ASC';
    $getTempTableResult = '
SELECT Songs_ID, Num_Of_Votes, Song_Title, Date_Released, MusicLink, ytVideoID, artists, genres FROM table2 ORDER BY '.$dateReleasedNewestFirstOrderBy.' LIMIT '.$firstSong.' , '.$songsToUse.' ;
';
}
else if ($sort == 'Artist(A-Z)') {//all artists a to z
    //artist a-z
    $artistAToZOrderBy = 'artists ASC, Song_Title ASC';
    $getTempTableResult = '
SELECT Songs_ID, Num_Of_Votes, Song_Title, Date_Released, MusicLink, ytVideoID, artists, genres FROM table2 ORDER BY '.$artistAToZOrderBy.' LIMIT '.$firstSong.' , '.$songsToUse.' ;
';
}
else if ($sort == 'Title(A-Z)') {
    //title a-z
    $songTitlesAToZOrderBy = 'Song_Title ASC, artists ASC';
    $getTempTableResult = '
SELECT Songs_ID, Num_Of_Votes, Song_Title, Date_Released, MusicLink, ytVideoID, artists, genres FROM table2 ORDER BY '.$songTitlesAToZOrderBy.' LIMIT '.$firstSong.' , '.$songsToUse.' ;
';
}
else {
    //top voted
    $topVotedOrderBy = 'Num_Of_Votes DESC, artists ASC, Song_Title ASC';
    $getTempTableResult = '
SELECT Songs_ID, Num_Of_Votes, Song_Title, Date_Released, MusicLink, ytVideoID, artists, genres FROM table2 ORDER BY '.$topVotedOrderBy.' LIMIT '.$firstSong.' , '.$songsToUse.' ;
';
}

$stmt3 = $mysqli -> prepare("$getTempTableResult");

$stmt3->execute();

$stmt3 -> bind_result($songId, $numOfVotes, $songTitle, $dateReleased, $musicLink, $ytVidId, $artists, $genres);

while ($stmt3 -> fetch()) {
//use results
}
$stmt3->close();

Answer 1

See: http://ca3.php.net/manual/en/mysqli.prepare.php

...[prepared statements] are not allowed for identifiers (such as table or 
column names)...

If you're using prepared statements for the ORDER BY clause, which contains column names, because you expect the order to be determined by user input then it looks like you need to validate the user input and/or use mysqli_real_escape_string.

:)