queries not returning any results

[AdRock]

I have tried to make a query which i can concatenate to but it doesn't work.

 

I have a form with a couple of text boxes and a drop down list and the userr can select any of them.

 

The code checks if there was anything entered on the form and if there was, concatenate to the standard query.

 

Here is the code in question

$address = $_POST['address'];
    $seats = $_POST['seats'];
    $departure = $_POST['departure'];

    if($seats =="any") $seats="";

    if (!empty($address)) {
    $result = "SELECT id, userid, seats_available, start_street, start_postcode, start_lat, start_long, 
end_street, end_postcode, end_lat, end_long, TIME_FORMAT(depart_time,'%H:%i') as depart, 
MATCH(start_street, start_postcode, end_street, end_postcode) AGAINST('$address*' IN BOOLEAN MODE)
as score FROM carshare WHERE MATCH(start_street, start_postcode, end_street, end_postcode) 
    AGAINST('$address*' IN BOOLEAN MODE) ";
    }
    else {
    	$result = "SELECT id, userid, seats_available, start_street, start_postcode, start_lat, start_long, 
end_street, end_postcode, end_lat, end_long, TIME_FORMAT(depart_time,'%H:%i') as depart FROM carshare WHERE 1=1 ";
    }

    if (!empty($seats)) {
    	$result .= "AND seats_available = '$seats' ";
    }
    if (!empty($departure)) {
    	$result .= "AND depart = '$departure' ";
    }

    if (!empty($address)) {
    	$result .= "ORDER BY score ASC";
     }
    else {
    	$result .=" ORDER BY id ASC";
    }    
    if(!$result) die(mysql_error()); 
