Displaying Column in One Table Based on Another Table

[loren646]

I am having problems with this:

 

$catergory1 = "SELECT l.landlord, l.building, l.location

                       FROM land as l

                       INNER JOIN updater as u

                       ON l.landlord = u.addbuilding

                       WHERE l.landlord = u.addbuilding";

 

----------------------------------------------------- CODE BELOW ----------------------------------------------------------------

 

<?php

 

$landlordselect=$_POST['landlordselect'];

$conn = mysql_connect("localhost","user","password");

mysql_select_db("database");

$sql = "UPDATE updater

        SET addbuilding='$landlordselect'

        WHERE user='user'";

 

mysql_query( $sql, $conn );

mysql_close($conn);

 

 

mysql_connect("localhost","user","password");

mysql_select_db("database");

   

   /* $category1 = "SELECT * FROM land 

                     WHERE landlord='$landlordselect'";

   */            

     

    $catergory1 = "SELECT l.landlord, l.building, l.location

                       FROM land as l

                       INNER JOIN updater as u

                       ON l.landlord = u.addbuilding

                       WHERE l.landlord = u.addbuilding";

                       

        $query_result1 = mysql_query($category1);

        while($result1 = mysql_fetch_assoc($query_result1))

        {

        ?>

          <option value = "<?php echo $result1['building']?>"><?php echo $result1['building']?></option>

        <?php

        }

mysql_close();

    ?>  

[Psycho]

And what is the problem?

[loren646]

it doesn't display the list.  something is wrong.

 

If i don't try and use two tables and just the one it works with this code (the one i have off):

 

 

   /* $category1 = "SELECT * FROM land 

                     WHERE landlord='$landlordselect'";

   */        

[loren646]

this is the error I keep getting:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource ...... on line 28

 

line 28 = while($result1 = mysql_fetch_assoc($query_result1))

[Psycho]

Ahh, so you are getting an error. That would have been helpful information with your initial post.

 

That error means that your query is failing - and the result ($result1) is false. You need to check what the error is.

 

Change the following line as shown

 

$query_result1 = mysql_query($category1) or die (mysql_error());

 

However, I can see that the query is "off" in that you have the same condition for the JOIN as you do in the WHERE clause. That makes no sense. Start by removing the WHERE condition. Or change it to something like you had before:

 

$catergory1 = "SELECT l.landlord, l.building, l.location
               FROM land as l
               INNER JOIN updater as u
                 ON l.landlord = u.addbuilding
               WHERE l.landlord='$landlordselect'";

[loren646]

 

Ahh, so you are getting an error. That would have been helpful information with your initial post.

 

That error means that your query is failing - and the result ($result1) is false. You need to check what the error is.

 

Change the following line as shown

 

$query_result1 = mysql_query($category1) or die (mysql_error());

 

However, I can see that the query is "off" in that you have the same condition for the JOIN as you do in the WHERE clause. That makes no sense. Start by removing the WHERE condition. Or change it to something like you had before:

 

$catergory1 = "SELECT l.landlord, l.building, l.location
               FROM land as l
               INNER JOIN updater as u
                 ON l.landlord = u.addbuilding
               WHERE l.landlord='$landlordselect'";

 

I have two tables "land" and "updater"

 

updater has two columns: "user" and "addbuilding"

 

i.e.

 

user      |    addbuilding

jane      |    Stone Properties

 

 

land has several columns landlord, building, location, etc. 

i.e. 

 

landlord                 |        building            | location 

Greenhill               |       30 East 3rd      | Chicago

Greenhill               |     12 Second St     | Boston

Stone Properties   |    12 Main St.         | L.A. 

Stone Properties   |   13 Spring St.       | NY

 

-----------------------------------------------------------

 

User jane has selected "Stone Properties" I want to output "12 Main St." and "13 Spring St." 

 

I was hoping the program i wrote would do this but it isn't. Do i need to join these two tables? I want to use one table to search another table and then I output it. 

Edited March 12, 2013 by loren646

[Psycho]

I'm just getting more confused. Based upon some earlier code you were using a value passed int he POST data ($_POST['landlordselect']) to determine which records to display. But, now you show a table which has a user and selected property values. So, is the dynamic value going to be the property or the user?

 

