PHP/Mysql

[jeadeb]

Hi everyone!!

I need some help with setting up a php page that calls an sql database..

here is what I want it to do?

-firstly: a user must be able to log in(because under this login you must be able to get stats from this user)

Then the user has 3 options (links) for example:

clink chlink elink

when a user clicks a link it takes you to another page with 3 more links.

plink cnlink hlink

(these links takes you to a list of tpics.. same topics for each link)

now I guess what I want to do here is make a counter, so that it couts each time you click a specific link and adds it to a table:

for example

clink 26

chlink 23

elink 27

in a table on a web interface..

for me it gets tricky when each user under a seperate username, logging into the same database, should have unique results,,

so you can call.. how many times did jsmith click clink,, for example//

I use Apache server so I want to use php,, i actually like the language,, and I am using mysql for a database, should I need one in this project..

I think I am on the right track with this, what I have done is built a mysql database with the info above in it, and am calling the databse withphp, selecting the appropriate links above... it's just counting it !! no I am stuck!!

Please help ,, Thanks

[zippee]

You can create a table which contains username and names of all the 9 links (3x3). When the user login, the counter only update the number of click of this user, not others. For example:

UPDATE table SET clink = clink+1, cnlink = cnlink+1 WHERE username = '$user'

Using a simple math, you can also sum up all the clicks you get for one particular link, and % of all clicks etc.

[jeadeb]

Thanks alot!!, this forum is great... I had writers block, thanks for putting me back on track :)

[jeadeb]

Zippee!!

your help was greatly appreciated, however you suggested I make a table with username and name of links.

When I started getting down to this, I started making tables, and just confused myself all over again...(I only started using mysql and php about 3 days ago, so please bear with me)... I learn pretty quick.

this is what I started: a table with all usernames and passwords. so I could make a login page, when the user logs in, the first step must start example:

clink chlink elink (this is in a table of course)

are you suggesting that I insert the above links/all the links under each user name? and then selecting what I want to see on each page?

like:

+-------------+
-USER1
+-------------+

[Puckdropper]

Create another table with the username and the stats you want to keep. You'll then have two tables:

[Users]
Username
Password

[Stats]
Username (Foreign key* to Users.Username)
LinkA
LinkB
LinkC
LinkD

[* A foreign key requires the data in that field to be unique and linked to another field in another table. It makes sure you don't have stats for Joe User while Joe User doesn't exist in your users table, for example.]

[jeadeb]

Thanks for the response, I will work on thsi right away.. however I am stuck with something again, I will paste the code here.. firstly some details:

there are 3 webpages involved here:

page 1

linkA ID = 1
linkB ID = 2
linkC ID = 3

