PHP - How To Search Two Mysql Tables For One Value??

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

 The result pages is supposed to have  pagination like google  help me please

 

I have now got a search engine to work, but it only show in normal text, could someone help me get into a table, I have a CSS file but cannot get the search page to print in my table.

 

<div id="main">
<table>
    <thead>
        <tr>
            <th>ID</td>
            <th>GAME</td>
            <th>PLATFORM</td>

        </tr>
    <thead>        

<?php
$output = NULL;
if(isset($_POST['submit'])) {
    //Connect to database
    $mysqli = NEW MySQLi ("localhost","root","","***.**");

    $search = $mysqli->real_escape_string($_POST{'search'});

    //Query the database
    $resultSet = $mysqli->query("SELECT * FROM games WHERE GAME LIKE '$search%'");

    if($resultSet->num_rows >0){
            while($rows = $resultSet->fetch_assoc())
            {
               $id = $rows['ID'];
               $gn = $rows['GAME'];
               $pf = $rows['PLATFORM'];
               $od = $rows['Owned'];?>
            }   
               
                <tr>
                    <td><?php $output .= $id; ?></tr>;
               
               <!-- Game: $gn<br />
               Platform: $pf<br />
               Owned: $od<br /><br />";-->
               

<form method="POST">
<input type="TEXT" name="search" />
<input type="SUBMIT" name="submit" value="Search" />
</form>
</table>
</body>
</html>

Also how can i use my connect file instead of having the connect to database in this file?

How do I search through table 1, 2, 3 where a=x and b=y and c=z?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=330205.0

I present videos on my site. The videos (their titles, descriptions, etc.) are stored in a MySQL database.

I want visitors to be able to search my database for videos.

My MySQL version is 5.5.40-36.1.

The storage engine of my tables is INNODB.

The two columns I want searched a title (varchar; unique index) description (text; no index) According to my research, the best way to allow users to search my database would be to use FULLTEXT, but my INNODB tables are incompatible. FULLTEXT is available for INNODB in MySQL 5.6.4 and above.

I would rather not use LIKE '%...%', and I would rather not use a third party solution such as Sphinx because my database will never grow particularly large (I doubt it'll ever exceed 6,000–8,000 videos).

What do you guys recommend? Is it okay to use LIKE with a database containing 6,000–8,000 items?

Bear with me, as I'm still quite new at PHP.

I'm trying to figure out the best way to build a search function for a database that will eventually be quite large.

The path of the search I'm building is to go from a straight-forward javascript and html search page, to a paginated search result. Then the user can narrow down those query results based on a new search of their results.

In trying to get the pagination to work across several pages(unsuccessfuly, I might add), I've been learning about sessions and temporary tables. But, because the database will be very large, I'm wondering if they are the wrong way. Sessions have a time limit, and temporary tables delete once the connection is closed.

Is it possible/feasible to build a permanent table that puts in the search variables, with a unique id and use this to manage the searches, pagination and all that? Then, at some point in the process, I can put code to delete the corresponding row (or even make it a saveable search). Maybe somebody sees where I'm going with this and  can describe it better than me? I'm just thinking off the cuff at the moment. Maybe there's some terminology that will help me find a tutorial. Any bit helps.
thanks!

Hi. Im trying to get my table so I can click on username and it will display ascending or click again and it descends.

I want to be able to do this will all of them..Username Password IP Country Type Status E-mail Date start Date end ..
If anyone can help me it would be great. I dont know PHP . I paid someone to make this for my and they binned it half way through the job taking my money...



Thank you

This is my code



<?php
if(session_is_registered('username')){
    include("header.php");
   
?>                 
            <div id="content_main" class="clearfix">
            <div id="main_panel_container" class="left">
   <div id="tabledata" class="section">
            <h2 class="ico_mug">Users table</h2>
            <?
            if($_GET['type']=='delete'){
                mysql_query("delete from users where id='".$_GET['id']."'");
                ?><div id="success" class="info_div"><span class="ico_success">User deleted successful!</span></div><?
            }
            if($_GET['type']=='edit'){
                if(isset($_POST['username'])){
                    mysql_query("UPDATE users SET username='".$_POST['username']."',
                                                  password='".$_POST['password']."',
                                                  ip='".$_POST['ip']."',
                                                  country='".$_POST['country']."'
                                 WHERE id='".$_GET['id']."'");
                }
                $row=mysql_fetch_array(mysql_query("SELECT * FROM users WHERE id='".$_GET['id']."'"));
                  ?>
                           <form action="" method="post" accept-charset="utf-8">
                           <fieldset>

                           <label>Username:<input type="text" name="username" value="<?echo $row['username'];?>" size=60/></label>
                           <label>Password:<input type="text" name="password" value="<?echo $row['password'];;?>" size=60/></label>
                           <label>IP:<input type="text" name="ip" value="<?echo $row['ip'];;?>" size=60/></label>
                           <label>Country:<input type="text" name="country" value="<?echo $row['country'];;?>" size=60/></label>
                             
                           <br />  <button>Submit</button>
             
                            </fieldset>
           
                            </form>
                            <button onclick="location.href='index.php?action=users';">Back</button>
                           <?
            }
            if($_GET['type']=='add'){
                if(isset($_POST)){
                    $j=0;
                    for($i=1; $i<=5; $i++){
                        if($_POST['username'.$i]!=''){
                        if(($_POST['username'.$i]!='') &&
                           ($_POST['password'.$i]!='') &&
                           ($_POST['ip'.$i]!='') &&
                           ($_POST['country'.$i]!=''))
                        {
                            if(($_POST['date_start'.$i]=='')||($_POST['date_end'.$i]=='')){
                                $date_start=0;
                                $date_end=0;
                            } else {
                            $date_start=strtotime($_POST['date_start'.$i]);
                            $date_end=strtotime($_POST['date_end'.$i]);
                            }
                            mysql_query("INSERT INTO users (username,password,ip,email,country,date_start,date_end) VALUES
                                        ('".$_POST['username'.$i]."','".$_POST['password'.$i]."','".$_POST['ip'.$i]."','".$_POST['email'.$i]."','".$_POST['country'.$i]."','".$date_start."','".$date_end."')
                                        ");
                            $j++;
                        } else {
                            ?><div id="fail" class="info_div"><span class="ico_cancel">Fields on the same row can't be blank</span></div><?
                        }
                        }
                    }
                    if($j>0){
                    ?><div id="success" class="info_div"><span class="ico_success"><? echo $j;?> users have been added!</span></div><?
                    }
                }
               
            ?>
            <fieldset>
            <i>
            Date format should be like 2000-05-25<br>
            Type: B for instant buy; S for subscription<br>
            Status: Y for active; N for inactive
            </i>
            </fieldset>
            <form action="" method="post" accept-charset="utf-8">
            <table id="table">
            <thead>
            <tr>
               
                <th>Username</th>
                <th>Password</th>
                <th>IP</th>
                <th>Email</th>
                <th>Country</th>
                <th>Type</th>
                <th>Status</th>
                <th>Date start</th>
                <th>Date end</th> 
            </tr>
            </thead>
            <tbody>
           
                           
           
                <?
                for ($i=1; $i<=5; $i++) {
?>
                <tr>
                <td>
                    <input type="text" name="username<?echo $i;?>" size="10">
                </td>
                <td>
                    <input type="text" name="password<?echo $i;?>" size="10">
                </td>
                <td>
                    <input type="text" name="ip<?echo $i;?>" size="10">
                </td>
                <td>
                    <input type="text" name="email<?echo $i;?>" size="10">
                </td>
                <td>
                    <select name="country<?echo $i;?>">
                        <option value="UK">UK</option>
                        <option value="US">US</option>
                    </select>
                </td>
                <td>
                    <select name="type<?echo $i;?>">
                        <option value="B" selected>B</option>
                        <option value="S">S</option>
                    </select>
                </td>
                <td>
                    <select name="status<?echo $i;?>">
                        <option value="Y" selected>Y</option>
                        <option value="N">N</option>
                    </select>
                </td>
                <td>
                    <input type="text" name="date_start<?echo $i;?>"  size="10">
                </td>
                <td>
                    <input type="text" name="date_end<?echo $i;?>" size="10">
                </td>
                </tr>
                <? } ?>
           
            <tr>
            <td><button>Submit</button> </td>
            </tr> 
                           
           
                           
            </tbody>
            </table>
            </form>
            <br>
            <?
           
            }
           
?>
       
       <?php
           
         if($_GET['type']=='import')
         {
            
            if($_POST['import'] && $_FILES['flimport']['name']!='')
            {
               //print_r($_FILES);
               $fieldseparator = ",";
               $lineseparator = "\n";
               $csvfile=$_FILES['flimport']['tmp_name'];
               $size = $_FILES['flimport']['size'];
               if(!$size) {
                  exit;
               }
               $file = fopen($csvfile,"r");
               $csvcontent = fread($file,$size);
               fclose($file);
               //////////////////////////////////////////////////////////////
               $lines = 0;
               $queries = 0;
               $linearray = array();
               foreach(split($lineseparator,$csvcontent) as $line) {
               
                  $lines++;
               
                  $line = trim($line," \t");
                  
                  $line = str_replace("\r","",$line);
                  $line = str_replace("'","\'",$line);
                  
                  $linearray = explode($fieldseparator,$line);
                  
                  $linemysql = implode("','",$linearray);
                  $exist=0;
                  if(is_numeric($linearray[0]))
                  {
                     $exist=mysql_num_rows(mysql_query("select id from users where username='".$linearray[2]."' or email='".$linearray[1]."'"));
                        $query = "insert into users values('$linemysql');";
                     
                  }
                  else
                  {
                     $exist=mysql_num_rows(mysql_query("select id from users where username='".$linearray[1]."' or email='".$linearray[0]."'"));
                        $query = "insert into users values('','$linemysql');";
                     
                  }
                  //echo $query."<br />";
                  if($exist==0)
                  {
                     @mysql_query($query);               
                     if(mysql_insert_id())
                        $queries++;
                  }
               }   
               
                  echo '<div id="success" class="info_div"><span class="ico_success">'.$queries.' users added successfully</span></div>';
               
            }
         ?>
            <form name="frmImport" action="" method="post" enctype="multipart/form-data">
            <input type="hidden" value="1" name="import" />
                <table id="table">
                <tbody>
                   <tr>
                         <td><span style="font-size:10px; color:#333">Note: Please entry ms excel <strong>.CSV (Comma delimited)</strong> file only</span></td>
                    </tr>
                    <tr>
                         <td><input type="file" accept="application/msexcel" name="flimport" />
                        </td>
                    </tr>
                    <tr>
                         <td><button>Import Now</button></td>
                    </tr>
                   </tbody>
                  </table>
            </form>
         <br />
         <?php } ?>
      
      <button onclick="location.href='index.php?action=users&type=add';">Add users</button>
      &nbsp;<button onclick="location.href='index.php?action=users&type=import';" name="btnimport">Import users</button>
         &nbsp;<button onclick="location.href='export.php?datafrom=users';" name="btnexport">Export users</button>
      
      
      
        <table id="table">
            <thead>
            <tr>
                <th>Actions</th>
                <th>Username</th>
                <th>Password</th>
                <th>IP</th>
                <th>Country</th>
                <th>Type</th>
                <th>Status</th>
                <th>E-mail</th>
                <th>Date start</th>
                <th>Date end</th>
               
            </tr>
            </thead>
            <tbody>
            <?
            if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }
            $start_from = ($page-1) * 20;
            $query=mysql_query("SELECT * FROM users WHERE 1 LIMIT $start_from, 20");
            while($row=mysql_fetch_array($query)){
               
           
?>
            <tr>
                <td><a href="index.php?action=users&type=delete&id=<?echo $row['id'];?>"><img src="img/cancel.jpg" alt="cancel"/></a><a href="index.php?action=users&type=edit&id=<?echo $row['id'];?>"><img src="img/edit.jpg" alt="edit"/></a></td>
                <td><?echo $row['username'];?></td>
                <td><?echo $row['password'];?></td>
                <td><?echo $row['ip'];?></td>
                <td><?echo $row['country'];?></td>
                <td><?echo $row['type'];?></td>
                <td><?echo $row['status'];?></td>
                <td><?echo $row['email'];?></td> 
                <td class="table_date"><?echo date("Y-m-d",$row['date_start']);?></td>
                <td class="table_date"><?echo date("Y-m-d",$row['date_end']);?></td>
            </tr>
           
            <?
            }
?>
            </tbody>
        </table>
        <?
           
            $rs_result = mysql_query("SELECT COUNT(id) FROM users");
            $row = mysql_fetch_row($rs_result);
            $total_records = $row[0];
            $total_pages = ceil($total_records / 20);
             
?>
           
            <div class="pagination">
                <span class="pages">Page 1 of <?echo $total_pages;?></span>
               
                <?php
                for ($i=1; $i<=$total_pages; $i++) {
                if($page==$i){
                    ?><span class="current"><b><?echo $page;?></span></b><?
                } else {
                    echo "<a href='index.php?action=users&type=view&page=".$i."'>".$i."</a> ";
                }
                }
                if($total_pages>1){
                $next=$page+1;
               
                ?>
                <a href="index.php?action=users&type=view&page=<? echo $next;?>" >&raquo;</a>
                <?
                }
?>
            </div>
        </div> <!-- end #tabledata -->

              <?
        include("shortcuts.php");
        ?>
            </div>
        <?
       
        ?>
        </div><!-- end #content_main -->


       
        </div><!-- end #content -->
 <?
    include("footer.php");
?>

<?
    } else {
    header( "Location: index.php?action=login" );
}
?>

just trying to see if i have this right in my head here..

if i have a page that has a topic on it and many replies,  the tables would look like this..


would this be the right code to pull out the topic AND all responses for say.
www.mysite.com/view?id=1

$topic = 'SELECT * from topics where id = $id'

$replies = 'SELECT * from replies where replies.topic_id = $id'

Hello friends, I am a novice in php. Though I create 2 scripts:   1. students registration form 2. search students by Registration Number   Our students' Registration Numbers are as follows: nifeA001, nifeA002 & so on...   But when someone enter just "nife" or "n" or "A" etc all the students's data is showing.   I want that students can search data by entering only Full Registration Number not a Part.   I am placing the search.php code below. Please help me out....   ----------------- Search.php ------------------ <?php mysql_connect("localhost", "root", "") or die("could not connect"); mysql_select_db("student") or die("could not connect");   //collect if(isset($_POST['search'])) {     $searchq = $_POST['search'];     $searchq = preg_replace("#[^0-9a-z]#i", "", $searchq);   //here $query = mysql_query("SELECT * FROM user WHERE registration LIKE '%$searchq%'") or die("could not search!");     $count = mysql_num_rows($query);     if($count == 0){         $output = 'There was no search results!';     }else{         while($row = mysql_fetch_array($query)) {             $fname = $row['firstname'];             $lname = $row['surname'];             $id = $row['registration'];               $output .= '<div> '.$id.' '.$fname.' '.$lname.'</div>';                }         } }   ?>   <html> <head> </head> <body> <form action="form.php" method="post">     <input type="text" name="search" placeholder="search for students.."     <input type="submit" value=">>" /> </form>   <?php print("$output");?> </body> </html>
Edited by NasimUddinAhmmad, 13 January 2015 - 05:43 AM.

Hey guys, I'm a newbie to PHP and MySql, and I need some help!

For the record, I am a web developer and designer, who always put off learning  , due to fear, but realized I had to do it.

Anyways, my problem is this. I am creating a website, where there has to be a seach box where people can lookup names, and or keywords, that are stored into my db.


I've googled it a billion times, and got everything to almost work. I know how to set-up a database, tables, insert info, and all the real basics of mysql.


I am running my site on MAMP right now, and here is the code that I am trying to use.



<?php
/* DON'T DISPLAY ANY PHP ERRORS OR WARNINGS
   (optional) comment out in development
-----------------------------------------------*/
error_reporting(0);

/*DATABASE INFO
--------------------------*/
$hostname_logon = 'localhost';         //Database server LOCATION
$database_logon = '';               //Database NAME
$username_logon = '';               //Database USERNAME
$password_logon = '';               //Database PASSWORD


/*MYSQL TABLE TO SEARCH
--------------------------*/
//update these values to table you want to search
$db_table       = '';               //TABLE NAME
$row_id       = '';               //TABLE ROW ID
$title_field    = '';               //FIELD CONTAINING ITEM TITLE
$content_field    = '';               //FIELD CONTAINING ITEM CONTENT
$link_field    = '';               //FIELD CONTAINING ITEM URL
$limit         = 10;               //NUMBER OF RESULTS PER PAGE

/*SEARCH SCRIPT FILENAME
--------------------------*/
//get the name of this file,so you can name this file whatever you want. This is used in the action parameter of the search form
$currentFile = $_SERVER["SCRIPT_NAME"];
$parts = explode('/', $currentFile);
$currentFile = $parts[count($parts) - 1];

/*CONNECT TO THE DATABASE
--------------------------*/
//open database connection
$connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unabale to connect to the database" );
//select database
mysql_select_db($database_logon) or die ( "Unable to select database!" );

/*GET SEARCH PARAMETERS
--------------------------*/
//get the search parameter from URL
$var = mysql_real_escape_string(@$_REQUEST['q']);

//get pagination
$s = mysql_real_escape_string(@$_REQUEST['s']);

//set keyword character limit
if(strlen($var) < 3){
   $resultmsg =  "<p>Search Error</p><p>Keywords with less then three characters are omitted...</p>" ; 
}
//trim whitespace from the stored variable
$trimmed = trim($var);
$trimmed1 = trim($var);
//separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);
$trimmed_array1 = explode(" ",$trimmed1);

// check for an empty string and display a message.
if ($trimmed == "") {
   $resulterror =  "<p>Search Error</p><p>Please enter a search...</p>" ;
}

// check for a search parameter
if (!isset($var)){
   $resulterror =  "<p>Search Error</p><p>We don't seem to have a search parameter! </p>" ;
}

//make sure search keyword is at least 2 characters. (buggy if not)
if(strlen($trimmed) < 2){
   $resulterror =  "<p>Search Error</p><p>Your keyword has to be at least two characters long! </p>" ;
}

/*CREATE SEARCH FORM
--------------------------*/
$search_form = '<form class="search-form" action="'.$currentFile.'" method="GET">
      <div>
      <input name="q" type="text" value="'.$q.'"><input name="search" type="submit" value="Search">
      </div>
</form>';


/*DISPLAY QUERY ERRORS OR DO THE SEARCH
--------------------------------------------*/
if($resulterror){
   $resultmsg = $resulterror;
}else{

// Build SQL Query for each keyword entered
foreach ($trimmed_array as $trimm){
   
   // MySQL "MATCH" is used for full-text searching. Please visit mysql for details.
   $query = "SELECT *, MATCH (".$title_field.",".$content_field.") AGAINST ('".$trimm."') AS score FROM ".$db_table." WHERE MATCH (".$title_field.",".$content_field.") AGAINST ('+".$trimm."' IN BOOLEAN MODE) HAVING score > 0.01 ORDER BY score DESC";
   // Execute the query to  get number of rows that contain search kewords
   $numresults=mysql_query ($query)  or die ("Error in query: $query. " . mysql_error());
   $row_num_links_main =mysql_num_rows ($numresults);
   
   //If MATCH query doesn't return any results due to how it works do a search using LIKE
   if($row_num_links_main < 1){
      $query = "SELECT * FROM ".$db_table." WHERE ".$title_field." LIKE '%".$trimm."%' OR ".$content_field." LIKE '%".$trimm."%' ORDER BY ".$row_id." DESC" ;
      $numresults=mysql_query ($query);
      $row_num_links_main =mysql_num_rows ($numresults);   
   }
   // next determine if 's' has been passed to script, if not use 0.
   // 's' is a variable that gets set as we navigate the search result pages.
   if (empty($s)) {
    $s=0;
   }
   
   // now let's get results.
   $query .= " LIMIT $s,$limit" ;
   $numresults = mysql_query ($query) or die ("Error in query: $query. " . mysql_error());
   $row= mysql_fetch_array ($numresults);
   
   
   //store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
   do{
     $adid_array[] = $row[ 'news_id' ];
   }while( $row= mysql_fetch_array($numresults));
} //end foreach



/*Display a message if no results found
--------------------------*/
if($row_num_links_main == 0 && $row_num_links_main1 == 0){
   $resultmsg = "<p>Search results for: ". $trimmed."</p><p>Sorry, your search returned zero results</p>" ;
}


//delete duplicate record id's from the array. To do this we will use array_unique function
$tmparr = array_unique($adid_array);
$i=0;
foreach ($tmparr as $v) {
   $newarr[$i] = $v;
   $i++;
}


//total number of results
$row_num_links_main = $row_num_links_main + $row_num_links_main1;


   if($resultmsg != ""){
      $resultmsg = "<p>Search results for: <strong>" . $var."</strong></p>";
   }
   
   /* FETCH RESULTS BASED ON THE ID
   --------------------------------------------*/
   foreach($newarr as $value){

   // EDIT HERE and specify your table and field unique ID for the SQL query
   $query_value = "SELECT * FROM ".$db_table." WHERE ".$row_id." = '".$value."'";
   $num_value=mysql_query ($query_value);
   $row_linkcat= mysql_fetch_array ($num_value);
   $row_num_links= mysql_num_rows ($num_value);
   
   //create summary of the long text. For example if the field2 is your full text grab only first 130 characters of it for the result
   $introcontent = strip_tags($row_linkcat[$content_field]);
   $introcontent = substr($introcontent, 0, 130)."...";
   
   //now let's make the keywods bold. To do that we will use preg_replace function.
   //Replace field
     $title = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $row_linkcat[ $title_field] );
     $desc = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $introcontent);
    
     //COMMENT OUT THIS LINE: If using database ID field to drive links
     $link = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" ,  $row_linkcat[$link_field]  );
    
     //UNCOMMENT and EDIT THIS LINE: If using database ID field to drive links
     //$link = 'http://yourdomain.com/pageid='. $row_linkcat[$link_field];
   
      foreach($trimmed_array as $trimm){
         if($trimm != 'b' ){
            $title = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $title);
            $desc = preg_replace( "'($trimm)'si" , "<strong>\\1</strong>" , $desc);
            $link = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $link);
          }//end highlight
      }//end foreach $trimmed_array
      //format and display search results
         $resultmsg.= '<div class="search-result">';
            $resultmsg.= '<div class="search-title"><a href="'.$link.'">'.$title.'</a></div>';
            $resultmsg.= '<div class="search-text">';
               $resultmsg.= $desc;
            $resultmsg.= '</div>';
            $resultmsg.= '<a href="'.$link.'" class="search-link">';
            $resultmsg.= $link;
            $resultmsg.= '</a>';
         $resultmsg.= '</div>';
   
   }  //end foreach $newarr

   /* CREATE PAGINATION
   --------------------------------------------*/
   if($row_num_links_main > $limit){
   // next we need to do the links to other search result pages
      if ($s >=1) { // do not display previous link if 's' is '0'
         $prevs=($s-$limit);
         $resultmsg.= '<div class="search-previous"><a href="'.$PHP_SELF.'?s='.$prevs.'&q='.$var.'">Previous</a></div>';
      }
   // check to see if last page
      $slimit =$s+$limit;
      if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
         // not last page so display next link
         $n=$s+$limit;
         $resultmsg.= '<div  class="search-next"><a href="'.$PHP_SELF.'?s='.$n.'&q='.$var.'">Next</a></div>';
      }
   }//end if $row_num_links_main > $limit
}//end if no errors
?>
<?php

