Foreach() & mysql queries, Can someone take a look plz

[Accurax]

I'm having major problems getting this to work ...... I have a feeling now that its down to something pretty simple, probably with the foreach loop and the way i try to construct my query .... but it does seem logical, and i cant see a major problem, Could someone possibly take a look over my code ? .... I'd really appreciate any suggestions on this.

ok, heres the form that the user will create the search query with;

[code]
  <table align ="center" cellpadding="10" id="info_tab">
<form method="POST" action= "results.php">

<tr>
<td>Name :</td>
<td><input name="search_name" type="text" size="20" maxlength="10"/></td>
</tr>

<tr>
<td>Location :</td>
<td>
<select name="search_location">
<option>Inhere are just a load of options for a location in the uk</option>
<option>......</option>
<option>......</option>
<option>....etc etc etc..</option>
</select>
</td>
</tr>
[/code]

Thats the only two option that i currentlyhave in my seach form, as once i get those two to work correctly i reckon i can add more. The basic premise is that the user should be able to select one or the other option, or both at the same time, and get valid results.

so now comes my results.php

The first section of results.php takes the $_POST values and arranges them into the array i want to use later, the $key is set to the value entered into the form, and the $value is set to the name of the corresponding field in my database.

[code]<?php
//Recieve the Variables from the Search form and form them into a sensible array.
$_POST['search_name'] = "name";
$_POST['search_location'] = "location";?>
[/code]

Following this i have a forecah() loop that should cycle through the above array and return a query to the database if the key is !=0 and basically return nothing if the key is = 0.

[code]<?php
//this is our foreach loop.

foreach( $_POST as $k => $v)
if(!empty($_POST[$k])) {

$query.=" && $v='%$k%'";
}
else {
$query.="";
}?>
[/code]

I am aware of the issue with having leading &&'s where the above loop is concerned, I think i saw some way of removing them somwhere so i'll look into that once i have the basics sorted out.

However, for the time  being, in order to circumvent this "leading && issue" i have simpley created a variable called $x and set it to be = to "dog" I have then added a row in my database called testing , and appended a query to the front before i call the results of my foreach loop. every item in the database is defaulted to dog for thie particular field.

I allso have a lot of code around my query that creates the pagination, this pretty much works for the time being, allthough i think ill have some problems with it displaying the correct number of pages, once the query works correctly.

For the sake of completeness ive posted the entire section of code, allthough i think the problem lies mostly with my foreach() loop and the query itself.

[code]<?php
//This next section executes the query and creates the pagination.

$rowsPerPage = 5;
$pageNum = 1;
$x = "dog";

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;

$user_query = "SELECT * FROM members m INNER JOIN pictures p ON p.user_name = m.user_name WHERE testing='$x'  $query".
" LIMIT $offset, $rowsPerPage";
$result = mysql_query($user_query)
or die ("no can do");


while ( $row = mysql_fetch_array($result))
{
echo '<a href="view_profile.php?user_name='.$row['user_name'].'">'; echo $row['user_name']; echo "</a>";
echo "<br>";
echo "<ul id='search_thumbs'><li><a href='viewprofile.php?user_name=".$row['user_name']."'><img src='../".$row['picture1']. "'></a></li></ul>";
}

// how many rows we have in database
$query  = "SELECT COUNT(user_name) AS numrows FROM members";
$result  = mysql_query($query) or die('Error, query failed line 95');
$row    = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
  if ($page == $pageNum)
  {
      $nav .= " $page "; // no need to create a link to current page
  }
  else
  {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
  }
}
// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
  $page  = $pageNum - 1;
  $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";

  $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
  $prev  = '&nbsp;'; // we're on page one, don't print previous link
  $first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
  $page = $pageNum + 1;
  $next = " <a href=\"$self?page=$page\">[Next]</a> ";

  $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
  $next = '&nbsp;'; // we're on the last page, don't print next link
  $last = '&nbsp;'; // nor the last page link
}

// print the links for moving around the pages.
echo $first . $prev . $nav . $next . $last;


?>[/code]

This is really starting to drive me mad... its the biggest most complicated script ive ever tried to put together, and im afraid i need a little help.

Thanks guys

[HuggieBear]

If you have your base query as...

[color=green]SELECT column_name FROM table_name WHERE 1=1[/color]

This is the same as...

[color=green]SELECT column_name FROM table_name[/color]

But it means that you know there's always going to be an AND or && before anything you add, it saves working out if it's the first result in the array or not.

Regards
Huggie

[Accurax]

Isnt that what ive done huggie ...... i know the preceding && is going to be a problem ...... so ive added WHERE testing='$x'

Also, when i remove $query from the query staement it works correctly.... so it has to be either a problem with the foreach loop, or a problem with the way i insert $query into the query

[Accurax]

Anyone got any idea's on this one guys ... all input is really appreciated... it might kick me in the right direction

[Accurax]

Honestly? .... does no-one know what im doing wrong .... ive nearly finished another aspect of the site, and im seriously  considering doing this in a different way.

Could anyone give me an indication of how complex this problem is?..... if its beyond you guys then i'll just go a different route.

Thanks chaps

[Accurax]

I guess i'd better accept that no one can help me with this ..... Thanks to everyone who took the time to take a look though, I really appreciate it.

I think im going to break my search function down in to several smaller search options that require a selection in every field.

:( /me hates giving up

[ted_chou12]

you are doing a search query?
ps. i think it will be easier if you break it down, ive got my age option search working.
Is an age range searching, which also includes gender, username as well.
Ted

[Accurax]

I think i am going to break it down... its just too much of a headache to do it this way

[ted_chou12]

yep, sometimes breaking it down is easier, I suggest you do the sort out the search query first:
[code]
<?php
//age query setup, get query from combo first, then use if for query fetch.
$ages = array('no preference' => '','under 20' => '20','20-29' => '30','30-39' => '40','40-49' => '50','50-59' => '60','60 above' => '70',);
$age_select = "";
foreach ($ages as $range => $year) {if ($range == $age) {$age_select = $year;}}
?>
[/code]
and then I got my variable by $age_select, so i suggest you put it into an array first.
Ted