Help With Messages in Mysql Stop Overwriting

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

Could you be using an UPDATE instead of an INSERT statement?

Newbie Poster

19 posts since Jul 2006

Reputation Points: 10

Solved Threads: 1

Thanks so much for your reply,

Now I am trying to use your suggestion , I think you may be right. But with INSERT INTO when I try to add the user id # like in UPDATE I can add the user id -Like this: id='2'");

But with INSERT INTO It does not work! I would appreciate any help again, Thank-you , puddin

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

Ok, this should just be a matter of syntax. For your query, use:

INSERT INTO table_name (id) VALUES ('2')

See here for more in-depth info (my example is a little short to be really useful): http://www.w3schools.com/sql/sql_insert.asp

Thanks so much for your reply,

Now I am trying to use your suggestion , I think you may be right. But with INSERT INTO when I try to add the user id # like in UPDATE I can add the user id -Like this: id='2'");

But with INSERT INTO It does not work! I would appreciate any help again, Thank-you , puddin

Newbie Poster

19 posts since Jul 2006

Reputation Points: 10

Solved Threads: 1

This is what I have , it's not working, it's creating a new id and not going into (id) VALUES ('2')

<?php
include'db.php';
$sql = mysql_query("INSERT INTO myd (id, fm, mg, last_login)VALUES
('$2','$fm','$mg', now())") or die (mysql_error());

if(!$sql){
echo 'There has been an error creating your account. Please contact the webmaster.';
} else {
echo("");
}
?>

Now in mysql table under fm and mg They Represent: who the message is fm=from and the mg=message : I have them set as a blob: Do you think that is correct?

I did that thinking it could hold a lot of messages : You see I want my users to be able to message each other - and when they click on their message button - They can view all their messages sent by other members -

I want to add a delete button beside each message so the user can delete their messages as they desire.

I have my message form setup great ,

You write your message,
It automatically goes to the mysql table correctly
Postes your message in the right users id -
Than it posts your messsage correctly on the users page
who you sent your message to...

Last it automatically includes that the message is from you...

But when a second message is sent it erases your message. I was using UPDATE now I am trying to use INSERT INTO.

I need mysql table to keep all messages. Users are listed by AUTO INCREMENT 1,2,3 and so on...

I want to allow the user on their message page to see all their messages I want to show a delete button beside each message and let the user delete their messages as they wish.

Thanks again so much, this is the last part of my site so any help again is much appreciated, puddin I also would like the user to recieve an email letting them know, they have a message! I think that part shouldn't be difficult.

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

Is the $2 in your query supposed to be a PHP variable? It may be a typo, but if not I don't think PHP allows a variable to begin with a number.

One trick I used to use a lot when beginning with PHP/MYSQL (not to imply you're a beginner!) is to assign the query string to a variable and then echo the string to make sure it looks like it's supposed to. Like this:

[php]$query = "SELECT * FROM table_name"; //using your query, of course
echo $query;
$result = mysql_query($query);

if (!result) { echo "error with db query!"; }[/php]
In your case, you will be able to see exactly what query your computer thinks your are trying to perform. If that doesn't point you to the problem, post back your intended query in PHP format and then what is echoes to your browser. That $2 seems awfully strange to me though.

-----

As far as the BLOBs go, it's probably the right choice for the message, but depending on what exactly you're storing for the 'from' field, you could probably use VARCHAR (and then you would specify the max number of characters - like 200 or whatever you choose). For instance, if the from field is a username and you specify a maximum length for username as 30 characters, then you would use VARCHAR(30). The VARCHAR type can hold a max of 255 characters.

---

I've used the PHPMailer class ( http://phpmailer.sourceforge.net/ ) without any problems. There may be something better out there now, haven't looked in a year or so.

Newbie Poster

19 posts since Jul 2006

Reputation Points: 10

Solved Threads: 1

I am a beginner 100% I love working with programming a lot, everytime I learn something new I get this big smile!

I have it setup that that the message is viewed before posting but what you have showed is excellent!, thanks,

I'm still trying to get the INSERT INTO to work, I'm hoping it will not overwrite the last message, any other suggestions on that?

Thanks again, puddin

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

Does the echoed query match what you expected? Can you post the echoed query here? Was the $2 in your original query a typo?

Newbie Poster

19 posts since Jul 2006

Reputation Points: 10

Solved Threads: 1

Sorry about the typo question, no I thought it would work, but it does not nor without it... if I script it like :
INSERT INTO table_name (id) VALUES ('2') It still creates a new ID.

It echos but it creates a new column a new id in my table,
I need it to go into id=2 than it would display on id2's messages page , you see what I mean... and it doesn't work

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

How can I create this message system, I'm trippin out , it has to be possible! I am wondering if I am going about it the right way, gee it's tuff when we don't know uh!

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

Ok, I'm not sure why it's creating a new row in the db. I thought maybe you had the id column set to auto-increment, but I think the query would actually fail if you did that. It might be something to check first anyway...

I tried to mimic what you are trying to do, and below is what I did. It seems to work ok for me.

I set up a test db / table with the following parameters:

-- 
-- Database: `_test`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `test_table`
-- 

CREATE TABLE `test_table` (
  `id` int(8) unsigned zerofill NOT NULL auto_increment,
  `user_id` varchar(30) NOT NULL default '',
  `fm` varchar(50) NOT NULL default '',
  `mg` blob NOT NULL,
  `last_login` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

(If you're not familiar with this syntax, it can be copied and saved as a text file, then imported into mysql. If you're using phpmyadmin, you would create the database called _test , click the SQL tab and then select the file to import.)

I then drafted test.php page with the following code:
[php]
<?php
//you'll need to modify this line
require '../db_control.php';

echo "Enter some text and then click Insert...

";

?>

User ID:

From:

Message:

<?

if ($_POST['submit']=='Insert') {

$user_id=$_POST['user_id'];
$fm=$_POST['fm'];
$last_login=time();
//clean up the message text
$mg = htmlentities($_POST['mg'],ENT_QUOTES);

$query="INSERT INTO test_table (user_id,fm,mg,last_login) VALUES ('$user_id','$fm','$mg','$last_login')";
echo "This is the query string that would be performed on the db:
".$query."";

echo "Now performing query...

";
mysql_select_db('_test');
$result=mysql_query($query);
if (!$result) {
"There was a problem with your query.";
}
else {
echo "Query performed successfully.

";
echo "This is the last row that was entered in the db:
";
$query2="SELECT * FROM test_table ORDER BY id DESC LIMIT 1";
$result2=mysql_query($query2);
$row = mysql_fetch_assoc($result2);
echo ""; print_r($row); echo "";
}
}
?>
[/php]
I know this code is ugly, but it does the trick for now. I like to get things working first and then pretty up the code later.

The php code will allow you to enter some text, submit the form, and then it will show you the results. If you use the code, remember to change the database name, etc. if needed.

Newbie Poster

19 posts since Jul 2006

Reputation Points: 10

Solved Threads: 1

That is really kind of you! Good heavens I am truly pleasantly suprised, Thanks so much!

I did the test_table , brought up the code as test.php and I see it all.
On the Test_table mysql said I had an error with :
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
What is that? I took it out the table is showing with everything else.

You know what I want to ask you is this, Is their a php code that repeats the INSERT INTO the same Id : The ID stays the same of course ID 2 is John for example:
But I need all fm's and mg'g to repeat and store log in Johns Id#2 , is this possible do you know. Cause that is what the idea is right i want all john's messages to log under his ID in the mysql database...
Now I filled out the form but I don't see nothing in the test table what do I do?....

Is it something to do with I needing to add Id's or is it because of this line: ENGINE=InnoDB DEFAULT CHARSET=latin1;
I'm confussed I'm going to look it over...
Thanks again really, puddin

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

I want to show you this is what I have now:

HERE IT IS GETTING WHO THE MESSAGE IS FROM THE SENDER

<?php
include 'db.php';
// get the variables from home page
$password = $_SESSION['password'];
$email_address = $_SESSION['email_address'];

$sql_check = mysql_query("SELECT fm FROM myd WHERE id='2'");

$fm = mysql_fetch_array($sql_check, MYSQL_BOTH); //MYSQL_BOTH;

$sql_check_num = mysql_num_rows($sql_check);
if($sql_check_num == 0){

echo ("");
} else {
echo ("");
}
?>

HERE IT IS PRINTING AND SHOWING THE SENDER THE MESSAGE IS FROM THEM

<?echo ucfirst($fm['fm']);?>

HERE IT IS GETTING THE MESSAGE THE SENDER IS SENDING

<?php
include 'db.php';
// get the variables from home page
$password = $_SESSION['password'];
$email_address = $_SESSION['email_address'];

$sql_check = mysql_query("SELECT mg FROM myd WHERE id='2'");

$mg = mysql_fetch_array($sql_check, MYSQL_BOTH); //MYSQL_BOTH;

$sql_check_num = mysql_num_rows($sql_check);
if($sql_check_num == 0){

echo ("");
} else {
echo ("");
}
?>

HERE IT IS PRINTING THE MESSAGE FOR THE SENDER TO VIEW

<?echo ucfirst($mg['mg']);?>

NOW AS YOU SEE ABOVE THIS MESSAGE IS BEING SENT TO MEMBER ID='2'
THE SENDER GETS TO VIEW THE COMPLETE WHO IT IS FROM AND MESSAGE, THEY LIKE IT ALL IS GOOD, THAN I HAVE A BUTTON THAT THEY CLICK TO CONFIRM AND IT GOES.

MY PROBLEM IS THE ID'S RECIEVING THEIR MESSAGES BECAUSE I AM USING UPDATE ONLY CAN RECIEVE ONE MESSAGE , UPDATE DOES NOT KEEP ALL THE MESSAGES BEING SENT , UPDATE OVERWRITES THE LAST MESSAGE, HOW DO I SCRIPT IT THAT ALL THE MESSAGES ARE KEPT IN MYSQL AND I CAN RELEASE THEM ON THE ID'S MESSAGE PAGE BY SELECTING FM' AND MG' SEVERAL TIMES AND ALL THE MESSAGES WILL SHOW. HOW DO I GET THE MYSQL DATABASE TO NOT OVERWRITE!

THIS IS THE MOST DIFFICULT PART I HAVE COME ACROSS.... IS IT SOMETHING.. HOW DO THEY DO THIS!

Junior Poster in Training

52 posts since Mar 2006

Reputation Points: 10

Solved Threads: 0

The text after the closing paranthese in the mysql table definition shouldn't matter. It definitely isn't impacting the id thing.

If the table wasn't created successfully, remove the text ENGINE=InnoDB DEFAULT CHARSET=latin1 from the mysql definition and try importing it again. Once the table is properly created, pressing Insert on the HTML form should insert a row into the table.