$err = mysql_num_rows($result); 
        if($err == 0) {
    echo "No matches met your criteria.";
    echo "<br />".$result; 
}
else {

here is the whole page

<?php
    $title = "Search for Car Shares";
    include("includes/init.php");

    require_once("header.inc.php");

if (!isset($_POST['send']))
{
    echo "<h2>Advance Search</h2><hr />";
    // Show the form
    include 'includes/search_form4.inc.php';
}
else
{
    echo "<h2>Car Shares</h2><hr />";

    $address = $_POST['address'];
    $seats = $_POST['seats'];
    $departure = $_POST['departure'];

    if($seats =="any") $seats="";

    if (!empty($address)) {
    $result = "SELECT id, userid, seats_available, start_street, start_postcode, start_lat, start_long, 
end_street, end_postcode, end_lat, end_long, TIME_FORMAT(depart_time,'%H:%i') as depart, 
MATCH(start_street, start_postcode, end_street, end_postcode) AGAINST('$address*' IN BOOLEAN MODE)
as score FROM carshare WHERE MATCH(start_street, start_postcode, end_street, end_postcode) 
    AGAINST('$address*' IN BOOLEAN MODE) ";
    }
    else {
    	$result = "SELECT id, userid, seats_available, start_street, start_postcode, start_lat, start_long, 
end_street, end_postcode, end_lat, end_long, TIME_FORMAT(depart_time,'%H:%i') as depart FROM carshare WHERE 1=1 ";
    }

    if (!empty($seats)) {
    	$result .= "AND seats_available = '$seats' ";
    }
    if (!empty($departure)) {
    	$result .= "AND depart = '$departure' ";
    }

    if (!empty($address)) {
    	$result .= "ORDER BY score ASC";
     }
    else {
    	$result .=" ORDER BY id ASC";
    }    
    if(!$result) die(mysql_error()); 
$err = mysql_num_rows($result); 
        if($err == 0) {
    echo "No matches met your criteria.";
    echo "<br />".$result; 
}
else {
    ?>
    <p style="text-align:right"><label>Sort results by:</label>
    <form ation="search.php" method="post"><input style="border:none" type="radio" value="start_street" checked="checked" name="sort" /> Street Name
    	<input style="border:none" type="radio" value="time" name="sort" /> Departure Time
    	<input type="button" name="sortbutton" value="Sort Results" class="sendbutton" /></p>
    </form>
    <table id="MyTable" class="style3">
    <?php

    	if(is_authed_user() || is_authed_admin()) {
    	    echo "<tr><th colspan='2'> </th><th colspan='2'>Start of Route</th><th colspan='2'>End of Route</th><th> </th></tr>
      	<tr><th>Seats</th><th>Depart</th><th>Street</th><th>Postcode</th><th>Street</th><th>Postcode</th><th>Driver</th></tr>\n";
    	}
    	else {
    	    echo "<tr><th colspan='2'> </th><th colspan='2'>Start of Route</th><th colspan='2'>End of Route</th></tr>
     	<tr><th>Seats</th><th>Depart</th><th>Street</th><th>Postcode</th><th>Street</th><th>Postcode</th></tr>\n";
    	}

    	$z = 0;

    	while($row=mysql_fetch_array($result)) {

    $seats_available = htmlentities($row['seats_available']);
    	    $start_street = htmlentities($row['start_street']);
    	    $start_postcode = htmlentities($row['start_postcode']);
    	    $end_street = htmlentities($row['end_street']);
    	    $end_postcode = htmlentities($row['end_postcode']);
    	    $depart = htmlentities($row['depart']);

    	    if(is_authed_user() || is_authed_admin()) {
    	$driver = htmlentities($row['first_name']);
    	$user_id = htmlentities($row['user_id']);
            }

    	    $start_postcode = substr($start_postcode, 0, strlen($start_postcode)-3) . ' ' . substr($start_postcode, strlen($start_postcode)-3);
    	    $end_postcode = substr($end_postcode, 0, strlen($end_postcode)-3) . ' ' . substr($end_postcode, strlen($end_postcode)-3);

    	    //this means if there is a remainder
    	    if($z % 2==0)
     	    {
            	echo "<tr class='yellow'>\n";
    	$z++;
    	    }
    	    //if there isn't a remainder we will do the else
    	    else
    	    { 
            	echo "<tr class='white'>\n";
    	$z++;
    	    }

    	    if(is_authed_user() || is_authed_admin()) {
    	    	echo "<td>".$seats_available."</td><td>".$depart."</td><td>".$start_street."</td><td>".$start_postcode."</td><td>".$end_street."</td><td>".$end_postcode."</td><td><a href='contactdriver.php?id=".$row['user_id']."'>".$driver."</td>\n";
     	    	echo "</tr>\n";
            }
    	    else {
    	echo "<td>".$seats_available."</td><td>".$depart."</td><td>".$start_street."</td><td>".$start_postcode."</td><td>".$end_street."</td><td>".$end_postcode."</td>\n";
    	   	echo "</tr>\n";
    	    }
    	}
    	//now let's close the table and be done with it
    	echo "</table>\n";
    } 
}
include_once("footer.inc.php");
?>

[BlueSkyIS]

can you expand on "it doesn't work"?

[AdRock]

It does this bit

 

if(!$result) die(mysql_error()); 
$err = mysql_num_rows($result); 
        if($err == 0) {
    echo "No matches met your criteria.";
    echo "<br />".$result; 
}

 

I've echoed the query and i can't see any reason for it not to work.

I have even tried it using phpmyadmin and it works in there

[cooldude832]

and not works means??? does it spit out fire when you try and run it?

[AdRock]

If I run the query that is echoed out after where it says "No matches met your criteria" and i run it in phpmyadmin, i get the expected results but not using the script

[BlueSkyIS]

you never execute the SQL you've stored in $result. after building the SQL, you have to execute it:

 

$real_result = mysql_query($result) or die(mysql_error());
$err = mysql_num_rows($real_result);

 

etc.

 

 

 

[AdRock]

I just worked that out just before reading this post....

[BlueSkyIS]

better to figure it out yourself anyway. you'll probably never make that mistake again.