page 2 (every link on page one takes you to this page below, thats why each one has an ID:

linkX ID=1
linkY ID=2
linkZ ID=3

Page 3 (all the links from page 2 takes you here)

link1
link2
link3
link4
link5
etc...

the result should be:

EXAMPLE

how many link2's of linkY from linkB have been clicked?

so you would get:

today, user1

linkX
------
linkA linkB linkC
link1 3
link2 5
link3 3
link4 1 3
etc...

linkY
-----
etc,, same until linkZ(so 3 tables with results for link X, Y and Z)

My fistp page liiks like this:

actions.php

<? 
$database = "strokesheet";
$user = "odbc";
$pass = "";
$hostname = "localhost";

mysql_connect ( $hostname, $user, $pass)or die("Could not connect: ".mysql_error());
mysql_select_db($database) or die(mysql_error());
 
$results = mysql_query("select * from actions");
$numrows = mysql_num_rows($results);
for ($i=0 ;$i<$numrows; $i++)
{
 $row = mysql_fetch_array($results);
 echo("<p><a href=\"companies.php?actionid=");
 echo($row["actionID"]);
 echo("\">");
 echo($row["actionName"]);
 echo("</a></p>");
}
?>

this page is perfect!!

the next page:

companies.php

<? 
$database = "strokesheet";
$user = "odbc";
$pass = "";
$hostname = "localhost";

mysql_connect ( $hostname, $user, $pass)or die("Could not connect: ".mysql_error());
mysql_select_db($database) or die(mysql_error());
 
$results = mysql_query("select * from companies");
//assume company 1
//<table>
//loop through topics
 // <tr><td>$topicname</td>
 //loop though actions
  //select count(*) as num from strokes where actionid = x and topicid = y and companyid = 1
  //<td>$num</td>
 //end loop actions
 //</tr>
//end loop topics
//</table>
$numrows = mysql_num_rows($results);
for ($i=0 ;$i<$numrows; $i++)
{

 $row = mysql_fetch_array($results);
 
 
 echo("<p><a href=\"topics.php?actionid=");
 
 echo($actionid);
 
 echo("&companyid=");
 echo($row["companyID"]);
 echo("\">");
 echo($row["companyName"]);
 echo("</a></p>");
}
?>

HERE IS THE PROBLEM: FINALY!!!

because I want each company ID to correspnd to actionID etc, eventually topcID...

I want to see in the url: http://blablablablabla//something?actionid=1&companyid=2 for EXAMPLE.

on page 2 above it doesnt want to work, and gives me a long explanation in the address bar(can't remeber now)

whats wrong with the code on page 2, is my question
hint: I think it has something to do with this variable i'm calling from page1: is this syntax correct?
echo($actionid);

THANKS A MILLION!!!!! any help with this project will be appreciated..

[zippee]

It would be useful to check the source code for each page, for example in your action.php page, make sure the link created is correspond to the action id you post from one page to another. You SQL query may have to change to select * FROM companies WHERE actionID = '$actionID'. It would be very useful if you paste the error message here so myself or someone could comment on it.

[jeadeb]

thanks for the reply, I'ts greatly appreciated, i will generate the error and post it here, I can only do this tomorrow as the PC at work has no access to php enabled server....

thanks again..

[jeadeb]

here is the error I got in the in address bar after I click on

http://localhost/phpmysql/topics.php?actionid=<br%20/><b>Notice</b>:%20%20Undefined%20variable:%20%20actionid%20in%20<b>c:\www\phpmysql\companies.php</b>%20on%20line%20<b>46</b><br%20/>&companyid=1

after I click on linkX ID=1 for example,, on page two.. like I said, if I remove, echo($actionid) ; it goes away, but I cannot follow the ID's from the previous page if I do this. please help ,, thanks alot.

[zippee]

No one can see your localhost... But the text appear in the link did showed that you have problem in line 46 (?) or more precisely is you did not define $actionid in your program. If the value is post from previous page, the use $_GET or $_POST to retrieve it.

[jeadeb]

YES!!

thanks man, I had to do this , I had register globals set to off in php.ini, so I needed to use $_get...

Thank you again.. something so small I forgot.. thats php!

now I can move forward,, I will come back for more advice later should I need it, thanks again..

P.S,, if there are anymore tips you can give me on this project, like resources, where applications like this have already been created,, please direct me..

cheers..

[jeadeb]

here is another idea I had,,,

Is it possible to add a link to my web page such as a topic"accounts issues"
when I click on "accounts issues" it adds "account issues" to mysql databse under table topics..???
table topics has topicName and topicID (topicID as primary key)

so that at the end of the day, I can use the count(*) command to see how many times "account issues"
was clicked..

Also I would like a counter that counts how many times "accounts issues" was clicked today,
how many times it was clicked yesterday + today, and eventually,, how many times it was clicked this month.

can someone write me a working php script for this exapmle, if it can be done and explain how it would work.

((I realize there might be nother database involved here to add the different days results to a month..))
Please!!!!
any help would be greatly appreciated..

[zippee]

I would suggest you go through a few hit counter scripts, as I found your application are quite similar. Try hotscripts.com and there should have a few hundred similar scripts.

I once wrote a counter script (couple months ago), but scrapped it as I realise there is many free yet more comprehensive counters available (based on the little amount of effort I put in). Well ... good luck .

[jeadeb]

Thanks alot,,

I am just about finished with this project now..

However, I am getting one more problem with my session login screen..

here's what happens:

I add users to the database, and then I use the username to login into the webpage above that I created..

It works great, .. but after using the same login a few times, it wont login in with the username anymore, and I have to create a new login name to login again.. I can never log in with the user name I created before..

Is this a common problem?

here is the script:

login.php

<?php
//Database Information
$dbhost = "localhost";
$dbname = "strokesheet";
$dbuser = "odbc";
$dbpass = "";
//Connect to database
mysql_pconnect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());
session_start();
$username = $_POST;

$query = "select * from strokes where empNo='$username'";
$result = mysql_query($query);
if (mysql_num_rows($result) != 1) {
$error = "user not in database";
include "login.html";
} else {
$_SESSION = "$username";
include "actions.php";
}
?>

the userID field in the databse is called empNo:

here is the page that registers this per session:

confirmation.php

<?
session_start();
$database = "strokesheet";
$user = "odbc";
$pass = "";
$hostname = "localhost";

mysql_connect ( $hostname, $user, $pass)or die("Could not connect: ".mysql_error());
mysql_select_db($database) or die(mysql_error());
$_SESSION = "$empNo";
//$username = $_POST;
$date = date("d M Y H:i");
$results1 = mysql_query("insert into strokes (strokeTime, empNo, actionID, companyID, topicID, subtopicID) values ('$date','$empNo', '$actionid', '$companyid', '$topicid', '$subtopicid')");
include "actions.php";
//header("Location: actions.php");
//$username = $_POST;
//$_SESSION = "$username";

?>

I think something is a bit messed up with the variables.. because sometimes I get error and sometimes I dont... Like whenI use the correct variable name.. it doesnt work , and when I use 'username' it works.. and then vice versa..

Also, While I am at it, my date function does not work.. the time in MYsql database shows 00:00:00:0000 (for example)

thanks for all your help!!

cheers..