dynamically constructed query not displaying results

[rbragg]

I am constructing a query depending on the values chosen from three dropdown lists. The dropdowns are on one page and are called, searchSystem, searchCat, searchName. A button is selected and a new page loads to display results. However, I am not seeing results even though I KNOW there are some.

 

If I echo the query I get the Resource ID #5 error. I've checked my table and column names for errors. I can't find a thing.

 

RESULTS PAGE:

<?php
include 'db_connect.php';

# constructing query
$querySearch = "SELECT *  
FROM configlog_action, configlog_user, configlog_system, configlog_cat 
WHERE configlog_action.userID = configlog_user.userID 
AND configlog_action.systemID = configlog_system.systemID 
AND configlog_action.catID = configlog_cat.catID
";

##### add restrictions except all #####
$searchSystem = mysql_real_escape_string($_POST['searchSystem']);
$searchCat = mysql_real_escape_string($_POST['searchCat']);
$searchName = mysql_real_escape_string($_POST['searchName']);

# SYSTEM
if ($searchSystem != "all"){
$querySearch.= " AND configlog_system.system = '$searchSystem' ";
}

# CATEGORY
if ($searchCat != "all"){
$querySearch.= " AND configlog_cat.category = '$searchCat' ";
}

# NAME
if ($seachName != "all"){
$querySearch.= " AND configlog_user.name = '$searchName' ";
}

$querySearch.= " ORDER by service_date DESC ";
$querySearch.= " LIMIT 50 ";

$searchResults = mysql_query($querySearch);

# if the query cannot be made due to an error
if(!$searchResults) 
{
  die( "Database error: " . mysql_error() );
} 

# if there are no matching records
else if(mysql_num_rows($searchResults) == 0) 
{
  echo "<table width='100%' border='0' cellspacin='0' cellpadding='1'>
<tr class='style7' bgcolor='#2966A3'>
<td width='10%'><input name='view' type='submit' class='style1' value='View'></td>
<td width='20%'>Service Date</td><td width='15%'>User</td>
<td width='20%'>Server</td>
<td width='35%'>Category</td></tr>
<tr><td colspan='5'><span class='style2'>Displaying the last 50 entries:</span></td></tr>
<tr><td colspan='5'><p class='style5'>There are no records for this search request.</p></td></tr>";
} 
# if the two above statements have returned false
else 
{
  echo "<form name='form_searchDisplay' method='POST' action='cl_admin_detail.php'>";
  echo "<table width='100%' border='0' cellspacing='0' cellpadding='1'>
<tr class='style7' bgcolor='#2966A3'>
<td width='10%'><input name='view' type='submit' class='style1' value='View'></td>
<td width='20%'>Service Date</td>
<td width='15%'>User</td>
<td width='20%'>Server</td>
<td width='35%'>Category</td></tr>
<tr><td colspan='5'><span class='style2'>Displaying the last 50 entries:</span></td></tr>"; 

  while($row = mysql_fetch_array($searchResults))  
  {
    # print the contents of each row
    echo "<tr class='style1'><td>";
    ?>
    <input type='radio' name='selected' value='<?php echo $row['actionID'];?>'>
    <?php
    echo "</td><td>";
    echo $row['service_date'];
    echo "</td><td>";
    echo $row['name'];
    echo "</td><td>";   
    echo $row['system'];
    echo "</td><td>";
    echo $row['category'];
    echo "</td></tr>";  
  }  
  echo "</table><br>";
  echo "</form>";
}
mysql_close;
?>

[monk.e.boy]

Do this:

 

$searchResults = mysql_query($querySearch);
echo '<br><br>'. $querySearch .'<br><br>';

 

And show us what is out put.

 

monk.e.boy

[rbragg]

I search using searchName only and get this:

 

SELECT * FROM configlog_action, configlog_user, configlog_system, configlog_cat WHERE configlog_action.userID = configlog_user.userID AND configlog_action.systemID = configlog_system.systemID AND configlog_action.catID = configlog_cat.catID AND configlog_user.name = '\\\\\\\'Mike' ORDER by service_date DESC LIMIT 50

 

??? Looks like a lot of unnecessary characters with my value.

[monk.e.boy]

$searchSystem

$searchCat

$searchName

 

try echoing the above (like we did with the sql)

 

I expect there is some dodgy data coming through.

 

monk.e.boy

 

 

[rbragg]

Btw, thank you for your replies.

 

I get:

SELECT * FROM configlog_action, configlog_user, configlog_system, configlog_cat WHERE configlog_action.userID = configlog_user.userID AND configlog_action.systemID = configlog_system.systemID AND configlog_action.catID = configlog_cat.catID AND configlog_user.name = '\\\\\\\'Mike' ORDER by service_date DESC LIMIT 50

 

all

 

all

 

\\\\\\\'Mike

 

 

[monk.e.boy]

so the $_POST variable contains \\\\\\\'Mike, you need to look at the page that has the form on it.

 

You also need to check if you have "magic quotes" on or off.

 

google all this, it is an old problem, and there is plenty of example code out there to help you out :-)

 

monk.e.boy

[rbragg]

This was exactly the problem. I removed some / and { from my form and now it works perfectly. Thanks!