Please help with newbie code!

influx

Hey all,

I'm very new to PHP and MySQL and this is my first script I am scripting. Thus far, I have created a registration database with MySQL where people input usernames, passwords, and address, etc. that I have been successful with. Now, when they are logged in, I have them update a list of wishes, that I would like to save in the database.

My first table in MySQL, titled "users" contains their id (a primary key that automatically increments), username, password, address, etc.

Now, I would like to create another table titled "wishes" that corresponds with each persons "id" (found in the 'users' table). I also labeled "id" a primary key in the "wishes" table as well...is this wrong? Should it be a so-called 'foreign key'? Anyways, here is my code for inputting users wishes into my database, and when they submit, there are two things that go wrong:

1) It adds all the wishes, except it doesnt add the right id, the id it adds for anybody is "0" when in reality they are, for example, "15".

2) If they want to modify their wishes, it wont let them because there is already something in the database for their wishes. Is there any way to make it so if they modify a wish, it will erase the old and add the new?

Thanks a lot guys in advance, and here is a copy of the code I'm using:

<?php

require('db_connect.php'); // database connect script.

if (isset($_POST['submit'])) { // if form has been submitted, you can add them to db

$query = "INSERT INTO wishes(
id,
wish1,
wish2,
wish3,
wish4,
wish5,
wish6) 
VALUES (
'".$_POST['$_SESSION[id]']."',
'".$_POST['w1']."',
'".$_POST['w2']."',
'".$_POST['w3']."',
'".$_POST['w4']."',
'".$_POST['w5']."',
'".$_POST['w6']."')

mysql_query($query) or die(mysql_error());
?>

leatherback

Hi,

First of all..
Make sure you call session_start() at the beginning fo every page if you wish to pass information through there.

Second: In your "wishes"table:
You should have an id colum, auto-inc, for identification of unique records, besides that, you would want to have a colum, which I would call user_nr. That way you know it is not necessarily unique. This is your user ID. THen you could either have one col. called wish, or, do as you did (just one col is usually easier later on).

Then you insert into table the values as you did.

You can also use the update statement: update table set col1=''value1, col2=value2 ...... where user_nr = $_SESSIO['id']

Hope this helps.

influx

Hi leatherback,

Thanks for your help thus far, but should I auto-inc the users ID in the wishes table, if I have already done so in the users table. What happens is when they register, they are given a unique ID in the USERS table, and I would just like to store the SAME unique ID in the WISHES table for reference purposes. But, it still doesnt do it.

To fix the problem, can I do this:

<?php 

require('db_connect.php'); // database connect script. 

if (isset($_POST['submit'])) { // if form has been submitted, you can add them to db 

$query = "UPDATE INTO wishes( 
user_id, 
wish1, 
wish2, 
wish3, 
wish4, 
wish5, 
wish6) 
VALUES ( 
'".$_POST['$_SESSION[id]']."', 
'".$_POST['w1']."', 
'".$_POST['w2']."', 
'".$_POST['w3']."', 
'".$_POST['w4']."', 
'".$_POST['w5']."', 
'".$_POST['w6']."') 

mysql_query($query) or die(mysql_error()); 
?>

Thanks in advance! Also, what do you exactly mean by user_nr, im sorry i dont know what it stands for or what it does.

leatherback

Hi,

For later integrety, it is wise to let each table have a unique record identifier.

table: users:
u_id : Auto-Inc, unique record identifier
u_name : text

table: wishes:
w_id : Auto_inc, unique record identifier
w_u_nr: integer; 'user nr in wishes table' -> Corresponds with the u_id colum (Your foreign key)
w_wish: text: The wish

note how I place a letter before each colum: That way you can refer to individual colums wihout using the tablename, which is also easier when you have larger databases: You know from the colum name to which table the coum belongs.

Update statement does not take into, and is
update table set col1 = 'value1', col2 = 'value2'..... where

Also: The $SESSION is a super-global variable of PhP and is not POST-ed. So if you have a session_start() on the first line, and the $SESSION['id'] variable is set on a previous page, you can use the variable straight away.

influx

Thanks a lot leatherback!

By this:

Update statement does not take into, and is
update table set col1 = 'value1', col2 = 'value2'..... where

How exactly do I write the statement:

UPDATE wishes(
w_id,
w_u_nr,
w_wish1,
w_wish2,
etc................

Is that okay? or do I have to write "UPDATE table set( w_id, w_u_nr, etc..."?