Jump to content

Whats the best structure for a Sql search?


garyneedham7202

Recommended Posts

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 by fenway
added code tags
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.