36 users online (0 members and 36 guests)  


  Results 1 to 8 of 8

Related

  1. help with mysql query    Forum: Databases
    Replies: 2
  2. MySQL query    Forum: PHP Forum
    Replies: 1
  3. Replies: 4
  4. Replies: 2
  5. Replies: 0
  1. #1
    pegboy's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Aug 2001
    Location
    Knoxville, TN
    Posts
    76

    mysql query / function problem....

    i've got alot of tables tables... the main table (users) holds all the user names & passwords. each user also has his/her own table (named like 'user_username_records').

    here's what i'm trying to do: read all the user names from the main table (users), then loop through all the user's sub tables and get the total number of rows for each one. untimately i want to display the top 5 users with the most rows.

    the big picture: table 'records' has information about albums. each row is a different album and each row has a unique id.

    table 'user_username_records' is for a specific user. each row has only a couple columns, record id and format.

    i'm trying to plan ahead... the main 'records' table will end up having thousands of entries. and, in theory there could be thousands of user tables. that is why i went with this method.

    here's the code that i have, thus far:

    PHP Code:
    $conn db_connect();
    $result $conn->query("select * from users where confirm is not null");
    $num_users $result->num_rows// total number of users
        
    for ($i=0$i $num_users$i++) 
        {
        
    $row $result->fetch_assoc();
        
    $username htmlspecialchars(strtolower($row["user"]));
        
    $user_db "user_".$username."_records";
        
    $query $conn->query("select * from $user_db");
        
    $num_records $query->num_rows// total number of records
        
    $users[$row['user']]=$num_records;
        } 
        
    arsort$users );
        foreach (
    $users as $num_records)
        {
        echo 
    "<a href=#>$username</a> // ($num_records) <br>";
        } 
    with 6 test users (each one has a different number of albums in their DB) this prints:

    user6 // (8)
    user6 // (4)
    user6 // (2)
    user6 // (2)
    user6 // (1)
    user6 // (0)

    so, i'm getting all the number in order, but i'm loosing the username associated with each $num_records. i'm also unsure of how to limit this to only the top 5.
    robert koons

  2. #2
    yoemanspiffy's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Aug 2006
    Posts
    28

    Re: mysql query / function problem....

    Don't do it like this amigo. (Web) Server Performance is going to seriously degrade the more records and users you add to it. Not only that, but you are going to kill your database performance by hitting it like this.

    You will want to generate a single SQL statement that can capture the information you want using a JOIN statement. From that you can SELECT TOP 5, etc.

    Why don't you export the table builds for the tables you are trying to access with this, post them here, and I will help you generate the SQL for it. If you have no idea what I am talking about, PM me and I can walk you through it.

    If you are using phpadmin you can create the table structure here for us and we can get you a better procedure for accessing what you want.


    This is what I am looking for:

    CREATE TABLE `emails` (
    `emailID` int(11) NOT NULL auto_increment,
    `emailAddress` varchar(254) NOT NULL default '',
    `emailEntryDate` timestamp NULL default CURRENT_TIMESTAMP,
    `emailCapture` varchar(254) default '1' COMMENT 'This is to capture how we got the email',
    `emailNotes` mediumtext,
    PRIMARY KEY (`emailID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
    --
    -- Dumping data for table `emails`
    --
    INSERT INTO `emails` VALUES (22, 'TEST @ TEST . com', '2006-08-19 18:56:13', '1', NULL);

    Don't post all the rows, just the first two or three, and X out anything important.

  3. #3
    pegboy's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Aug 2001
    Location
    Knoxville, TN
    Posts
    76

    Re: mysql query / function problem....

    here's the structure for the main users table:
    id int(10) No
    email varchar(50) No
    user varchar(16) No
    pass varchar(40) No
    fname varchar(20) No
    lname varchar(20) No
    country varchar(20) No
    gender char(1) No
    dob varchar(19) No
    confirm char(1) Yes NULL
    joindate varchar(19) No

    here's the structure for the user's records table:
    id int(11) No
    record_id varchar(10) No
    format varchar(50) Yes NULL
    notes text Yes NULL


    is it possible to do a JOIN on these tables?
    robert koons

  4. #4
    pegboy's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Aug 2001
    Location
    Knoxville, TN
    Posts
    76

    Re: mysql query / function problem....

    by the way, i got the original code to work:
    PHP Code:
    <?
    $conn 
    db_connect();
    $result $conn->query("select * from users where confirm is not null");
    $num_users $result->num_rows// total number of users
        
    for ($i=0$i $num_users$i++) 
        {
        
    $row $result->fetch_assoc();
        
    $username htmlspecialchars(strtolower($row["user"]));
        
    $user_db "user_".$username."_records";
        
    $query $conn->query("select * from $user_db");
        
    $num_records $query->num_rows// total number of records
        
    $users[$row['user']]=$num_records;
        } 
        
    arsort$users );
            foreach (
    $users as $username => $num_records)
            {
                if(
    $n<5)
                { 
    ?>
    <tr class="highlight">
    <td align="left" valign="top">
    <a href="./users/<? echo $username;?>"><? echo $username?></a>
    </td>
    <td align="right" valign="top">
    <? echo $num_records?> )
    </td>
    </tr>            
                <?
                $n
    ++;
                }
            }
    ?>
    ...but if you've got a better way then i'm all for it.
    robert koons

  5. #5
    yoemanspiffy's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Aug 2006
    Posts
    28

    Re: mysql query / function problem....

    Got swamped today amigo, I will have to look at it over the weekend, but generally you want to get those data-call loops out of there if you can. Looks pretty straight forward. I will create a DB and run a couple of sample queries. You will still need a loop, but it will be in-code and not calling the DB. L8R - and I promise to look at this over the weekend.

    YM

  6. #6
    yoemanspiffy's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Aug 2006
    Posts
    28

    Talking Re: mysql query / function problem....

    Now that I have had a good chance to look at the table structures, it appears you are stuck with what you have.

    There is nothing to join with, and as each user has a separate table you couldn't do it with a single SQL call.

    If you had a single Users table and a single Records table (that had userID) you could do it. (Of course you could go the Users, Records, User__Records 3 table route)

    Just out of curiousity, what is the rationale behind creating a single table for each user?
    YM

  7. #7
    pegboy's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Aug 2001
    Location
    Knoxville, TN
    Posts
    76

    Re: mysql query / function problem....

    i just figured that was the best way to do it... this is a new project for me and i'm pretty new at MySQL.

    the big thing i'm trying to do is plan ahead for what might be a popular site. i want this site to navigate so that you browse to a record (or search for it) and add it to 'my records' (similar to the way you add friends on myspace.com).

    someone on another forum suggested having one table for all the users' records like you did, but how would you organize this? seems like that one table would be massive in just a couple months.

    this site is for record collectors... so assuming i have only 100 users (being very modest), each with over 1,000 records/albums wouldn't that be too big if the site ever gets more poular?
    Last edited by pegboy; 08-26-2006 at 08:46 AM.
    robert koons

  8. #8
    yoemanspiffy's Avatar
    Junior Member

    Status
    Offline
    Join Date
    Aug 2006
    Posts
    28

    Talking Re: mysql query / function problem....

    Au contraire mon frere! (Thats French for not quite!)

    If you have a Users table all it contains is information unique to a user. (this will grow over time)

    If you have a Records table it only contains unique records. (this will grow over time, but will not be anywhere near as hefty on the database as your current setup)
    The gem of it all is the lookup table the user__records table. This consists of nothing more than an ID, userID, recordID. Three integers. Then when you want to hit the database, you can hit it with a single query: in and out, no looping, etc.
    the records for each user are contained as rows in the user__records table. This is pretty standard DB design. Just setup your indexes and realtionships to speed up the searching and quesries and BAM!(says Emeril) you got it.
    This issue that you have been dealing with about the looping datacalls will kill your server a heck of a lot faster than a well-designed databse.
    YM



Tags for this Thread