Something wrong with my WHERE clause?

Curtis782

I was curious if there was something wrong with my WHERE clause (order, etc.)?

The following SELECT is on my search results page. On the page before, I have 3 inputs: 1 for Event Name/Keyword, a 2nd for Industry and a 3rd for Type of Show. I only selected "Transportation" from the Industry pull-down list which should display 20+ events but it results with 0 events.

SELECT * FROM table1 WHERE Company_Event LIKE '%%' AND (Classification1 = 'Transportation' OR Classification2 = 'Transportation' OR Classification3 = 'Transportation' OR Classification4 = 'Transportation') AND Classification5 = '' ORDER BY id

dalecosp

That's a boatload of conditions, for starters. And this is the "Coding" forum on PHP Builder ... you might do better in the Database forum here, or a site dedicated to SQL.

But I'll give it a shot:

Like '&#37;%'

--- sure you don't want something in between those?

And

Condition 5=

... is equal to, um, what? 🙂

dalecosp

Oh, and since this is the coding forum, it might be nice to see a little PHP. Particularly if you have some sort of check in the event that no result is returned (hint, hint) 😉

bradgrafelman

dalecosp;10968359 wrote:
Like '%%'
--- sure you don't want something in between those?

My guess is that it's a cheap hack at trying to take care of an empty search parameter (where it would be better to simply remove that condition from the WHERE clause altogether).

dalecosp;10968359 wrote:
And
Condition 5=
... is equal to, um, what? 🙂

The empty string, which is specified after the equal sign in the OP's query.

@: Can you show us a couple of the rows in the database that you believe should be matched by this query?

EDIT: Also, thread moved to Database forum.

dalecosp

bradgrafelman;10968364 wrote:
My guess is that it's a cheap hack at trying to take care of an empty search parameter (where it would be better to simply remove that condition from the WHERE clause altogether).

I was hoping someone (looks around) might catch my drift on that.

The empty string, which is specified after the equal sign in the OP's query.

I guess that is what that is. I keep forgetting my glasses when I wear this little netbook. Although, again, if it's empty ... well, I guess there could be a reason for that; but I'd rather search for something than nothing, generally.

EDIT:Oh, and did I stretch this thread out so far that way ----->?

Edit button disappears now after a while, eh?

Curtis782

The LIKE '%%' is there because no event name/keyword was typed in. Same thing with Classification5 = '' (no event type was selected so it is blank).

$whereclause = " WHERE Company_Event LIKE '%".$_REQUEST["Company_Event"]."%'
	AND Classification5 = '".$_REQUEST["ShowType"]."'
	AND (Classification1 = '".$_REQUEST["Industry"]."'
	OR Classification2 = '".$_REQUEST["Industry"]."'
	OR Classification3 = '".$_REQUEST["Industry"]."'
	OR Classification4 = '".$_REQUEST["Industry"]."')
	";

This was a 'test' query as I imagine many will not want to fill out all 3 inputs: the event name/keyword (Company_Event) or the event type (Classification5). Do I need to write a conditional statement in my PHP to not display some of the elements above in my WHERE clause if there is no data? I thought it would still work.

bradgrafelman

Curtis782 wrote:
Do I need to write a conditional statement in my PHP to not display some of the elements above in my WHERE clause if there is no data?

That depends on the logic you want. As it stands now, the 'Classification5' field must be empty... hence why I requested some sample data from the DB that you expect your query to match.

Speaking of DB structure, it definitely seems like your DB schema isn't normalized. If you designed this DB (or have the power to re-design it), perhaps you should find some decent articles on "Database Normalization" and/or the "Database Normal Forms" before continuing. Normalizing your DB schema not only helps optimize how you store/relate data, but it often makes querying the DB for data easier as well.

dalecosp