Also, you should not be using the property name as the values that ties the two tables together. You should have "property" table that has each unique property name with a unique ID. Then use that ID in place of the property name in the related tables.

 

Anyway, with the structure you have (which I don't advise) you can get those record either with a variable property name

 

SELECT building
FROM land
WHERE landlord = '$landlordselect'

 

Or with a variable value for the username

 

SELECT building
FROM land
JOIN user
  ON user.addbuilding = land.landlord
WHERE user.user = '$username'

[loren646]

 

I'm just getting more confused. Based upon some earlier code you were using a value passed int he POST data ($_POST['landlordselect']) to determine which records to display. But, now you show a table which has a user and selected property values. So, is the dynamic value going to be the property or the user?

 

Also, you should not be using the property name as the values that ties the two tables together. You should have "property" table that has each unique property name with a unique ID. Then use that ID in place of the property name in the related tables.

 

Anyway, with the structure you have (which I don't advise) you can get those record either with a variable property name

 

SELECT building
FROM land
WHERE landlord = '$landlordselect'

 

Or with a variable value for the username

 

SELECT building
FROM land
JOIN user
  ON user.addbuilding = land.landlord
WHERE user.user = '$username'

 

Sorry for the confusion. Let me explain. The goal of the site is for a user to update available apartments for rent.:

 

You can visit this site:  and notice after you click "add available apartment" the buildings are wiped clean.

 

------------

 

 Currently configured for 1 user so no login yet.  

 

This user will select the landlord they want to update and then be taken to a second page. Here they add an available apartment for rent by selecting the building, the apartment #, the price, etc. Then click "Add Available Apartment." 

 

This goes to a 3rd page to update another table and then redirects back to the 2nd page so they can add another available apartment from that specific landlord. 

 

Unfortunately, the "$landlordselect=$_POST['landlordselect'];" is wiped clean after the header redirect on the 3rd page so I needed to save the user with the landlord they selected on the first page in a table. 

 

-----

Edited March 14, 2013 by Philip

[loren646]

i got it to work but i think this is bad logic...? 

 

first page: select landlord

second page: enter that information into database. header to 3rd page.

3rd page: select the information from second page and make it into a variable. and now add availablity.

4th page: added availability header to 3rd page. 

 

The logic i'm worried about if i have mulitple users updating at the same time... 

[Psycho]

You should NOT be storing information into the database to save a selection that a user made on a page to determine what happens on another page. That is what session data is for! But, in this case you don't need to use session data at all. In fact, you only need 2 pages (although you can create separate pages for separating the code into logical chunks for the purpose of including in page 2)

 

Here is a framework of how page 2 might work

 

<?php

if(!isset($_POST['landlordselect']))
{
    //No landlord selected, redirect to or includepage 1
    header("Location page1.php");
    exit();
}

//Create vars from POST data.
$landlordselect = trim($_POST['landlordselect']);
//These vars will be used to repopulate the form if an error occurs to make the form "sticky"
$building = isset($_POST['building']) ? trim($_POST['building']) : '';
$apartNo = isset($_POST['apartNo']) ? trim($_POST['apartNo']) : '';
$price = isset($_POST['price']) ? trim($_POST['price']) : '';
//Set default error message
$errorMessage = "";

//Check a field to see if data to add apartment was submitted
if(isset($_POST['price']))
{
    //User submitted form to add apatment

    //Perform validation of input data
    //If there are NO validation errors then insert the record and set
    //the form vars to empty strings so the form does not repopulate

    //If there are errors, then set the error message var accordingly
    //The vars for $building, $apartNo, etc. will be used to repopulate the form
}


?>
<html>
<head></head>
<body>
<?php echo $errorMessage; ?><br>
<form action="" method="post">
<input type="hidden" name="landlordselect" value="<?php echo  htmlspecialchars($landlordselect); ?>" />
Building:
<input type="text" name="building" value="<?php echo  htmlspecialchars($building); ?>" /><br>
Apartment #:
<input type="text" name="apartNo" value="<?php echo  htmlspecialchars($apartNo); ?>" /><br>
Price:
<input type="text" name="price" value="<?php echo  htmlspecialchars($price); ?>" /><br>


</form>
</body>
</html>