Help With SQL Table

DootThaLoop · May 19, 2008

Hi, I'm working on a userlist that will not only display the names of users logged into my site, but their locations as well. Except I ran into a bit of trouble with my database... I'm not very good at SQL so could someone tell me what is wrong with the database? The problem is that the userlist will display the user locations and not their names. Here is a screenshot:

And here is the script. It is in two files.

users.php:

<?php

session_start(); #Start the session
include("getpage.php");

$hostname = "localhost"; #MySQL Hostname
$username = "helloism_hellois"; #MySQL Username
$password = "*********"; #MySQL Password
$database = "helloism_login"; #MySQL Database

$connect = mysql_connect(localhost, helloism_hellois, ********); #Connect to the mysql host
$select_db = mysql_select_db(helloism_login, $connect); #Select the database

if (isset($_SESSION['PHPSESSID'])) { #If the user is logged in, good for them, if not, they become an ip address
$username = $_SESSION['username']; #Username
} else {

$username = $_SERVER['REMOTE_ADDR']; #Username is IP Address
}

$time = time(); #Current time
$previous = "1"; #Time to check in seconds

$timeout = $time-$previous; #Timeout

$query = "SELECT * FROM active_users WHERE username=\"$username\" AND timestamp < \"$timeout\""; #Past 2 minutes

$verify = mysql_query($query); #Execute query

$row_verify = "mysql_fetch_assoc($verify)"; #Check if you have been here in two minutes

if (!isset($row_verify['username']))  #See if you were found
$query = "INSERT INTO online (username, timestamp) VALUES (\"$username\", \"$time\")"; #Put you on the online list

$insert = mysql_query($query); #Execute query

$query = "SELECT * FROM active_users WHERE timestamp < \"$timeout\""; #Check and see who is online
?>
<font size="3" face="Courier New" color="FFFFFF">
<?
$active_users = mysql_query($query); #Execute query
$row_active_users = "mysql_fetch_assoc($nowonline)";
if (isset($row_active_users['nowonline']))
{
do
{

echo ($row_active_users['nowonline']."");


}

while($row_active_users = mysql_fetch_assoc($active_users));

}

$online = mysql_query($query); #Execute query
$row_online = "mysql_fetch_assoc($username)"; #Grab the users
if (isset($row_online['username']))

{
do
{

echo ($row_online['username'].""); #Output username

echo '<br>';  #put a break after each database

}

while($row_online = mysql_fetch_assoc($online)); #Until all records are displayed

} else {
echo "No one's online."; #Inform user that no one is online
}

{

echo $page; # will show the full page
echo $QueryString; #echo query
echo '<br>';  #put a break after each database

}


?>
<title>[:Users Online:]</title>
</head>
<body bgcolor="000000" text="FFFFFF">
</body>
</html>

And getpage.php:

<?php
session_start(); #Start the session

$hostname = "localhost"; #MySQL Hostname
$username = "helloism_hellois"; #MySQL Username
$password = "********"; #MySQL Password
$database = "helloism_login"; #MySQL Database

$connect = mysql_connect(localhost, helloism_hellois, *********); #Connect to the mysql host
$select_db = mysql_select_db(helloism_login, $connect); #Select the database

$QueryString="";
foreach ($_GET as $key => $value)
{ 
$value = urlencode(stripslashes($value));
if($QueryString!="")
$QueryString .="&";

$QueryString .= "$key=$value";
}

$pageName=basename($_SERVER['REQUEST_URI']);

$page =$pageName."?".$QueryString;

$query = "INSERT INTO active_users (nowonline) VALUES ('$page')";
$sql = "SELECT (nowonline) FROM active_users";
$verify = mysql_query($query) or die(mysql_error());
$verified=mysql_query($sql)or die(mysql_error());

if($verify) { //dostuff
}else{ //error 
}
if($verified) { //dostuff 
}else{ //error
}
?>

nloding · May 19, 2008

What contains their location? I only see username, nowonline, and timestamp in that DB.

Look into JOIN commands if you're using two tables:

http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

I use this in my current site, but I think the DB is different.

SELECT location FROM users JOIN user_data ON users.userid=user_data.userid WHERE username='$username'

DootThaLoop · May 19, 2008

What contains their location? I only see username, nowonline, and timestamp in that DB.

Look into JOIN commands if you're using two tables:

http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

I use this in my current site, but I think the DB is different.
SELECT location FROM users JOIN user_data ON users.userid=user_data.userid WHERE username='$username'

