[SOLVED] concatenating a variable to use for sql search

[coutts]

hello:

I have working on this piece of PHP to create an SQL search and it always comes back no results (when I know there should be). I have echoed the result and tried it directly in PHPmyAdmin and the SQl query works that way. I am at my wits end and as I am a rank beginner I ask someones help to see what is wrong with is code.

 

---------------------------------

 

<?

 

$composition = $_POST['composition'];

$origin = $_POST['origin'];

$bgenre = $_POST['bgenre'];

$voice = $_POST['voice'];

$vibe = $_POST['vibe'];

$tempo = $_POST['tempo'];

$decade = $_POST['decade'];

$searching = $_POST['searching'];

 

 

//This is only displayed if they have submitted the form

if ($searching =="yes")

{

echo "<h2>Results</h2><p>";

 

//If they did not enter a composition type we give them an error

if ($composition == "" )

{

echo "<p>You must enter composition type";

exit;

}

 

//If they did not enter a search term we give them an error

if ($composition == "" AND $origin == "" AND $bgenre == "" AND $voice == "" AND $vibe == "" AND $tempo == "" AND $decade == "")

{

echo "<p>You forgot to enter a search term";

exit;

}

 

 

// Otherwise we connect to our Database

mysql_connect("localhost", "user", "password") or die(mysql_error());

mysql_select_db("database") or die(mysql_error());

 

//Build search term

$data = "mysql_query(\"SELECT * FROM music WHERE";

if ($composition != "") {

$data.= " composition LIKE '$composition'";

}

if ($origin != "") {

$data.= " AND origin LIKE '$origin'";

}

if ($bgenre != "") {

$data.= " AND bgenre LIKE '$bgenre'";

}

if ($voice != "") {

$data.= " AND voice LIKE '$voice'";

}

if ($tempo != "") {

$data.= " AND tempo LIKE '$tempo'";

}

if ($decade != "") {

$data.= " AND decade LIKE '$decade'";

}

if ($vibe != "") {

$data.= " AND (vibe1 LIKE '$vibe' OR vibe2 LIKE '$vibe' OR vibe3 LIKE '$vibe')";

}

$data.= "\")";

 

//just to troubleshoot

echo ( $data );

 

//And we display the results

while($result = mysql_fetch_array( $data ))

{

if( $result['approved'] == "YES" ){

echo $result['username'];

echo " ";

echo $result['title'];

echo "<br>";

echo $result['label'];

echo "<br>";

echo "<br>";

}

}

 

//This counts the number or results - and if there wasn't any it gives them a little message explaining that

$anymatches=mysql_num_rows($data);

if ($anymatches == 0)

{

echo "Sorry, but we can not find an entry to match your query<br><br>";

}

 

}

?>

----------------------------------------------

 

As you can see the html form part is not posted but I have used the same HTML form before I made changes to allow the user not to give results on certain search terms and it is OK

 

Thanks

Robert

[Zwiter]

I cannot figure out the way you do the query.

You should construct the query on a string variable.

Then execute the query.

And finaly use the results :

<?php
$sql = "SELECT * FROM test";
$sql .= "WHERE id=10";
$query = mysql_query($sql) or die ('Error SQL!<br/>'.$sql.'<br/>'.mysql_error());
if(mysql_num_row($query) != 0) echo 'ok';
else echo 'not ok';
?>

[paul2463]

you could change this line

$data.= "\")";

 

to

$data.= "\") or die ("error in query " . mysql_error))";

 

and see what errors come up, i think the main problem will be in the following code

 

$data = "mysql_query(\"SELECT * FROM music WHERE";
if ($composition != "") {
$data.= " composition LIKE '$composition'";
}
if ($origin != "") {
$data.= " AND origin LIKE '$origin'";

 

if you have a composition and an origin your query string will be

SELECT * FROM music WHERE composition LIKE blah AND origin LIKE blah "

 

whereas if you dont have a composition but have an origin your query string will be

 

SELECT * FROM music WHERE AND origin LIKE blah "

 

and will fail big style

 

HTH

[coutts]

Hello:

 

Thank you for your reply. I tried adding the error checking, the line changes you proposed resulted in a blank page, it sounds like a good idea but there must be something not quite right.

 

I understand what you mean about having an AND before the rest of the line but The code above makes it mandatory to choose a composition so that wont happen.

 

Here is what the echo( $data ) statement puts on the screen when you fill in all search terms

 

 

mysql_query("SELECT * FROM music WHERE composition LIKE 'ORIGINAL' AND origin LIKE 'YES' AND bgenre LIKE 'Ancient' AND voice LIKE 'MALE' AND tempo LIKE 'Slow' AND decade LIKE 'Current' AND (vibe1 LIKE 'Acoustic' OR vibe2 LIKE 'Acoustic' OR vibe3 LIKE 'Acoustic')")

 

 

if you only choose a couple it is like this

 

mysql_query("SELECT * FROM music WHERE composition LIKE 'ORIGINAL' AND origin LIKE 'YES' AND bgenre LIKE 'Ancient'")

 

 

I have tried SELECT * FROM music WHERE composition LIKE 'ORIGINAL' AND origin LIKE 'YES' AND bgenre LIKE 'Ancient' directly in phpMyAdmin and it works

 

Thats why I cant understand why I am getting nowhere

 

 

 

 

[mbeals]

the mysql_query() shouldn't be part of the string.

 

change

$data = "mysql_query(\"SELECT * FROM music WHERE";

 

to

$data = "SELECT * FROM music WHERE";

 

then after the last if:

 

$query_result = mysql_query($data) or die(mysql_error());

 

and change

while($result = mysql_fetch_array( $data )) 

 

to

 

while($result = mysql_fetch_array( $query_result )) 

 

[coutts]

Thank you so much that did the trick - and I learned something.

 

Robert