SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql how to create a dynamic table

    Hello everyone.

    i have a problem that I am unable to resolve myself. I am building a jobs board for members to be able to communicate with each other. I anticipate that members will send out and receive a lot of correspondence between each other via the site. So i obviously need to have a database to store their communications with each other.

    My questions are

    What is the best way to approach this?

    1. Do I dynamically create individuals table for each members ( i suspect this is the right approach).
    2. Or simple table for all the members ( i suspect that such a table will be too cumbersome and difficult to control in the long run. It would end up with millions of emails) .


    I tried to dynamically create a table for each member.
    The title for the dynamic table is member ( plus a concatenating of the $user_Id ( thus creating a unique table for each indivudial ). However when i tried to run it to the database, the mysql database did not create the tables. i have no idea where i have gone wrong with the SQL syntax. could someone please look at my SQL query and tell me if its correct.

    Thank you in advance for your kind help.



    PHP Code:


     
    require_once ('databaseb_functions.php'); // Connect to the database.
                        
            
             
    $sqlCommand  "USE jobs; 
                     CREATE TABLE  members.'
    $id (    
                     user_id int(11) NOT NULL ,
                     foreign_id   int(11) NOT NULL ,
                     messages_sent varchar(255) NOT NULL,
                     date_sent varchar(255) NOT NULL,
                         message_received  DATETIME NOT NULL,
                     date_received  DATETIME NOT NULL,    
                       
                    ) "
    ;
                       
                        
         
    $SqlCommand  mysqli_query ($dbc,    $sqlCommand);        // Run the query. 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i would ~not~ use multiple tables for multiple users

    use only one single table, and declare indexes properly so that all your queries are optimized

    millions of rows are not "too cumbersome and difficuilt to control"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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