PHP - Database Search Query Optimization

Hi everyone!

So, this is probably one of the most simplest things to all you pros out there, but unfortunately I think I'm using the wrong words in Google as I can't seem to remember what they're called (Yeah I'm not that brilliant.)

Anyway, a quick one for you guys. What I'm looking for is a simple script or method to send a Search Query to end with a URL. For example.

Lets say I'm on http://mysite.com/index.php, and on that page is a search box. Now, lets say I search the word "Pizza" in that search box. I want the end result to be "http://myspace.com/search.php?s=pizza"

I already have the script I'd like to use to read that value, I just simply don't know how to put that value there in the first.

Yes, I know, noobish question, I'm sorry. XD

Thanks in advance!

Hi All.
I am looking to search a database of products based upon someone typing the name of the product they are looking for.
I am using the below SQL request:

Unfortunately this only shows what ever your typing and anything before or after (%) is there any way that it could also search unplaced string instances?
For example
$q = "SELECT * FROM products WHERE prod_name LIKE '%$id%' order by fee asc LIMIT 20";

If i type black ops ps3 it brings back search result until i get to the PS3 part. because entries in the db are "black ops for the PS3" because i havent conformed it says no results.
I need it to show all results.
is there another way? or can someone point me in the right direction?

Hello,

I can't really explain it so a example will be better:

my_table:

col1  col2  col3  col4
1      1      23     A
2      1      25     A
3      2      21     B
4      3      24     A
5      3      28     A

the query is--
SELECT col3 FROM my_table WHERE col2=SELECT(MAX(col2) FROM my_table WHERE col4='A')

For some reason this is illegal, I can also not use a limit because the number of rows is not known a priori. I remember seeing some sort of redirection in some other forum but would appreciate a more explicit response.

Thanks

This is not legal for some reason -- is there a way

Hi,

i have the following problem. I use a script to enter data in a mysql database and for now i only have three variable...

Quote

<h2>Invoeren</h2>
<form action="insert.php" method="post" class="cmxform">
  <fieldset>
   <legend>Gegevens</legend>
      <table border="2">
      <tr>
         <td>1. </td>
         <td><label for="merk">merk:</label></td>
         <td><input id="merk" name="merk" /></td>
      </tr>
      <tr>
         <td>2. </td>
         <td><label for="cpu">cpu:</label></td>
         <td><select name="cpu" id="cpu" />
            <option value="">Maakt niet uit</option>
            <option value="amd">amd</option>
            <option value="intel">intel</option>
         </select></td>
      </tr>
      <tr>
         <td>3. </td>
         <td><label for="hdd">hdd:</label></td>
         <td><select name="hdd" id="hdd" />
            <option value="">Maakt niet uit</option>
            <option value="160GB">160GB</option>
            <option value="320GB">320GB</option>
            <option value="500GB">500GB</option>
         </select></td>
      </tr>
   </table>
    <input type="submit" value="Verstuur" />
</fieldset> 
</form>



Data goes in to the database correctly but now i use the following search script...

Quote


<h2>zoeken</h2>
<form action="search.php" method="post" class="cmxform">
  <fieldset>
      <legend>Gegevens</legend>
      <table border="2">
      <tr>
         <td>1. </td>
         <td><label for="merk">merk:</label></td>
         <td><input id="merk" name="merk" /></td>
      </tr>
      <tr>
         <td>2. </td>
         <td><label for="cpu">cpu:</label></td>
         <td><select name="cpu" id="cpu" />
            <option value="0">Maakt niet uit</option>
            <option value="amd">amd</option>
            <option value="intel">intel</option>
         </select></td>
      </tr>
      <tr>
         <td>3. </td>
         <td><label for="hdd">hdd:</label></td>
         <td><select name="hdd" id="hdd" />
            <option value="0">Maakt niet uit</option>
            <option value="160GB">160GB</option>
            <option value="320GB">320GB</option>
            <option value="500GB">500GB</option>
         </select></td>
      </tr>
   </table>
    <input type="submit" value="Verstuur" />
  </fieldset>
</form>



The values go correct to the search script because i checked this with an echo function. The search.php loks like this

Quote

