Page 1 of 1

Selecting results for each person in the database

Posted: Fri Jan 03, 2014 11:03 am
by madwilliams
So this works great for one person when you type thier name in name here but how do I get it to print the results for each person in the table personnel? I've tryed SELECT name FROM personnel Where name between 1 and 90 and it prints out all of the names and then i changed the name here filed to $name but is prints out zeros. please help

Code: Select all

<?php
                  //load configuration
                  require("config.php");
  
                  //connect to database
                  mysql_connect($db_server,$db_user,$db_password) or die("Database server connection failed. Check variables \$db_server, \$db_user and \$db_password in config.php");
                  mysql_select_db($db_name) or die("Selecting database failed. Check variable \$db_name in config.php");
?>

     <link href="/style.css" rel="stylesheet" type="text/css" />
     <style type="text/css">

     .auto-style100 
     { 	
     font-family: Arial, Helvetica, sans-serif; 	
     border-style: solid; 
     border-width: 1px;
     color: #000000;
     } 
 
     .auto-style200 
     { 	
     text-align: center; 	
     font-family: Arial; 	
     font-size: xx-small;
	 color: #000000;
     } 
 
     .auto-style300 
     { 	
     text-align: center; 	
     font-family: Arial; 	
     font-size: small;
     color: #000000; 
     } 
 
</style>
<?php				  
				  // Print out results for Names
				  $sql = ('SELECT name FROM personnel Where name LIKE "%name here%"' );
                                                                  $result = mysql_query($sql); 
                                                                  $num_rows = mysql_num_rows($result);   
                                                                  while ($get_info = mysql_fetch_row($result)){ 
                                                                  foreach ($get_info as $name)
				  print "<table class=auto-style100 width=96% border=1px align=center>";
				  print "<tr>";				  
                                                                  print "<td class=auto-style200 width=16%><p align=center>$name</td>"; 
                                                                  }
				  
				  //Print out results for Meetings
                                                                  $sql = ('SELECT COUNT(*)FROM meeting WHERE personnel_attended LIKE "%name here%"');
                                                                  $result = mysql_query($sql);
                                                                  $num_rows = mysql_num_rows($result);
				  while ($get_info = mysql_fetch_row($result)){
				  foreach ($get_info as $meeting)
				  print "<td class=auto-style200 width=16%><p align=center>$meeting</td>";
				  }

				  //Print out results for Fires
                                                                  $sql = ('SELECT COUNT(*)FROM fires WHERE personnel_responding LIKE "%name here%"');
                                                                  $result = mysql_query($sql);
                                                                  $num_rows = mysql_num_rows($result);
				  while ($get_info = mysql_fetch_row($result)){				  
				  foreach ($get_info as $fires)
				  print "<td class=auto-style200 width=16%><p align=center>$fires</td>";
				  }
				  
				  //Print out results for Accidents
                                                                  $sql = ('SELECT COUNT(*)FROM accidents WHERE personnel_responding LIKE "%name here%"');
                                                                  $result = mysql_query($sql);
                                                                  $num_rows = mysql_num_rows($result); 
				  while ($get_info = mysql_fetch_row($result)){			  
				  foreach ($get_info as $accidents)
				  print "<td class=auto-style200 width=16%><p align=center>$accidents</td>";
				  }
				  
				  //Print out results for Medical
                                                                  $sql = 'SELECT COUNT(*)FROM medical WHERE personnel_responding LIKE "%name here%"';
                                                                  $result = mysql_query($sql);
                                                                  $num_rows = mysql_num_rows($result); 
				  while ($get_info = mysql_fetch_row($result)){				  
				  foreach ($get_info as $medical)
				  print "<td class=auto-style200 width=16%><p align=center>$medical</td>";
				  }
				  
				  //Print out results for Service
                                                                  $sql = 'SELECT COUNT(*)FROM service WHERE personnel_responding LIKE "%name here%"';
                                                                  $result = mysql_query($sql);
                                                                  $num_rows = mysql_num_rows($result);
				  while ($get_info = mysql_fetch_row($result)){				  
				  foreach ($get_info as $service)
				  print "<td class=auto-style200 width=16%><p align=center>$service</td>";
                                                                  print "</tr>";
				  }
                                                                  print "</table>";

                                                                  //close database connection
                                                                  mysql_close();
?>

Re: Selecting results for each person in the database

Posted: Fri Jan 03, 2014 12:56 pm
by Celauran

Code: Select all

SELECT name FROM personnel
If you want everything in the table, you don't really need a WHERE clause.

Re: Selecting results for each person in the database

Posted: Fri Jan 03, 2014 1:26 pm
by madwilliams

Code: Select all

<?php
                  //load configuration
                  require("config.php");
  
                  //connect to database
                  mysql_connect($db_server,$db_user,$db_password) or die("Database server connection failed. Check variables \$db_server, \$db_user and \$db_password in config.php");
                  mysql_select_db($db_name) or die("Selecting database failed. Check variable \$db_name in config.php");
