Jump to content

mysql search help

hyster

By hyster
in PHP Coding Help

Recommended Posts

hyster Regular Member

hyster

Posted
Posted

i downloaded a script and i have modified it to almost what i need. im stuck on the display of the info as it was designed to list 1 piece of info and i need it to display 6. it does do this after i modified it but i want to put each individual piece of data into its own cell in a table.

the way it works the data is displayed as 1 $var. what i want to do is split it so they are diffrent $vars which i can then work with.

 

i tryed emailing the auther but no reply as of yet.

 

the code i need to separate is

 

while ($row = mysql_fetch_assoc($searchResult)) {            
$results[] = "{$i}: -  {$row['origsku']} - {$row['reconsku']} - {$row['make']} - {$row['model']} - {$row['working']} - {$row['actions']} <br />";            $i++;

 

displayed by

 

<td><?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("",  $results):""; ?></td>

 

full script

 

<?php
/*****************************
*  Simple SQL Search Tutorial by Frost
*  of Slunked.com
******************************/

$dbHost = 'mysql10.000webhost.com'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'a3288237_exel'; 
$dbPass = 'exel1234';
$dbDatabase = 'a3288237_exel'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT origsku, reconsku, make, model, working, actions FROM dsgi WHERE ";
      
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['origsku'])?"`origsku` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['reconsku'])?"`reconsku` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['make'])?"`make` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['model'])?"`model` LIKE '%{$searchTermDB}%'":'';
  $types[] = isset($_GET['working'])?"`working` LIKE '%{$searchTermDB}%'":'';
  $types[] = isset($_GET['actions'])?"`actions` LIKE '%{$searchTermDB}%'":'';
  
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`origsku` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
      
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `id`"; // order by title.

      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: -  {$row['origsku']} - {$row['reconsku']} - {$row['make']} - {$row['model']} - {$row['working']} - {$row['actions']} <br />";
            $i++;
	 }
      }
   }
}

function removeEmpty($var) {
   return (!empty($var)); 
}
?>
<html>
   <title>My Simple Search Form</title>
   
   <body>
   <form method="GET" action="<?php echo $_SERVER['../../AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/8GJWA5WT/PHP_SELF'];?>" name="searchForm">
      <p align="center">Search For:
  <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br />
         Search In:</p>
<table width="519" border="1" align="center" cellpadding="1">
           <tr>
             <td width="110"><div align="center">Orig SKU:
             <input name="origsku" type="checkbox" <?php echo isset($_GET['origsku'])?"checked":''; ?> />
             </div></td>
             <td width="110"> <div align="center">Recon SKU:
  <input name="reconsku" type="checkbox" value="on" <?php echo isset($_GET['reconsku'])?"checked":''; ?> />
             </div></td>
             <td width="124"><div align="center">Make:
                 <input name="make" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"make":''; ?> />
             </div></td>
             <td width="78" rowspan="2">Match All Fields?
  <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?>>
  <br />
  <br /></td>
             <td width="63" rowspan="2"><input type="submit" name="submit" value="Search!" /></td>
           </tr>
           <tr>
             <td height="26"><div align="center">Model:
             <input name="model" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"model":''; ?> />
             </div></td>
             <td><div align="center">Working:
             <input name="working" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"working":''; ?> />
             </div></td>
             <td><div align="center">Action:
             <input name="actions" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"actions":''; ?> />
             </div></td>
           </tr>
        </table>
         <p><center><?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?></center></p>
   </form>
   <table width="1000" border="1" align="center">
        <tr>
                      <td><?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("",  $results):""; ?></td>
        </tr>
   </table>
   </body>
</html>

 

big thanks to any1 who can help

Link to comment
https://forums.phpfreaks.com/topic/200946-mysql-search-help/
Share on other sites
hyster Regular Member

hyster

Posted
  • Author
Posted

ive worked some more on this and so far i can display the results in a table the way i wanted but it only shows the last result. its not looping when multiple results are returned.

 

<?php
/*****************************
*  Simple SQL Search Tutorial by Frost
*  of Slunked.com
******************************/

$dbHost = 'mysql10.000webhost.com'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'a3288237_exel'; 
$dbPass = 'exel1234';
$dbDatabase = 'a3288237_exel'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
   
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT origsku, reconsku, make, model, working, actions FROM dsgi WHERE ";
      
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['origsku'])?"`origsku` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['reconsku'])?"`reconsku` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['make'])?"`make` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['model'])?"`model` LIKE '%{$searchTermDB}%'":'';
  $types[] = isset($_GET['working'])?"`working` LIKE '%{$searchTermDB}%'":'';
  $types[] = isset($_GET['actions'])?"`actions` LIKE '%{$searchTermDB}%'":'';
  
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
      
      if (count($types) < 1)
         $types[] = "`origsku` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
      
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `id`"; // order by title.

      $search = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      
      if (mysql_num_rows($search) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
        while ($row = mysql_fetch_assoc($search)) {
            $results[] = "{$i}: -  {$row['origsku']} - {$row['reconsku']} - {$row['make']} - {$row['model']} - {$row['working']} - {$row['actions']} <br />";
            $i++; 
		$test1 = $row["origsku"];
            $test2 = $row["reconsku"];
            $test3 = $row["make"];
            $test4 = $row["model"];
            $test5 = $row["working"];
            $test6 = $row["actions"];
           $i++;
	 }
           }
         }
      }
   


function removeEmpty($var) {
   return (!empty($var)); 
}
?>
<html>
   <title>My Simple Search Form</title>
   
   <body>
   <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
      <p align="center">Search For:
  <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br />
         Search In:</p>
<table width="519" border="1" align="center" cellpadding="1">
           <tr>
             <td width="110"><div align="center">Orig SKU:
             <input name="origsku" type="checkbox" <?php echo isset($_GET['origsku'])?"checked":''; ?> />
             </div></td>
             <td width="110"> <div align="center">Recon SKU:
  <input name="reconsku" type="checkbox" value="on" <?php echo isset($_GET['reconsku'])?"checked":''; ?> />
             </div></td>
             <td width="124"><div align="center">Make:
                 <input name="make" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"make":''; ?> />
             </div></td>
             <td width="78" rowspan="2">Match All Fields?
  <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?>>
  <br />
  <br /></td>
             <td width="63" rowspan="2"><input type="submit" name="submit" value="Search!" /></td>
           </tr>
           <tr>
             <td height="26"><div align="center">Model:
             <input name="model" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"model":''; ?> />
             </div></td>
             <td><div align="center">Working:
             <input name="working" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"working":''; ?> />
             </div></td>
             <td><div align="center">Action:
             <input name="actions" type="checkbox" value="on" <?php echo isset($_GET['desc'])?"actions":''; ?> />
             </div></td>
           </tr>
        </table>
         <p><center><?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?></center></p>
   </form>
   <table width="800" border="1" align="center">
  <tr>
    <td width="160">Recon Sku</td>
    <td width="110">Origanal SKU</td>
    <td width="74">Make</td>
    <td width="111">Model</td>
    <td width="46" bgcolor="#00CC00">Status</td>
    <td width="259">Actions</td>
</tr><tr>
               <td><?php echo $test1 ;  ?></td> <td><?php echo $test2 ;  ?></td> <td><?php echo $test3 ;  ?></td> <td><?php echo $test4 ;  ?></td> <td bgcolor="#00CC00"><?php echo $test5 ;  ?></td> <td><?php echo $test6 ;  ?></td>
        </tr>
   </table>
   </body>
</html>

Link to comment
https://forums.phpfreaks.com/topic/200946-mysql-search-help/#findComment-1054461
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • 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.