...

View Full Version : Showing Number Of Members Associated With Certain Table



tomyknoker
03-29-2007, 06:58 PM
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?

iLLin
03-29-2007, 07:01 PM
$info = mysql_query("SELECT *, (select count(*) from tblmem where tblmem.id = tblrep.rep_NBR) as total_members FROM tblrep");

tomyknoker
03-29-2007, 07:04 PM
Thanks Illin could you tell me how to add that to my code if possible?



<?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';

?>

iLLin
03-29-2007, 07:08 PM
Change your query to mine with the subselect, and then echo $qty['total_members'] where you want the number of members displayed.

iLLin
03-29-2007, 07:09 PM
Also I noticed your are adding in your stylesheet with each row that is outputted, I suggest moving that outside of your while loop.

tomyknoker
03-29-2007, 07:12 PM
It doesn't like this line any ideas?



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?

Fumigator
03-29-2007, 08:29 PM
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.


$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....

tomyknoker
03-29-2007, 08:36 PM
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?

Fumigator
03-29-2007, 08:37 PM
So.... did you add the query check then? How's that going for ya?

aedrin
03-29-2007, 08:38 PM
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.

tomyknoker
03-29-2007, 08:42 PM
Ok here is the error



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

aedrin
03-29-2007, 09:05 PM
I think this is one you can fix yourself.

Fumigator
03-29-2007, 09:10 PM
That's optimistic.

StupidRalph
03-29-2007, 10:13 PM
I think this is one you can fix yourself.


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.

tomyknoker
03-29-2007, 10:54 PM
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?

Fumigator
03-29-2007, 11:02 PM
You probably just copied my example code into your code without changing it at all and assumed it would work.

Bad assumption...

What you want to do is pretend like you are the PHP processor. If you've ever seen the movie Tron, you'll be able to picture this. Shrink yourself down and jump into a light cycle. Go from the top of your code, one line at a time, and picture in your mind what the code is doing. Try to predict the result of each line of code. Become one with the code. BE THE CODE.

When you come across a line of code you do not understand, go read the PHP manual's description of the function that line of code is using. Read it over and over until it makes sense to you.

Now that you've done this, go back to my post about checking mySQL query results, and take that code I gave as an example only, and then fit it into your own code.

iLLin
03-29-2007, 11:07 PM
I suggest picking up a book. Its well worth it.

@Fumigator, thats mad comedy :D

tomyknoker
03-29-2007, 11:20 PM
Myabe some legends around here can do it on there own, but I can't so thanks for the advice but I'm giving up on this one. I've read your code. I'll go through what I think it's asking...



/*result equals the mysql query which is ("SELECT *, (select count(*) from tblmembers where tblmembers.ID = tblrepresentatives.rep_NBR) as total_members FROM tblrepresentatives");*/
$result = mysql_query($query);
//if the query DOESN'T equal the result then display the error message
if (!$result) {
die("Query failed. Query text: $query<br />Error Message: ".mysql_error());
}

else {
//if the query does equal the result then display the information
while ($qry = mysql_fetch_array($info)) {


Is this not a correct interpretation?

Fumigator
03-29-2007, 11:28 PM
What have you assigned to your variable $query?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum