I know this is pretty general... (how to post search results)

[xcandiottix]

This is probably a pretty basic question but before I tackle it I figure I'll ask the best way to go about it.

 

I'm working on a site in which someone can submit a form as well as a photo with the form. My php script successfully stores the data into my database with both the results of the form as well as where the picture is uploaded to on the server.

 

I would now like to add a 'search' feature so that someone could search for an item and get a page that shows the best matching uploads. Are there any good how to's out there that I can start with?

 

Thanks

-Keith

[scotmcc]

Well Sir,

 

I think you need to be a little more specific if you'd like a real answer... However, based on what I guess that you're asking (keep in mind this is just a little pseudo-code):

 

// ... I assume you have some input from the user, probably through $_REQUEST

$keywords = explode(',', $user_input_keywords);

/*
    You will probably want to do some validation 
*    here on the list of keywords the user has entered. 
*    Also, I imagine you'll need to do some formatting 
*    for the MySQL query below.
*/

// ... I also assume you have a keyword section in your database.

$query = "SELECT [ field ] FROM [ table ] where 
    `keyword` in (" . implode(',', $keywords) .")";

// Then do some stuff with the data you get back.

 

There are several informative posts for MySQL here: http://us.php.net/manual/en/book.mysql.php

[xcandiottix]

Well, not so much a key word per say. Say my table is as so:

 

Name | Birth Date | Email        | Picture

Joe        1/1/01      a@b.com    c:/uploads/cat.jpeg

Tom        2/2/01      c@d.com    c:/uploads/dog.jpeg

 

Now I'd like a search box that will look in column Name or Birth Date but not Email or Picture.

 

Search: Joe

 

Result:

[loads cat.jpeg]

Joe        1/1/01      a@b.com

 

The data that will be submitted by users is optional, not from a drop down box. So my search would need flexibility. Also, I'd like to have it make educated guesses. So if someone searches

 

Tommy

 

the search result would be something like:

Tommy not found

Related results

[c:/uploads/dog.jpeg]

Tom        2/2/01      c@d.com

 

 

[xcandiottix]

<?php

// This could be supplied by a user, for example

$firstname = 'fred';

$lastname  = 'fox';

 

// Formulate Query

// This is the best way to perform a SQL query

// For more examples, see mysql_real_escape_string()

$query = sprintf("SELECT firstname, lastname, address, age FROM friends WHERE firstname='%s' AND lastname='%s'",

    mysql_real_escape_string($firstname),

    mysql_real_escape_string($lastname));

 

// Perform Query

$result = mysql_query($query);

 

// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

if (!$result) {

    $message  = 'Invalid query: ' . mysql_error() . "\n";

    $message .= 'Whole query: ' . $query;

    die($message);

}

 

// Use result

// Attempting to print $result won't allow access to information in the resource

// One of the mysql result functions must be used

// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.

while ($row = mysql_fetch_assoc($result)) {

    echo $row['firstname'];

    echo $row['lastname'];

    echo $row['address'];

    echo $row['age'];

}

 

// Free the resources associated with the result set

// This is done automatically at the end of the script

mysql_free_result($result);

?>

 

This was on the link you sent.. seems to be what I need to start with.

[scotmcc]

Well, one way to do something *like* that might be to get a list of distinct values (name, for instance) before the user even enters his data. Then, using JavaScript you could simply display a dropdown keyed from a text box that the user can type into, quickly eliminating the words in the dropdown that do not match a regex value...

 

OR

 

You could create a loop in PHP that loops over the results that match name one character at a time until the number of results are manageable... for instance:

 

foreach($letter in explode($user_name_entered)) {
    $SQL = "SELECT [ field ] FROM [ table ] WHERE [ field ] LIKE '%".$letter."'";
    // get the result
    if (mysql_num_rows($result) < 10) {
        // return list;
    }
}

 

A lot more processing for the second one...

[xcandiottix]

Ah, yes that's pretty smart. One problem I'm foreseeing is that a user may upload, for example a picture of his Chevrolet Corvette. A second user then wants to find a picture of one and searches for:

 

Car Type: Chevy

Model: Vette

 

So I believe that both of your examples would work. I'm out of my comfort zone using Java and just starting to dabble in php/mysql. Flash and XML are my specialties so I'm trying to adjust to php's functions. The second example you gave, all though more process intensive, would support the site early on as the database would grow slowly. That might give me time to figure out how to java code the drop downs.

 

thanks for the great advice so far!

[scotmcc]

That is an easy fix, though... Just make sure you allow them to search for any part of the string. Also, JavaScript (not Java  ) is very simple to use. I'd imagine you could probably implement the JS solution in a few hours, even if you are not familiar with the language.

 

foreach($letter in explode($user_name_entered)) {
    // See the % before and after the $letter. That will
    // search the entire string for a like string.
    $SQL = "SELECT [ field ] FROM [ table ] WHERE [ field ] LIKE '%".$letter."%'";
    // get the result
    if (mysql_num_rows($result) < 10) {
        // return list;
    }
}