//display form
echo $search_form;

//display results
echo $resultmsg;
?>


I know that this might be asking a lot, but can someone who is smarter than me please give me instructions on how to set this up.

I am at a complete loss. I've tried everything. I can connect to the db, and all, but it returns errors, and or blank screens.

I will owe everyone. Thank you.

I wonder whether someone may be able to help me please.

I'm trying to put together php code and an html form that allows an administrator to search for a user via the email address, retrieving the 'first' and 'surname' from the pertinent record in a mySQL database.

What I would like to happen is for the retrieved 'first' and surname' values to be automatically populated into the 'first' and 'surname' text boxes on the same search form.

I can perform the search and retrieval of the correct record without any problem, but I can't work out how to populate the 'first' and 'surname' values into my search form. I am quote a novice with PHP so apologies if this is a really daft question, but I just windered whether somone could take a look at my code below and let me know where I've gone wrong?

Many thanks

PHP CODE
Code: [Select]
<?php 
require("phpfile.php"); 

// Opens a connection to a MySQL server 

$connection=mysql_connect ("hostname", $username, $password); 
if (!$connection) { die('Not connected : ' . mysql_error());} 

// Set the active MySQL database 

$db_selected = mysql_select_db($database, $connection); 
if (!$db_selected) { 
die ('Can\'t use db : ' . mysql_error()); 
} 


