Searching database using input from form

[Grenz]

Hi all
I have a MySQL database where different forms post data.
I want to be able to have users search the database, using a form, and get output showed that match their search criteria.
So far I have only build the code and I dont worrry about styling until the code works.

This is my form: (url = /test-sog-form)

<form action="/test-sog" method="post">
<input type="text" /><br />
<input type="submit" value="search" />
</form>

This is my PHP: (url = /test-sog)

<?php
$search = mysql_real_escape_string($_POST["search"]);
$con = mysql_connect("localhost","grenzjob_grenzjo","#*$!");
if (!$con) { // checking of connection errors
die('Could not connect: ' . mysql_error());
}
else {
mysql_select_db("grenzjob_grenzjobber", $con);
$result = mysql_query("SELECT * from profile WHERE firstname LIKE '%$search%'");
if (mysql_num_rows($result) > 0) { // checking that there is a result
echo $result;
}
else {
echo "Sorry! Couldn't find match.";
}
}
?>

The result of entering anything in the search form is:

"Resource id #22"

What is wrong?

[STeeL]

First, you should add name tag to your text field:

<input type="text" name="search"/><br />

Replace "echo $result;" with:

while ($row = mysql_fetch_assoc($result)) {
echo "<pre>"; print_r($row); echo "</pre>";
}

It will echo every row in array format. You can use "echo $row['column_name'];" to echo specific column

[Grenz]

It worked - THANKS A LOT! :-)

[Grenz]

Hi again
Now I wanted to expand the function to the actual purpose but something went wrong in the process.

This is my expanded PHP code:

<?php
$city = mysql_real_escape_string($_POST["city"]);
$country = mysql_real_escape_string($_POST["country"]);
$education = mysql_real_escape_string($_POST["education"]);
$language = mysql_real_escape_string($_POST["language"]);
$branche = mysql_real_escape_string($_POST["branche"]);
$con = mysql_connect("#*$!","#*$!","#*$!");
if (!$con) { // checking of connection errors
die('Could not connect: ' . mysql_error());
}
else {
mysql_select_db("#*$!", $con);
$result = mysql_query("SELECT * from profile WHERE city LIKE '%$city%'
AND country LIKE '%$country%'
AND education LIKE '%$education%'
AND language LIKE '%$language%'
AND branche LIKE '%$branche%'");
if (mysql_num_rows($result) > 0) { // checking that there is a result
while ($row = mysql_fetch_assoc($result)) {
echo "<pre>"; print_r($row); echo "</pre>";
}
}
else {
echo "Sorry! Couldn't find advertiser.";
}
}
?>

The error message is:

« MODx Parse Error »
MODx encountered the following error while attempting to parse the requested resource:
« PHP Parse Error »

PHP error debug
Error: mysql_num_rows(): supplied argument is not a valid MySQL result resource
Error type/ Nr.: Warning - 2
File: /home/grenzjob/public_html/manager/includes/document.parser.class.inc.php(769) : eval()'d code
Line: 18

Parser timing
MySQL: 0.0074 s(3 Requests)
PHP: 0.0392 s
Total: 0.0466 s

Im not the best at PHP so I find it difficult to track down errors like these.

Hope someone can help again :-)

[tbarbedo]

There is probably something wrong with your query. Try executing that query directly on your database and ensure that it works.

[Grenz]

I made it work by removing 3 of the 5 variables.
The error was on line 18 so it shouldn't have any effect but it had. Now im trying to find out how to incorporate the remaining variables.
I wonder - is there a maximum to have many times you can query "AND"?

<?php
$city = mysql_real_escape_string($_POST["city"]);
$country = mysql_real_escape_string($_POST["country"]);
$education = mysql_real_escape_string($_POST["education"]);
$language = mysql_real_escape_string($_POST["language"]);
$branche = mysql_real_escape_string($_POST["branche"]);
$con = mysql_connect("localhost","#*$!","#*$!");
if (!$con) { // checking of connection errors
die('Could not connect: ' . mysql_error());
}
else {
mysql_select_db("#*$!", $con);
$result = mysql_query("SELECT * from profile WHERE city LIKE '%$city%'

AND branche LIKE '%$branche%'");
if (mysql_num_rows($result) > 0) { // checking that there is a result
while ($row = mysql_fetch_assoc($result)) {
echo "<pre>"; print_r($row); echo "</pre>";
}
}
else {
echo "Sorry! Couldn't find advertiser.";
}
}
?>

[tbarbedo]

As far as I know there is no limit to how many "AND" statements you can use...Try it with the following query...

$result = mysql_query('SELECT *
FROM profile
WHERE city LIKE "%$city%"
AND country LIKE "%$country%"
AND education LIKE "%$education%"
AND language LIKE "%$language%"
AND branche LIKE "%$branche%"');

[Grenz]

Sorry but is doesnt work. I've tried to insert just one extra variable and then I get the error message. It works with 2 so I really dont know....

[STeeL]

Try adding:

if (!$result) die('Database error: ' . mysql_error());

before "if (mysql_num_rows($result) > 0)" line. It will stop the script if query failed and let you know what the problem is.

[Grenz]

Brilliant

It turned out to be stupid spelling and naming errors. Thanks a lot :-)

You wouldnt happen to know how to style the ECHO output?

What I want is to style the output using CSS but I havent figured out how to do it yet (I started learning PHP today :-))

[tbarbedo]

You can output HTML with echo and give each element a class that you can define in your css...

echo '<pre class="red">$row["column"]</pre>';

Then in your css you can do...

.red {
color: red;
}

This would make the color of the font red in the <pre> tag with class="red"