<?php
    $con = mysql_connect("localhost","admin","t1tan1um");
    if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("model", $con);
   
   $qry = "SELECT * FROM eigenschappen WHERE 1=1";
      if (isset($_POST['merk']) && $_POST['merk'] != 0) {
      $qry .= " AND merk='" . mysql_real_escape_string($_POST['merk']). "'";
      }
      if (isset($_POST['cpu']) && $_POST['cpu'] != 0) {
      $qry .= " AND cpu='" . mysql_real_escape_string($_POST['cpu']). "'";
      }
      if (isset($_POST['hdd']) && $_POST['hdd'] != 0) {
      $qry .= " AND hdd='" . mysql_real_escape_string($_POST['hdd']). "'";
      }
                  
      $result = mysql_query($qry) or die(mysql_error()); // lelijke foutmelding, zelf mooier maken of $result door een if statement halen
      
      while($row = mysql_fetch_array($result))
      
      {
      echo $row['merk'] . " " . $row['cpu'] . " " . $row['hdd'];
      echo "<br />";
      }
?>



When i search for hdd wich is last in de search list the query only returns the records wich contain that value. When the condition has to be for example Intel cpu AND 500GB it returns everyting with 500GB but also every kind of CPU.

Anyone?

This is for a faux stock photo website. When I only use the $query string to search for $searchSubject, everything goes golden. Once I put everything else in there though, the search function will not work at all.

Any help will be greatly appreciated. I've been staring at this for too long and it's starting to give me a headache.

PS. This is my first post here. Seems like a great community!

<?php

$searchSubject = $_POST['searchSubject'];
$searchPhotoname = $_POST['searchPhotoname'];
$searchLocation = $_POST['searchLocation'];

if (isset($_POST['search'])) {
   
   //Select records from the database

$query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%' OR `name` LIKE '%" . $searchPhotoname . "%' OR `location` LIKE '%" . $searchLocation . "%' ";






} else {
   
   $query = "SELECT * FROM photos";
   
}

$result = mysql_query($query);
// count the number of rows in the database
$num = mysql_num_rows($result);

?>



If I leave it at this:


$query = "SELECT * FROM `photos` WHERE `subject` LIKE '%" . $searchSubject . "%'";

Then everything works perfect.

Hi All,

Just a couple of things with my site search, I am having trouble getting the search results to pickup any and all keywords. For example, if keywords for a search result are listed in the keywords column of my db as 'one, two, three' a search query for 'one two' is fine but a search for 'one three' will not display the search result. Instead of treating the keywords seperately it is treating all keywords as a whole phrase.

How can I make the search results pickup any keyword regardless of order.

Secondly I'm having trouble getting the search results to display by keyword relevance, any help is greatly appreciated.

Thanks.

Code: [Select]


function search()
{
parent::Controller();
$this->load->model('Templating');
$this->load->model('Company');
}

function index()
{
$this->load->view('search');
}
function search_redirect()
{
if ($_POST['keyword'] == '') $_POST['keyword'] = 'Keyword';
redirect('search/showlist/'.
urlencode(underscore($_POST['location'])).'/'.
urlencode(underscore($_POST['category'])).'/'.
urlencode(underscore($_POST['keyword'])));
}
function showlist($location = 'any', $category = 'any', $keyword = 'keyword',  $offset = '0')
{
/* get results */

$data['companies'] = $this->Company->search($keyword, $category, $location, $offset, $name);
/* paginate results */
$row = $data['companies']->row();

if($keyword == 'keyword' and $category=='any' ) {
$data['categoryList'] = $this->buildCategoryList($location);
}
elseif(isset($row->categoryId)) {
$categoryId = $row->categoryId;
$data['linkList'] = $this->buildRefineList($categoryId);
}
$this->load->library('pagination');
$config['base_url']    = site_url().'search/showlist/'.$location.'/'.$category.'/'.$keyword.'/';
$config['total_rows']  = $this->Company->total_companies;
$config['per_page']    = $this->Company->per_page;
$config['num_links']   = 3;
$config['uri_segment'] = $this->uri->total_segments();
$this->pagination->initialize($config);

$data['pagination'] = $this->pagination->create_links();
$data['logged_in'] = $this->session->userdata('logged_in');
$data['company_id'] = $this->session->userdata('company_id');
$data['search_category'] = $category;
$data['search_location'] = $location;
if ($this->session->userdata('admin') != ''){
$data['admin'] = $this->session->userdata('admin');
}

/* initialise template settings */
$center = 'center/list_companies';
$this->load->vars($data);
$this->Templating->show($center);
}

I'm trying to build a search function that will search all columns in a table. Here is what I've got:

