|
PHP - Simplifying This Sql
I have some SQL fpr a search form that I am working on which searches across a MySQL table. There are quite a few fields I want searchable and I currently have the code as such
$term = strip_tags(substr($_POST['search_term'],0, 100)); $term = mysql_escape_string($term); $sql = "select * from Phase1A_1B_TotalScores_2011 where last_name like '%$term%' or first_name like '%$term%' or employee_id like '%$term%' or title like '%$term%' or territory like '%$term%' or district like '%$term%' or Phase1A_Score like '%$term%' or Phase1B_HS_Exam like '%$term%' or Phase1A_HS_Exam_RT like '%$term%' or Phase1B_HS_Exam like '%$term%' or Phase1B_HS_Exam_RT like '%$term%' or Class_Date like '%$term%' I understand it is not a good idea to use a bunch of OR clauses. Is there a way to simplify this? Similar TutorialsHey I have this code thats basically repeating but the thing is Im adding a LIMIT 1+,1+ to each one since im echoing from the database row. Here is the code: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Example Form</title> <link rel="stylesheet" type="text/css" href="dd.css" /> <script type="text/javascript" src="jquery-1.3.2.min.js"></script> <script type="text/javascript" src="jquery.dd.js"></script> </head> <body> <!-- .style1 { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; color: #000000; background-color: #FFFFCC; padding: 2px; height: 14px; width: 200px; border: 1px solid #7F9DB9; } --> <?php error_reporting(E_ALL); ini_set('display_errors', '1'); mysql_connect("localhost", "", "")or die("cannot connect"); mysql_select_db("test")or die("cannot select DB"); $tbl_name="test_mysql"; $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); $count=mysql_num_rows($result); //FIRST ROW $result0 = mysql_query("SELECT * FROM test_mysql")or die(mysql_error()); $row0 = mysql_fetch_array($result0); //SECOND ROW $result1 = mysql_query("SELECT * FROM test_mysql LIMIT 1,1")or die(mysql_error()); $row1 = mysql_fetch_array($result1); //THIRD ROW $result2 = mysql_query("SELECT * FROM test_mysql LIMIT 2,2")or die(mysql_error()); $row2 = mysql_fetch_array($result2); //FIRST ROW SHORTEN PARAGRAPH $result0 = mysql_query("SELECT * FROM test_mysql")or die(mysql_error()); $row0 = mysql_fetch_array($result0); $paragraph0 = $row0['message']; $rough_short_par0 = substr($paragraph0, 0, 100); $clean_short_par0 = substr($rough_short_par0, 0); $clean_sentence_row1 = $clean_short_par0 . "..."; //SECOND ROW SHORTEN PARAGRAPH $result1 = mysql_query("SELECT * FROM test_mysql LIMIT 1,1")or die(mysql_error()); $row1 = mysql_fetch_array($result1); $paragraph1 = $row1['message']; $rough_short_par1 = substr($paragraph1, 0, 100); $clean_short_par1 = substr($rough_short_par1, 0); $clean_sentence_row2 = $clean_short_par1 . "..."; //THIRD ROW SHORTEN PARAGRAPH $result2 = mysql_query("SELECT * FROM test_mysql LIMIT 2,2")or die(mysql_error()); $row2 = mysql_fetch_array($result2); $paragraph2 = $row2['message']; $rough_short_par2 = substr($paragraph2, 0, 100); $clean_short_par2 = substr($rough_short_par2, 0); $clean_sentence_row3 = $clean_short_par2 . "..."; ?> <br> <?php //$picture = array(); while($row = mysql_fetch_assoc($result)) { $id = $row['id']; switch ($row['icon']) { case 1: $picture[$id] = '<img src="http://www.nikibone.com/recipe/fruit/graphics/apples.jpg" title="apple" alt="apple" />'; //echo $picture[$id]; break; case 2: $picture[$id] = '<img src="http://www.nikibone.com/recipe/fruit/graphics/bananas.jpg" title="banana" alt="banana" />'; //echo $picture[$id]; break; case 3: $picture[$id] = '<img src="http://www.nikibone.com/recipe/fruit/graphics/oranges.jpg" title="orange" alt="orange" />'; //echo $picture[$id]; break; default: $picture[$id] = ''; echo $row['icon'] . " is something other than 1 2 or 3"; break; } } ?> <hr> <?php echo $row0['monthday']; echo "<br>"; echo $row0['title']; echo "<br>"; echo $row0['message']; echo "<br>"; echo $clean_sentence_row1; echo "<br>"; echo $picture['1']; echo "<br>"; ?> ----- <?php echo "<br>"; echo $row1['monthday']; echo "<br>"; echo $row1['title']; echo "<br>"; echo $row1['message']; echo "<br>"; echo $clean_sentence_row2; echo "<br>"; echo $picture['2']; echo "<br>"; ?> ----- <?php echo "<br>"; echo $row2['monthday']; echo "<br>"; echo $row2['title']; echo "<br>"; echo $row2['message']; echo "<br>"; echo $clean_sentence_row3; echo "<br>"; echo $picture['3']; echo "<br>"; ?> </body> </html> <hr> Is there a way to simplify the code so its shorter and better but works, functions and looks the same. Id be happy with some help. Hey guys, Im trying to post data on a page from multiple rows. Here is how it looks: <?php //FIRST ROW $result0 = mysql_query("SELECT * FROM test_mysql")or die(mysql_error()); $row0 = mysql_fetch_array($result0); //SECOND ROW $result1 = mysql_query("SELECT * FROM test_mysql LIMIT 1,1")or die(mysql_error()); $row1 = mysql_fetch_array($result1); //THIRD ROW $result2 = mysql_query("SELECT * FROM test_mysql LIMIT 2,2")or die(mysql_error()); $row2 = mysql_fetch_array($result2); ?> <?php echo $row0['name']; echo "<br>"; echo $row0['lastname']; echo "<br>"; echo $row0['email']; echo "<br>"; ?> ----- <?php echo "<br>"; echo $row1['name']; echo "<br>"; echo $row1['lastname']; echo "<br>"; echo $row1['email']; echo "<br>"; ?> ----- <?php echo "<br>"; echo $row2['name']; echo "<br>"; echo $row2['lastname']; echo "<br>"; echo $row2['email']; echo "<br>"; ?> As you can see, I am needing to make a new MYSQL_QUERY all the time because I have to change the limit on it so It shows data on another row. Is there any way to do this easier by putting the row I want to show inside the $row2['email']. I have about 6 rows and I need to show data on a page. This is how it looks. Can anyone show me a simple way of doing this? |
|||||||