PHP - Moved: Search 2 Tables

Hi,
So I'm trying to make a car rental project, and I need to show cars of a specific type, available in a specific period.
I'm currently using this query:
SELECT * FROM car, reservations
WHERE car.car_id = reservations.car
AND car.type = 1 AND reservations.reserved_from >= "2011-12-22"
AND reservations.reserved_to <= "2012-12-20";

So this returns all the cars of the type 1 reserved in 2011-12-22 - 2012-12-20 period.
So know that I know which cars are unavailable, how would you go ahead and pick out what's left?

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?

Not sure if this topic goes in here, it is related to PHP but also MySql, so if i'm on the wrong board sorry!

What i'm trying to do is search for a keyword in 5 different tables and return the keyword ID from the table that its in

The tables i'm trying to search are as follow

location
state
county
region
continent

The "location" table has all the locations i.e cities and each row has the following columns:
id | continent_id | country_id | state_id | region_id | city_name

The "state" table is set the the following:
id | name

"county" table :
id | name

"region" table:
id | region

and "continent" table
 id | name

The way it works is the can search for any city or state or county or region or continent and ideally it should look into the five different tables and return the id of that table. So if the use searches for United States it will look for United States in all five tables, obviously it would find it in the "country" table so it should return that "id". The results are returned in "json format"

Below is the code i have:

Code: [Select]
<?
$input = $_GET['keyword'];
$data = array();

/*

In this query i'm attempting to search in all databases, but i'm not sure if i'm doing this right.
I'm not getting any results so i know something is wrong just don't know how to write it.

*/

$query = mysql_query("SELECT * FROM locations JOIN states ON states.id = locations.state_id JOIN countries ON countries.id=locations.country_id JOIN regions ON regions.region_id = locations.region_id JOIN continents ON continents.id=locations.continent_id WHERE name LIKE '$input%' OR state LIKE '$input%' OR region LIKE '$input%' OR country LIKE '$input%' OR continent LIKE '$input%'") or die(mysql_error());

/*

Here the values are added to to the $json array. The "value" should be the "id" from the table that the keyword matched. The 'name'
Should be the name of the actual keyword. Again if they search for United States the "id" will come from the "countries" table and the "value"
would come from the "countries" table as the name

*/

while ($row = mysql_fetch_assoc($query)) {
$json = array();
$json['value'] = $row['id'];
$json['name'] = $row['name'];
$data[] = $json;
}

header("Content-type: application/json");

echo json_encode($data);
?>


Any help would he be appreciated, i don't want people to do it for me, but rather just guide me a little bit.

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" );
}
?>

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to HTML Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic is now in MySQL Help.

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

This topic has been moved to Third Party PHP Scripts.

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

This topic has been moved to Third Party PHP Scripts.

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

This topic has been moved to Ajax Help.

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

This topic has been moved to Miscellaneous.

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