?>

     <link href="/style.css" rel="stylesheet" type="text/css" />
     <style type="text/css">

     .auto-style100 
     { 	
     font-family: Arial, Helvetica, sans-serif; 	
     border-style: solid; 
     border-width: 1px;
     color: #000000;
     } 
 
     .auto-style200 
     { 	
     text-align: center; 	
     font-family: Arial; 	
     font-size: xx-small;
	 color: #000000;
     } 
 
     .auto-style300 
     { 	
     text-align: center; 	
     font-family: Arial; 	
     font-size: small;
     color: #000000; 
     } 
 
 </style>
<?php				  
				  // Print out results for Names
				  $sql = ('SELECT name FROM personnel Where id BETWEEN 1 and 90' );
                  $result = mysql_query($sql); 
                  $num_rows = mysql_num_rows($result);   
                  while ($get_info = mysql_fetch_row($result)){ 
                  foreach ($get_info as $name)
				  print "<table class=auto-style100 width=96% border=1px align=center>";
				  print "<tr>";				  
                  print "<td class=auto-style200 width=16%><p align=center>$name</td>"; 
                  }
				  
				  //Print out results for Meetings
                  $sql = ('SELECT COUNT(*)FROM meeting WHERE personnel_attended LIKE "%$name%"');
                  $result = mysql_query($sql);
                  $num_rows = mysql_num_rows($result);
				  while ($get_info = mysql_fetch_row($result)){
				  foreach ($get_info as $meeting)
				  print "<td class=auto-style200 width=16%><p align=center>$meeting</td>";
				  }

				  //Print out results for Fires
                  $sql = ('SELECT COUNT(*)FROM fires WHERE personnel_responding LIKE "%$name%"');
                  $result = mysql_query($sql);
                  $num_rows = mysql_num_rows($result);
				  while ($get_info = mysql_fetch_row($result)){				  
				  foreach ($get_info as $fires)
				  print "<td class=auto-style200 width=16%><p align=center>$fires</td>";
				  }
				  
				  //Print out results for Accidents
                  $sql = ('SELECT COUNT(*)FROM accidents WHERE personnel_responding LIKE "%$name%"');
                  $result = mysql_query($sql);
                  $num_rows = mysql_num_rows($result); 
				  while ($get_info = mysql_fetch_row($result)){			  
				  foreach ($get_info as $accidents)
				  print "<td class=auto-style200 width=16%><p align=center>$accidents</td>";
				  }
				  
				  //Print out results for Medical
                  $sql = 'SELECT COUNT(*)FROM medical WHERE personnel_responding LIKE "%$name%"';
                  $result = mysql_query($sql);
                  $num_rows = mysql_num_rows($result); 
				  while ($get_info = mysql_fetch_row($result)){				  
				  foreach ($get_info as $medical)
				  print "<td class=auto-style200 width=16%><p align=center>$medical</td>";
				  }
				  
				  //Print out results for Service
                  $sql = 'SELECT COUNT(*)FROM service WHERE personnel_responding LIKE "%$name%"';
                  $result = mysql_query($sql);
                  $num_rows = mysql_num_rows($result);
				  while ($get_info = mysql_fetch_row($result)){				  
				  foreach ($get_info as $service)
				  print "<td class=auto-style200 width=16%><p align=center>$service</td>";
                  print "</tr>";
				  }
                  print "</table>";

                  //close database connection
                  mysql_close();
?>
Here is what I have tryed this result gives me all of the names but not how many times each of these personnel have been on each call type. Im guessing I can't use $name. How do I make it print out all of the personnel between 1 and 90 and how many times each personnel have been on each one of the calls? in the first post if I replaced name here with a personnels name it gave me the results for that one person but I need it to do them all between 1 and 90

Re: Selecting results for each person in the database

Posted: Fri Jan 03, 2014 1:41 pm
by madwilliams
Like the image I submitted.

Re: Selecting results for each person in the database

Posted: Fri Jan 03, 2014 2:31 pm
by Celauran
Running queries inside a loop is a sure sign you're doing things wrong. In this case, you're running 450 queries when 1 would suffice. Why not try something like this?

Code: Select all

SELECT p.name AS name, COUNT(m.*) AS meeting_count, COUNT(f.*) AS fire_count, COUNT(a.*) AS accident_count, COUNT(med.*) AS medical_count, COUNT(s.*) AS service_count
FROM personnel AS p
JOIN meeting AS m ON p.name = m.personnel_attended
JOIN fires AS f ON p.name = f.personnel_responding
JOIN accidents AS a ON p.name = a.personnel_responding
JOIN medical AS med ON p.name = med.personnel_responding
JOIN service AS s ON p.name = s.personnel_responding
WHERE p.id BETWEEN 1 AND 90
Also, you really shouldn't be using the MySQL extension. Look into PDO.