I'm developing a RESTApi using Php with Slim framework to connect an Android App to a MySQL db (Sending test requests with Postman right now). The app is a song request app for a live music event in my town- Every artist who participates in the show adds their songs to the app, and attendees at the show can select and request a song which sends an email to the artist with their position in the queue.
I'm trying to insert into a table called setqueues(ShowID int, SongID int, ArtistID int, Position int), but first I have to see if the ShowID given has any songs assigned to it. If it does I take the number of rows returned, add 1, and assign it to a variable called $pos; otherwise $pos = 1. I then try to insert a new row into the table, but am getting
Call to a member function bind_param() on boolean
whenever I try to do this. My code is:
index.php
$app->post('/songtoqueue', function(Request $request, Response $response){
if(!haveEmptyParameters(array('show_id', 'song_id', 'artist_id'), $request, $response)){
$request_data = $request->getParsedBody();
$show_id = $request_data['show_id'];
$song_id = $request_data['song_id'];
$artist_id = $request_data['artist_id'];
$db = new DbOperations;
$result = $db->addSongToShow($show_id, $song_id, $artist_id);
if($result == SHOW_SONG_ADDED){
$message = array();
$message['error'] = false;
$message['message'] = 'Song added to Show successfully';
$response->write(json_encode($message));
return $response
->withHeader('Content-type', 'application/json')
->withStatus(201);
}else if($result == SHOW_SONG_FAILURE){
$message = array();
$message['error'] = true;
$message['message'] = 'Some error occurred while attempting to add Song to Show.';
$response->write(json_encode($message));
return $response
->withHeader('Content-type', 'application/json')
->withStatus(422);
}
}
return $response
->withHeader('Content-type', 'application/json')
->withStatus(422);
});
DbOperations.php
public function addSongToShow($show_id, $song_id, $artist_id){
$stmt = $this->con->prepare("SELECT COUNT(*) FROM (SELECT * FROM setqueues WHERE ShowID = ?) as c");
$stmt->bind_param("i", $show_id);
$stmt->execute();
$stmt->bind_result($count);
$stmt->fetch();
$pos = 0;
if($count == 0){
$pos = 1;
}else{
$pos = $count + 1;
}
$stmt = $this->con->prepare("INSERT INTO setqueues(ShowID, SongID, ArtistID, Position) VALUES (?,?,?,?)");
$stmt->bind_param("iiii", $show_id, $song_id, $artist_id, $pos);
if($stmt->execute()){
return SHOW_SONG_ADDED;
}
return SHOW_SONG_FAILURE;
}
DbConnect.php
<?php
class DbConnect{
private $con;
function connect(){
include_once dirname(__FILE__) . '/Constants.php';
$this->con = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if(mysqli_connect_errno()){
echo "Failed to connect" . mysqli_connect_error();
return null;
}
return $this->con;
}
}
I have checked that the queries run properly in phpmyadmin, and can get them to work if I only use one or the other, but any time I attempt to do both my code fails and I get the error above, with the offending line being
$stmt = $this->con->prepare("INSERT INTO setqueues(ShowID, SongID, ArtistID, Position) VALUES (?,?,?,?)");
There are already many questions on here where the solution was incorrect MySQL statements; however, I am certain these queries are correct, and have even tried copy/pasting the auto generated insert statement from phpmyadmin into my code. The issue is using both of the above queries together. Any advice is greatly appreciated
--UPDATE-- This was solved by enabling mysqli error reporting with the line
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
before my connect statement. This allowed me to see that I needed to add $stmt->close(); in between my queries, because mysqli uses unbuffered queries by default