Curtis782;10968374 wrote:
The LIKE '%%' is there because no event name/keyword was typed in. Same thing with Classification5 = '' (no event type was selected so it is blank).
$whereclause = " WHERE Company_Event LIKE '%".$_REQUEST["Company_Event"]."%'
    AND Classification5 = '".$_REQUEST["ShowType"]."'
    AND (Classification1 = '".$_REQUEST["Industry"]."'
    OR Classification2 = '".$_REQUEST["Industry"]."'
    OR Classification3 = '".$_REQUEST["Industry"]."'
    OR Classification4 = '".$_REQUEST["Industry"]."')
   ";
This was a 'test' query as I imagine many will not want to fill out all 3 inputs: the event name/keyword (Company_Event) or the event type (Classification5). Do I need to write a conditional statement in my PHP to not display some of the elements above in my WHERE clause if there is no data? I thought it would still work.

Well, thinking it will work isn't the same as determining whether or not is does; I also think it might work, but I'd wrap the thing in a conditional and give output regardless; this is the essence of debugging, which I assume is what you're hoping to accomplish (are we in MySQL?) Is your code construct anything like this?:

$result=mysql_query($whereclause) or die("Error: ".mysql_error());
if (!$result) {
   echo "No result, apparently ... dumping: ".var_dump($result);
} else {
   do_something();
}

bradgrafelman

dalecosp;10968380 wrote:

Is your code construct anything like this?:

$result=mysql_query($whereclause) or die("Error: ".mysql_error());
if (!$result) {
   echo "No result, apparently ... dumping: ".var_dump($result);
} else {
   do_something();
}

The semantics of that code aren't quite right; if $result evaluates to FALSE, then that likely indicates a semantic problem with the query (e.g. a parse error). A well-formed query that doesn't return any results will not cause $result to evaluate to FALSE, however.

Curtis782

My database is not normalized because I have a tight deadline to get some data online by month's end and do not currently know how to properly normalize a database. With that said however, my DB has 1 table which has approximately 100-120 columns which I set the proper data type and character limits for and maybe 5,500-6000 records (I'm assuming a MySQL DB supports this for 1 table?) My hope is I can get the queries to function so I can place this online and once online I can work with a programmer to optimize the database structure (not too concerned about speed optimization, etc. right now).

Here are some sample results (please note updated SQL query below without blank input):

SELECT * FROM bookdata WHERE Company_Event LIKE '%show%'
    AND Classification5 = 'Public'
    AND (Classification1 = 'Transportation'
    OR Classification2 = 'Transportation'
    OR Classification3 = 'Transportation'
    OR Classification4 = 'Transportation')
     ORDER BY id

<table width="100%" border="1">

<tr><td>ID</td><td>Company/Event Name</td><td>Industry #1</td><td>Industry #2</td><td>Industry #3</td><td>Industry #4</td><td>Type of Show</td></tr>


<tr>
<td>1847</td>
<td><a href="profile.php?id=1847">First Hawaiian International Auto Show</a></td>
<td>Transportation</td>

<td></td>
<td></td>
<td></td>
<td>Public</td>
</tr>


<tr>
<td>3666</td>
<td><a href="profile.php?id=3666">Pennsylvania Auto Show</a></td>
<td>Automotive & trucking</td>
<td>Transportation</td>

<td></td>
<td></td>
<td>Public</td>
</tr>


<tr>
<td>4988</td>
<td><a href="profile.php?id=4988">Virginia Motor Trend International Auto Show</a></td>
<td>Transportation</td>
<td></td>
<td></td>
<td></td>

<td>Public</td>
</tr>


<tr>
<td>5001</td>
<td><a href="profile.php?id=5001">Washington DC Auto Show</a></td>
<td>Automotive & trucking</td>
<td>Transportation</td>
<td></td>
<td></td>

<td>Public</td>
</tr>


</table>
<br>

<a class="current" href="#">1</a> 
<span class="paginate">Items per page:</span><select class="paginate" onchange="window.location='/search_results.php?offset=1&ipp='+this[this.selectedIndex].value+'&Company_Event=show&Industry=Transportation&ShowType=Public&search=GO';return false"><option selected value="10">10</option>
<option value="25">25</option>
<option value="50">50</option>