$email = $_POST['email']; 
$result = mysql_query("SELECT * FROM userdetails WHERE emailaddress like '%$email%'"); 

while($row = mysql_fetch_array($result)) 
{ 
echo $row['forename']; 
echo $row['surname']; 
echo "<br />"; 
} 
?>

HTML FORM
Code: [Select]
!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" xml:lang="en" lang="en">
    <head>
        <title>Maptitle>
        <script src="js/gen_validatorv4.js" type="text/javascript"></script>
    </head>
    <h1><span class="blue">Sign Up</span> For Map My Finds</h1>
    <form name="userpasswordreset" id="userpasswordreset" method="post" action="search.php">
        <h2>Your Details </h2>
    </div>
        <table width="100%" border="0" cellspacing="0" cellpadding="0">
            <tr>
                <td width="26%" height="25"><strong>Email Address</strong></td>
                <td width="4%">&nbsp;</td>
                <td width="70%"><input name="email" type="email" id="email" size="50" /></td>
            </tr>
            <tr>
                <td height="25"><strong>Confirm Email</strong></td>
                <td>&nbsp;</td>
                <td><input name="conf_email" type="email" id="conf_email" size="50" /></td>
            </tr>
           
            <tr>
                <td height="25"><label>
                    <input type="submit" name="Submit" value="Search" />
                    </label></td>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td height="25"><strong> First Name </strong></td>
                <td>&nbsp;</td>
                <td><input name="fname" type="text" id="fname" size="30" value="<?php echo $forename; ?>" /></td>
            </tr>
            <tr>
                <td height="25"><strong> Address Last Name </strong></td>
                <td>&nbsp;</td>
                <td><input name="lname" type="text" id="lname" size="30"  value="<?php echo $surname; ?>" /></td>
            </tr>
            <tr>
                <td height="25">&nbsp;</td>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td height="25">&nbsp;</td>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td height="25"><strong>Password</strong></td>
                <td>&nbsp;</td>
                <td><input name="pass" type="password" id="pass" size="30" /></td>
            </tr>
            <tr>
                <td height="25"><strong>Confirm Password </strong></td>
                <td>&nbsp;</td>
                <td><input name="conf_pass" type="password" id="conf_pass" size="30" /></td>
            </tr>
            <tr>
                <td height="25">&nbsp;</td>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td height="25"><strong>Password Hint </strong></td>
                <td>&nbsp;</td>
                <td><input name="hint" type="text" id="hint" size="30" /></td>
            </tr>
            <tr>
                <td height="25">&nbsp;</td>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td height="25">&nbsp;</td>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
            </tr>
        </table>
    </form>
    <script language="JavaScript" type="text/javascript">
        // Code for validating the form
        // Visit http://www.javascript-coder.com/html-form/javascript-form-validation.phtml
        // for details
        var frmvalidator = new Validator("userpasswordreset");
        frmvalidator.addValidation("email","req","Please provide your email address");
        frmvalidator.addValidation("email","email","Please enter a valid email address");
        frmvalidator.addValidation("conf_email","eqelmnt=email", "The confirmed email address is not the same as the email address");
    </body>
    </html>

