[SOLVED] any suggestions??

[nelquintin]

I have developed a membership system with help from this site and many a like and all i would like to know if it better to have a database per user or should i use a table for each user. when the user logs in i just want to display his/her contacts that they have added to the database. i have a vague idea about sessions.

[supermerc]

You use a table for all your users

[saint959]

hi, i did something similiar not too long ago. i wrote all the users' contacts into one table and linked them using the users' unique username. One table with many records.

 

hope this helps

[supermerc]

Ya you would have a table with like ID that is auto increment then username, password, name , email, date joined, and what ever else you want.

[nelquintin]

ok so i have tried

#

$result = "SELECT * FROM users WHERE username like '".$_GET['$session->username']."'" ;

or die("SELECT Error: ".mysql_error());

#

any other ideas thanks again

[saint959]

i would recommend you do not use LIKE in your SQL query. you could run into problems if users have simliar usernames.

 

i.e. if a user has the username "andrew" and another users username is "andrews", when andrew logs in he will also see andrews contacts because of the likeness in usernames.

 

another feature i had was, when one user added another they had to be accepted by the user before he would be visible. i have a similiar table structure to how supermerc recommended, i just had another field "status" where i would change the value in the field to "verified" once the contact had been accepted. so when i (for example) added a contact the status field would be "pending" until the user i added accepted me as their contact.

 

here is the SQL Query i use:

SELECT * FROM `tablename` WHERE `username` = '$username' AND status = "verified";

 

This is to stop users from spamming other users by guessing usernames and sending messages to randomn people.

[nelquintin]

ok now i have to write the contacts into the right table.so i tried

$result=mysql_query("INSERT INTO users WHERE username = '$username' (name, surname, birthday, mobile, office, home, email, maxprice, minprice, bed, gar) no luck also dont i have to specify the tablename which i would only know once they have signed in?

[boo_lolly]

http://w3schools.com/php/php_mysql_insert.asp

[nelquintin]

i have no problem with the syntax if i know which table to use.the problem is that i dont know how to speficy which table to use unless i know which user has logged in.

[boo_lolly]

so, how is it you're working on an application where you don't know the structure of the database? seems like important information.

 

check the manual to see if there's a way to print the database structure through a query: http://us.php.net/mysql

[nelquintin]

i do know the structure of my database.Maybe i can try explain a bit better. When a user logs in i want him to retrive his contacts/client that he has submitted. i have a table called user and then i have tables for each of the user that will use this database. So how can i input the table name in

SELECT * FROM `tablename` WHERE `username` = '$username'  when i would only know what the tablename is according to who signs in?hope this helps.

[trq]

You should not be using a different table for each user period. That is just poor database design.

 

Look up some tutorial on database normalization, there is just no need for each user to have a specific table related to them.

[nelquintin]

thank you now could you just help me with this line please

_{$result = mysql_query("SELECT name, surname, birthday, mobile, office, home, email, maxprice, minprice, bed, gar FROM client WHERE username = ".$_GET['username'])}

_{or die("SELECT Error: ".mysql_error());}

[chriscloyd]

$result = mysql_query("SELECT * FROM `client` WHERE `username` = '".$_GET['username']."'")

or die("SELECT Error: ".mysql_error());

[nelquintin]

that returns no result so i tried

$result = mysql_query("SELECT * FROM `client` WHERE `username` = '".$_GET['$session->username']."'")

or die("SELECT Error: ".mysql_error());

is this the wrong way to pass the username?

[saint959]

just for a test have you tried to hard code the username and see if you get any results?

[nelquintin]

i have tried

$result = mysql_query("SELECT `username` FROM `client` WHERE `username` = $user")

or die("SELECT Error: ".mysql_error());

this brings

SELECT Error: Unknown column 'quintin' in 'where clause'

[nelquintin]

used

$result = mysql_query("SELECT name, surname, birthday, mobile, office, home, email, maxprice, minprice, bed, gar " . "FROM client WHERE username = '$user'")

or die("SELECT Error: ".mysql_error());

solved.

[boo_lolly]

$sql = "SELECT
                *
          FROM
                client
          WHERE
                username = '$user'";

$query = mysql_query($sql) OR die($sql . "<br />\nCaused the following error: ". mysql_error());

 

replace $user with something you KNOW is in the database under the column 'username'.