# 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();
 
 我希望这是帮助所需的所有信息。 我需要弄清楚为什么stmt3没有正确执行。 谢谢！ 我正在寻找mysqli_multi_query，但我希望以这种方式使查询工作，在我冒险使用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();

   # 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();

原文：https://stackoverflow.com/questions/24353449