I found an article on how to implement a mysql search (http://www.iamcal.com/publish/articles/php/search/) and it seemed like a good way to go, but I can't get it up and running.

Here is my code:
Code: [Select]
<?php
$db_host = 'localhost';
$db_user = 'username';
$db_pwd = 'password';

$database = 'dbname';
$table = 'Principals';

if (!mysql_connect($db_host, $db_user, $db_pwd))
die("Can't connect to database");

if (!mysql_select_db($database))
die("Can't select database");




$searchterms = 'dorr oliver';


function search_split_terms($terms){

$terms = preg_replace("/\"(.*?)\"/e", "search_transform_term('\$1')", $terms);
$terms = preg_split("/\s+|,/", $terms);

$out = array();

foreach($terms as $term){

$term = preg_replace("/\{WHITESPACE-([0-9]+)\}/e", "chr(\$1)", $term);
$term = preg_replace("/\{COMMA\}/", ",", $term);

$out[] = $term;
}

return $out;
}

function search_transform_term($term){
$term = preg_replace("/(\s)/e", "'{WHITESPACE-'.ord('\$1').'}'", $term);
$term = preg_replace("/,/", "{COMMA}", $term);
return $term;
}

function search_escape_rlike($string){
return preg_replace("/([.\[\]*^\$])/", '\\\$1', $string);
}

function search_db_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = '[[:<:]]'.AddSlashes(search_escape_rlike($term)).'[[:>:]]';
}
return $out;
}