'nowonline' is what contains their locations.

nloding · May 19, 2008

Take the quotes off your mysql_fetch_assoc calls, then see what happens. Example:

<?php
// You have:
$row_verify = "mysql_fetch_assoc($verify)"; #Check if you have been here in two minutes

// It should be:
$row_verify = mysql_fetch_assoc($verify);
?>

Also, you have an insane amount of code for something that should be minimal. Example:

<?php
// Your code:
$active_users = mysql_query($query); #Execute query
$row_active_users = "mysql_fetch_assoc($nowonline)";
if (isset($row_active_users['nowonline']))
{
do
{
echo ($row_active_users['nowonline']."");
}
while($row_active_users = mysql_fetch_assoc($active_users));
}


// This is easier and cleaner and requires less processing
$active_users = mysql_query($query); #Execute query
while($row_active_users = mysql_fetch_assoc($nowonline))
{
echo $row_active_user['username'] . " is online.  Their location is: " . $row_active_users['nowonline'];
}

Check out the tutorials sections and read up on some MySQL/PHP examples and 'best' practices.

DootThaLoop · May 19, 2008

When I remove the quotes I get errors:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/helloism/public_html/ynw/login/users.php on line 42

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/helloism/public_html/ynw/login/users.php on line 58

Also, its not an issue with the way I have my database setup?

nloding · May 19, 2008

You DB is fine for what you want to do here, why wouldn't it be?

I think your SQL statement is wrong and I missed it before. Try:

$query = "SELECT * FROM active_users WHERE username='$username' AND timestamp < '$timeout'";
--OR--
$query = "SELECT * FROM active_users WHERE username=" . $username . " AND timestamp < " . $timeout;

Swap the \" for ' or concatenate it with the variables (building a string) ... again, do some reading in the tutorials section for PHP/MySQL. Also, it helps to echo your query if you're having issues with it so that you can see exactly what is being queried. As a good practice when debugging, do the "or die("Could not query database: " . mysql_error());" after EVERY DB call -- query, fetch_assoc, connect, select, whatever. Then you can see exactly where you're dying.

DootThaLoop · May 19, 2008

I did exactly what you said and put "or die(mysql_error())"; after everything. But it doesn't display any error and also the usernames still don't display.

DootThaLoop · May 20, 2008

*bump*

nloding · May 20, 2008

Once again, I may have missed something when reviewing the script. Why are you echoing everything in between your <head> tags? In fact, why isn't there a <html> or <head> tag, and only </head>?

I don't mean to sound rude or offensive; you need to go back and either a) relearn basic markup (XHTML) then basic PHP, or b) go back and redo your markup.

When you echo something in PHP, it shows up in the markup (ie, you'll see it when you view the source of a page, but you won't see the PHP code). Basically, a website should be:

<html>
<head><title>Title</title></head>
<body>
<!-- body text here -->
</body>
</html>

Without that, you don't have anything. What you're doing is this:

<table><tr><td>username</td></tr></table>
<title>Online Users</title>
</head>
<body></body>
</html>

I'd suggest starting the page over, with just the design. Manually type in random usernames and pages so you get the design how you like it. Then go through and replace the usernames you typed in with the variables and MySQL results.

DootThaLoop · May 20, 2008

I know the basic HTML structure.... and I understand what you're saying. But if I were to put the script in the HTML structure you're talking about......

<html>
<head>
<title></title>
</head>
<body>
Script goes here...
</body>
</html>

I get a headers error. Now I'm fairly new at PHP and this whole thing is a learning experience for me. I have been working several days at perfecting this script to no avail.

Basically.... I'd just like to know what I'm doing wrong, and how to fix it, plain and simple. I'm not a PHP expert, I don't know all these big terms that you all use.

nloding · May 20, 2008

Header errors don't have to do with the <head> of the page. A header is what the browser sends a server, and what the server sends in response. Just like email headers.

Are you making any Header() calls in your script? I don't see any, but that doesn't mean you've posted the entire script.

Post what Header error you are getting. And repost the entire script -- the entirety of the page you're having issues with, and the entirety of all the included/required pages.

Also, as a side note, why do you place the MySQL username and password into variables, and then not use the variables when connecting to the database? If you're going to define them in variables, do mysql_connect($server, $username, $password).

DootThaLoop · May 20, 2008

Here is the headers error I get:

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/helloism/public_html/ynw/login/users.php:5) in /home/helloism/public_html/ynw/login/users.php on line 6

Sign In

Help With SQL Table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information