[SOLVED] Variables in SQL query with mysqli & AJAX

[JonathanV]

Hi,

 

I'm fairly new to php and I've found this forum using google.

I'm kind of stuck in my first real project on something and was hoping someone here would know the answer.

 

I've got a select box of wich the options are dynamically loaded with PHP. When I click on one of the options I want to use that to filter a list with songs using AJAX so the page doesn't have to reload.

 

This is my phpfile to filter the songs :

 

I am using an auto incremented id on the option in my selectbox, they are linked to the id's of the artist/genre so I can filter the results. That all works without a problem but I also want to be abled to select " all artists " while the genre is set at something different. I could just create another piece of 40 lines of code for a workaround but I'd rather find a more performant way so what I'm trying to do is this :

When "all genres" or "al artists" is selected they give the id="0" to my php file. And in my php file I check wether it's "0" and if it is I change the variable to replace the "?" in my querystring to "*" ( instead of what would be an integer linking the option to a certain artist/genre ) wich should just select everything in that column if I should believe what I learned. But it doesn't work ...

 

I've put the important pieces of code in bold.

 

I hope you understand what I mean because my english isn't that great 

 

<?php

require_once('functions.php');
$dbc = getDBConnection();

$genre =  $_POST['genre'];
$groep =  $_POST['groep'];

[b]if($genre == 0){ $genre = '*'; }
if($groep == 0){ $groep = '*'; }[/b]

// sql query voorbereiden.
$sql = "SELECT song.titel as songnaam, groep.titel as groepsnaam, genre.titel as genrenaam ";
$sql .= "FROM tblsongs as song ";
$sql .= "INNER JOIN tblgroepen as groep ";
$sql .= "ON song.groep_id = groep.id ";     // spaties op het einde van elk stuk van de query niet vergeten anders werkt hij niet !!!!
$sql .= "INNER JOIN tblgenres as genre ";
$sql .= "ON groep.genre_id = genre.id ";
[b]$sql .= "WHERE groep.id = ? AND genre.id = ?";[/b]

if( $stmt = $dbc->prepare( $sql )){

[b]$stmt->bind_param("ss", $groep, $genre);[/b]
if( $stmt->execute() ){

	// opslaan van het resultaat ( sneller bij het fetchen door bufferen doch vereist meer geheugen van de server! ).
	$stmt->store_result();

	$stmt->bind_result($songnaam, $groepsnaam, $genrenaam);

	while($stmt->fetch()) {

		$str = "<a href=\"#\" class=\"songlink\"><span class=\"song\">" . $songnaam . "</span>";
		$str .="<span class=\"song\">" . $groepsnaam . "</span>";
		$str .="<span class=\"song\">" . $genrenaam . "</span></a><br />";

		echo $str;
	}

	exit();

} else {

	echo("Er is een fout opgetreden: " . $stmt->error . "");	
}

} else {	

echo("Er is een fout opgetreden: " . $dbc->error . ""); 
}

?>

[GingerRobot]

If i understand you correctly, an easy work-around would be to use the LIKE operator rather than the = operator in your query. For example:

 

if(showingAllGenres){
     $genres = '%';
}else{
     $genres = $_POST['genre'];
}
//Similarly for the group
$sql .= "WHERE groep.id LIKE '$groups' AND genre.id = '$genres'";

 

I hope you understand what I mean because my english isn't that great

 

Unfortunately (or, perhaps from your perspective, fortunately) your English seems better than most. Also, it's nice to see a new member trying to help other members as well as asking their own question 

 

[JonathanV]

Thanks for your reply, I have adjusted my application using your solution and it works like a charm.

If I understand correctly it's necessary to use LIKE instead of the equal sign ( = ) in SQL Query's when using wildcards then ?!

 

For anyone who could still find this usefull this is how it eventually worked ( with only minor changes + I changed my comments to English, you never know they might help someone in the future ) :

 

<?php

require_once('functions.php');
$dbc = getDBConnection();

$genre =  $_POST['genre'];
$groep =  $_POST['groep'];

if($genre == 0){$genre = '%';}          // <---- This was changed to use the '%' ( zero or more characters ).
if($groep == 0){$groep = '%';}

// prepare querystring
$sql = "SELECT song.titel as songnaam, groep.titel as groepsnaam, genre.titel as genrenaam ";
$sql .= "FROM tblsongs as song ";
$sql .= "INNER JOIN tblgroepen as groep ";
$sql .= "ON song.groep_id = groep.id ";
$sql .= "INNER JOIN tblgenres as genre ";
$sql .= "ON groep.genre_id = genre.id ";
$sql .= "WHERE groep.id LIKE ? AND genre.id LIKE ?";     // <------ This was changed to use LIKE instead of = , necessary when using wildcard characters like '%' or '_' in the query .

if( $stmt = $dbc->prepare( $sql )){

$stmt->bind_param("ss", $groep, $genre);
if( $stmt->execute() ){

	// save the result ( makes fetching faster because of buffering but uses more cpu power from the server so use wisely ! ).
	$stmt->store_result();

	$stmt->bind_result($songnaam, $groepsnaam, $genrenaam);

	while($stmt->fetch()) {

		$str = "<a href=\"#\" class=\"songlink\"><span class=\"song\">" . $songnaam . "</span>";
		$str .="<span class=\"song\">" . $groepsnaam . "</span>";
		$str .="<span class=\"song\">" . $genrenaam . "</span></a><br />";

		echo $str;
	}

	exit();

} else {

	echo("An error occured: " . $stmt->error . "");	
}

} else {	

echo("An error occured: " . $dbc->error . ""); 
}

?>

 

Thx a lot for your help !

And yes, I think it's good to help others solve their problems as a beginner too because you learn a lot by doing so. More experience makes better code !

 

Grtz

[GingerRobot]

If I understand correctly it's necessary to use LIKE instead of the equal sign ( = ) in SQL Query's when using wildcards then ?!

 

That's correct, yes.

[JonathanV]

Great, it all makes sense now !

 

Your help was appreciated