PHP - What's The Fastest Way To Search Innodb Tables?

This topic has been moved to MySQL Help.

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

I have two tables in which different information of members are stored...

table 1 stores the users main info, name, city, category, location

table 2 stores extra info such as sub categories of items they have.

Now i want to be able to search both tables for one value...say i type in red..i want to be able to search both tables at the same time to find the results and display them

So far i'm stuck in figuring out how to do this, which is why i'm asking for help now.

Hope someone can give me some guidance

I need some help on how to search in two different mysql tables, and then show the results sorted/ordered by the tables.

Quote

RESULTS:

Tabel 1
# ...
# ...

Tabel 2
# ...
# ...


Thanks in advance!

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

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?

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.

I have two arrays, X and Y, which contain names. The arrays are of the same size. The arrays are related to each other such that for each pair

X[n] , Y[n]

X is friends with Y.

For example:
X[0,1,2] = [Link, Cloud, Cloud, Mario, Mario, Luigi]
Y[0,1,2] = [Zelda, Barrett, Tifa, Luigi, Bowser, Mario]

Link is friends with Zelda
Cloud is friends with Barrett and Tifa
Mario is friends with Luigi and Bowser
Luigi is friends with Mario

I want to loop through these arrays and, for each unique name, find all of that person's friends.

I then want to print the results to a text file, like so:

Link, Zelda
Cloud, Barrett, Tifa
Mario, Luigi
Luigi, Mario

I know how to do this theoretically, but I just need help with the PHP syntax. Thanks very much.

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!

Nothing fancy, I am just looking for a simple php script that would check a text file on my server that has a bunch of domains in it. I want the script to ping a random 5 or 10 sites in the list, and then redirects the visitor to the fastest one, or at least one that responds, and is not down. If all that are checked are down, then it would need to recheck the list.

I want it to pick random domains out of the list, as I don't want users directed to the same site(s) all the time, just because it pings the best.

When I say ping, I am talking about making sure the user isn't directed to a website that is down, or a slow loading website. I am not sure if "ping" is the correct word for that tho?

Can anybody offer me code like this for free, or is this type of request getting into something that would need to be paid code? (and if so, any idea how much? I didn't post this in the freelancing forum, because it sounds like it might be something simple to code.

I am not a coder, so I am looking for something complete that I can add to a php file and it works.

Thanks for any code, pointers/problems with this idea, or price quotes (if necessary) on this little script I am looking for.

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

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

 

Im doing a search system and Im having some problems. I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do. I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment. But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table. I need to acess in my news table this fields: id, title, content, link, date, nViews I need to acess in my pages table this fields: id, title, content, link Im trying to do this also with UNION, but in this case Im not having any row returning. Do you see what I have wrong in my code?   <?php
//first I get my $search keyword
$search = $url[1];

$pdo = connecting();
//then I want to show number of returned rows for keyword searched
$readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ?
UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?");
$readALL->bindValue(1,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(2,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(3,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(4,"%$search%", PDO::PARAM_STR);
$readALL->execute();
//I show number of returned rows
echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>';
//If dont return any rows I show a error message
if($readALL->rowCount() <=0){
echo 'Sorry but we didnt found any result for your keyword search.';
}
else{
//If return rows I want to show, if it is a page result I want to show title and link that I have in my page table
//if it is a news result I want to show title and link that I have in my news table and also date of news
echo '<ul class="searchlist">';
$readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4
UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4");
$readALL2->bindValue(1, '1');
$readALL2->bindValue(2, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(3, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(4, "%$search%", PDO::PARAM_STR);
$readALL2->execute();

while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){
echo '<li>';
echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>';
echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>';
//if it is a news result I also want to show date on my list
//echo '<span id="date">'.$result['date'].'</span>';
echo '</li>';
}
echo ' </ul>';
//but how can I do my select statement to have access to my news table fields and my page table fields??
}
?>

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!

I am trying to write some data from multiple SQL tables to a page. In the first table is a list of places. I then have more tables that are named after the different places. For example, say my first place in the list is called Place1. I have a table named Place1 with data that corresponds to place1. The data contained in the table named Place1 is a list of things to do in this place. It has 21 columns and each one is something to do in the morning, afternoon, and at night for each day of the week in the place Place1. What I am trying to do is display a sort of weekly calendar as a table on a webpage that lists all of the places in one column and then lists seven days of the week as 7 more columns. Then in each data cell I would want to list the things to do in the morning, afternoon and at night for the certain day of the week and for the place. The problem is that I am creating a CMS to allow other users with no coding knowledge to update events for other places, so I have to display data that could have been altered.

The only solution I know of is to do a while loop that gets all of the place names and while there are still place names, write the place names to the page and set a variable equal to the place name. Inside the while loop I would create another while loop that each time the first while loop is executed uses the variable set in the first while loop to know which table to reference and then make a call to that table pulling out the 21 columns and writing them to the page. Each time the outer while loop executes, it would (hopefully) write the place name, and then set the variable as the current place name so that the inner while loop uses the variable to write the rest of the information about that place.

I don't know if that would even work and if it did, I know it cannot be the best way to do this. I am pretty stuck here and don't really have a good solution so if anyone proposes a solution that is radically different to anything I have done, I am open to everything. Thank you!

I require a page to be added to my website. This page will facilitate a refined search via Google, Bing and Yahoo search engine simultaneously , show the top 5 of each engine.

Is this possible using php ?

Thank's