Login system and user system, help needed

[ben.matthews18]

Hi,

So i've tried asking this question many times and haven't been able tp come up with an answer so i'm trying again in a much more understandable way, well i'll try..

Scenario -

• Ok if you think of facebook, there are many different users.
  
• These users are able to input personal information about themselves.
  
• This means every user on facebook will be inputting personal information which is *Unique to themselves.
  
• Still with me?

What I want to do - - This is going to be used as an example so I understand how it works.

• Once a user logs into my web site and is re-directed to the index.
  
• I want them to be able to write a number in a text box which they can save.
  
• This number will be saved in a SQL database.
  
• The number should then be echoed on a page which the user can access, lets say it's called numberpage.php

So if I have 100 different users registered to my web site they obviously aren't goint to input the same number in that text box.

I haven't been able to write a php statement that is able to 'get' this unique information for each different user..

My current php code - - here's what I have so far..

I currently only have a login system..

I just need some help writing the following -

SQL tables - Users and the number table with relationships

PHP sctipt to 'get' the random number from the SQL database using 'where' statements

I know this is a long post but help woult be appreciated! Thanks

[almostbob]

your current code, show it,
it is easy to add a column to the user table & use the same script that is used to verify the user at login, to get the random number
or create another table for the id and number

the same table for a single extra column

id, autoincremenmt int
name varchar
password_hash varchar
number int

select from table where id = $_Session number

[diafol]

As AB puts it.

You don't ask an user to supply a unique identifier, this is done automatically through an autoincrement field as noted. This is created when an user registers.

[ben.matthews18]

I don't mean once the user logs in a number is generated automatically. Think of Facebook, people can log in and post status', i'm trying to write a code that 'gets' only the data that is related to that user.

See if this SQL helps show whati'm trying to do-

Users table

CREATE  TABLE IF NOT EXISTS `Users` (
  `userid` INT NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`userid`) )
ENGINE = InnoDB

Status table -

CREATE  TABLE IF NOT EXISTS `status'` (
  `statusid` INT NOT NULL AUTO_INCREMENT ,
  `userid` VARCHAR(45) NOT NULL ,
  `status` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`statusid`) ,
  INDEX `userid` () ,
  CONSTRAINT `userid`
    FOREIGN KEY ()
    REFERENCES `mydb`.`Users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Current PHP -

<?php

$host="localhost"; // Host name
$username="test"; // Mysql username
$password="test"; // Mysql password
$db_name="test"; // Database name
$tbl_name="status"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE statusid ='$statusid'";
$result=mysql_query($sql);

// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
?>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td width="10%"><? echo $rows['Status']; ?></td>

</tr>
</table>

<?
// close while loop
}

// close connection
mysql_close();
?>

Some of this php may be incorrect, I need to intergrate the following;

Once the user logs in
User can write a status
PHP statement 'gets' only the status' that the user has inputted and not the whole table!

[diafol]

PK `userid` in users table is INT... - correct
FK `userid` in status table is VARCHAR(45) NOT NULL... - why?

These fields are the ones that join the two tables surely?

So change

FK `userid` in status table to INT

SO you log in and the session variable holds the userid for you.

$sql="SELECT status FROM $status WHERE userid =$userid";

that's the sql for getting the logged in user's status.

HOWEVER, why do you need a separate table for status. I assume the relationaship between user table and status table will be 1:1, so you could avoid all kinds of nonsense by combining them.

UNLESS an user can have multiple statuses? Not sure that would make sense though.

[ben.matthews18]

Thanks for your help, that's just what I needed. I'll set up a test site to test if it works and get back to you.

Many thanks again!

[ben.matthews18]

Hi again, i've set up a test site.. here's what I have set up.

SQL table -

CREATE TABLE `users` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `emailaddress` varchar(65) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `password` varchar(65) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `name` varchar(65) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1002 ;

Here's my php code to 'get' the name of the user that has logged in -

<?php

$host=""; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
$tbl_name="users"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database
$sql="SELECT FROM $tblname WHERE name='$name'";
$result=mysql_query($sql);

// Start looping rows in mysql database.
$rows=mysql_fetch_array($result);
?>
<table width="100%" border="0">
  <tr>
    <td colspan="2"><strong>This is your profile -</strong></td>
  </tr>
  <tr>
    <td>
<? echo $rows['name']; ?>

<?php
mysql_close();
?>

Nothing seems to have been echoed on the page and i'm getting this error on the page -

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/[Hidden]/public_html/profile.php on line 34

Line 34 in Dreamweaver is -

$rows=mysql_fetch_array($result);

Any help will be appreciated!

[almostbob]

I dont think you are approching this from the right direction

the user is presented with a login form
the for requests a username and password
the password is hashed and the hash and username are $_post-ed to the server on submit

>> should mean that >> when the select is performed there is only a single user name returned, there are no rows to step through

<?php
$host=""; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name=""; // Database name
$tbl_name="users"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database
$sql="SELECT FROM $tblname WHERE name='$_POST[\"name\"]'" ;
$result=mysql_query($sql);
if(!$result['password']==$_submit['hash']) die('incorrect login details'); // yeah its crap code
?>
<table width="100%" border="0">
<tr><td colspan="2"><strong>This is your profile -</strong></td></tr>
<tr><td><? echo $result['name']; 
mysql_close();
?>

not verified code, its after midnight & drinkin