php mysql question

[powell1]

Hey everyone. Still trying to get php down. I have a database that contains states, cities, zip codes, and counties. What I am trying to do is get it so when you first load it lists out the states in the database as links, like
www.domain.com/index.php?state=[ST] The states are listed more than once in the db (each row has city info) so is there a way to filter duplicates?



Also, after the state has been set, I am trying to pull a list of cities that match the state like this:


$sql = 'SELECT * FROM zips WHERE state = "$state";';


It doesn’t seem to like it for some reason. Are the queries case sensitive? Maybe I’m not creating an array to store the city info correctly. Any ideas?



Thanks!

 

[SeanW]

www.domain.com/index.php?state=[ST] The states are listed more than once in the db (each row has city info) so is there a way to filter duplicates?

SELECT DISTINCT state FROM unnormalizedtable;

assuming the column is called state, and your table is called unnormalizedtable.

$sql = 'SELECT * FROM zips WHERE state = "$state";';

Nothing within single quotes is interpolated. Keep sane and use sprintf.

$sql = sprintf('SELECT * FROM zips WHERE state="%s"', $state); // no trailing ; needed in your sql query

 

[Enigmabomb]

Dont you want SELECT ZIPS from....

 

[argh01]

1) your quotes are backwards.
$sql = 'SELECT * FROM zips WHERE state = "$state";';

The single quotes are keeping $state from being translated into a state, so you're actually querying for all states where the state is '$state'.

2) No semicolon in the query string
$sql = "SELECT * FROM zips WHERE state = '$state'";

Don't forget you'll probably need to do something like
$state = $_GET['state'];
at the top. Unless you have "register globals" turned on, which is contra-indicated.

 

[Darin]

Umm, if I am hearing you correctly, for the first page to list all of the states try: (assuming your table name is zips)

$sql = "SELECT DISTINCT state FROM zips";

And to list the cities in a particular state after they click on the state link:

$sql = "SELECT DISTINCT city FROM zips WHERE state = '$state'";

Is this what you needed?

 

[powell1]

awesome. that worked. thanks a ton guys!

 

[powell1]

Allright, I got that working the way I need it. Now the page checks to see if the variable is set, if not, displays states as links. Then checks to see if city is set, if not, display cities in state.

I have a second database that lists jobs and descriptions from seocracy. I can add a job variable and do the same checks, but if the job is not set, how do I query the jobs db instead of the zips db? This is my connection code:

$state= $_GET['state'];
$city= $_GET['city'];

$host   = 'localhost'; // Hostname of MySQL server
$dbUser = 'root';    // Username for MySQL
$dbPass = '';    // Password for user
$dbName = 'jobs'; // Database name

function &connectToDb($host, $dbUser, $dbPass, $dbName)
{
 // Make connection to MySQL server
 if (!$dbConn = @mysql_connect($host, $dbUser, $dbPass)) {
   return false;
 }

 // Select the database
 if (!@mysql_select_db($dbName)) {
   return false;
 }

 return $dbConn;
}


$dbConn = &connectToDb($host, $dbUser, $dbPass, $dbName);

$sql_states = "SELECT DISTINCT state FROM zips ORDER BY state"; 
$state_list = mysql_query($sql_states); 

$sql_cities = "SELECT DISTINCT city FROM zips WHERE state = '$state' ORDER BY city"; //only selects the one set of city data
$city_list = mysql_query($sql_cities);

Can I just build it into this page? The title tags change too depending on what has been selected. For example:

<?php if (isSet ($state)){
            
            if (isSet ($city)){
            
            echo "<title>Online Job Service: Find a local job in $city, $state </title>" ;
                  
            }
            
            else {
            echo "<title>Online Job Service: Find a local job in $state </title>" ;}
            }
            
        else{
        echo "<title>Online Job Service: Search and find a local job now! </title>" ;}
                  
            
    ?>

Can I simply add another if statement in there that pulls from the second database?

 

[threehundred]

I believe you have to close the first db, then connect to the second db. You could save yourself some trouble and just create two separate "tables" in one db. I'm sure the database seocracy gave you is set in its own table called "jobs" or something like that.

 

[argh01]

actually you can have two connections to two different databases you'd just need to specify which connection you want. example

<code>
$conn1=mysql_connect( $blah, $blah, $blah );

$conn2=mysql_connect( $blah,$blah, $blah );
mysql_select_db( 'dbname'. $conn1 );
msyql_select_db( 'dbname', $conn2 );

$data=mysql_query( $sql, $conn1 );

et cetera.
</code>

Actually you'd just need to specify the connection in the cases where you were connecting to conn1, but I thought that was too much detail

 

[argh01]

Uh, Powell1, I'd like to answer your question but I don't understand it. Do you want to connect to two different databases at once, or only one connection but it depends on variables being set?

 

[threehundred]

I see, thank you. Good to know.

 

[powell1]

well, I shouldn't need 2 simultaneous connections, because if the $state and $city variables are set, then it won't have to pull from the zips database, it'll just use GET to populate certain areas.

So I need to just make a connection depending on what variables are set. I have the following now:

<?php

$state= $_GET['state'];
$city= $_GET['city'];

$host   = 'localhost'; // Hostname of MySQL server
$dbUser = 'root';    // Username for MySQL
$dbPass = '';    // Password for user

$zipsDbConn = mysql_connect($host, $dbUser, $dbPass);
$jobDbConn = mysql_connect($host, $dbUser, $dbPass);

mysql_select_db( 'zips'. $zipsDbConn );
mysql_select_db( 'jobs', $jobDbConn );

$sql_states = "SELECT DISTINCT state FROM zips ORDER BY state"; 
$state_list = mysql_query($sql_states, $zipsDbConn); 

$sql_cities = "SELECT DISTINCT city FROM zips WHERE state = '$state' ORDER BY city"; //only selects the one set of city data
$city_list = mysql_query($sql_cities, $zipsDbConn); 


?>

and later down the page:

<?php if (isSet ($state)){
            
            while ($row = mysql_fetch_array($city_list)) {
             echo '<a href="index.php?state=' . $state . '&city=' . $row['city'] . '">' . $row['city'] . '</a><br>' ;}
                  
            }
            else {
            while ($row = mysql_fetch_array($state_list)) {
            echo '<a href="index.php?state=' . $row['state'] . '">' . $row['state'] . '</a><br>' ;}
            }
            ?>

the first line of that while loop gives me this error: "mysql_fetch_array(): supplied argument is not a valid MySQL result resource"

What do you think?

 

[powell1]

ok nevermind it's working now.