[RESOLVED] Database/Field in a row question

[graphicgirly]

I have a page that displays a table of addresses from a database and I want a user to be able to click on the address and that will bring them to a page with more information on that address. How do I do this?

I can write the SELECT statement for the page where the detailed information will be displayed but I’m stuck on how to make my address field bring me to the next page.

Thanks,
Lisa

[bbreslauer]

Your address table should have a primary key associated with it (possibley address_id), which gets queried in the SELECT statement. The address should then be a link with ?addressid=ADDRESSID in it, where ADDRESSID is the ID # of the address in the database. Then, on the page that you bring them to, you need to query the database again for the address with address_id=intval($_GET['addressid'])

[graphicgirly]

So in my database, I should have the address in there as the link with ?addressid=ADDRESSID in it so it shows up as a link, not just the plain text address as I do now, right?

[bbreslauer]

No...sorry if I wasn't clear, I'll try again.

Your database should probably be setup something like this:

address:
address_id int primary key auto_increment
address1
address2
city
state
zip

Or you can combine the entire address into just an entire_address field which is a text field, if you don't need the ability to separate the address out.

Then, you'll have a page which lists all the addresses. Here's what you want to do on that page:

connect to database
do SQL query to get address_id's and addresses
create a link to another page (see below for what that'll look like) for each of the addresses
close database connection

<a href="page2.php?addressid=ADDRESSID">address goes here</a>

Then, on page2.php, you would put in your SELECT query, to get the address and display more information,

WHERE address_id=$_GET[addressid]

This would select just the row that has that ID

[graphicgirly]

Thanks, that helped out tremendously, but I seem to have a problem. For some reason it isn't passing by buildingId value to the next page. Here's my code:

Page 1:
I opened my db connection...
$q = "SELECT * from propertyListOffice where
status = 'Vacant' or
status = 'Available' or
displayAvail = 'Y' or
displayAvail = 'y'";
print '<td><div align="center"><a href="propertySpecPage.php?buildingId=BUILDINGID">'.$row->propertyAddress.'</a></td>';
...I can print out my buildingId number on this page, so I know it's in my db

Page 2:
I opened my db connection...
$q = "SELECT * from propertyListOffice where
buildingId = $_GET[BUILDINGID]";

What am I doing wrong?

[TobyRT]

It might help if you defined your building id as a variable - I think.
Once you've done your query and defined it as an array (or object, I think in your case)
$row=mysql_fetch_object($query_you_listed_above)
$id=$row->buildingid;

Then your hyperlink can be:
<a href="propertySpecPage.php?buildingID='$id'">'.$row->propertyAddress.'</a>

[graphicgirly]

That didn't work. I already had fetched my object. I just didn't show that part of my code. Am I getting my variable names mixed up or is it something more?

Here's my code from the first page:
$db = mysql_select_db("masterTenantDirect0ry")
or die(mysql_error());

$q = "SELECT * from allPropertyList where
status = 'Vacant' or
status = 'Available' or
displayAvail = 'Y' or
displayAvail = 'y'";

$r = mysql_query($q,$conn)
or die(mysql_error());
while ($row = mysql_fetch_object($r))
{

print '<td><div align="center"><a href="propertySpecPage.php?buildingID=BUILDINGID">'.$row->propertyAddress.'</a></td>';
print '<td><div align="center">'.$row->buildingID.'</td>';

]

Here's the code from the referring to page:

db = mysql_select_db("masterTenantDirect0ry")
or die(mysql_error());

$building_ID = $_GET['buildingID'];

$q = "SELECT * from allPropertyList where
buildingID = $_GET[buildingID]";

$r = mysql_query($q,$conn)
or die(mysql_error());

while ($row = mysql_fetch_object($r))
{
print '<td><div align="center">'.$row->buildingID.'</td>';
print '<td><div align="center">'.$row->propertyAddress.'</td>';
}

[TobyRT]

I'm sure your query is fine if you're not getting a mysql_error - but you're still not defining your variable:

Your first page needs to have the ID included in the link to the second which displays the info:

$building_ID = $_GET['buildingID']; 

$q = "SELECT * from allPropertyList where 
buildingID = $_GET[buildingID]"; // -  why're you using the $_GET command here? You've already placed that in a variable in the line above: $building_ID

You need to change the query to:
$q ="SELECT * FROM allPropertyList WHERE buildingID = '$building_ID'"

Likewise, in your link you need to make use of that variable - you've defined it beautifully, but then not used it!

echo '<a href="propertySpecPage.php?buildingID='.$building_ID.'">'.$row->propertyAddress.'</a>

Try that and see if you get any better - if you can post the relevant code for BOTH pages, in the order we'd see them as a user.

[graphicgirly]

Thank you. That did work out better but for some reason I still have a slight problem....the builder_id value isn't being pulled or grabbed from the referring page, because I am getting an empty table.

Do you see something I missed?

First Page:

while ($row = mysql_fetch_object($r))
{
print '<td><a href="propertySpecPage3.php?buildingID='.$building_ID.'">'.$row->propertyAddress.'</a></td>';
}

Referred Page:

$building_ID = $_GET['buildingID'];

$q = "SELECT * FROM allPropertyList WHERE buildingID = '$building_ID'";

while ($row = mysql_fetch_object($r))
{
print '<td><div align="center">'.$row->buildingID.'</td>';
print '<td><div align="center">'.$row->propertyAddress.'</td>';
}

[TobyRT]

I'm slightly stumped on this one. I'm sure we're missing something obvious.

I tend not to use fetch_object myself, but use mysql_fetch_array.

Try that and replace instances of
$row->fieldname

with

$row['fieldname'].

it's a long shot, but it might work.

When you've done that, post the full php code (including SQL query) for both pages, in the order we would meet them as a user.

Thanks!

[graphicgirly]

That didn't work, but it didn't hurt to try....

I noticed that when I goto to the page - propertySpecPage.php, no value passes after the words buildingID=

Sorry I don't know how to insert in any way but this - Here's my php code:

First page you come to -
<?php

$conn = mysql_connect("dbName", "usr", "pswd") or die(mysql_error());

$db = mysql_select_db("masterTenantDirect0ry") or die(mysql_error());

$q = "SELECT * from allPropertyList";

$r = mysql_query($q,$conn) or die(mysql_error());

while ($row = mysql_fetch_array($r))
{

print '<td><a href="propertySpecPage.php?buildingID='.$building_ID.'">'.$row['propertyAddress'].'</a>';
print '<td><div align="center">'.$row['buildingID'].'</td>';

}

?>

Here's the next page, the referred to page:
<?php

$conn = mysql_connect("dbname", "usr", "pswd") or die(mysql_error());

$db = mysql_select_db("masterTenantDirect0ry") or die(mysql_error());

$building_ID = $_GET['buildingID'];

$q = "SELECT * FROM allPropertyList WHERE buildingID = '$building_ID'";

$r = mysql_query($q,$conn) or die(mysql_error());

while ($row = mysql_fetch_array($r))
{
print '<td><div align="center">'.$row['buildingID'].'</td>';
print '<td><div align="center">'.$row['propertyAddress'].'</td>';
}
?>

Thanks for all your help so far.

[graphicgirly]

I fixed it!!!!

I changed this line -
print '<td><a href="propertySpecPage.php?buildingID='.$building_ID.'">'.$row['propertyAddress'].'</a>';

to this line -

print '<td><a href="propertySpecPage.php?buildingID='.$row['buildingID'].'">'.$row['propertyAddress'].'</a>';

I was passing $building_ID instead of buildingID.

Again thanks for all the help!

[TobyRT]

Hiya, your problem was you got a little confused in your hyperlink:
This bit is fine:

<?php 

$conn = mysql_connect("dbName", "usr", "pswd") or die(mysql_error()); 

$db = mysql_select_db("masterTenantDirect0ry") or die(mysql_error()); 

$q = "SELECT * from allPropertyList"; 

$r = mysql_query($q,$conn) or die(mysql_error()); 

The issue is that in the first line after you open your while() loop you haven't defined $building_ID.
Your options are to use this, where you just take the value directly from the array

print '<td><a href="propertySpecPage.php?buildingID='.$row['buildingID'].'">'.$row['propertyAddress'].'</a>';  

OR

while ($row = mysql_fetch_array($r)) 
{
$buildingID=$row['building_ID']
print '<td><a href="propertySpecPage.php?buildingID='.$buildingID.'">'.$row['propertyAddress'].'</a>';
} 
?> 

Once you choose which of the above, the rest of your pages should work as written.

[TobyRT]

Well done! You spotted the same as me (and I took my time writing my post which I then messed up and had to re-edit!

[graphicgirly]

Thanks again for all you help!