How do I search for data, then display?

visevo

[FONT="Tahoma"]
Hi,
I'm trying to write a php script that allows a webmaster to search for data located within a column (in any row) in a specified table, then display the entire row. Is this possible? I'm still fairly new to PHP and have been reading up on it quite a bit. Here's what I have come up with thus far, but its where I get stuck.

<?
$username="user";
$password="pass";
$database="database";
$table="tablename";

mysql_connect(localhost,$username,$password) or die("Unable to connect to MySQL");
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM $table";

// Now I'm lost.

?>

Once I get that figured out, I want to format the output information into a form so that the administrator can change what he wants, then resubmit it.

Any tips, help, and suggestions would be wonderful.

Thanks

[/FONT][/SIZE]

Roger_Ramjet

Read up about WHERE clause

big_nerd

You can do it in a series of ways.

Here is one way..

if you want to display the "most like" way, as in how a search engine would display the data...


$variable = $_POST['search_str'];

$query = "SELECT DISTINCT * from tablename where column1 like '%$variable%' "

$result = mysql_query($query);

while ($row = mysql_fetch_object($result)) {
	echo "Here is your data: $row->column1";
	echo "Here is your data2: $row->column2";
	echo "Here is your data3: $row->column3";
}

A Breif Explaination:

The DISTINCT keyword is used to return only distinct (different) values,

The "like" statement will allow the query to be a little loose with what it gives, this may display MORE results then you like, however.

Some Useful Places to Look:

http://en.wikipedia.org/wiki/SQL_injection

The "like" is slow if you are going to have a ton of results, there is a faster way as well.

http://jeremy.zawodny.com/blog/archives/000576.html

This is just a start, you can do string manipulation before executing the query to narrow down results (based on the words provided from the previous form)

Good Luck.

big.nerd

visevo

Okay, I've got it searching through the column, but it's displaying everything. Not displaying what I am searching for. Here's the code that I came up with.

<?
$username="user";
$password="pass";
$database="database";
$table="tablename";

mysql_connect(localhost,$username,$password) or die("Unable to connect to MySQL");
@mysql_select_db($database) or die( "Unable to select database");
$variable = $_POST['search_str'];

$query = "SELECT DISTINCT * from column where name like '%$variable%'";

$result = mysql_query($query);

while ($row = mysql_fetch_object($result)) {
    echo "Here is your data: $row->column";
} 

$num=mysql_numrows($result);


echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$name=mysql_result($result,$i,"name"); // <- $name is defined as a mysql query result.

echo "<b>$name<br><hr><br>"; // displays the result

$i++;
}
mysql_close(); // close the connection

?>

Suggestions to make the search specific?

big_nerd

How much are you planning on displaying?

while ($row = mysql_fetch_object($result)) {
    echo "Here is your data: $row->column";
}

This section displays the entire row.

If you search for "dogs" (if the column was named animals) it will display everything that has dogs in that column animals.

If you wish to parse the text further, you would have to bring the data in as a variable.

while ($row = mysql_fetch_object($result)) {
    $column_data = "$row->column";
}

You could then parse $column_data a little further.

If you want a suggestion.. I would need a little more info, or an example of what the data field would look like, and what you want to pull from it.

If you are worried about security, make something up!

You can always check out PHP's string functions: http://php.net/strings

Perhaps there is something in there that is useful?

big.nerd

visevo

Okay I finished the script, it works great. Here's what it looks like:

<?php
include 'config.php';
$item=$_GET['name'];

mysql_connect($host,$username,$password);
@mysql_select_db($db_name) or die( "Unable to select database");
$var = $_POST['name'];
$none="No records found.";
$result = mysql_query("SELECT * FROM item_template WHERE (name LIKE \"%$item%\")");

echo mysql_error();

while($row = @mysql_fetch_array($result)){
echo "<form action=\"results.php\" method=\"POST\">";
echo "<input type=\"text\" name=\"id\" size=\"10\" value=\""; echo "$row[entry]\"> &nbsp;";
echo "$row[name]&nbsp; <input type=\"submit\" name=\"Choose\"><br>";
}
if (!$item)
{
print ("$none");
}
?>

So this is the basic search engine that points to a results page. The results page displays the search information that the user selected from the search in a form, the user chooses edits what he wants to edit, then clicks the 'save' (submit) button, which then takes you to the processing page.

The processing page puts the POST data from the results page into an SQL query that is to be reinserted into the database. Now here's the problem: I can't use the mysql query:

INSERT INTO `database` (table1, table2, table3)
VALUE
(value1, value2, value3)

because you're editing a current row. To be exact, there are 106 columns in each row. I have a variable for each column. My question is, how do I replace the entire row? Here's my script "process.php" (most variables removed):

<?
include 'config.php';

mysql_connect($host,$username,$password) or die('Cannot connect to Database');
@mysql_select_db($db_name) or die( "Unable to select database");
$var = $_POST['entry'];
$XX="No records found.";
$result = mysql_query("SELECT * FROM item_template WHERE (entry LIKE \"%$var%\")");

echo mysql_error();

while($row = @mysql_fetch_array($result)){

$entry=$_POST['entry'];
$class=$_POST['class'];
$subclass=$_POST['subclass'];


$insert="INSERT INTO item_template (Entry, Class, Subclass)";

// this doesn't work, it deletes the $row[table] before $vari can be inserted.
mysql_query("DELETE from item_template WHERE entry = $entry") or die('Could not remove old entry');

// Setting the variable to array in this order //
$vari="($entry,$class,$subclass);";
	// Remove the slashes //
$vari = stripslashes($vari);

// First part of SQL script //
mysql_query("$insert $value $vari") or die('Error: Query Failed');

echo "
<strong>Success!</strong><br>
The following query was inserted into your database:<br>
$insert<br>
$value<br>
$vari<br>";
}
?>

I really appreciate your help in this

big_nerd

Sorry fro the late reply, Work has been hectic to say the least! (again, the sole php programmer there).

For modifying an existing row, you should technically have an identifier column, I like "idNum" or "id_num", with auto_increment (and obviously set as the primary key).

This way you can grab the specific row you wish to edit.

106 Columns! Wow, Your fingers are probably going to hurt when you are done.

I can't think of any automated way of updating rows, but the standard way is:

$query = "UPDATE table SET column1 = '$data1', column2 = '$data2', ";
// ... 102 more of these ..
$query = $query." column105 = '$data105' column106 = '$data106' WHERE id_num = '$your_identifier_variable'";

If you do not have an identifier, you should use a unique column, and practice with using "WHERE xxx LIMIT 1" to limit to one affected row (pending you wish for this to happen)

I Hope this helps!