Passing variables & dummy variables

concat a number of selects before sending query

[omfLondon]

Hi! thanks for all the previous help - I've made some progress & have dropdowns and a checkbox; however I have 2 probs which I can't figure out: grateful for any further help. THANKS!

1. Not sure how to code so that user can query from just 1 or all of the dropdowns (at the moment unless they select the correct answers from each dropdown - no results!) = defeats the whole purpose of searching. By selecting from menu options user selects from 2 diff tables.

[the page allows user to search catalogue by selecting year of release, artist, music type - results from 2 tables need to be selected & sent to mysql.

I just don't know how to code it so that user must make an initial selection to get any results, and whichever menu options they choose "add them together" if you like to make a complete query.... sorry my coding just isnt deep enough!

A friend suggested using a dummy variable but as I'm a bit out of my depth; I need to get some help!

The coding I've done so far;

THE FORM:

<?php
$link = mysql_connect("localhost","","");
mysql_select_db("createdb1", $link) or die("Unable to select database");
#
$selectSql1 = "SELECT * FROM music WHERE releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."'
AND title = '".mysql_real_escape_string($_POST['title'])."'";

$selectSql2 = "SELECT * FROM type WHERE type='" . $_POST['type'] . "'";

#
echo $selectSql1;
$result1 = mysql_query($selectSql1, $link) or die(mysql_error());
while($row1 = mysql_fetch_assoc($result1)) {

echo $selectSql2;
$result2 = mysql_query($selectSql2, $link) or die(mysql_error());

while ($row2 = mysql_fetch_array($result2))
{

echo '<pre>';
print_r($row);
echo '</pre>';
}

mysql_close($link);
?>

HTML:

<form action="TCTest8.php" method="post">
<p>&nbsp;</p>
<p> Title:
<select name="title">
<option>-</option>
<option>Devil Drive</option>
<option>Amarillo</option>
<option>The The</option>
</select>
<br/>
</p>
<p>Year of Release
<select name="releaseyear">
<option>Choose Year</option>
<option>1999</option>
<option>2000</option>
<option>2005</option>
<option>2006</option>
</select>
</p>
<p>Actor/Artist Name:
<select name="name" size="3" multiple>
<option>Choose</option>
<option>A</option>
<option>B</option>
<option>E</option>
</select>
</p>
<p> type </p>
<input type="checkbox" value="R&B" name="type[]" />
<input type="checkbox" value="blues" name="type[]" />
<input type="checkbox" value="pop" name="type[]" />
</select>
<p><br/>
<input type="submit" />
</p>

</form>

[cameraman]

I don't know if this will work for you exactly, but hopefully it will get you going in the right direction:
$selectSql1 = "SELECT * FROM music WHERE ";
if($_POST['releaseyear']!= 'Choose Year') {
$selectSql1 .= "releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."' ";
if($_POST['title']!= '')
$selectSql1 .= ' AND ';
}
if($_POST['title']!= '')
$selectSql1 .= "title = '".mysql_real_escape_string($_POST['title'])."'";

That should allow the query to look for either release year or title or both. If it gives you fits look at the combination of quotes carefully - I was pasting text from your post and didn't squint real hard at the screen to make sure I got them matched up.

I'm not understanding how you're using the type; it doesn't look like anything is dependent on it - can you make that whole second query optional so it doesn't run if they don't select a type?
Maybe it's in a segment that you didn't include here, but you're missing a closing brace.

[BananaFish]

//allow only numbers
$releaseyear=preg_replace("/[^0-9]/","",$_POST['$releaseyear']);
//for text in case magic quotes are on and to trim
$name=stripslashes(trim($_POST['name']));
$title=stripslashes(trim($_POST['title']));
$sql="SELECT * FROM music";
$where=array();
if($releaseyear)array_push($where,"releaseyear=$releaseyear");
if($name)array_push($where,"name='".mysql_real_escape_string($name)."'");
if($title)array_push($where,"name='".mysql_real_escape_string($title)."'");
//now for the type
$types=array();
$type=$_POST['type'];
$cnt_type=count($type);
if($cnt_type){
 for($xx=0;$xx<$cnt_type;$xx++){
 array_push($types,"type='".mysql_real_escape_string($types[$xx])."'");
 }
}
//check for types
if(count($types)){
 $type_cond="(".implode(" OR ",$types).")";
 array_push($where,$type_cond);
}
//check if there are conditions
if(count($where)){
 $condition=implode(" AND ",$where);
 $sql.=" WHERE $condition";
}

If type is another table, and you're using a typeID, you'd have to do a join and use IDs in your form for the value instead of the names.

[omfLondon]

Hi, thanks for that - it has been really enlightening although a bit terrifying for this noob!

Looking at the database, I wonder if there might be a more logical way to query.

There are 3 tables as explained - but all are related by primary keys or related via another table. My question is: Should I make use of the SELECT query based on primary keys etc (have to say I'm not great on this either but...)

for example; would something like this suffice:

SELECT artist.artistid, type.typeid FROM artist, type WHERE artistid.artist = artistid.music, type.typeid = musictype.typeid, type.typeid=$_POST['type']

Something a little like the above (sorry its a bit scrappy).

I'm keen to learn this but I'm equally keen to get past this hurdle!

Many thanks!