Jump to content

colby07

New Members
  • Posts

    9
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

colby07's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Hi guys, I'm having trouble querying my database. I have a table with 5 columns. Houseid, city, material 1, material 2, material3. Either of the 3 materials fields can have the same entered materials. I am trying to buid a query with a format similar to the following: $query = "SELECT * FROM houses WHERE city = '$city' AND material1 = '$material1' or material2 = '$material1' or material3 = '$material1'"; The results include houses that don't have $material1 but are in the designated $city AND houses with the materials but not in the designated city designated by the $city variable. I would like the query to restrict the results to those that are in the $city AND have material 1. Any insight would be greatly appreciated! Thank you!
  2. hey guys, I've been trying to figure a solution to this problem for quite some time now and just can't seem to figure it out. I have a search form with a bunch of fields that are used to query a database. the query is of the following format. It generates all the records in the database for the matching search criteria in a form with fields type of house, location, size: $set = FALSE; $query = "SELECT * FROM table_name"; if (!empty($type)) { $query .= " WHERE type= '$type'"; $set = TRUE; } if (!empty($location)) { $query .= ($set===TRUE ? " AND" : " WHERE") . " location= '$location"; $set = TRUE; } if (!empty($size)) { $query .= ($set===TRUE ? " AND" : " WHERE") . " size = '$size'"; } $results = mysql_query($query) or die(mysql_error()); while ($row = mysql_fetch_array($results)) { extract($row); } I have two other tables in the database. 1) users and 2) pricelink. In the pricelink table there are three fields: userid, houseid, and price. In another form I let users give a price that they would pay for the associated houseid. I managed to get a table output for the search criteria above but I would like to join a field to the output table that would be the "average price" of all the suggested prices from the users (corresponding to the house ids of the search output records). I tried a query based on results from the search of the user that looks like the following to get the right data I need: $query2 = "SELECT houseid, AVG(price) FROM pricelink GROUP BY houseid"; but i need to limit the query to the searched house ids. I think it isnt possible to add a WHERE houseid = "$houseid" clause in query2 but i need something of that nature. I know this sounds confusing and I apologize in advance, let me know if clarifications are needed. Thank you!
  3. Hey, I just wanted to say that there was a small input value in my form and now that it's fixed, the search and query function properly! THANK YOU Maq! Really appreciate your help. Problem solved
  4. <?php include('db_login.php'); $connection = mysql_connect($db_host, $db_username, $db_password); if (!$connection){ die ("Could not connect to the database: <br />". mysql_error( )); } $db_select=mysql_select_db($db_database); if (!$db_select) { die ("Could not select the database: <br />". mysql_error( )); } $name = $_POST['name']; $address = $_POST['address']; $country = $_POST['country']; $set = FALSE; $query = "SELECT * FROM table_name"; if (!empty($name)) { $query .= " WHERE name = '$name'"; $set = TRUE; } if (!empty($address)) { $query .= ($set===TRUE ? " AND" : " WHERE") . " address = '$address'"; $set = TRUE; } if (!empty($country)) { $query .= ($set===TRUE ? " AND" : " WHERE") . " country = '$country'"; } $results = mysql_query($query) or die(mysql_error()); while ($row = mysql_fetch_array($results)) { extract($row); //For the sake of testing the search, I only make it return the //countries associated with the record. echo $country; echo "<br>"; } ?> I've tried everything within my capabilities to get the script to display all the records when all fields are empty but just haven't been able to. When I fill in the name field, it provides the records with that name. When I fill in the name field with the address and the country, it gives me the appropriate records. When only the address or counrty field is filled in, nothing is displayed. It is as if the only time I get a result is when the name is filled in.
  5. thx again for the reply Maq. I tried running the script but when all three fields are blank, nothing is displayed on screen. Just to test it, if I remove the if statements, all the records show up and the only field that works data is inputed is the first one. Do you think there is a another way of going about solving this? Thank you again, colby07
  6. Thank you so much for your response, I have to leave for the day but I'll try it as soon as I get back! Colby07
  7. Hey guys, I'm new to PHP and MySql but have been attempting to improve. I currently have an HTML form with certain inputs where some of them are optional. Based on the results of this form, I would like to query the database. For example, lets say I have the following variables: $name $address $country that have been posted from a form where either of them can be empty. How could I create a php script that would return all the records in the database table if all the fields are empty and that would return the correct records any combination of inputs were given. Thank you in advance for your time and generosity. I came accross a forum post where the following solution was suggested: $query = "SELECT * FROM table_name WHERE 1";} if ($name != "") {$query .= " AND name = '$name'";} if ($address !="") {$query .= " AND addresse = '$addresse'";} if ($country !="") {$query .= " AND country = '$country'";} but when all fields are empty all I get is a blank page instead of all the records in the table. When I input a name, the result is correct though. This issue has been driving me nuts... Thank you in advance for your help and generosity, Colby07
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.