how the hell can I seach a DB????

Anon

I'm a new at php, for the last 3 days I'm trying to create a simple search for my db.
I'm using PHP4/Mysql and would like to get information out of this database.
for example I'm looking for a name of a person and the seach will provide me with the information of this row...
I'm to stupied to do so. I tried to find a simple script on the net but it didn't do the search. I'm desperate please help me. dan

amcgrath

Well, since most posts here regard searching a database of some kind, I'm surprised you weren't able to find anything. Regardless, if you follow these basic steps, you should be able to do it:

Establish a connection to your database (learn the mysql_connect() function.
Format a query against the table you want ("select * from some table")
Send this query to the database using the mysql_query() function.
Manipulate the results however you see fit (mtsql_fetch_array(), mysql_fetch_row(), etc...)

Anon

OK, thanks I'm new at this

The error is:
You have an error in your SQL syntax near 'LIKE '%dd%' LIMIT 0, 30 ' at line 1
Warning: Supplied argument is not a valid MySQL result resource in /home/sites/home/web/resulttest.php on line 17

Warning: Supplied argument is not a valid MySQL result resource in /home/sites/home/web/resulttest.php on line 21

When I do the form search of :

<?
$hostname = "localhost"; // Usually localhost.
$username = "dweibel"; // If you have no username, leave this space empty.
$password = "aysecy72"; // The same applies here.
$usertable = "arona"; // This is the table you made.
$dbName = "arona"; // This is the main database you connect to.

MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database");
@mysql_select_db( "$dbName") or die( "Unable to select database");
?>
<?
//error message (not found message)
$XX = "No Record Found";

// Performing SQL query from a form called test
$query = mysql_query("SELECT * FROM $dbName WHERE $test LIKE '%$search%' LIMIT 0, 30 "); echo mysql_error();
while ($row = mysql_fetch_array($query))

// Printing results in HTML
print "<table>\n";
while ($row = mysql_fetch_array($query)) {
print "\t<tr>\n";
foreach ($line as $col_value) {
print "\t\t<td>$col_value</td>\n";
}
print "\t</tr>\n";
}
print "</table>\n";

//below this is the function for no record!!
//end
?>
If sombody can tell me why it doesn't work
Tanks
Dan

Anon

In your WHERE statement, you refer to a variable $test. You will have to refer to a columnname of your MySQL table. If your table contains the column name "name", and you would like to know the information about user "Mark", your query should look like this:

SELECT * FROM $dbName WHERE name LIKE '%Mark%' LIMIT 0, 30

This query returns each user whos name looks like "Mark". You can also do an exact search, which will return all the information for anyone named "Matk". Your query looks likes this then:

SELECT * FROM $dbName WHERE name='Mark' LIMIT 0, 30

Anon

thanks,
however my gool with this script is to search not only one table but the full database. So people navigating can search the database for Mark and get the information printed about all the row information of mark on a webpage.
Am I on the right track?
Thanks
Dan

yendor

Wipking,

First of all you probably already know that these errors all point to an error in your query statement.

Warning: Supplied argument is not a valid MySQL result resource in /home/sites/home/web/resulttest.php on line 17

Warning: Supplied argument is not a valid MySQL result resource in /home/sites/home/web/resulttest.php on line 21

$query = mysql_query("SELECT * FROM $dbName WHERE $test LIKE '%$search%' LIMIT 0, 30 "); echo mysql_error();

It appears as thought the syntax is correct, but you may be using the wrong variables. If you want info from multiple tables in a database you have to specify that. You can't just do one select for a whole database.

In your query you're using $dbName and $test. If $dbName is really the name of your DATABASE it should be the name of your TABLE or TABLES. And $test should be the FIELD NAME from the TABLE(S) you are selecting the data from.

Here's an example what I'm talking about.

$query = mysql_query("SELECT * FROM table1,table2,table3 WHERE $fieldname LIKE '%$search%' LIMIT 0, 30 "); echo mysql_error();

Hope this helps,

Rodney

Anon

ok, ok I get closer to the answer, however I have more than one fieldname. Can I also add more field name with , ?
Like this
$query = mysql_query("SELECT * FROM $dbName WHERE protocollo,test,db, LIKE '%$search%' LIMIT 0, 30 "); echo mysql_error();
Thanks
Dan

amcgrath

Wip, you can't blanket check the fields this way. You have to do them individually:

SELECT * FROM $dbName
WHERE protocollo LIKE '%$search%
AND test LIKE '%$search%
AND db LIKE '%$search%' LIMIT 0, 30

and this assumes protocollo test and db are columns in the $dbName table.

yendor

Close, but you will have to do it like this.

$query = mysql_query("SELECT * FROM $dbName WHERE protocollo LIKE '%$search%' and protocollo=test and protocollo=db LIMIT 0, 30 "); echo mysql_error();

OR if it makes more sense!

$query = mysql_query("SELECT * FROM $dbName WHERE protocollo LIKE '%$search%' and test LIKE '%$search%' and db LIKE '%$search%' LIMIT 0, 30 "); echo mysql_error();

You can also use OR's in your statment like this example

$search = "Sam";

$query = mysql_query("SELECT * FROM table1,table2 WHERE fname like "%$search%" OR lname like "%$search%" or phone like "%$search%"; echo mysql_error();

This would return all rows from tables 1,2,3 where their fields lname, OR fname, OR phone are equal to Sam;

You just have to follow the SELECT Syntax and adapt it to your database structure. If your tables have fields with the same name you have to specify which table to match the query with otherwise you will get an ambiguity error.

If my explaination is not clear you might try.
http://www.mysql.com/doc/S/E/SELECT.html

It's the syntax structure for MySQL, but it follows the basic SQL rules.

yendor

Sorry! To aviod confusion and correct myself.
In the example I gave for the select statement using OR. It would only return rows from tables 1,2 because I didn't table3 in my query. DUH 🙂