MySQL SELECT question...please look!

[brianb]

hello people,

I have created a search function for my site which seraches through the records in my database.

Now, the user has 13 options to search by.

The way I've set it up is that the user select a certain category to serach, then my PHP search script looks at the selected category and runs the correct SELECT for the category.

Now, I want to add mulitple SELECTs to certian categories, searching more then one table, BUT I want to do it cleanly.

Question:

Make a SELECT query/echo equal a $value, then call that value down further on the page?

OR should can put each query into a function then call it down on the page somewhere?

Any help is appreciated.

[4ebura6ka]

Hi,
Why don't You want to have 1 query... Then You have to pass search results into the function and format a query. For example, you have a table users:

ID | USERNAME | NAME | SURNAME |... ADDRESS |

A little bit of code:

function search ($username, $name, ...)
{
   $query = "SELECT USERNAME, NAME, SURNAME, ADDRESS FROM users";

   if (!empty($username))
      $query = $query." where $username like '".$username."%'";

   And etc...

}

[4ebura6ka]

Sorry, the $query after if construction have to be :

$query = $query." WHERE USERNAME LIKE '".$username."%'";

[stoppel]

don't you mean you want to join tables and search differnt fields from different tables??

select tbl1.field, tbl2.field
from tbl1.table1 tbl1 join tbl2.table2 tbl2
on tbl1.id = tbl2.table1_fk
where
tbl1.field = "condition"

of course it's possible to make left or right joins

or maybe i didn't understand the question correctly 😃 :rolleyes:

[brianb]

Hahhaha....

Ok, maybe I didn't explain myself correctly...

Here what I want to do...

I want to have say 5 chunks of code...all independent from each other.

Each chunk contains a SELECT, and some echos to print out the HTML and the result of the SELECT.

Now, what I'm looking to do is to take each chunk of code (select and HTML echos) and throw it into 1 variable. Either a $php variable (if possible) or a function (if possible).

Make sense so far?

These chunks of code will be at the top of the page, before the HTML starts...

After this, in the actaul design of the HTML page, I want to mix and match my variables (which equal my code chunks) by whatever was called...

For instance...

if ($t == 1)
{
DO code chunk1
DO code chunk3
}

if ($t == 2)
{
DO code chunk2
DO code chunk1
}

etc...

Make sense? So the question is....how to I make some like the following code equal 1 variable:

$sql = "SELECT filmID, title, director, starring, YEAR(date) AS releaseyear FROM Film WHERE title LIKE '%$searchString%' ORDER by title ";
$res = mysql_query ( $sql );
$num_rows = mysql_num_rows($res);

if( mysql_num_rows($res) == 0 )

{
echo "<div align='center'><span class='main'><font color='#000000'>Your search for </font><u>$searched</u> <font color='#000000'>returned NO results in our MOVIE TITLE search.<br>";
no_results();
}

else { 

echo "Your search for <b>'$searched'</b> returned <font color='#000000'><span class='main3'>$num_rows</span></font> results in our <i>MOVIE VAULT</i>:<br><br>"; 
echo "<table width='95%' border='1' bordercolor='#000000' cellspacing='0' cellpadding='4' bgcolor='#F0F0F0' class='table_thin'><tr><td><span class='main3'><font color='#333333'>Movie Vault Search Results:</font></span></td></tr></table>";
echo "<table width='99%' border='0' cellspacing='0' cellpadding='6'>";

//Table colors
$color1 = "";
$color2 = "bgcolor='#F0F0F0'";
$row_count = 1;

while ( $arr = mysql_fetch_array ( $res ) ) 
  {

   $row_color = ($row_count % 2) ? $color1 : $color2;
   $title = fix_title($arr[title]);

echo "<tr><td $row_color><a href='/movies/film.php?filmID=$arr[filmID]' class='main'><u>$title";
echo " ($arr[releaseyear])</u></a>";

echo "<table width='98%' border='0' cellspacing='0' cellpadding='2' align='center'><tr>";

if ($arr[director] == '') 
{
$director = "Unknown at this time.";
} else {
$director = $arr[director];
}
echo "<td valign='top' width='0%' align='right'><span class='small2'><font color='#666666'><u>Director:</u></font></span></td>
     <td width='100%'><span class='small'>$director</span></td></tr><tr>";


if ($arr[starring] == '') 
{
$starring = "Unknown at this time.";
} else {
$starring = $arr[starring];
}
echo "<td valign='top' width='0%' align='right'><span class='small2'><font color='#666666'><u>Starring:</u></font></span></td>
     <td width='100%'><span class='small'>$starring</span></td></tr>";

echo "</table>";


  //Add 1 to the row count
  	$row_count++;
  }//end else

 echo "</table><br>";

Can I slam all of that code into a function? Can I put all of the above into a variable? Should I make seperate files for each code chunk that looks like the above, then call the file when needed?

Any help is greatly appreciated.

Thanks.