查询返回没有找到的行

Question

I developed the form in PHP to search a MySQL database, however no matter how I try it still does not retrieve any results at all. And I cannot find any Syntax errors or other similar issues in the code below,

    <?php
    $txtkv=$_POST['txtkv'];
    $cbgen=$_POST['txtgenerator'];
    $cbinsulation=$_POST['txtinsulation'];
    $cbclass=$_POST['txtclass'];
    $cbairinlet=$_POST['txtairInlet'];
    $cbip=$_POST['txtIp'];

    // set database server access variables: 
    $host = "localhost"; 
    $user = "root"; 
    $pass = ""; 
    $db = "nordhavn";

    $connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); 
    mysql_select_db($db) or die ("Unable to select database!"); 

    $query="SELECT Scania.GensetType, Scania.EngineType, Scania.Engine60Hz, Scania.Alternator,NordhavnGenset.MaxKw,  NordhavnGenset.MaxKva, Scania.PriceEur
    FROM Scania 
    LEFT JOIN NordhavnGenset ON Scania.Alternator=NordhavnGenset.Alternator 
    LEFT JOIN Generator ON Generator.Alternator=Scania.Alternator
    LEFT JOIN Insulation ON Insulation.Insulation=NordhavnGenset.Insulation
    LEFT JOIN Klasse ON Klasse.Klasse=NordhavnGenset.Class
    LEFT JOIN AirInletFilter ON AirInletFilter.AirInletFilter=NordhavnGenset.AirInletFilter
    LEFT JOIN IP ON IP.IP=NordhavnGenset.Ip
    WHERE (NordhavnGenset.MaxKva='".$txtkv."')   and (Generator.Alternator='".$cbgen."')  and (Insulation.Insulation='".$cbinsulation."')  and (Klasse.Klasse='".$cbclass."')  and (AirInletFilter.AirInletFilter='".$cbairinlet."')  and (IP.IP='".$cbip."')";        
    $result = mysql_query($query) or die ("Error in query: $query. ".mysql_error()); 
    $i = 0;
    if (mysql_num_rows($result) > 0) { 
          echo "<table cellpadding=10 border=1>
                <tr>
                    <td>    Gense Type (Scania.GensetType)  </td>
                <td>    Engine type (Scania.Enginetype) </td>
                <td>    Engine 60Hz  (Scania.Engine60hz)</td>
                <td>    Alternator (Scania.Alternator and Genrator.Alternator)  </td>   <td>    Max Kw  (NordhavnGenset.MaxKw)  </td>
                    <td>    Max Kva  (NordhavnGenset.MaxKva)</td>
                    <td>    Price Euro (Scania.PriceEur)    </td>
                </tr>";    
          while(($row = mysql_fetch_row($result)) !== false) { 
                $i++;
                echo "<tr class=\"d".($i & 1)."\">"; 
                echo "<td>"."<center>".$row[1]."</center>"."</td>";
                echo "<td>"."<center>".$row[2]."</center>"."</td>";
                echo "<td>"."<center>".$row[3]."</center>"."</td>";
                echo "<td>"."<center>".$row[4]."</center>"."</td>"; 
                echo "<td>"."<center>".$row[5]."</center>"."</td>";
                echo "<td>"."<center>".$row[6]."</center>"."</td>";
                echo "<td>"."<center>".$row[7]."</center>"."</td>";
                echo "<td>"."<center>".$row[8]."</center>"."</td>"; 
                echo "<td>"."<center>".$row[9]."</center>"."</td>";
                echo "<td>"."<center>".$row[10]."</center>"."</td>";
                echo "<td>"."<center>".$row[11]."</center>"."</td>";
                echo "</tr>"; 
            } 
                echo "</table>"; 
        }else { 
            echo "No rows found!"; 
        }     
    mysql_free_result($result); 
    mysql_close($connection); 
    ?>

Answer 1

What are you expecting as a result, you seem to have many conditions in there. Search forms should provide many criterias to the user but only use them if the user provided something.

For example, instead of using all criterias at once in one big query, do this:

$criterias = array("1=1");
if(isset($_POST['txtkv'])){ $criterias[] = 'NordhavnGenset.MaxKva = "'.mysql_real_escape_string($_POST['txtkv']).'"'; }
if(isset($_POST['cbgen'])){ $criterias[] = 'Generator.Alternator = "'.mysql_real_escape_string($_POST['cbgen']).'"'; }
if(isset($_POST['cbinsulation'])){ $criterias[] = 'Insulation.Insulation = "'.mysql_real_escape_string($cbinsulation).'"'; }
if(isset($_POST['cbclass'])){ $criterias[] = 'Klasse.Klasse = "'.mysql_real_escape_string($cbclass).'"'; }
if(isset($_POST['cbairinlet'])){ $criterias[] = 'AirInletFilter.AirInletFilter = "'.mysql_real_escape_string($cbairinlet).'"'; }
if(isset($_POST['cbip'])){ $criterias[] = 'IP.IP = "'.mysql_real_escape_string($cbip).'"'; }

$query = "
SELECT 
    Scania.GensetType, Scania.EngineType, Scania.Engine60Hz, 
    Scania.Alternator,NordhavnGenset.MaxKw, NordhavnGenset.MaxKva, 
    Scania.PriceEur

FROM 
    Scania 
    LEFT JOIN NordhavnGenset ON Scania.Alternator = NordhavnGenset.Alternator 
    LEFT JOIN Generator ON Generator.Alternator = Scania.Alternator
    LEFT JOIN Insulation ON Insulation.Insulation = NordhavnGenset.Insulation
    LEFT JOIN Klasse ON Klasse.Klasse = NordhavnGenset.Class
    LEFT JOIN AirInletFilter ON AirInletFilter.AirInletFilter = NordhavnGenset.AirInletFilter
    LEFT JOIN IP ON IP.IP = NordhavnGenset.Ip

WHERE 
    ".implode(' AND ', $criterias);

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

This will create a full query with only the criterias that you need. Notice that at the top, we CREATE the array of criterias and then fill it will all criterias as we go. We also take time to escape them to secure our input which i didn't see in the original request.

Then, using IMPLODE, we collate all items together in between ANDs to create a list of criterias. The "1=1" is used to prevent an empty WHERE clause in case no criterias are provided but in all cases 1=1 is always true, so it will not filter anything out.

I hope this helps and i hope it really was your problem...

Good luck