Page 2 - Showing results for Mysql Query multiple results for each name

Codewalkers Forums Sponsor:

#16

August 21st, 2008, 03:22 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

That doesn't show all the details linked with each name it just shows each name with each detail.

If there is a name 3 times in the database with 3 different types of details that are search for it should show the name with 3 different types of details under it and then the count should be 3, the order should start with the names that has most details.

Thanks for your help

#17

August 21st, 2008, 03:45 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

This mite make it easier details is the same as criteria

#18

August 21st, 2008, 05:08 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

I got it to show the right details under the name but I can't get the div to end on the last details so it looks messed up any1 got any ideas??

PHP Code:


		
			
if ($a1["Name"] != $fundn) 

        {

        $fundn = $a1["Name"];

          echo "<img src=\"Images/" . (($expanded[$n] == 0) ? "plus" : "minus") . ".gif\" name=\"image$n\" id=\"image$n\" onclick=\"toggle($n)\">  $a1[Name]";

          echo "<div id=\"criteria$n\" style=\"font-size: 12px; color: #666666; margin-top: 10px; margin-bottom: 10px;" . (($expanded[$n] == 0) ? "display: none" : "") . "\">";

        }

      echo $a1['Details'];

      if ($a1["Name"] != $fundn) 

        {

            echo "</div>";

        }

#19

August 21st, 2008, 05:13 PM

jamestrowbridge

Contributing User

Join Date: Jul 2008

Location: Cleveland, Ohio, USA

Posts: 400

Time spent in forums: 4 Days 15 h 59 m 8 sec
Reputation Power: 2

if ($a1["Name"] != $fundn) <--- This must be false.

__________________
Sir, a desire of knowledge is the natural feeling of mankind; and every human being, whose mind is not debauched, will be willing to give all that he has to get knowledge.

#20

August 21st, 2008, 05:17 PM

jamestrowbridge

Contributing User

Join Date: Jul 2008

Location: Cleveland, Ohio, USA

Posts: 400

Time spent in forums: 4 Days 15 h 59 m 8 sec
Reputation Power: 2

In fact on second glance you set $fundn = $a1["Name"]; so of course the if ($a1["Name"] != $fundn) statement would be false, you can't say:

If 'X' != 'Y' then set 'X'='Y' and then expect 'X' to not equal 'Y' after you just set it equal to 'Y'.

#21

August 21st, 2008, 06:05 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,907

Time spent in forums: 1 Week 4 Days 16 h 43 m 30 sec
Reputation Power: 4

PHP Code:


		
			
if ($a1["Name"] != $fundn)  

        { 

          echo "<img src=\"Images/" . (($expanded[$n] == 0) ? "plus" : "minus") . ".gif\" name=\"image$n\" id=\"image$n\" onclick=\"toggle($n)\">  $a1[Name]"; 

          echo "<div id=\"criteria$n\" style=\"font-size: 12px; color: #666666; margin-top: 10px; margin-bottom: 10px;" . (($expanded[$n] == 0) ? "display: none" : "") . "\">"; 

        } 

      echo $a1['Details']; 

      if ($a1["Name"] != $fundn)  

        { 

            echo "</div>"; 

        }  

        $fundn = $a1["Name"];

just set $fundn at the end. It would be equal to whatever the last iteration was. If name is different from the last iteration, then you echo the div stuff.

#22

August 22nd, 2008, 04:35 AM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Nope still don't work, heres the code so far:

PHP Code:


		
			
<?php

  $n = 0;

  $count = 0; 

   while($a1 = mysql_fetch_array($r1))

    {    

      $n++;

?>

  <tr>

    <td valign="top">

    <script language="JavaScript">

    expanded[<?php echo $n?>] = 0

    </script>

<?php

    if ($a1["Name"] != $fundn) 

        {

        $fundn = $a1["Name"];

          echo "<img src=\"Images/" . (($expanded[$n] == 0) ? "plus" : "minus") . ".gif\" name=\"image$n\" id=\"image$n\" onclick=\"toggle($n)\">  $a1[Name]";

          echo "<div id=\"criteria$n\" style=\"font-size: 12px; color: #666666; margin-top: 10px; margin-bottom: 10px;" . (($expanded[$n] == 0) ? "display: none" : "") . "\">";

        //reset the count.

        $count = 0; 

        }

          echo $a1['Criteria'];

           //add 1 to the count.

            $count++; 

        

        if ($a1["Name"] != $fundnn)  

        { 

            echo "</div>"; 

        }  

        $fundnn = $a1["Name"]; 

    ?>    </td>

    <td align="center" valign="top"><?php echo $count; ?></td>

  </tr>

<?php

    }

?>

The count works, but the div does not close in the right place it should close on the last echo $a1['Criteria']; for that "Name".

Is there a way to put it in order so the Name with the highest count is at the top?

Thanks

Last edited by drewj2k : August 22nd, 2008 at 04:48 AM.

