Help With Querys: Insert, Select, etc...

DootThaLoop · May 18, 2008

Hi, I'm kind of new at PHP. Anyway, I have a login system on my site, and I would like to have a 'Userlist' of sorts that shows what pages the logged in users are on. Here is my main script, it is a file called 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
}

/* Grab current pages complete URL including after the question mark */    
echo $page; // will show the full page
echo $QueryString; // will show after ? 
// end of URL grab

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

users.php does a good job of showing the users that are on ANY page in the site, but I would like it to point out specific pages the users are on. You may have noticed the getpage.php, I'll explain that next, it also deals with some of the last few lines. This is 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)";
$query = "SELECT nowonline FROM active_users";
$verify = mysql_query($query);

?>

Now... here's where the problem comes in. I have included getpage.php on every page that I want it to reflect the users' locations. I don't know if this is the correct tactic. I also don't know if the last three lines (query and verify lines) are correct. Whatever the case, my Userlist will NOT show the pages that the users are located on. In fact, it would show what users were anywhere on the site before I started tampering with getpage.php. Now I'm having to constantly recreate the active_users table to even get that to work. But my main focus is, to find the users' page locations. So maybe I could get a little help?

Barand · May 18, 2008

$page is a string variable so should have quotes

$query = "INSERT INTO active_users (nowonline) VALUES ('$page')";

DootThaLoop · May 18, 2008

Thanks but... that still doesn't fix my issue. Could someone give me the right code to use or at least point me in the right direction please? :-\

Barand · May 18, 2008

You need to execute the INSERT with mysql_query($query);

DootThaLoop · May 18, 2008

Sorry for being kind of a beginner.... how would I do that?

Barand · May 18, 2008

... with mysql_query($query);

DootThaLoop · May 18, 2008

Yeah but when I do that, it doesn't come up as displaying the members on individual pages... I think I did something wrong in some of my previous scripting.

DootThaLoop · May 18, 2008

*bump*

darkfreaks · May 18, 2008

Try:

<?php

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

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

LooieENG · May 18, 2008

mysql_query("INSERT INTO active_users (nowonline) VALUES '$SERVER[php_SELF]'");

DootThaLoop · May 18, 2008

Try:

<?php

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

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

Worked like a charm for displaying the page locations.... but now my users' names are not being displayed for some reason.

darkfreaks · May 18, 2008

<?php

$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
}
?>

darkfreaks · May 18, 2008

<?php

$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
}
?>

darkfreaks · May 18, 2008

<?php

$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
}
?>

DootThaLoop · May 18, 2008

Okay now that I can see the issue, I'm getting this error.... what does it mean?

Duplicate entry '' for key 1

LooieENG · May 18, 2008

Should that not be

<?php

$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
}
?>

DootThaLoop · May 18, 2008

I changed it to that and I am still getting that error...

I have a theory, maybe I did something incorrect with the 'nowonline' field? It's currently of the type varchar(30). Should I edit that somehow? Change the type, or add an index or something? I'm not very skilled with SQL....

DootThaLoop · May 19, 2008

Allright I managed to fix that error. But it still wont' display member names..... :/

bilis_money · May 19, 2008

If i'm not mistaken you failed to declare PRIMARY KEY (INDEX). on one of your column.

DootThaLoop · May 19, 2008

Ok here is my table [active_users]. Can someone tell me what exactly is wrong with it?

DootThaLoop · May 19, 2008

*bump*

darkfreaks · May 19, 2008

<?php

$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
}
?>

DootThaLoop · May 19, 2008

<?php

$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
}
?>

Yeah I have that code, and it works for the most part. But I need to know what to do with my database [refer to image above].

Sign In

Help With Querys: Insert, Select, etc...

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

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