selecting * from mysql database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

selecting * from mysql database

Post 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.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

thanx.
I'll try that.
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post 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.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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");
?>
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post 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.
Post Reply