Code: [Select]
if (isset($_POST['submitted'])) {
$q = "SELECT * FROM children ";

if(isset($_POST['query'])) {
$q .= "WHERE ";

$all = "SELECT * FROM children WHERE 1";
$r_all = mysqli_query($dbc, $all);

while ($field = $r_all->fetch_field()) { // Get field headers for building query
$f_name = "{$field->name} LIKE {$_POST['query']},:"; // Build query with search string
$fn = explode(":",$f_name); // Need to convert $f_name to an array but this does not convert it to a single array but multiple arrays

$q .= implode("OR", $fn); // Need to insert OR for the db query

} // End while ($field = $r_all->fetch_field())

} // End if(isset($_POST['query']))

} // End if (isset($_POST['submitted']))

The explode function is what is not working for me. It gives me this (sam is the search string I entered into the form):

Code: [Select]
Array ( [0] => child_id LIKE sam, [1] => ) Array ( [0] => first_name LIKE sam, [1] => ) Array ( [0] => second_name LIKE sam, [1] => ) Array ( [0] => last_name1 LIKE sam, [1] => ) Array ( [0] => last_name2 LIKE sam, [1] => ) Array ( [0] => gender LIKE sam, [1] => ) Array ( [0] => dob LIKE sam, [1] => )

so instead of getting one array with each string being a value I get multiple arrays. Any ideas?

On my search result page, I wanted to search for "Nintendo DS Lite - Pink"

I used following code:

Ex:
Code: [Select]
$search_text = "Nintendo DS Lite Pink";

$kt=split(" ",$search_text);//Breaking the string to array of words

// Now let us generate the sql
while(list($key,$val)=each($kt))
{
if($val<>" " and strlen($val) > 0)
{
$q .= " name like '%$val%' or ";
}
}// end of while

//Remove the last 'OR'
$q=substr($q,0,(strlen($q)-3));



Than the $q would be:
Code: [Select]
SELECT * FROM `products`
WHERE 
name like '%Nintendo%' or 
name like '%DS%' or 
name like '%Lite%' or 
name like '%Pink%'


And i am getting Mysql Output given below:


1) Activity Meter - DS.
2) Nintendo DS Red.
3) Nintendo DS Lite Pink.
4) Nintendo DS Lite Turquoise.

But the third result is most accurate/relevant then first two result.

Please help me out to get the most accurate row first then the relevant rows as per their relevancy with search term "$search_text"

Many Thanks  in Advance.

Hi guys,
 I have a php file that will go to a site and scrape the data I need. However this site is setup to use pagination so when I try to scrape all the players names I have to do separate queries to search each page. Is there a way to find out by using code how many pages there are and query all the pages at same time?

I use  this code
Code: [Select]
<?php //first page

//turn error reporting on
libxml_use_internal_errors(true);

//get data from this page
$dom = new DOMDocument;
$dom->loadHTMLFile('http://www.gametracker.com/server_info/76.73.3.42:1716/top_players/?searchipp=50#search');
$xpath = new DOMXPath($dom);

// Get the total player count
$rows2 = $xpath->query('//div[@class="block774"]/div');

// Get the rows from the search list
$rows = $xpath->query('//table[@class="table_lst table_lst_spn"]/tr');


for ($i=1; $i<$rows->length-1; $i++) {
    $row = $rows->item($i);

    // Get the columns for a row
    $cols = $row->getElementsByTagName('td');

// Get the player rank (1st column)
    echo 'Rank:'.trim($cols->item(0)->textContent).PHP_EOL;

    // Get the player name (2nd column)
    echo 'Name:'.trim($cols->item(1)->textContent).PHP_EOL;

// Get the player score (3rd column, actually 4th but number 3 is hidden)
    echo 'Sco '.trim($cols->item(3)->textContent).PHP_EOL;

echo "<br />";
}
?>





<?php //secondpage

//turn error reporting on
libxml_use_internal_errors(true);

//get data from this page
$dom = new DOMDocument;
$dom->loadHTMLFile('http://www.gametracker.com/server_info/76.73.3.42:1716/top_players/?searchipp=50&searchpge=2#search');
$xpath = new DOMXPath($dom);

// Get the rows from the search list
$rows = $xpath->query('//table[@class="table_lst table_lst_spn"]/tr');


for ($i=1; $i<$rows->length-1; $i++) {
    $row = $rows->item($i);

    // Get the columns for a row
    $cols = $row->getElementsByTagName('td');

// Get the player rank (1st column)
    echo 'Rank:'.trim($cols->item(0)->textContent).PHP_EOL;

    // Get the player name (2nd column)
    echo 'Name:'.trim($cols->item(1)->textContent).PHP_EOL;

// Get the player score (3rd column)
    echo 'Sco '.trim($cols->item(3)->textContent).PHP_EOL;
echo "<br />";
}

?>
I also have to go to that website first to see how many pages there are so I can have enough queries.

hi all,
in mysql, how can i extract 100 words before and 100 words after my search key?
example:

"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla est nibh, mattis eu mattis id, pulvinar eu augue. Duis ut sem nisi. Sed id ante sed orci vestibulum lacinia ac id nibh. Donec cursus, elit eget auctor semper, ipsum eros laoreet quam, nec ullamcorper"

vestibulum is my search key

After a few days of searching and running across a problem what is almost like mine on these forums (cant seem to find the link now) where the person was looking to search a database using checkboxes.

What I am trying to do is use a list of checkboxes (http://www.animeseason.com/anime-list/categories/) exactly like this link and search my table which has the genres as columns.

I am new to PHP and don't really have a starting point. I have taken a bunch of examples and came up with this.

My HTML form with a list of all my checkboxes.
Code: [Select]
<form method="post" ACTION = "php/do_search.php">
                           <ul class="cat_col">
                               <li><input type="checkbox" name="cat_check[]" value="1"> <a href="#Action">Action</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="2"> <a href="#Adventure">Adventure</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="3"> <a href="#Comedy">Comedy</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="4"> <a href="#Demons">Demons</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="5"> <a href="#Drama">Drama</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="6"> <a href="#Ecchi">Ecchi</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="7"> <a href="#Fantasy">Fantasy</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="8"> <a href="#Harem">Harem</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="9"> <a href="#Horror">Horror</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="10"> <a href="#Magic">Magic</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="11"> <a href="#Martial Arts">Martial Arts</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="12"> <a href="#Mecha">Mecha</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="13"> <a href="#Music">Music</a></li>
                           </ul>
                            <ul class="cat_col">
                               <li><input type="checkbox" name="cat_check[]" value="14"> <a href="#Mystery">Mystery</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="15"> <a href="#Ninja">Ninja</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="16"> <a href="#Parody">Parody</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="17"> <a href="#Psychological">Psychological</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="18"> <a href="#Reverse Harem">Reverse Harem</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="19"> <a href="#Romance">Romance</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="20"> <a href="#Samurai">Samurai</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="21"> <a href="#School Life">School Life</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="22"> <a href="#Science Fiction">Science Fiction</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="23"> <a href="#Seinen">Seinen</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="24"> <a href="#Shoujo">Shoujo</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="25"> <a href="#Shounen">Shounen</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="26"> <a href="#Slapstick">Slapstick</a></li>
                           </ul>
                            <ul class="cat_col">
                               <li><input type="checkbox" name="cat_check[]" value="27"> <a href="#Slice of Life">Slice of Life</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="28"> <a href="#Sports">Sports</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="29"> <a href="#Supernatural">Supernatural</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="30"> <a href="#Thriller">Thriller</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="31"> <a href="#Tragedy">Tragedy</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="32"> <a href="#Vampire">Vampire</a></li>
                           </ul>
                           <ul class="cat_col">
                                <li><input type="checkbox" name="cat_check[]" value="33"> <a href="#Movie">Movie</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="34"> <a href="#OVA">OVA</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="35"> <a href="#Top Rated">Top Rated</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="36"> <a href="#Currently Airing">Currently Airing</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="37"> <a href="#Ended">Ended</a></li>
                                <li><input type="checkbox" name="cat_check[]" value="38"> <a href="#Ongoing">Ongoing</a></li>
                           </ul>
                      <div id="submit_btn_pos">
                      <input type="submit" name="search" value="Lets Make Magic" class="submit_btn">
                            <>
                        </form>

My PHP script that right now seems to do nothing.

Code: [Select]
<?php
//if we got something through $_POST
if (isset($_POST['cat_check'])) {
    $con = mysql_connect("localhost","USERNAME","PASSWORD") or die("Connection error");
mysql_select_db("DATABASENAME")or die("database doesn't exist");

// sanitize user input
$cleanData = array();
foreach($_POST['cat_check'] as $_comp) {
    //$cleanData = mysql_real_escape_query($_comp);
}

// Build query
  $compStr = implode("'), ('", $cleanData);
  $sql = "SELECT anime_name WHERE $_comp='1'";
  
}
?>
My database has a layout of "Name of show, (every genere listed above)".

Thanks for any help.

Hi i am an absolute PHP/MYSQL Novice but i guess we all gotta start somewhere.

I have a table in my database with names and addresses, f_name, s_name, street, zip_code, city etc etc etc

What i want to do is display a drop down of all cities in my database then click on any city in the list to display all the details of people in that city, showing all fields.

So i have got this far ..
Code: [Select]
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'cities_wrdp1';
mysql_select_db($dbname);


$sql = "SELECT DISTINCT City FROM PBM";
$result = mysql_query($sql);

echo "<select name='City'>";
while ($row = mysql_fetch_array($result)) {
    echo "<option value='" . $row['City'] . "'>" . $row['City'] . "</option>";
}
echo "</select>";


This produces the nice drop down list of cities from the database ...

Now on click i would like to return results similar to this 

Code: [Select]
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'cities_wrdp1';
mysql_select_db($dbname);


$query = sprintf("SELECT * FROM PBM");

$result = mysql_query($query);


if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

echo "<br />";
echo "<table border='0'>";


while ($row = mysql_fetch_assoc($result))
{
echo "<tr><td>";
echo "<a href=\"" .$row['profileURL'] . "\">
<img src=\"" . $row['faviconURL'] . "\" border=0 alt=\"" . $row["Thumbnail"] . "\">
</a>";
echo "</td><td align='center'>";
echo $row['f_name'];
echo "</td><td align='center'>";
echo $row['s_name'];
echo "</td><td align='center'>";
echo $row['number'];
echo "</td><td align='center'>";
echo $row['street'];
echo "</td><td align='center'>";
echo $row['zip'];
echo "</td><td align='center'>";
echo $row['city'];
echo "</td><td align='center'>";
echo $row['Country'];
echo "</td><td align='center'>";
echo $row['email_address'];
echo "</td><td align='center'>";
echo $row['fb'];
echo "</td><td align='center'>";
echo $row['web_site'];
echo "</td><td align='center'>";
echo $row['phone'];
echo "</td><td align='center'>";
echo $row['land'];
echo "</td><td align='center'>";
echo $row['age'];
echo "</td></tr>";
}

echo "</table>";


mysql_free_result($result);

So my question is .. how do i make the first piece of code selectable to produce similar results to the second piece of code based on the city selected ?
Thanks for any help .. and please excuse my ignorance ..

Hello all. First of all, if this isn't the right place for this topic, please excuse me and may the moderators/admins move it where it should belong.

Second, I'm trying to make a mysql search - Everything works, but if say, a user searches for 34 it displays all numbers that have the number 34 in them.

I want the results to be exact - if a user searches for 34, to display only 34, not 334, 5034 or 3401 (like it happens now).

Here's the code:

Code: [Select]
<?php
mysql_connect ("**************", "*******","*****")  or die (mysql_error());
mysql_select_db ("********");
 
$term = $_POST['term'];
 
$sql = mysql_query("select * FROM countries WHERE cocode LIKE '%$term%'");
 
while ($row = mysql_fetch_array($sql)){
    echo '<br/> Code: '.$row['cocode'];
    echo '<br/> Country: '.$row['coname'];
    echo '<br/><br/>';
    }
 
?>
If you have any questions, you're welcome to ask them in the thread. 

Thank you in advance  

Hello,

I have a search engine on my website that uses the following code:
Code: [Select]
//SQL FOR *SEARCH.PHP

$table = "videos";
  $search = $_GET['q'];
  // explode search words into an array
  $arraySearch = explode(" ", $_GET['q']);
  // table fields to search
  $arrayFields = array(0 => "title", 1 => "titletext", 2 => "tags", 3 => "originaltitle", 4 => "url");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $sql = "SELECT * FROM ".$table."  WHERE status ='1' AND (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $sql = $sql."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $sql = $sql." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $sql = $sql.") OR (";
    }
  }
  $sql = $sql.") ORDER BY (videos.up-videos.down) DESC
LIMIT $start, $limit";

$result = mysql_query($sql);


My problem is that the SQL Query includes even videos that have STATUS='0' .. I only want the STATUS='1' to be shown.. How come? Thanks for help

Need some help I have 2 tables in a database and I need to search the first table and use the results from that search, to search another table, can this be done? and if it can how would you recommend that I go about it?

Thanks For Your Help Guys!