Inserting data into certain fields

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
nick2
Forum Contributor
Posts: 118
Joined: Fri Jul 25, 2003 2:34 pm

Inserting data into certain fields

Post by nick2 »

Ok, I am creating a messaging script.. and when you send a message it will need to know whos account to store the message in.

so I have fields on a table called trading.

ID
password
name
location
email
signature
message

I need it to basicly search for the ID field the user selects to send message to.. then add the data to the field "message" in the database table trading.

how would I do this I have NO idea how.

I can connect and insert data to specific fields but i dunno how to find the ID then know how to make it add data in that message field on that row.

I know I suck at explaining things but its the best I could up with. :(

-Nick
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

First with your database structure a given user can only receive one message a time. If someone sends a second message and you use your system, it will erase the old message.

I think you want to do something like

Code: Select all

// get the id that matches the recipient's name
$query = "SELECT ID from trading where name='$recipient';";
$result = mysql_query($db,$query);
list($targetID) = mysql_fetch_array($result,0);

// now use the found ID to update the user's row
$query = "UPDATE trading SET message='$message' WHERE ID=$targetID;";
mysql_query($db,$result);
Again, I suspect your database design is flawed, but given your description this is how to do it. (NOTE: I haven't used mysql, so I may have the syntax slightly wrong for its function calls.)
User avatar
nick2
Forum Contributor
Posts: 118
Joined: Fri Jul 25, 2003 2:34 pm

Post by nick2 »

dude that really doesn't help.. its just incorrect code.. as you said its not mysql right? :(

and I amnot good with databases yet so I really can't fix it.
User avatar
nick2
Forum Contributor
Posts: 118
Joined: Fri Jul 25, 2003 2:34 pm

Post by nick2 »

look scratch the database crap..

HOW WOULD "YOU" make a messaging system? :(
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I would use a database, but I wouldn't cram it all into one table.

IE you have a "users" table with username, email, password

You have a messages table with "to","from","title","message". Both the "to" and "from" fields have foreign key reference clauses back to the users table.

That way any user can have multiuple message, from multuple people at any given time.

What was wrong with the code I gave? As I said, I can't test mysql stuff because I don't use it. I use PostGreSQL with my own wrappers around the function calls so I'm, unfamiliar with the MySQL way of doing things.

I would think that the code I gave at least gives the outline of what you ahve to do:
1. Use a select to search for the user by name
2. Use an update to change the field within the target table
User avatar
nick2
Forum Contributor
Posts: 118
Joined: Fri Jul 25, 2003 2:34 pm

Post by nick2 »

i'm giving up.. I really suck at it all.. maybe in couple weeks i'll pick it up again..
Post Reply