[RESOLVED] Getting user's zip_code from MySql database

[balloontrader]

I am starting to set up a weather page that I want to be specific to my user's location.....

What I need to learn is how to access my MySql data base and retreave the zip_code my user has in his/her profile from the site's database...

I know how to get php to acces the database via

 

$server = "localhost"; 
$user = "my_username"; 
$password = "my_password"; 
$database = "my_database"; 

mysql_connect($server, $user, $pass) or die(mysql_error());

mysql_select_db($database) or die(mysql_error()); 

$query = mysql_query("SELECT * FROM My_table");

where can I find a tutorial to learn this?

[bradgrafelman]

This link has a bunch of tutorials about database topics.

Also, I see that you've used SELECT * in your coding example. I think now would be a good time to drop that habit before you start interacting with MySQL through PHP 😉 For more information about what I mean, see my post here.

[balloontrader]

ok great....

So what this does...

$server = "localhost"; 
$user = "my_username"; 
$password = "my_password"; 
$database = "my_database"; 

sets the variable to my specific log-in info

This....

mysql_connect($server, $user, $pass) or die(mysql_error());

uses the variables to actualy log into my SQL server (or kills the script and echos the error

this

mysql_select_db($database) or die(mysql_error()); 

opens the specific database in my server or kills the script echoing the error

this

$query = mysql_query("SELECT `user_zip`FROM `my_table`");
 

is searching the column "user_zip' in the the table called "my_table"

I THINK what I need to learn is how to check the user logged in for the user's id

and match that id with it's user zip.... then assign that zip to a variable.

I'm just taking a shot at this though.

[Houdini]

Basically just use a WHERE clause for your query

$query = "SELECT user_zip FROM my_table WHERE user_id='$user_id'";

which will return just one row of data for that one user provided it is a valid id. Also you can if you want use backticks (not single quotes) for field names if you really want to but it is not really necessary, unless you just like typeing them.

[balloontrader]

ok

I get that

How does the script know what user is calling the script?..... ($user_id)

if I have 2 users logging in at the same time and both call the script, will the script as you suggest and get the correct zip for each one?

[etully]

The only way the script can know which user is visiting is if you have a login page. You have a form that asks them for their Username and Password. That form posts to another page where you check the database to see if that username matches that password. If so, (and this is the answer to your question), then you either set a cookie called "userid" and assign it the value of the username that you just checked in the database OR you set up a session and create a session variable called "userid" that gets the username. Either way, cookies and session variables (stored from a successful login) are how your script knows the user's userid.

[balloontrader]

I am using a canned forum and portal and am adding the weather scrips in as a block

It looks to me like the forum's log in is using session IDs.

// session id check
if (!empty($HTTP_POST_VARS['sid']) || !empty($HTTP_GET_VARS['sid']))
{
	$sid = (!empty($HTTP_POST_VARS['sid'])) ? $HTTP_POST_VARS['sid'] : $HTTP_GET_VARS['sid'];
}
else
{
	$sid = '';
}

if( isset($HTTP_POST_VARS['login']) || isset($HTTP_GET_VARS['login']) || isset($HTTP_POST_VARS['logout']) || isset($HTTP_GET_VARS['logout']) )
{
	if( ( isset($HTTP_POST_VARS['login']) || isset($HTTP_GET_VARS['login']) ) && (!$userdata['session_logged_in'] || isset($HTTP_POST_VARS['admin'])) )
	{
      	$username = isset($HTTP_POST_VARS['username']) ? phpbb_clean_username($HTTP_POST_VARS['username']) : '';
		$password = isset($HTTP_POST_VARS['password']) ? $HTTP_POST_VARS['password'] : '';

	$sql = "SELECT user_id, username, user_password, user_active, user_level, user_rank, user_actviate_date, user_expire_date, user_regdate
		FROM " . USERS_TABLE . "
		WHERE username = '" . str_replace("\\'", "''", $username) . "'";
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Error in obtaining userdata', '', __LINE__, __FILE__, $sql);
	}

	if( $row = $db->sql_fetchrow($result) )
	{

so I need to add a line to that the code above that sets a cookie....

like this?

setcookie("userweather",$username);

then I need to call the cookie at the start of my weather script like this?

if (isset($_COOKIE["userweather"])) {
    $username = $_COOKIE["userweather"];
     }

then do the SQL seach for the zip where the $usrename matches the cookie?

[etully]

You got it.

Remember that you can only use the setcookie command BEFORE you have displayed ANY text or any HTML. And I mean any text - occasionally, people accientally put a space at the top of their script just before the <? and then they get an error message when they try to set a cookie.

[balloontrader]

I got the bit about no prior display before the cookie set.....
now; is that just for the login script?... or the entire page start?

I am assuming it's just this script.

[balloontrader]

Tested the cookie

did not crash my site
allowed my auto login
site seems to work ok

called the php page
get a blank white page....
did a view source...

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252"></HEAD>
<BODY></BODY></HTML>

this is what I see

never got an error that i KNOW of...

[balloontrader]

ok I am getting "A" chart to display partly.... wrong data and missing info in the header and X axis label....

not getting any errors popping up from SQL stuff.

I think I may be reading wrong info from the database.

At the end of this code in my login.php

// session id check 
if (!empty($HTTP_POST_VARS['sid']) || !empty($HTTP_GET_VARS['sid'])) 
{ 
    $sid = (!empty($HTTP_POST_VARS['sid'])) ? $HTTP_POST_VARS['sid'] : $HTTP_GET_VARS['sid']; 
} 
else 
{ 
    $sid = ''; 
} 

if( isset($HTTP_POST_VARS['login']) || isset($HTTP_GET_VARS['login']) || isset($HTTP_POST_VARS['logout']) || isset($HTTP_GET_VARS['logout']) ) 
{ 
    if( ( isset($HTTP_POST_VARS['login']) || isset($HTTP_GET_VARS['login']) ) && (!$userdata['session_logged_in'] || isset($HTTP_POST_VARS['admin'])) ) 
    { 
          $username = isset($HTTP_POST_VARS['username']) ? phpbb_clean_username($HTTP_POST_VARS['username']) : ''; 
        $password = isset($HTTP_POST_VARS['password']) ? $HTTP_POST_VARS['password'] : ''; 

    $sql = "SELECT user_id, username, user_password, user_active, user_level, user_rank, user_actviate_date, user_expire_date, user_regdate 
        FROM " . USERS_TABLE . " 
        WHERE username = '" . str_replace("\\'", "''", $username) . "'"; 
    if ( !($result = $db->sql_query($sql)) ) 
    { 
        message_die(GENERAL_ERROR, 'Error in obtaining userdata', '', __LINE__, __FILE__, $sql); 
    } 

    if( $row = $db->sql_fetchrow($result) ) 
    { 

I added one line

setcookie("userweather",$username);

This is the start of my chart.php

<?php
if (isset($_COOKIE["userweather"])) { 
$username = $_COOKIE["userweather"]; 
} 
$server = "localhost"; 
$user = "myusername"; 
$password = "mypassword"; 
$database = "mydatabase"; 
mysql_connect($server, $user, $password) or die(mysql_error()); 
mysql_select_db($database) or die(mysql_error()); 
$query = mysql_query("SELECT `user_station` FROM `forum_users` WHERE `username`=`$username`");
mysql_close();
$user_station=$query;
$url = 'http://www.nws.noaa.gov/cgi-bin/mos/getmav.pl?sta='.$user_station; 
$handle = fopen($url, "r"); $contents = file_get_contents($url);  

fclose($handle);

did I miss sometrhing?

[balloontrader]

ok

I have been testing my script by echoing out the variable values....

It seems that the cookie variable is reading blank or null

I am opening the SQL ok, but I don't have a value in the $username variable.....

[etully]

In your browser, you should be able to see the contents of your cookies. In Firefox, I click Edit -> Prefs -> Privacy -> View Cookies

If the cookie isn't there, then that's your problem, it's not getting set.
If the cookie is there but it has no value, then that's your problem, it's not getting set right.
If the cookie is there and it has the right value, then you are not reading it correctly (is the domain name changing at all?)

[balloontrader]

Been working out my "issues"

I mannaged to get my cookie worked out.... now I'm working on getting the data I need from my SQL database.

I dug about in my forum files to get the cookie name that it is using (for a session ID)

I am now working out the code for the MySQL queries.

Thanks all

[balloontrader]

etully wrote:

In your browser, you should be able to see the contents of your cookies. In Firefox, I click Edit -> Prefs -> Privacy -> View Cookies

If the cookie isn't there, then that's your problem, it's not getting set.
If the cookie is there but it has no value, then that's your problem, it's not getting set right.
If the cookie is there and it has the right value, then you are not reading it correctly (is the domain name changing at all?)

etully
I just read your post....

I can get the cookie to echo it's value ok... to it's setting ok and I'm reading it ok...

what I am doing is this...

<?php
if (isset($_COOKIE["my_cookie_name"])) {
$weathercookie = $_COOKIE["my_cookie_name"];
} 
$server = "my_server"; 
$user = "my_username"; 
$password = "my_password"; 
$database = "my_database"; 
mysql_connect($server, $user, $password) or die(mysql_error()); 
mysql_select_db($database) or die(mysql_error());
$getweatheruser = mysql_query("SELECT `session_user_id` FROM `forum_sessions` WHERE `session_id`= ($weathercookie)");
$getstation = mysql_query("SELECT `station` FROM `forum_users` WHERE `user_id`= ($getweatheruser)");
mysql_close();
echo "$weathercookie<br>
$getweatheruser<br>
$getstation<br>";
?>

I think I am not coding the queries properly

the cookie is the session ID

so where i need to go is...

get the user ID from session table where it matches the cookie value

$getweatheruser = mysql_query("SELECT `session_user_id` FROM `forum_sessions` WHERE `session_id`= ($weathercookie)");

then get the users station from the user table where it matches the the user id gotten from the session table

$getstation = mysql_query("SELECT `station` FROM `forum_users` WHERE `user_id`= ($getweatheruser)");

I just don't think I have the syntax right.

the echoes only produce the cookie value

OH... yes I checked to see if the Session ID in the cookie is valid... it is.

[etully]

Why are you putting parentheses around ($getweatheruser) ? Maybe you know some specific reason to do that which is fine - but I don't normally see that.

When I write queries where the userid is a string, I do it like this:

SELECT name,address from customers where userid='$userid'
(notice the single quotes around the key because I know it's a string).

When I know that the key is an integer, I leave it off:

select name,zipcode,state from cities where city_id = $id

That should give you a place to start.

[balloontrader]

The session_id is a mixed string

The user_id is a numerical string

I had already tried it all 3 ways

= $getweatheruser
=($getweatheruser)
echo nothing

='$getweatheruser'
echoes Resource id#2

based on your post it SHOULD go like this then

mysql_connect($server, $user, $password) or die(mysql_error()); 
mysql_select_db($database) or die(mysql_error());
$getweatheruser = mysql_query("SELECT `session_user_id` FROM `forum_sessions` WHERE `session_id`= '$weathercookie'");//using the mixed session string value
$getstation = mysql_query("SELECT `station` FROM `forum_users` WHERE `user_id`=$getweatheruser"); // using the numerical userid value
mysql_close();

which when tested echoes
Resource id#2 for $getweatheruser
and nothing for $getstation

[etully]

Once you perform the query, you need to extract the field from the row. For example, here are four queries and how you can get the data:

$query = "select code from product where id='$product_id'";
$result = @($query,$dbh) or die("$query<br>\n".@mysql_error());
list($code) = @mysql_fetch_row($result);

$query = "select country_code from ip_table where lower_ip < $long and high_ip > $long";
$result = mysql_query($query,$dbh) or trigger_error("$query<br>\n".mysql_error());
$number = mysql_numrows($result);
if ($number) { $countryfromip = mysql_result($result,0,"country_code"); }

$query="select * from key_words where key_words='$origkw'";
$result = MYSQL_QUERY($query);
$body1 = mysql_result($result,0,"body1");
$body2 = mysql_result($result,0,"body2");

$query="select name from customers where city='Hartford'";
$result = MYSQL_QUERY($query);
$number = mysql_numrows($result);
$name=array(); $i = 0;
while ($i<$number) {$name[$i] = mysql_result($result,$i,"name"); $i++;}

And there are many more. But it's not enough to just perform the query, that just creates a result handle. You then need to extract the row's data from the handle.

[balloontrader]

OK

here's what i came up with... probibly not the fastest or neatest code..

but it seems to work....

$connect=mysql_connect($server,$user,$password); 
mysql_select_db($database) or die(mysql_error()); 

//getting the user id;
$who_am_i="SELECT `session_user_id` FROM `forum_sessions` WHERE `session_id`= '$weathercookie'";
$weather_user = mysql_query($who_am_i,$connect);
$count_rows = mysql_num_rows($weather_user); 
if($count_rows == 1) 
{ 
$weather_id = mysql_fetch_assoc($weather_user); 
}
$my_weather_id=$weather_id[session_user_id];
echo "The count is $count_rows<br>
The session ID is $weathercookie<br> 
The User Id is $weather_user<br>
The user ID VARIABLE is $my_weather_id!<br><br>";

//getting the weather station;

$get_station =("SELECT `user_station` FROM `forum_users` WHERE `user_id`= $my_weather_id");
$user_station = mysql_query($get_station);
$count_rows2 = mysql_num_rows($user_station); 
if($count_rows2 == 1) 
{ 
$station_id = mysql_fetch_assoc($user_station); 
}
$my_weather_station=$station_id[user_station];

echo "The count is $count_rows2<br>
The user Station ID is $my_weather_station!"; 
mysql_close(); 
?> 

now let me see if I understand it right...

I am checking for a cookie.....

opening the SQL database...

assigning a variable to the info I want to look for (user ID where the row the row contains a session ID that must match the cookie value....

getting ALL rows that meet the Session ID requirement...
Counting the number of rows...

if rows = 1 then continue....

creating an array....

getting the one item from that array (user_id's VALUE).....

assigin the value to a new variable ....

doing the next serch for the station... based on the last user variable.....

repeat the step to ger the station Variable......

then tack that onto my weather page scrape (dropping out the echos of course)....

close?