function search_perform($terms){

$terms = search_split_terms($terms);
$terms_db = search_db_escape_terms($terms);
$terms_rx = search_rx_escape_terms($terms);

$parts = array();
foreach($terms_db as $term_db){
$parts[] = "Principals RLIKE '$term_db'";
}
$parts = implode(' OR ', $parts);

$sql = "SELECT * FROM Principal WHERE $parts";

$rows = array();

$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

$row[score] = 0;

foreach($terms_rx as $term_rx){
$row[score] += preg_match_all("/$term_rx/i", $row[Principals], $null);
}

$rows[] = $row;
}

uasort($rows, 'search_sort_results');

return $rows;
}

function search_rx_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = '\b'.preg_quote($term, '/').'\b';
}
return $out;
}

function search_sort_results($a, $b){

$ax = $a[score];
$bx = $b[score];

if ($ax == $bx){ return 0; }
return ($ax > $bx) ? -1 : 1;
}

function search_html_escape_terms($terms){
$out = array();

foreach($terms as $term){
if (preg_match("/\s|,/", $term)){
$out[] = '"'.HtmlSpecialChars($term).'"';
}else{
$out[] = HtmlSpecialChars($term);
}
}

return $out;
}

function search_pretty_terms($terms_html){

if (count($terms_html) == 1){
return array_pop($terms_html);
}

$last = array_pop($terms_html);

return implode(', ', $terms_html)." and $last";
}


#
# do the search here...
#

$results = search_perform($searchterms);
$term_list = search_pretty_terms(search_html_escape_terms(search_split_terms($searchterms)));
The table name is 'Principals' and the field name I am trying to search is 'Principal'.  I am getting an error on the line:

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

The error is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Can anyone shed some light on this for me?

Hello

I wonder if it is possible to make a search form with different fields where all conditions should be met if the field is set.

I have this code:

$sel = "SELECT * FROM database ";
  $sel .= "WHERE ((test1 = '$test1' ";
  $sel .= "AND test2 = '$test2' ";
  $sel .= "AND test3 = '$test3' ";
  $sel .= "ORDER BY id ";
  $query = mysql_query($sel) or die(mysql_error());
  if (mysql_num_rows($query) == 0){
    echo ("No result");
   exit;


When I use this and leave for example 1 field empty I get the error:
No result.


Is it possible to make smothing so the condition only should be met if the field is set?
So If test1 and test3 are set these condition should be met..


I hope you can help!


Thanks in advance!



- Frederik