Jump to content


Photo

Whats the best structure for a Sql search?


  • Please log in to reply
8 replies to this topic

#1 garyneedham7202

garyneedham7202

    Member

  • Members
  • PipPip
  • 10 posts

Posted 23 February 2013 - 01:13 AM

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">&nbsp;</span>&nbsp;&nbsp;
   <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, 24 February 2013 - 03:45 PM.
added code tags


#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,775 posts
  • LocationCheshire, UK

Posted 23 February 2013 - 06:00 AM

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 garyneedham7202

garyneedham7202

    Member

  • Members
  • PipPip
  • 10 posts

Posted 23 February 2013 - 02:35 PM

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.

#4 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 23 February 2013 - 02:45 PM

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.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#5 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,964 posts
  • LocationCanada

Posted 23 February 2013 - 04:36 PM

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">&nbsp;</span>&nbsp;&nbsp;
   <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>

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#6 garyneedham7202

garyneedham7202

    Member

  • Members
  • PipPip
  • 10 posts

Posted 24 February 2013 - 04:43 PM

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

#7 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 24 February 2013 - 04:50 PM

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

My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#8 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,976 posts
  • LocationSpring, TX USA

Posted 24 February 2013 - 09:30 PM

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.
-- I haven't lost my mind, it's backed up on tape ... somewhere!

#9 garyneedham7202

garyneedham7202

    Member

  • Members
  • PipPip
  • 10 posts

Posted 25 February 2013 - 01:50 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com