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 19
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Showing Number Of Members Associated With Certain Table

    I have the following MySQL query '$info = mysql_query("SELECT * FROM tblrep");' which displays my 'tblrep' data in a table, in this table there is a column 'rep_NBR'. This column gets associated with sub members in my database, these submembers are in a table called 'tblmem', When I am showing the data for 'tblrep' I also want to add into the table the number of 'tblmem' associated with each 'tblrep' using the 'rep_NBR', does anyone have any ideas on how to do this?

  • #2
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts
    PHP Code:
    $info mysql_query("SELECT *, (select count(*) from tblmem where tblmem.id = tblrep.rep_NBR) as total_members FROM tblrep"); 

  • #3
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Illin could you tell me how to add that to my code if possible?

    PHP Code:
    <?php

    /* connect to the mysql database and use different queries for the count of members */

    include 'config.php';
    include 
    'open.php';

    //navigation
    include("nav.php");

    $info mysql_query("SELECT * FROM tblrepresentatives");

    echo 
    '<table border="1" cellpadding="3" cellspacing="1">
      <tr valign="top">
        <td>First Name</td>
        <td>Last Name</td>
        <td>Username</td>
        <td>Password</td>
        <td>Super User</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr><tr valign="top">
        <td><input type="text" name="firstname" /></td>
        <td><input type="text" name="lastname" /></td>
        <td><input type="text" name="username" /></td>
        <td><input type="text" name="password" /></td>
        <td><select name="select">
          <option value="0">Yes</option>
          <option value="1">No</option>
        </select></td>
        <td>&nbsp;</td>
        <td>Insert</td>
      </tr>'
    ;
        
    if (
    mysql_num_rows($info) < 1) {
    echo 
    '<tr valign="top">
      <td colspan="4">There are no members that match the query. Please go back and try again</td>
        </tr>'
    ;
    }

    else {
       while (
    $qry mysql_fetch_array($info)) {

    //create the layout
    ?>
    <link href="cs_style.css" rel="stylesheet" type="text/css" />

      <tr valign="top">
        <td><?php echo $qry['rep_Firstname']; ?></td>
        <td><?php echo $qry['rep_Lastname']; ?></td>
        <td><?php echo $qry['username']; ?></td>
        <td><?php echo $qry['password']; ?></td>
        <td><?php echo $qry['superuser']; ?></td>
        <td><a href="showAdminEdit.php?record_id=<?php echo $qry['id']; ?>">Edit</a></td>
        <td>Delete</td>
      </tr>
    <?php
       
    }
    }

    echo 
    '</table>';

    include 
    'library/closedb.php';

    ?>

  • #4
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Change your query to mine with the subselect, and then echo $qty['total_members'] where you want the number of members displayed.

  • #5
    Regular Coder
    Join Date
    Oct 2005
    Location
    Right Here
    Posts
    654
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Also I noticed your are adding in your stylesheet with each row that is outputted, I suggest moving that outside of your while loop.

  • #6
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    It doesn't like this line any ideas?

    PHP Code:
    if (mysql_num_rows($info) < 1) {
    echo 
    '<tr valign="top">
      <td colspan="4">There are no members that match the query. Please go back and try again</td>
        </tr>'
    ;

    Sorry I am totally new to php could you explain what your last comment meant? Is there a better way I could be displaying it?

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You may as well start some good habits now, since you're new to coding.

    Good habit #1:

    Always check a mySQL query to see if it failed.
    PHP Code:
    $result mysql_query($query);
    //now check it for failure and display a decent error message if it did fail
    if (!$result) {
        die(
    "Query failed. Query text: $query<br />Error Message: ".mysql_error());

    The reason I am nagging you about this is because my guess at the error you are getting is something about "resource is not a valid mySQL resource". Well, your query failed, but you didn't bother checking that and just went ahead and used the query result anyway.

    Mind you, this is only a guess, since you didn't bother to tell us what error you are getting. I wonder why you wouldn't tell us that little nugget of info? Did you want us to guess? Are you sadistic? Do you enjoy being obtuse? Are we rats in a maze, and you want to see if we'll go after the food pellet?

    Inquiring minds want to know....

  • #8
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Ok thanks for that you made me laugh! But yes I agree with everything you said ok the full error is this

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in e:\showRepresentatives.php on line 21
    First Name Last Name Members Edit
    There are no members that match the query. Please go back and try again
    PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in e:\showRepresentatives.php on line 21
    Hopefully that helps a bit?

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    So.... did you add the query check then? How's that going for ya?

  • #10
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    If you read his post, he is suggesting you to make a change (especially considering the error he guessed at is right).

    Once you do that, post the error you get.

  • #11
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Ok here is the error

    Query failed. Query text:
    Error Message: Query was empty
    First Name Last Name Members Edit

  • #12
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    I think this is one you can fix yourself.

  • #13
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    That's optimistic.

  • #14
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Quote Originally Posted by aedrin View Post
    I think this is one you can fix yourself.
    Quote Originally Posted by Fumigator View Post
    That's optimistic.
    I love this type of feedback. And I loved the Hank Hill impression earlier Fumigator.

    @TomyKnoker
    If you were to apply yourself a bit more you will see this (as well as some of your other posts) are not really complicated problems and can be solved with minimal thought.

    You shouldn't rely on the members here for every error you encounter. If so, you'll never learn and will continue to posts these types of questions. I'm not trying to sound harsh or insult you so do not take it as such.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #15
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Well my first thoughts straight away was that there is an error in the MySQL query? Would that be correct? I have only been using MySQL for 2 weeks, still trying to get my head around it, and the MySQL query to me is fairly complicated... So could someone atleast give me a clue?


  •  
    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
    •