[xcandiottix]

Thanks for the help. I haven't added the java yet but I do have a bare bones that works pretty well right now. Here's the code if anyone else would find it useful:

 

search.html :

 

<html>

 

<table width="100%" border="0">

  <tr>

    <td width="20%"> </td>

    <td width="60%"><h2 align="center">Search</h2>

<form name="search" method="post" action="result.php">

  <div align="center">Seach for:

  <input type="text" name="find" /> in

  <Select NAME="field">

    <Option VALUE="N">N </option>

    <Option VALUE="E">E</option>

    <Option VALUE="M">M</option>

    <Option VALUE="A">A</option>

    <Option VALUE="H">H</option>

    <Option VALUE="T">T</option>

  </Select>

  <input type="hidden" name="searching" value="yes" />

  <input name="search" type="submit" value="search" />

  </div>

</form></td>

    <td width="20%"> </td>

  </tr>

</table>

</html>

 

result.php:

 

<?php

$con = mysql_connect("dbase address","database","DB pw");

mysql_select_db("database", $con);

 

$field = $_POST['field'] ;

$find = $_POST['find'] ;

$searching = $_POST['searching'] ;

 

if ($searching =="yes")

{

echo "<h2>Results</h2><p>";

 

 

if ($find == "")

{

echo "<p>You forgot to enter a search term";

exit;

}

 

$find = strtoupper($find);

$find = strip_tags($find);

$find = trim ($find);

 

$data = mysql_query("SELECT * FROM table WHERE upper($field) LIKE'%$find%'");

 

echo '<table width="100%" border="0">';

echo  "<tr>";

echo    '<th width="11%" scope="col">N</th>';

echo    '<th width="11%" scope="col">E</th>';

echo    '<th width="11%" scope="col">M</th>';

echo    '<th width="11%" scope="col">A</th>';

echo    '<th width="11%" scope="col">H</th>';

echo    '<th width="11%" scope="col">T</th>';

echo  '<th width="11%" scope="col">C</th>';

echo  "</tr>";

echo "</table>";

 

while($result = mysql_fetch_array( $data ))

{ 

echo '<table width="100%" border="0">';

echo  "<tr>";

echo    '<th width="11%" scope="col">' . $result['N'] . '</th>';

echo    '<th width="11%" scope="col">' . $result["E"] . '</th>';

echo    '<th width="11%" scope="col">' . $result["M"] . '</th>';

echo    '<th width="11%" scope="col">' . $result["A"] . '</th>';

echo    '<th width="11%" scope="col">' . $result["H"] . '</th>';

echo    '<th width="11%" scope="col">' . $result["T"] . '</th>';

echo  '<th width="11%" scope="col">','<img src="http://www.website.com/photos/' . $result["C"] . '"', 'width="140" height="115" />','</th>';

echo  "</tr>";

echo "</table>";

}

 

$anymatches=mysql_num_rows($data);

if ($anymatches == 0)

{

echo "Sorry, but we can not find an entry to match your query<br><br>";

}

if ($anymatches > 1)

{

echo "One or more results found.";}

echo "<b>Searched For:</b> " .$find;

}

?>

 

The final product creates a table with search results and a preview picture for each result.

 

Thanks!

 

[xcandiottix]

Currently, the site i'm working on allows a user to upload information and a picture. The information and photo location are saved into the mysql database. Another user can access the site and search through the various uploads. When the second user searches, a table is displayed with the closest results per their search. Also, a thumbnail of the uploaded picture is displayed next to the corresponding result. I would now like that second user to click on the thumbnail and be directed to a page with the full size picture as well as a full version of the data uploaded into the database with that picture.

 

So for example let's say I enter into the DB:

Name: John Doe Age: 25 Picture: me.jpeg

 

A second user sarches for the name John, the result:

1 Entry found

Name: John Doe [thumbnail of me.jpeg]

 

---

 

So, can I make the thumbnail an active link? And then does it point to a PHP or a HTML page for the results? I'd imagine a php page will be better then having to create individual HTML pages for each result. I think I have a good idea of where to go with this but I'd like some input beforehand to make sure I'm on the right track.

 

Thanks much,

K.Candiotti

[trq]

This example I posted a while back should get you on your way. The concept is basically the same.

[xcandiottix]

Thanks!

[xcandiottix]

Alrighty... well the above link for the code did have some small errors that slowed my progress but success!! It perfectly put me where I wanted to be. Thanks! ... For my next task:

 

I would like a browse page that will pretty much  put all of the database information on to one page for people to browse.

 

My table's first column is which number row it is. I'd imagine I could have the php script do a check to see if the column number is >0 and if so to post that row and go to the next. Easier said then done Does anyone know how i can efficiently tackle this?