PHP Coding
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers Forums > PHP Related > PHP Coding
Reload this Page

Showing results for Mysql Query multiple results for each name

Discuss Showing results for Mysql Query multiple results for each name in the PHP Coding forum on Codewalkers.
Showing results for Mysql Query multiple results for each name Having problems with a PHP script you are coding? This is the place to get help!

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 3 1 2 3 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
  #1  
Old August 20th, 2008, 10:25 AM
drewj2k drewj2k is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2007
Posts: 160 drewj2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 8 m 15 sec
Reputation Power: 2
Showing results for Mysql Query multiple results for each name
Hi,

This is a bit hard to explain but basically I have a form which has a load of tick box's and it posts to my reports page then queries the database for the correct results this sort of works:

PHP Code:
if(isset($_POST['criteria']) && !empty($_POST['criteria'])){
            foreach($_POST['criteria'] as $key=>$value){
                if($value==1) $criteria[] = "Name = '".mysql_escape_string($key)."'";
            }
            $criteria = implode(' OR ', $criteria);
                
            $q1 = "SELECT * FROM table WHERE (".$criteria.")";
        }
        
    //echo $q1;    
$r1 = mysql_query($q1) or die(mysql_error());

<?php
  $n = 0;
   while($a1 = mysql_fetch_array($r1))
    {    

      $n++;
?>
  <tr>
    <td valign="top">
    <script language="JavaScript">
    expanded[<?php echo $n?>] = 0
    </script>
<?php
      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'];
      echo "</div>";
?> 


If you look at the bottom of the code you will see $a1[Details] this part works but doesn't show everything I want it to, it should show all the details which are linked to the name but it only shows 1 detail.

For example if I search for Cheese and Cow if the name was in the database with cheese as the details and was in the database again with cow as the details the $a1['Details'] bit it should show Cheese & Cow but it only shows Cheese.

I also want to count the amount of Details associated it each name for example if it was just cheese it would say 1 if it was cheese and cow it would show 2.

Hope some 1 can help!

Thanks!!1
Reply With Quote
  #2  
Old August 20th, 2008, 11:44 AM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 337 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 Days 16 h 12 m
Reputation Power: 2
The code that you've shown doesn't have very good syntax.

PHP Code:
<tr> 
    <td valign="top"> 
    <script language="JavaScript"> 
    expanded[<?php echo $n?>] = 0 
    </script> 


Missing the ";" after the $n

PHP Code:
} 
         
    //echo $q1;     
$r1 = mysql_query($q1) or die(mysql_error()); 

<?php 
  $n = 0; 
   while($a1 = mysql_fetch_array($r1)) 
    { 


the "<?php" tag is in the middle of a bunch of PHP code (it's above and below).


But assuming all of this is fixed:

Can you echo out your $criteria variable and post the HTML source?

Also, you can use mysql_num_rows() to count the rows.
__________________
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.
Reply With Quote
  #3  
Old August 20th, 2008, 01:21 PM
drewj2k drewj2k is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2007
Posts: 160 drewj2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 8 m 15 sec
Reputation Power: 2
Thumbs down
Ok ignore that this is a bit less messy can you help this:

PHP Code:
if(isset($_POST['criteria']) && !empty($_POST['criteria'])){
            foreach($_POST['criteria'] as $key=>$value){
                if($value==1) $criteria[] = "Criteria= '".mysql_escape_string($key)."'";
            }
            $criteria = implode(' OR ', $criteria);
                
            $q1 = "SELECT * FROM table WHERE (".$criteria.")";
        }
        
    //echo $q1;    
$r1 = mysql_query($q1) or die(mysql_error());

   while($a1 = mysql_fetch_array($r1))
    {    
echo $a1[Name];
echo $a1['Details'];
echo "number of times the name has a details linked to it"
} 


So name will only appear once for each result, details should have all the details linked, details is basically the criteria we posted from the form.

Hope that helps you understand more what I am trying to do?

Thanks
Last edited by drewj2k : August 20th, 2008 at 02:37 PM.
Reply With Quote
  #4  
Old August 20th, 2008, 01:47 PM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 337 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 Days 16 h 12 m
Reputation Power: 2
Can you echo out your $criteria variable and post the HTML source?

Or upload your relevant files in a ZIP file?
Reply With Quote
  #5  
Old August 20th, 2008, 02:17 PM
MatthewJ MatthewJ is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: May 2007
Location: Davenport, Iowa
Posts: 474 MatthewJ User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 Days 13 h 41 m 29 sec
Reputation Power: 3
James,

Sorry if you saw the original post... I found what you were saying and agree

Also, not to hijack... but I was just in Cleveland last week. West lake actually at a Hyland Software training. Not a bad little town ya got there . I will be returning next month for another training. Any reccomendations on things to do besides the R&R Hall of Fame... Did it already?

Matt
Last edited by MatthewJ : August 20th, 2008 at 02:22 PM. Reason: stupididty
Reply With Quote
  #6  
Old August 20th, 2008, 02:36 PM
drewj2k drewj2k is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2007
Posts: 160 drewj2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 8 m 15 sec
Reputation Power: 2
Smile
ahhh sorry it would appear like

Criteria = Cheese Or Criteria = Cow Or Criteria = Poo

And it what I want it to show is data like this

Cheese Factory (2):
Cheese
Cow

Cow Field (2):
Cow
Poo

Cow Factory (1):
Cow

Shop (1):
Cheese
--------------------------------------

At the moment it shows:

Cheese Factory ():
Cheese

Cow Field ():
Cheese

Cow Factory ():
Cheese

Shop ():
Cheese

---------------------------------------
In the data base it would appear like this

1: Name:Cheese Factory | Criteria = Cheese
2: Name:Cheese Factory | Criteria = Cow

I hope that helps!
Last edited by drewj2k : August 20th, 2008 at 02:40 PM.
Reply With Quote
  #7  
Old August 20th, 2008, 05:30 PM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 337 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 Days 16 h 12 m
Reputation Power: 2
Quote:
Originally Posted by MatthewJ
James,

Sorry if you saw the original post... I found what you were saying and agree

Also, not to hijack... but I was just in Cleveland last week. West lake actually at a Hyland Software training. Not a bad little town ya got there . I will be returning next month for another training. Any reccomendations on things to do besides the R&R Hall of Fame... Did it already?

Matt


haha cool- yeah it's not too bad here. The science center is okay and the science center's omni-max theater is always cool. We also have a lot of bars (haha). All kinds of restaurants. Browns stadium, Progressive stadium.
Reply With Quote
  #8  
Old August 21st, 2008, 07:20 AM
drewj2k drewj2k is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2007
Posts: 160 drewj2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 8 m 15 sec
Reputation Power: 2
Cool
Any ideas??
Reply With Quote
  #9  
Old August 21st, 2008, 09:13 AM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 337 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 Days 16 h 12 m
Reputation Power: 2
Can you post the entire form page and the entire processing page (the one you posted code from already?) I'm going to try to test it out on my server.
Reply With Quote
  #10  
Old August 21st, 2008, 09:31 AM
drewj2k drewj2k is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2007
Posts: 160 drewj2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 8 m 15 sec
Reputation Power: 2
Well this is it really at the moment the page previous to this is just a form that post's multiple criteria's to this page:

PHP Code:
if(isset($_POST['criteria']) && !empty($_POST['criteria'])){
            foreach($_POST['criteria'] as $key=>$value){
                if($value==1) $criteria[] = "Criteria= '".mysql_escape_string($key)."'";
            }
            $criteria = implode(' OR ', $criteria);
                
            $q1 = "SELECT * FROM table WHERE (".$criteria.")";
        }
        
    //echo $q1;    
$r1 = mysql_query($q1) or die(mysql_error());

   while($a1 = mysql_fetch_array($r1))
    {    
echo $a1[Name];
echo $a1['Details'];
echo "number of times the name has a details linked to it"
} 
Reply With Quote
  #11  
Old August 21st, 2008, 09:52 AM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 337 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 Days 16 h 12 m
Reputation Power: 2
I'd like to see the form page- so I have an idea of the values that are being passed.
Reply With Quote
  #12  
Old August 21st, 2008, 09:54 AM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 337 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 Days 16 h 12 m
Reputation Power: 2
Also- what are all of the different column names in the table you are using?
Reply With Quote
  #13  
Old August 21st, 2008, 10:23 AM
drewj2k drewj2k is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2007
Posts: 160 drewj2k User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 8 m 15 sec
Reputation Power: 2
The form page bit works heres an example form :

PHP Code:
<tr class="shaded">
          <td class="left"><strong>Retail</strong></td>
          <td class="middle">Major</td>
          <td width="1%"><input name="criteria[Tobacco Retail Major]" type="checkbox" id="Tobacco Retail Major" value="1"></td>
        </tr>
        <tr class="shaded">
          <td class="left">&nbsp;</td>
          <td class="middle">Minor</td>
          <td width="1%"><input name="criteria[Tobacco Retail Minor]" type="checkbox" id="Tobacco Retail Minor" value="1"></td> 



The fields I Need are Name And Details and we are searching by Criteria.

thanks
Reply With Quote
  #14  
Old August 21st, 2008, 11:31 AM
jamestrowbridge jamestrowbridge is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Jul 2008
Location: Cleveland, Ohio, USA
Posts: 337 jamestrowbridge User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 Days 16 h 12 m
Reputation Power: 2
From what you have given me- it doesn't look like any of this works at all. Please paste actual working code. Right now I can only make your idea work by rewriting the entire thing. You can post the actual complete code, or post working code at least.
Reply With Quote
  #15  
Old August 21st, 2008, 11:32 AM
IAmALlama IAmALlama is offline
Me
Click here for more information
 
Join Date: Apr 2007
Location: Seattle, WA
Posts: 1,597 IAmALlama User rank is Private First Class (20 - 50 Reputation Level)IAmALlama User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Week 1 Day 20 h 32 m 28 sec
Reputation Power: 4
PHP Code:
if(isset($_POST['criteria']) && !empty($_POST['criteria'])){
    foreach($_POST['criteria'] as $key=>$value){
        if($value==1) $criteria[] = "Criteria= '".mysql_escape_string($key)."'";
    }
    $criteria = implode(' OR ', $criteria);
    //add an order by to the end.
    $q1 = "SELECT * FROM table WHERE (".$criteria.") ORDER BY Name, Details";
} 

$r1 = mysql_query($q1) or die(mysql_error());
//make a blank variable to start for use in the loop.
$current = '';
$count = 0;
while($a1 = mysql_fetch_array($r1)){
    //check if our current variable has changed.
    if($current !== $a1['Name']){
        //if so, echo out the count from the last name before resetting it.
        echo "The number of times the name has details linked to it is: $count";
        //reset the count.
        $count = 0;
        //set $current to the current name.
        $current = $a1['Name'];
        echo $a1['Name'];
    }
    echo $a1['Details'];
    //add 1 to the count.
    $count++;
} 
Comments on this post
drewj2k agrees: Excellent PHP Guru
Reply With Quote
Reply
Page 1 of 3 1 2 3 >

Viewing: Codewalkers Forums > PHP Related > PHP Coding > Showing results for Mysql Query multiple results for each name

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump



 Free IT White Papers!
 
How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.
Request Your Free Technology Downloads!
 
Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.
Request Your Free Technology Downloads!
 
Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.
Request Your Free Technology Downloads!
 
Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.
Request Your Free Technology Downloads!
 
Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.
Request Your Free Technology Downloads!
 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2009 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway. 
Stay green...Green IT