Update Client Record and Insert New Records

[Sponge Bob]

Hello,

I have this issue and would like some help on it.

1. I want to design a system whereby a person can register and the details get logged into MYSQL. ( I have done this )
2. The person can login and go to the members area, the username and password should be correct from the mysql ( Done this )

3. I want a administrator panel whereby i can have a form or any info, where i can pick the user from a dropdown list, and then add the following info to their records.

- Phone Number Called
- Minutes called
- Extension Called

Now there will be extensions like 0845, 0800 and others.

I want 0800 to be free and 0845 to be $1/minute so when i add the number of minutes called and the extension there should be a field in the mysql that gets updated with the calculation.

Then after all the input is done the mysql should be updated with the necessary records.

Also then when the user logs into his members panel he should see who he called within the last 10,20,30 days and how much is his charge.

I have only one database table for now which is setup as follows and is for the registration details.



Please assist.

Please reply if you have any questions or anything

Thanks & Regards

Sponge Bob Reviewed by Sponge Bob on 16th Jul 2010. Update Client Record and Insert New Records Hello, I have this issue and would like some help on it. 1. I want to design a system whereby a person can register and the details get logged into MYSQL. ( I have done this ) 2. The person can login and go to the members area, the username and password should be correct from the mysql ( Done this ) 3. I want a administrator panel whereby i can have a form or any info, where i can pick the user from a dropdown list, and then add the following info to their records. - Phone Rating: 5

[Hyperz]

I'll leave this for litewarez.

[dermechove]

hmmmm...
sorry litewarez!!! u didn't show up yet.
anyway I may help out with some PHP code

include (DB.php); //DB config and function.
connect_DB(); //connect to the DB
$querry_select="select username FROM userstable";
$result_select=fetch_results($querry_select);// pulling results out of DB and processing it.
echo "<select>";
foreach($result_select AS $user )
{
echo  "<option>$user</option>";
 }
echo "</select>";
//users are pulled and displayed into a dropdown list.
/*
here put the HTML code of the calls proprieties ( Phone Number Called,Minutes called, Extension Called).
*/ 

a second file that will receive the result.(it could be done with one file but this simpler )

$username=$_POST['username'];  //the drop down menu that contain the username.
$phone=$_POST['phone_number']; //Phone Number Called
$minutes=$_POST['minutes']; //Minutes called
$ext=$_POST['extension']; //Extension Called
$cost=0;
if ($ext == 0845) then
{
$cost=$minutes; 
}
$querry_insert="insert into Calls values (null,'$username','$phone','$minutes','$ext','$cost')";
//the ID of the call is a primary key and it's auto incremented thus the null in the beginning .
result_insert=mysql_querry( $querry_insert);
echo $result;
} 

make the username as your primary key for the users table so that it will be no dublicates(same user name).

[Sponge Bob]

hmmmm thanks for that code.

I will try that code and get back to you.

[Sponge Bob]

Well as you can see here

www.callsforu.com is the name i have registered for this project.

I have for the login system and all done.

Now i need to have another page for the admin who can be able to post the various details to a clients database.

Do i need another database opened for this, and if yes what fields can i put on that database?

Please assist.

[litewarez]

ok so make sure you always setup abstract tables

what i mean by abstract is keep certain entities separate but linked.

For example you obv are doing some cost per call system and each member has a number dedicated to them

Create a new table called phone_types and you can populate like so

|------------------------------------------|
| ptype_id    ptype_code   ptype_costpermin|
-------------------------------------------|
|     1             0800         1.00      |
|     2             0845         0.00      |
-------------------------------------------|

So you have an id for each type of call

you can then add a column in your users table and depending on the type of call you putt an id in the ptype column for users..

this way each user will have the id number to a row in the other table

then what you can do is use JOINS to get the type of call with the user

SELECT *.user FROM users LEFT JOIN ptypes ON (users.ptype_id = ptypes.ptype_id) WHERE user_id = 2;

then you can always change the prices and it will propagate throughout the application.

Does that help ?

---

And derm.... wtf is

if $_post["extension"] == 0845 then 

and

$querry_insert="insert into Calls values (null,'$username','$phone','$minutes','$ext','$cost')";
result_insert=fetch_results( $querry_insert); 

you cant INSERT data queries with a FETCH command

[dermechove]

And derm.... wtf is

if $_post["extension"] == 0845 then 

and

                       $querry_insert="insert into Calls values (null,'$username','$phone','$minutes','$ext','$cost')";
result_insert=fetch_results( $querry_insert); 

</div>
you cant INSERT data queries with a FETCH command

hehe my bad [corrected the code in the first post] (thx LW)
didn't think about the "types of calls" table,this will avoid a lot of redundancy.

[litewarez]

Also query is on spelt with 2 r's

[DeLeTeD]

I would also like 2 add and suggest not to use JP's login script as its 5+ years old and can be exploited fairly easy.

[Mr Happy]

I don't know how much you've done or coded of the general login area but I wouldn't use dermechove example above as it enters the data directly into the DB without checking for malicious script leaving you prone to exploits.

Personally I don't bother creating a login script every time from scratch every time I'm starting a new project as it's unnecessary and time consuming. I start with a base and modify it to my needs. For a great and easy to modify base I'd suggest http://php-login-script.com/ It has an admin area, validation, protection, simple admin area etc etc. all you need and will save you time. For a simple script and site like this it's ideal.

Then use litewarez idea of a separate table for the caller part and you'll be sorted. I wouldn't have it in the same table as the user details.