#23

August 22nd, 2008, 07:57 AM

jamestrowbridge

Contributing User

Join Date: Jul 2008

Location: Cleveland, Ohio, USA

Posts: 400

Time spent in forums: 4 Days 15 h 59 m 8 sec
Reputation Power: 2

Can you post the HTML source of this page? I think what you mean is:

$count = 0;
}
echo $a1['Criteria'];
echo "</div>";
//add 1 to the count.
$count++;

$fundnn = $a1["Name"];

#24

August 22nd, 2008, 09:58 AM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Quote:

Originally Posted by jamestrowbridge

Can you post the HTML source of this page? I think what you mean is:

$count = 0;
}
echo $a1['Criteria'];
echo "</div>";
//add 1 to the count.
$count++;

$fundnn = $a1["Name"];

Because you could have for example 3 criteria s listed there if you closed the div on the first criteria the second 2 wouldn't appear in the correct place if that makes any sense?

#25

August 22nd, 2008, 12:52 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,907

Time spent in forums: 1 Week 4 Days 16 h 43 m 30 sec
Reputation Power: 4

and if you wanted to sort by the name with the most details, you would probably need to do a subquery to get a count of how many details are under each name and sort by that.

PHP Code:


		
			
 $q1 = "SELECT *, (SELECT COUNT(*) FROM table AS t2 WHERE `Name`=`table`.`Name`) AS `count` FROM table WHERE (".$criteria.") ORDER BY `count`, `Name`, `Details`";

Then you can get rid of the $count variable and use the count from the query.

#26

August 22nd, 2008, 01:18 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Thanks Ala but it does not order them by the count here is the query with my values:

PHP Code:


		
			
 $q1 = "SELECT *, (SELECT COUNT(*) FROM TableName AS t2 WHERE `Name`=`TableName`.`Name`) AS `count` FROM TableName WHERE (".$criteria.") ORDER BY `count`, `Name`, `Criteria`";

Can you check it is correct?

Thanks

#27

August 22nd, 2008, 01:45 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Also the count is showing weird numbers ?!

Here is all the code so far:

PHP Code:


		
			
<?php

$q1 = "SELECT *, (SELECT COUNT(*) FROM TableName AS t2 WHERE `Name`=`TableName`.`Name`) AS `count` FROM TableName WHERE (".$criteria.") ORDER BY `count`, `Name`, `Criteria`";  

  

  $n = 0;

  $count = 0; 

   while($a1 = mysql_fetch_array($r1))

    {    

?>

  <tr>

    <td valign="top">

    <script language="JavaScript">

    expanded[<?php echo $n?>] = 0

    </script>

<?php

    if ($a1["Name"] != $fundn) 

        {

        $n++;

        $fundn = $a1["Name"];

          echo "<img src=\"Images/" . (($expanded[$n] == 0) ? "plus" : "minus") . ".gif\" name=\"image$n\" id=\"image$n\" onclick=\"toggle($n)\">  $a1[Name]";

          echo "<div id=\"criteria$n\" style=\"font-size: 12px; color: #666666; margin-top: 10px; margin-bottom: 10px;" . (($expanded[$n] == 0) ? "display: none" : "") . "\">";

          //reset the count. 

        }

           echo $a1['Criteria'];

           

        if ($a1["Name"] != $fundcheck) {

            echo "</div>"; 

            echo '</td><td align="center" valign="top">';

            echo $a1['count'];

            echo "</td></tr>";

        }

        

        $fundcheck = $a1["Name"];

    }

#28

August 22nd, 2008, 01:54 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,907

Time spent in forums: 1 Week 4 Days 16 h 43 m 30 sec
Reputation Power: 4

oops, the count should be order desc.

ORDER BY `count` DESC, `Name` ASC, `Criteria` ASC

#29

August 22nd, 2008, 02:00 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Haha the order is correct now but the count is still wrong I have weird numbers like:

29
29
29
29
29
27
27
25
24

when it should be like

4
4
4
3
3
3
2
2

I don't understand the sub query but It needs to count the amount of Criterias to each Name.

Thanks

Last edited by drewj2k : August 22nd, 2008 at 02:03 PM.

#30

August 22nd, 2008, 03:00 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,907

Time spent in forums: 1 Week 4 Days 16 h 43 m 30 sec
Reputation Power: 4

well you can still use the other $count method to get the correct count if you want, but it shouldn't be needed. The subquery should just get a count of how many rows share the name of the primary query. That should essentially get the count of how many criteria there are under that name. I guess though you would need to add the criteria part to that query too to get a count of how many match the criteria also. Try this:

PHP Code:


		
			
 $q1 = "SELECT *, (SELECT COUNT(*) FROM TableName AS t2 WHERE `Name`=`TableName`.`Name` AND ($criteria)) AS `count` FROM TableName WHERE (".$criteria.") ORDER BY `count`, `Name`, `Criteria`";

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: