Whats the best structure for a Sql search?

[garyneedham7202]

Hi

 

I have the following layout which works fine, but I would like to know if its the best structure and is there a easier more structured way to achieve the same thing.

 

<?php
include("includes/db.config.php");
?>


<?php
$sql = "SELECT *
FROM log
WHERE PLC LIKE '%" . mysql_escape_string($_GET['PLC']) . "%'
ORDER BY DateOpened DESC ";
if (empty($_GET['PLC']))
$sql = "SELECT * FROM PLC WHERE 1 = 2 ";
$res = mysql_query($sql);
?>



<form>
  <span class="ContentStyle">Please enter PLC name </span><span class="style4"> </span>  
  <input type="text" name="PLC" size="20" maxlength="20" value="<?=$_GET['PLC']?>" >
  <input name="submit" type="submit" value="Search">	
  <span></span>
</form>




<table width="692">
 <tr>
 <?php
while ($r = mysql_fetch_assoc($res)) {
?>
<td width="133"><a href="ocado_edit_my_log.php?id=<?=$r['id']?>">Edit</a></td>
<td width="543"><a href="singlelog.php?id=<?=$r['id']?>"><?=$r['PLC']?></a></td>
 </tr>
 <tr>
<td>Date raised</td>
<td><?=$r['DateOpened']?></td>
 </tr>
 <tr>
<td>raised by</td>
<td><?=$r['Raised_by']?></td>
 </tr>
 <tr>
<td>Block</td>
<td><?=$r['Block']?></td>
 </tr>
 <tr>
<td>Description</td>
<td><?=$r['Description']?></td>
 </tr>
  <?php
}
?>
</table>

Edited February 24, 2013 by fenway
added code tags

[Barand]

You were told about using "WHERE 1 = 2" in a previous post.

 

Also, you would always get NO results if $_GET['DateOpened'] is empty based on that logic because you have the where clause as

 WHERE 1 = 2

What is the purpose of that? If you don't want to run the query - then don't run it. Don't create a query that you know will never return results.

 

Sorry but I see no point in wasting my time when you clearly don't listen

[garyneedham7202]

Sorry but I think you are confusing don't listen to not understanding i really don't know why you bother replying to people if you cannot be polite and helpful who do you think you are!! I think you need to climb down from that silver tower you have put yourself on.

[Jessica]

You are confused about the fact that 1 will never be equal to 2?*

 

It's rude of you to keep wasting people's time when you should be going back to elementary math class apparently. Barand and Psycho are both very helpful and smart, and by not only ignoring their advice but rebuking them for it you only make yourself look like a damn fool.

 

*except for extremely large values of 1.

[Psycho]

If you dont understand something - then ask.

 

<?php

include("includes/db.config.php");

$plc = isset($_GET['PLC']) ? trim($_GET['PLC']) : '';
$tableHTML = '';

if(!empty($plc))
{
   $plcSQL = mysql_escape_string($_GET['PLC']);
   $query = "SELECT id, PLC, DateOpened, Raised_by, Block, Description
		  FROM log
		  WHERE PLC LIKE '%{$plcSQL}%'
		  ORDER BY DateOpened DESC ";
   $result = mysql_query($sql);
   if(!$result)
   {
    $tableHTML .= "<tr><td>There was an error running the query</td></tr>\n";
    //Uncomment next lines for debugging only
    //$tableHTML .= "<tr><td>Query: {$query}</td></tr>";
    //$tableHTML .= "<tr><td>Error: " . mysql_error() . "</td></tr>";
   }
   elseif(!mysql_num_rows($result))
   {
    $tableHTML .= "<tr><td>There were no matches for the search</td></tr>";
   }
   else
   {
    while ($row = mysql_fetch_assoc($result))
    {
	    $tableHTML .= "<tr>\n";
	    $tableHTML .= "  <td width=\"133\"><a href=\"ocado_edit_my_log.php?id={$row['id']}\">Edit</a></td>\n";
	    $tableHTML .= "  <td width=\"543\"><a href=\"singlelog.php?id={$row['id']}\">{$row['PLC']}</a></td>\n";
	    $tableHTML .= "</tr>\n";
	    $tableHTML .= "<tr>\n";
	    $tableHTML .= "  <td>Date raised</td>\n";
	    $tableHTML .= "  <td>{$row['DateOpened']}</td>\n";
	    $tableHTML .= "</tr>\n";
	    $tableHTML .= "<tr>\n";
	    $tableHTML .= "  <td>raised by</td>\n";
	    $tableHTML .= "  <td>{$row['Raised_by']}</td>\n";
	    $tableHTML .= "</tr>\n";
	    $tableHTML .= "<tr>\n";
	    $tableHTML .= "  <td>Block</td>\n";
	    $tableHTML .= "  <td>{$row['Block']}</td>\n";
	    $tableHTML .= "</tr>\n";
	    $tableHTML .= "<tr>\n";
	    $tableHTML .= "  <td>Description</td>\n";
	    $tableHTML .= "  <td>{$row['Description']}</td>\n";
	    $tableHTML .= "</tr>\n";
    }
   }
}
?>

<form>
  <span class="ContentStyle">Please enter PLC name </span><span class="style4"> </span>  
  <input type="text" name="PLC" size="20" maxlength="20" value="<?=$plc?>" >
  <input name="submit" type="submit" value="Search">    
  <span></span>
</form>

<table width="692">
<?php echo $tableHTML; ?>
</table>

[garyneedham7202]

Hi Psycho Thanks very much for the example above its just what I need as a reference. I have run the Query and I'm getting this back Do you have any idea what is going wrong. I've run the same query on the old one and I do get a result back

 

Cheers

 

There was an error running the query Query: SELECT id, PLC, DateOpened, Raised_by, Block, Description FROM log WHERE PLC LIKE '%AI02A%' ORDER BY DateOpened DESC Error: Query was empty

[Jessica]

Look at the variables. Psycho made a small mistake which is fairly obvious if you read the lines

$query = "SELECT id, PLC, DateOpened, Raised_by, Block, Description
                          FROM log
                          WHERE PLC LIKE '%{$plcSQL}%'
                          ORDER BY DateOpened DESC ";
        $result = mysql_query($sql);

[DavidAM]

Jessica! How can you say Psycho made a mistake?! Obviously, the bugs in the post editor are getting worse, now they are changing variable names in the posted code.

[garyneedham7202]

Hi Psycho I have managed to find the small mistake Jessica hinted at and all is fine now I apologise to those people who think I'm wasting there time. Again thank you psycho for you patience and time