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

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

# using combination of php prepared statements mysql. have created stored functions work fine. creating temp table. when hardcode lastly prepared query result table sorted correctly when seek utilize prepared bind parameters doesn't sort correctly. here flow of going on # $createtemptable = ' create temporary table if not exists table2 ( select st.songs_id, st.num_of_votes, st.song_title, st.date_released, st.musiclink, st.ytvideoid, " " artists, " " genres songs_table st inner bring together songs_genres_crossover sgc on sgc.song_id = st.songs_id inner bring together song_genres sg on sgc.genre_id = sg.genre_id , sg.genre_label = ? 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 , requires no params $updatetemptable = ' select populatesongidswithartistsandgenres(); '; $stmt2 = $mysqli -> prepare("$updatetemptable"); $stmt2->execute(); $stmt2->close(); //this function doesn't seem bind correctly //notice if hardcode , not utilize ? in query executed fine $gettemptableresult = ' select songs_id, num_of_votes, song_title, date_released, musiclink, ytvideoid, artists, genres table2 order ? 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 info needed help. need figure out why stmt3 not executing correctly. thanks! looking int mysqli_multi_query create query work way protects prepared statements before venture multi_query. guys using query in way workaround. messier works fine # $createtemptable = ' create temporary table if not exists table2 ( select st.songs_id, st.num_of_votes, st.song_title, st.date_released, st.musiclink, st.ytvideoid, " " artists, " " genres songs_table st inner bring together songs_genres_crossover sgc on sgc.song_id = st.songs_id inner bring together song_genres sg on sgc.genre_id = sg.genre_id , sg.genre_label = ? 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 table2 order '.$datereleasednewestfirstorderby.' limit '.$firstsong.' , '.$songstouse.' ; '; } else if ($sort == 'artist(a-z)') {//all artists 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 table2 order '.$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 table2 order '.$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 table2 order '.$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();

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

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

if you're using prepared statements order clause, contains column names, because expect order determined user input looks need validate user input and/or utilize mysqli_real_escape_string.

:)

php mysqli