Display data from 2 tables

What is the syntax?

[youfoundjake]

Hi all.
I have 2 tables in my database.
The first table contains just the state abbreviations (50 records)
The second table contains 2 people per state with their address.. phone, name... yada yada. (100 records)
I want my visitor to select which state they are curious about on page1.php and click submit
The results on page2.php should pull both people's information for the state that was selected on page1.php

My questions...
1. Do I need to set up a relationship in the database like I do in Access? Also it would have to be a one to many (2) if I do.

My knowledge is limited, but I'm learning by doing...

2. I keep thinking the the query would like something like:
select * from table2 where table2.'State Rep' = table1.'State' order by table2.'Name'

which would display the 2 records from table 2 that have the state that was selected from table 1.
Does that look right?

If anyone has any good sources to look at for pulling data from a table and displaying it through php, I am completely open to receiving that. I just don't know exactly what I need to start with, other then the limited knowledge I have.

[jatar_k]

what you put there would be fine but I don't see it being necessary

on first page load you pull all the states from your states table

the user selects one

at this point, as long as you have set up the relationship in the db and the info in your 'State Rep' (that shouldn't have spaces in it change to no space or underscore) column are only values from your state table then you can just grab from there

SELECT * FROM table2 WHERE State_Rep = 'selectedstate';

that would grab your 2 records where 'selectedstate' is the value selected from the dropdown by the user.

for some tutorials you could try our very own PHP Library [webmasterworld.com]

maybe this one
Extracting Data from MySQL using PHP [webmasterworld.com]

[youfoundjake]

Perfect. That is a good link to get started, got it bookmarked and everything. Thanks for the tip about state rep. makes sense, don't know why i didn't set up that way originally, i should know better since an application i support that uses sqlany8 always bombs out on applying patches across the network when the unc path has a space in it.

[youfoundjake]

I have shifted things a bit. Combined everything into one table, still has 100 rows, 2 per state
So the question is being able to select a state.
I'm thinking that it is something along the lines of
select * DISTINCT from state
so that I get a list of 50 states, instead of a list of 100.
Is that assumpption correct? I am asking this as I put in all the data into an access db right now, so I'm not testing it or else I would answer my own question.
2nd.
The results returned would pull 2 address's
The landing page is going to be a letter that the user can generate.
Unfortunately, my query is going to put 2 address's on top of each other and the letter down below. Is there a way to split the address's so that it goes address1>letter and address2>letter?
The question is do I have to add a nother column (primary key?) and specify a sort by order on the 2 results?

[jatar_k]

>> select * DISTINCT from state

it would be

SELECT DISTINCT columnname FROM tablename

for the second part

you need to keep the database and its relational logic seperate from the display stuff. Yes, the relations are used by the display but you can do whatever you want with the data once you are on the php side of things.

pull the addresses, put them in an array
pull the letter and out it into a variable

output them later as you like

[youfoundjake]

Thanks on the syntax
Theres going to be an option that they can select a premade letter or they can type in their own. I wil pass one of those two variables (premade letter or custom letter) to the display page. But ultimately there are going to be 2 letters, either both custom or premade. As I get closer, I'm sure questions will arise.

[youfoundjake]

<?php
mysql_select_db($database_site, $site);
$query_senators_results = "SELECT * FROM senators";
$senators_results = mysql_query($query_senators_results, $whoelse) or die(mysql_error());
$row_senators_results = mysql_fetch_assoc($senators_results);
$totalRows_senators_results = mysql_num_rows($senators_results);
?>

<?
$host = "localhost";
$user = "user";
$pass = "password";
$dbname = "dbname";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);

$sql = "select * from senators where 'state'='" . $_POST['state']. "'";
echo $sql;

$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)) { echo "<p>",$row['state'];
}

?>

Im clicking submit on page1.php and on page2.php I get the following error:

select * from senators where 'state'=''
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /path/public_html/senators-letter.php on line 30

my page1.php form is pulling the data (using DISTINCT ;P ) fine from the table put for some reason I keeps bombing out. I have gone through the threads and cannot see it( don't move until you see it...quote from bobby fisher movie i can't think of at this moment.)
Can anyone else see it?

[eelixduppy]

Try changing:

$sql = "select * from senators where 'state'='" . $_POST['state']. "'"; 

To

$sql = "select * from senators where state ='" .[url=http://us2.php.net/manual/en/function.mysql-real-escape-string.php]mysql_real_escape_string[/url]($_POST['state']). "'"; 

[edited by: eelixduppy at 3:08 am (utc) on July 26, 2006]

[youfoundjake]

dang it.. still the same error... i gonna throw the laptop across the room, maybe a shutdown restart will fix it...

[eelixduppy]

There is an error in your query somewhere. Change the following:

$query = mysql_query($sql) or die(mysql_error());

[youfoundjake]

select * from senators where state =''No Database Selected

Thats the error im getting now.

<?
$host = "localhost";
$user = "user";
$pass = "password";
$dbname = "contact";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($contact);

$sql = "select * from senators where state ='" .mysql_real_escape_string($_POST['state']). "'";

echo $sql;

$query = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($query)) { echo "<p>",$row['state'];
}