<option value="100">100</option>
</select>


</div>

</td></tr>
</table>

This is what my search_results.php script looks like presently:

<?php
include ("includes/common_vars.php");
require_once ("includes/paginatormod.class.php");
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php echo $SITE_TITLE; ?> | Search Results</title>
<link rel="stylesheet" type="text/css" href="css/master.css" />
</head>

<body>
<?php include ("includes/header.php"); ?>

<?php

$link = mysql_connect("$DB_HOSTNAME1", "$DB_USERNAME1", "$DB_PASSWORD1") or die("Could not connect: " . mysql_error());
mysql_select_db("$DB_NAME1", $link);

//Genrate the pagination
$Pages = new Paginator;
$Pages->showAll = false;  // Set true if you want to enable the "Show all results in one page" button.

$whereclause = " WHERE Company_Event LIKE '%".$_REQUEST["Company_Event"]."%'
    AND Classification5 = '".$_REQUEST["ShowType"]."'
    AND (Classification1 = '".$_REQUEST["Industry"]."'
    OR Classification2 = '".$_REQUEST["Industry"]."'
    OR Classification3 = '".$_REQUEST["Industry"]."'
    OR Classification4 = '".$_REQUEST["Industry"]."')
    ";

$orderby = " ORDER BY id ";

$sql_initial_count = $basic_sql . $whereclause . $orderby;

$Pages->Total = $sql_initial_count;

$Pages->AllItems = $Pages->getTotal(); // Get the total number of records.
$Pages->Break = 5; // The breaking point of pagination.
$Pages->makePages(); // Build the pages.

// Make your db query here. Include $pages->limit as described in step 8. (i.e. SELECT id,fname,lname FROM employees $pages->limit)
//$sql_get_doctors = $basic_sql;

$paging_limit = $Pages->limit;

$sql=$basic_sql . $whereclause . $orderby . $paging_limit;

//echo $sql; // remove this line to not display sql output
$result_sql_get_events = mysql_query($sql, $link) or die(mysql_error());
$numofrows = mysql_num_rows($result_sql_get_events);

?>

<form>

<i>Your search returned <span style="color:#0066cc; font-weight:bold;"><?php echo $Pages->AllItems; ?></span> results. Jump to page #: <?php echo $Pages->showJump(); ?></i>

<p style="font-size:10px; font-family:Arial, Helvetica, sans-serif; color:#000000;"><?php echo $sql_initial_count; ?></p>

<?php

if ($Pages->AllItems == 0) {
echo "<p class=\"normalDirectoryText\">No results found for query entered. <a href=\"$BASE_URL.index.php\">Try a new search</a>.</p>";
}

?>

<table width="100%" border="1">
<tr><td>ID</td><td>Company/Event Name</td><td>Industry #1</td><td>Industry #2</td><td>Industry #3</td><td>Industry #4</td><td>Type of Show</td></tr>

<?php while ($row = mysql_fetch_array($result_sql_get_events)) { ?>

<tr>
<td><?php echo $row["id"]; ?></td>
<td><a href="<?php echo $BASE_URL."profile.php?id=".$row["id"]; ?>"><?php echo $row["Company_Event"]; ?></a></td>
<td><?php echo $row["Classification1"]; ?></td>
<td><?php echo $row["Classification2"]; ?></td>
<td><?php echo $row["Classification3"]; ?></td>
<td><?php echo $row["Classification4"]; ?></td>
<td><?php echo $row["Classification5"]; ?></td>
</tr>

<?php } // end while ?>

</table>
<br>

<?php echo $Pages->showPages(); // Show the pages. ?>

<?php echo $Pages->showItems(); // Shows the total items per page dropdown box. ?>

<?php mysql_free_result($result_sql_get_events); ?>

</div>

</td></tr>
</table>

</form>

<?php include ("includes/footer.php"); ?>
</body>
</html>