Page 1 of 1

selecting * from mysql database

Posted: Sat Jun 11, 2005 12:30 pm
by zoe
I'm very new to PHP & databases and am trying to write a script that displays information about the local businesses in my MySQL database, based on information from a form about the nature/name of business and the location (village). It all works fine in a search for businesses of a specified name/type in a specified village, but I really want to offer the option of searching for a specified name/type of business in the whole area (G63). 'G63' is one of the options in the form. I'm getting no error messages, but no results are being returned. Please help, I've hit a brick wall!

The PHP is as follows:

Code: Select all

<?php
$businesstype= ($_POST['businesstype']);
$village= ($_POST['village']);
if ($village == 'G63') {//This is the bit I'm getting stuck at!
$query= "SELECT * FROM business_directory WHERE Name LIKE '%businesstype%' OR Type LIKE '%businesstype%'";
$result= mysql_query($query)
or die ("couldn't execute query");
}
else{
$query= "SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%')"; 
} 
$result= mysql_query($query)
or die ("couldn't execute query");


?>
Many thanks in advance.

Posted: Sat Jun 11, 2005 12:44 pm
by anjanesh
Before doing $village= ($_POST['village']); do
echo $_POST['village'];
and see whats the value of $_POST['village'] when clicking the G63 option.

Im assuming your HTML part is having either
<input type="radio" name="village" value="G63"/> G63
or
<input type="checkbox" name="village" value="G63"/> G63


And in your MySQL query change Type to `Type`
TYPE is a keyword in MySQL and in your case you have a fieldname called Type - Even though it may work - but still.

Posted: Sat Jun 11, 2005 12:49 pm
by zoe
thanx.
I'll try that.

Posted: Sat Jun 11, 2005 12:59 pm
by zoe
Ok,
the output is G63.

On my HTML form, the tags are:

Code: Select all

<select name=&quote;village&quote;>
<option>G63</option>
<option>Killearn</option>
<option>Strathblane</option>
<option>etc.</option>
</select>
changed the Type to 'Type'. Not working yet.

Posted: Sat Jun 11, 2005 1:50 pm
by anjanesh
Ok. I think this is the problem.
If G63 then it'll execute line 6 (of your code)
But Look at line 12 (your code) - thats outside the else loop - put that inside the else loop.

Code: Select all

<?php
$businesstype = ($_POST['businesstype']);
$village = ($_POST['village']);

if ($village == 'G63')
 {
        $query = "SELECT * FROM business_directory WHERE Name LIKE '%businesstype%' OR Type LIKE '%businesstype%'";
 }
else
 {
        $query = "SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%')";
 }
$result = mysql_query($query) or die ("couldn't execute query");
?>

Posted: Sat Jun 11, 2005 3:01 pm
by onion2k
On line 5 you've missed the $ signs on businesstype. So it's actually trying to match "businesstype" instead of what's held in your variable

Posted: Sun Jun 12, 2005 7:59 am
by zoe
Thanx for that Onion. I can't believe I spent half a day pulling my hair out over that one. What a muppet! I thought the script should have worked. Thanks again.