?>

so in my cpanel, i created a db called contact
in cpanel it shows as jake_contact
should i be putting the db name as contact, or jake_contact
my username is jake_admin, and that allows me access but when i change the db name to jake_contact also... i get the error

edited code by me

[youfoundjake]

I should also add that when the user selects the state that the mailing address should display, now do i need to specify each field to be displayed or does the select * create an array to display all fields?
the query is always going to return 2 results.

[eelixduppy]

You need to change the db name to the actual db name. If it is jake_contact, then it must be

$dbname = "jake_contact";

Also, make sure your username and password is correct in your script.

By the way, * selects all the cols in a table.

Good luck!

[youfoundjake]

so i changed the db name, and im still getting the "select * from senators where state =''No Database Selected" error. Just can't seem to quite get it....and i haven't even gotten to the point to formating the output.

[adnovice88]

don't know if you have solved your problem, but in the above code i see that you are using the wrong variable name.

the select db should be:

mysql_select_db($dbname);

Your code says (i am new to this and couldn't find how to grab a snippet of previous post):
$dbname = "contact";
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($contact);

[youfoundjake]

changed the dbname
now i just get

select * from senators where state =

adnovice88, use [ quote ] and [\ quote ] but with out the spaces.

[adnovice88]

Thank you for the tip youfoundjake.

mysql_real_escape_string($_POST['state'])

looks like either mysql_real_escape_string returning empty string or the $_POST['state'] doesn't contain anything. i think your usage of the mysql_real_escape_string is correct. it is also being called only after the connection to the database has been established.

i would put a debug statement to check if there is any value in the $_POST. i would re-write your code in the following way:

<?
echo 'STATE:' . $_POST['state'] . '<br>';

$host = "localhost";
$user = "user";
$pass = "password";
$dbname = "contact";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");

if(!$connection)
{
echo 'ERROR: Could not connect to Database<br>';
}

mysql_select_db($contact);

$sql = "select * from senators where state ='" .mysql_real_escape_string($_POST['state']). "'";

echo $sql;

$query = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($query)) { echo "<p>",$row['state'];
}

?>

if the state value is not present check the select statement in the previous html file and see if the field name you have given is state or something else.

another way to debug is to put '?state=CA' to the url and use $_GET in the code. this will atleast help you figure out if your mysql and php code is working properly or not.

HTH

[youfoundjake]

I added the code and was not notified that there was no connection, I did however receive the same error

$sql = "select * from senators where state ='" .mysql_real_escape_string($_GET['state']). "'";

I did change the POST to GET and tried the url like this.

http://www.example.com/senators-letter.php?state=CA
I still got the error.

[youfoundjake]

And I am now getting

select * from senators where state ='IL'
IL

IL

IL

IL

What ever state i select on the previous page is the one state that is listed the 3 times.
So now im getting some kind of connection, just need to work on formatting.

[jatar_k]

it looks like it is now working as designed. Your output is from this

while ($row = mysql_fetch_array($query)) { echo "<p>",$row['state'];

so you are getting exactly what you asked for.

When I am doing my first run in a query like this I often have my fetch array loop doing this

while ($row = mysql_fetch_array($query)) {
echo '<p><pre>';
print_r($row);
echo '</pre>';
}

then I can look at the full dump of each row and it helps me know what vars I need to output when doing my formatting.

[youfoundjake]

I got rid of the

select * from senators where state ='IL'

from displaying on the page
but the results should be returning 2 people.

Instead I am getting
person1
person2
person2
person1

I tried taking echo out of

while ($row = mysql_fetch_array($query)) { echo "<p>",$row['state'];

but then i get an error that there is a "," out of place...
But I am getting there.. able to display name address, city state zip and such as a letter header, I will add the text once I get rid of the duplicate results.

[youfoundjake]

here is the current code in all its glory... bask in my(everyone else's) glory... hehe

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");

if(!$connection)
{
echo 'ERROR: Could not connect to Database<br>';
}

mysql_select_db($dbname);

$sql = "select * from senators where state ='" . $_POST['state']. "'";

//echo $sql;

$query = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($query)) {echo "<p>",$row['first_name'], " ",$row['last_name'], "<br>", $row['address'], "<br>",
$row['city'], ", ", $row['state'], " ", $row['zip'], "<BR>", $row['website'], $row['email'];
}

?>

</body>
</html>
<?php
mysql_free_result($senators_results);
?>

just getting the duplicate results mirrored but getting results none the less. thanks for everyones help.

[youfoundjake]

argghhh i broke it...arrghhh where is my fdisk key?

[youfoundjake]

ok. I got it working now. Thanks to everyone for the help. Working on the formatting now. As long as all my variables are being displayed properly, I should be ok. Asking in another thread about how to create a page break which should complete the php portion of the site.