Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    New Coder
    Join Date
    Aug 2013
    Posts
    15
    Thanks
    3
    Thanked 0 Times in 0 Posts

    mysql php problem reading sent messages

    Hi



    I would be ever so grateful if there is a PHP / MYSQL genius out there that can solve this annoying issue I have



    I have a member site with just 3 test members at present. Where users can simply send and receive internal messages to each other. The messaging system works to a fashion. However when a user goes to send a message to another user the following error occurs;



    Database error: Invalid SQL: insert into sky_message (to_id,from_id,subject,message,send_date) values('1','3','testy','another test',now())
    MySQL Error: 1062 (Duplicate entry '1' for key 1)
    Session halted.



    Database error: Invalid SQL: insert into sky_message (to_id,form_id,subject,message,send_date) values('4','2','test','another test',now())
    MySQL Error: 1062 (Duplicate entry '2' for key 2)
    Session halted.



    Database error: Invalid SQL: insert into sky_message (to_id,form_id,subject,message,send_date) values('3','2','test','again',now())
    MySQL Error: 1062 (Duplicate entry '3' for key 1)
    Session halted.





    Within mysql table called sky_member



    I have set member id to int(5) and there are currently 3 members with member_id as primary key with auto_increment 1,2,3 (1 for 1st member, 2 for second and so forth)



    table called sky_message



    The problem is there is a sky_message table with fields; to_id, from_id and msg_id.

    These values are conflicting with the member_id value within the sky_member table.



    Please help solve this one? I have attached screen shots of the table
    Attached Files Attached Files

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,100
    Thanks
    23
    Thanked 594 Times in 593 Posts
    member_id as primary key
    That's the problem. make the primary key separate. member_id should not be the primary key.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    New Coder
    Join Date
    Aug 2013
    Posts
    15
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi

    sky_member table holds the member_id. This field does not exist in the sky_message table

  • #4
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,871
    Thanks
    2
    Thanked 164 Times in 159 Posts
    The primary key for sky_message should be msg_id and to_id could be setup as a foreign key.

  • #5
    New Coder
    Join Date
    Aug 2013
    Posts
    15
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks but issue still remains

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    In that ".doc" file you wrote:
    Although I have made to_id and from_id unique.
    Why in the world would you do *THAT*?

    That means that you can only have *ONE* message with each unique to_id. And *ONE * message with each unique from_id.

    Start by removing *ALL* keys from your sky_message table *EXCEPT* the PRIMARY KEY on msg_id. (And even then you might consider getting rid of the msg_id field. What purpose does it serve?)

    If you decide to put indexes back on sky_message table *NONE* of them should be UNIQUE (again, except for the msg_id, assume you can find some reason to even keep that field).

    If you want us to see the table *AS IT REALLY EXISTS* then don't show us a crappy phpmyadmin table dump. That tells us nearly nothing.

    Instead execute THIS query and show us what it shows you:
    Code:
    SHOW CREATE TABLE sky_message
    Last edited by Old Pedant; 08-19-2013 at 09:04 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    You have other problems in your DB.

    Code:
    insert into sky_message 
          (to_id,form_id,subject,message,send_date)
    values('4','2','test','another test',now())
    First of all, why do you put apostrophes around *NUMBERS*. to_id and from_id are INTEGERS. Integers (and, indeed, ANY kinds of numbers) do *NOT* use apostrophes.

    But the bigger problem: How can you send a message to to_id number 4??? You don't *HAVE* any member_id of 4 in your members table!!

    You *SHOULD* get a reference error when you attempt to do this!

    Tell you what. Just wipe out your sky_message table and create it anew using this query, instead of depending on crappy phpmyadmin to do the wrong thing for you:
    Code:
    CREATE TABLE sky_message (
        msg_id INT AUTO_INCREMENT PRIMARY KEY,
        to_id INT,
        from_id INT,
        read_status INT,
        send_date TIMESTAMP,
        subject VARCHAR(255),
        message TEXT,
        CONSTRAINT FOREIGN KEY (to_id) REFERENCES sky_members(member_id),
        CONSTRAINT FOREIGN KEY (from_id) REFERENCES sky_members(member_id),
        INDEX (to_id),
        INDEX (from_id)
    ) ENGINE INNODB;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    And, by the way, if you do re-create the table using that, then your query to insert a new record can be simplified a tad:
    Code:
    INSERT INTO sky_message 
          (to_id,from_id,subject,message)
    VALUES(3,2,'test','another test')
    Note that I did not include send_date in the query: No need. As a TIMESTAMP column, it will automatically be set to NOW(). And notice that I scrapped your apostrophes around the numbers.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Aug 2013
    Posts
    15
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Old Pedant

    Thank you for the information much appreciated

    I have amended as suggested and this seems to have fixed the above issues.

    However, I am now getting the following

    Database error: Invalid SQL: insert into sky_message (to_id,form_id,subject,message,send_date) values('1','','test','test',now())
    MySQL Error: 1452 (Cannot add or update a child row: a foreign key constraint fails (`mydomain/sky_message`, CONSTRAINT `sky_message_ibfk_2` FOREIGN KEY (`form_id`) REFERENCES `sky_member` (`member_id`)))
    Session halted.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Yes? That's because you did *NOT* specify a valid from_id ("form_id" in your code, and why don't you fix that?).

    These *are* messages, aren't they? From one member to another? So each message should have a valid to_id and a valid from_id. Anything else *should* be illegal, and because of the way we constructed the MySQL table, it is.

    Now, I *could* imagine having a "broadcast" message that you wanted sent to all members. So that the to_id could be blank. But the from_id being blank makes no sense. If you *did* want such a broadcast message, I would still leave the DB design alone and insist on having a valid to_id, but then I would add dummy user to the sky_member table with, say, a name of "all".

    Finally, how in the world did you expect that query to work, even if the foreign key constraint weren't there??? You are passing '' as the value for the from_id, but that field is an INT field! A *NUMBER*. You can *NOT* assign a blank value to a number. Period.

    **********
    To change your incorrectly named field, just issue this command/query:
    Code:
    ALTER TABLE sky_message CHANGE from_id form_id INT;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New Coder
    Join Date
    Aug 2013
    Posts
    15
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks again for the concise reply. I appreciate your time with this. Please bare with me I am trawling through and re-engineering someone else's mistakes

    Ok so I have renamed the typo form_id to from_id

    Members can now send messages to and from each other fine. That functionality is sorted. Now I am having trouble sending a direct message to a specific member from admin. There is a sky_admin table, but the admin user does not have say an admin_id.

    The admin user exists only within the sky_admin table. Do you suggest creating a new user admin called all within sky_member table to get around the query passing a blank value as int was defined with from_id?

    Getting this error now,same with typo changed from not form;

    Database error: Invalid SQL: insert into sky_message (to_id,from_id,subject,message,send_date) values('1','','test','testy',now())
    MySQL Error: 1452 (Cannot add or update a child row: a foreign key constraint fails (`discreetheart/sky_message`, CONSTRAINT `sky_message_ibfk_2` FOREIGN KEY (`from_id`) REFERENCES `sky_member` (`member_id`)))
    Session halted.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Quote Originally Posted by andrewjamesb View Post
    Do you suggest creating a new user admin called all within sky_member table to get around the query passing a blank value as int was defined with from_id?
    ABSOLUTELY.

    Actually, I don't understand why you have a sky_admin table that is separate from sky_members table. If you want to show me the field names in each I would comment further.

    Getting this error now,same with typo changed from not form;

    Database error: Invalid SQL: insert into sky_message (to_id,from_id,subject,message,send_date) values('1','','test','testy',now())
    MySQL Error: 1452 (Cannot add or update a child row: a foreign key constraint fails (`discreetheart/sky_message`, CONSTRAINT `sky_message_ibfk_2` FOREIGN KEY (`from_id`) REFERENCES `sky_member` (`member_id`)))
    Session halted.
    No different at all from my last answer.

    And you are *STILL* putting apostrophes around your NUMERIC values. WHY WHY WHY WHY WHY???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    New Coder
    Join Date
    Aug 2013
    Posts
    15
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Pedant

    That is the output from the error as is. I didn't add any apostrophes

    I got the members able to send and receive messages to and from eachother with your suggestion above

    The only thing I need to get admin to be able to have the ability to send a specific message to any given user, or broadcast to all users. I have created an admin login function so I could send members emails using a third party email editing tool fckeditor. That is the only reason admin is seperate from users. And of course, admin have other functions to administer users from the admin portal

    I would be very grateful if you could help sort this mess. I know its very untidy. I am learning..... slowly bare with me :-)

    The value 1 relates to admin_id of 1. I dont understand why there is ' where the to_id should be a specific member_id. I just cant get my head around that. values('1','','test','test',now())

    I have had others suggest I need to amend the PHP code itself, the functions in the background. Do you think its just a case of sorting via the mysql table to get it all working?

    Any help you could offer is always gratefully received. I can send you the code functions I have if that helps?

    Thanks
    Andrew

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Yes, of course you must amend the PHP code. It's the PHP code that is putting those bogus apostrophes in there.

    And I told you what I need to see: A schema dump of your sky_admin and sky_members tables: All the fields in each table by name AND type.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    New Coder
    Join Date
    Aug 2013
    Posts
    15
    Thanks
    3
    Thanked 0 Times in 0 Posts
    sky_members

    CREATE TABLE `sky_message` (
    `msg_id` int(5) NOT NULL auto_increment,
    `to_id` int(5) NOT NULL,
    `from_id` int(5) NOT NULL,
    `read_status` int(1) NOT NULL,
    `send_date` date NOT NULL,
    `subject` varchar(255) NOT NULL,
    `message` text NOT NULL,
    PRIMARY KEY (`msg_id`),
    KEY `to_id` (`to_id`),
    KEY `from_id` (`from_id`),
    CONSTRAINT `sky_message_ibfk_1` FOREIGN KEY (`to_id`) REFERENCES `sky_member` (`member_id`),
    CONSTRAINT `sky_message_ibfk_2` FOREIGN KEY (`from_id`) REFERENCES `sky_member` (`member_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

    sky_Admin

    CREATE TABLE `sky_admin` (
    `username` text NOT NULL,
    `password` char(20) NOT NULL,
    `admin_id` int(5) NOT NULL auto_increment,
    PRIMARY KEY (`admin_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8

    sky_member

    CREATE TABLE `sky_member` (
    `screen_name` varchar(25) NOT NULL,
    `country_id` int(5) NOT NULL,
    `location` varchar(15) NOT NULL,
    `password` varchar(15) NOT NULL,
    `email` varchar(45) NOT NULL,
    `date_of_birth` date NOT NULL,
    `date_of_reg` date NOT NULL,
    `subcribe_plan` varchar(25) NOT NULL,
    `member_id` int(5) NOT NULL auto_increment,
    `start_subscription` date NOT NULL,
    `end_subscription` date NOT NULL,
    `last_login` varchar(35) NOT NULL,
    `sess_random` varchar(35) NOT NULL,
    PRIMARY KEY (`member_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8

    functions running via admin

    // ========================== Post Message =================================
    function post_message($to_id,$from_id,$subject,$content)
    {
    $sql="insert into sky_message (to_id,from_id,subject,message,send_date) values('".$to_id."','".$from_id."','".$subject."','".$content."',now())";
    /*echo $sql;
    exit;*/
    $this->query($sql);
    }

    ------------------------------------------------------------------
    function post_send_message($to_id,$from_id,$subject,$content)
    {
    $sql="insert into sky_send_message (to_id,from_id,subject,message,send_date) values('".$to_id."','".$from_id."','".$subject."','".$content."',now())";
    /*echo $sql;
    exit;*/
    $this->query($sql);
    }
    --------------------------------------------------------------

    function post_temp_message($to_id,$from_id,$subject,$content)
    {
    $sql="insert into sky_tmp_message (to_id,from_id,subject,message) values('".$to_id."','".$from_id."','".$subject."','".$content."')";

    $this->query($sql);
    }



    email_to_member.php


    //creation of objects
    $obj_post = new admin;
    $obj_send = new admin;
    $obj_post_send= new admin;
    // request value
    $member_id=$_REQUEST['member_id'];

    $error_flag=0;

    if(isset($_REQUEST['submit1']))
    {

    // ------ post value ---------
    $subject=$_POST['subject'];
    $content=$_POST['content'];
    $content=str_replace("/fckeditor/editor/","http://mydomain.com/fckeditor/editor/",$content);
    $content=str_replace("/>",">",$content);
    $content=stripslashes($content);
    // ------- Sending Email of admin news-------------
    $obj_post->member_detail_id($member_id);
    $obj_post->next_record();
    if($obj_post->num_rows()>0)
    {
    $obj_send->post_message($member_id,'',$subject,$content);
    $obj_post_send->post_send_message($member_id,'',$subject,$content);
    $receiver_email=$obj_post->f('email');
    $receiver_name=$obj_post->f('screen_name');


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •