PHP - Moved: Need Help With Correct Group By (might Be Something Else) :)

This topic has been moved to Third Party PHP Scripts.

http://www.phpfreaks.com/forums/index.php?topic=346015.0

This topic has been moved to Ajax Help.

http://www.phpfreaks.com/forums/index.php?topic=330251.0

This topic has been moved to PHP Applications.

http://www.phpfreaks.com/forums/index.php?topic=327636.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=311502.0

It's pretty simple to see what I am trying to do here. For some reason all results in the table are the same exact cityName replacing all existing records. The echoed results are correct. I've include a small dump of my table as well.

$query = "SELECT cityName FROM sys_city_dev_2";
      $resource = mysqli_query($cxn, $query) or die("MySQL error: " . mysqli_error($cxn) . "<hr>\nQuery: $query");
      
  while($result = mysqli_fetch_assoc($resource)) {

          $nox = $result['cityName'];

          $toUpper = ucfirst($nox);
  echo "$toUpper" . "<br />";

          $setString = "UPDATE sys_city_dev_2 SET cityName = '" . $toUpper ."' WHERE cityName != ''";
          mysqli_query($cxn,$setString);


      } 

100 Records of table dump (pre running my script above):

--
-- Table structure for table `sys_city_dev_2_backup`
--

CREATE TABLE IF NOT EXISTS `sys_city_dev_2_backup` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Mid` int(11) NOT NULL DEFAULT '0',
  `cityName` varchar(30) NOT NULL DEFAULT '',
  `forder` int(4) NOT NULL DEFAULT '0',
  `disdplay` int(4) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=113970 ;

--
-- Dumping data for table `sys_city_dev_2_backup`
--

INSERT INTO `sys_city_dev_2_backup` (`ID`, `Mid`, `cityName`, `forder`, `disdplay`, `cid`) VALUES
(84010, 1, 'dothan', 0, 0, 0),
(84011, 1, 'alabaster', 0, 0, 0),
(84012, 1, 'birmingham', 0, 0, 0),
(84013, 2, 'flagstaff', 0, 0, 0),
(84014, 1, 'auburn', 0, 0, 0),
(84015, 1, 'florence', 0, 0, 0),
(84016, 1, 'gadsden', 0, 0, 0),
(84017, 1, 'huntsville', 0, 0, 0),
(84018, 1, 'mobile', 0, 0, 0),
(84019, 1, 'montgomery', 0, 0, 0),
(84020, 1, 'tuscaloosa', 0, 0, 0),
(84021, 2, 'mohave valley', 0, 0, 0),
(84022, 2, 'phoenix', 0, 0, 0),
(84023, 2, 'prescott', 0, 0, 0),
(84024, 2, 'sierra vista', 0, 0, 0),
(84025, 2, 'tucson', 0, 0, 0),
(84026, 2, 'yuma', 0, 0, 0),
(84027, 3, 'bakersfield', 0, 0, 0),
(84028, 3, 'chico', 0, 0, 0),
(84029, 3, 'fresno / madera', 0, 0, 0),
(84030, 3, 'gold country', 0, 0, 0),
(84031, 3, 'humboldt county', 0, 0, 0),
(84032, 3, 'imperial', 0, 0, 0),
(84033, 3, 'inland empire', 0, 0, 0),
(84034, 3, 'los angeles', 0, 0, 0),
(84035, 3, 'alhambra', 0, 0, 0),
(84036, 3, 'merced', 0, 0, 0),
(84037, 49, 'fayetteville', 0, 0, 0),
(84038, 49, 'fort smith', 0, 0, 0),
(84039, 49, 'jonesboro', 0, 0, 0),
(84040, 49, 'little rock', 0, 0, 0),
(84041, 49, 'arkadelphia', 0, 0, 0),
(84042, 49, 'texarkana', 0, 0, 0),
(84043, 3, 'modesto', 0, 0, 0),
(84044, 3, 'alta sierra', 0, 0, 0),
(84045, 3, 'alpine', 0, 0, 0),
(84046, 3, 'pedley', 0, 0, 0),
(84047, 3, 'redding', 0, 0, 0),
(84048, 3, 'alondra park', 0, 0, 0),
(84049, 3, 'sacramento', 0, 0, 0),
(84050, 4, 'canon city', 0, 0, 0),
(84051, 3, 'san luis obispo', 0, 0, 0),
(84052, 3, 'santa barbara', 0, 0, 0),
(84053, 3, 'stockton', 0, 0, 0),
(84054, 3, 'aliso viejo', 0, 0, 0),
(84055, 3, 'visalia', 0, 0, 0),
(84056, 3, 'yuba city', 0, 0, 0),
(84057, 4, 'boulder', 0, 0, 0),
(84058, 4, 'colorado springs', 0, 0, 0),
(84059, 4, 'denver', 0, 0, 0),
(84060, 4, 'applewood', 0, 0, 0),
(84061, 4, 'pueblo', 0, 0, 0),
(84062, 4, 'air force academy', 0, 0, 0),
(84063, 5, 'avon', 0, 0, 0),
(84064, 5, 'hartford', 0, 0, 0),
(84065, 5, 'new haven', 0, 0, 0),
(84066, 5, 'ansonia', 0, 0, 0),
(84067, 5, 'fairfield', 0, 0, 0),
(84068, 7, 'daytona beach', 0, 0, 0),
(84069, 7, 'sebastian', 0, 0, 0),
(84070, 5, 'wallingford center', 0, 0, 0),
(84071, 8, 'belvedere park', 0, 0, 0),
(84072, 7, 'sarasota springs', 0, 0, 0),
(84073, 7, 'sandalfoot cove', 0, 0, 0),
(84074, 7, 'san carlos park', 0, 0, 0),
(84075, 7, 'st. augustine', 0, 0, 0),
(84076, 7, 'tallahassee', 0, 0, 0),
(84077, 7, 'safety harbor', 0, 0, 0),
(84078, 7, 'ruskin', 0, 0, 0),
(84079, 8, 'athens-clarke county', 0, 0, 0),
(84080, 8, 'atlanta', 0, 0, 0),
(84081, 8, 'augusta-richmond county', 0, 0, 0),
(84082, 8, 'brunswick', 0, 0, 0),
(84083, 8, 'columbus', 0, 0, 0),
(84084, 8, 'americus', 0, 0, 0),
(84085, 8, 'acworth', 0, 0, 0),
(84086, 8, 'valdosta', 0, 0, 0),
(84087, 10, 'boise', 0, 0, 0),
(84088, 10, 'ammon', 0, 0, 0),
(84089, 10, 'moscow', 0, 0, 0),
(84090, 10, 'blackfoot', 0, 0, 0),
(84091, 10, 'twin falls', 0, 0, 0),
(84092, 10, 'meridian', 0, 0, 0),
(84093, 10, 'jerome', 0, 0, 0),
(84094, 10, 'idaho falls', 0, 0, 0),
(84095, 11, 'addison', 0, 0, 0),
(84096, 10, 'garden city', 0, 0, 0),
(84097, 10, 'eagle', 0, 0, 0),
(84098, 10, 'chubbuck', 0, 0, 0),
(84099, 10, 'caldwell', 0, 0, 0),
(84100, 12, 'bloomington', 0, 0, 0),
(84101, 12, 'evansville', 0, 0, 0),
(84102, 12, 'fort wayne', 0, 0, 0),
(84103, 12, 'indianapolis', 0, 0, 0),
(84104, 12, 'muncie / anderson', 0, 0, 0),
(84105, 12, 'lafayette / west lafayette', 0, 0, 0),
(84106, 12, 'south bend / michiana', 0, 0, 0),
(84107, 12, 'terre haute', 0, 0, 0),
(84108, 12, 'northwest indiana', 0, 0, 0),
(84109, 13, 'ames', 0, 0, 0);

Hi all, does anyone know how to perform this, or if it's even possible? I need every 6th row to be a GROUP BY of the following 5 rows, they all share a common group_id so that's what the GROUP BY is performed on.

1. GROUP BY group_id
2. item #1
3. item #2
4. item #3
5. item #4
6. item #5
7. GROUP BY group_id
8. item #6
... and so on

I am trying to get a group by to work but for some reason it will only display one output.  Not sure what the problem is.

Code: [Select]
mpid  mpyear mpmonth mpday  mptitle
  4       2012      3             2        Text stuff here
  3       2012      3             1        Day 1 stuff here
  2       2012      2             28      Feb stuff here
  1       2011      12           27      First post test here


So what I am trying to do is sort out per year month

ie:

2012
3
Text stuff here
Day 1 stuff here
2
Feb stuff here
2011
12
 First post test here
That is what I eventually want it to look like, but first thing I need to do is get atleast the mptitle to group first.
Code: [Select]
$query = "SELECT * FROM monsterpost group BY 'mpmonth'";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo $row['mptitle'];
echo "<br />";
}
?>

Hi,

I have database, screenshot attached. I have to print all records of attendance sort by ID and have sum group by ID. How to know or figure out when the ID changes so that i can show the total for that ID. Bare with me please and try to show me where is my mistake!

Code:

<?Php

	include('session.php');
	include('dbcon.php');
	
?>

<html>
<head>
<link rel="stylesheet" type="text/css" href="bootstrap.css">
</head>

<body>
	
	<div class="container">
		<div class="row">
			<div class="col m-auto">
				<div class="card mt-5">
					<table class="table table-bordered">
					
						<tr>

							<input type="button" onClick="window.print()" value="Print The Report"/>
							<td>OracleID</td>
							<td>Name</td>
							<td>Designation</td>
							<td>Clocking In Time</td>
							<td>Clocking Out Time</td>
							<td>Duration</td>
						
						</tr>
						
						<?php
						
							$isdone = -1;
							
							$query = "select * from attendance_records where isdone= '".$isdone."' order by OracleID";
							$result = mysqli_query($con, $query);
																			
							$query2 = "SELECT sec_to_time(SUM(timestampdiff(SECOND, ClockingInDate, ClockingOutDate))) as total from attendance_records group by OracleID";
							$result2 = mysqli_query($con, $query2);
							
							while ($row = mysqli_fetch_assoc($result))
							{
								
								$userid = $row['OracleID'];
								$name = $row['Name'];
								$des = $row['Des'];
								$clockingindate = $row['ClockingInDate'];
								$clockingoutdate = $row['ClockingOutDate'];
								$duration = $row['Duration'];
								$t=time();
								$curdate = date("d-m-Y",$t);
								$curTime = date("g:i:s A",$t);
																		
						?>
						
						<tr>
						
							<td><?php echo $userid ?></td>
							<td><?php echo $name ?></td>
							<td><?php echo $des ?></td>
							<td><?php echo $clockingindate ?></td>
							<td><?php echo $clockingoutdate ?></td>
							<td><?php echo $duration ?></td>
		
						</tr>
						
						<?php									
							
							}	
								while($row2 = mysqli_fetch_assoc($result2))
									{
										$totals= $row2['total'];
									
										echo '<td><h5>Total Attendance Time:</h5></td>';
										echo '<td>';
										echo $totals; 
										echo '</td>';
									}						
										
							
								echo '<tr>';
								echo 'Report created by ', $_SESSION['user_id2'], ' on ', $curdate, ' at ', $curTime;
								
								echo '</tr>';
							
						?>
						
					</table>
					
					<a href="home.php" width="100%">Click here to go back to Main Menu</a>
			
				</div>
			</div>
		</div>
	</div>

</body>

</html>

and screenshot of result is attached. I have the totals but not at the end of each OracleID, it is at the end of the report.

Thanks.

Hi all,

I have a script that is essentially a log file of all preious versions of an article (versioning)

You can see the script I am talking about in action at:

http://danielrhyshardy.com/AWT/forumadmin.php

What I would like to do is, instead of displaying the title of each instance (you will see there are two instances named "apple", as they are two previous versions of an article), I would like to display that title once, and then all of the instances of that title be displayed below.

I hope that makes sense.

Here is the code Code: [Select]
<?

$sql = "SELECT id,title,message,date_added FROM messages2 ORDER BY id "; 
 
 
 
 
   $result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
   
   



   while(list($id,$title,$message,$date_added)=mysql_fetch_row($result)){





     
echo '<div style="color:#bb0000;width:250px;text-align:center;float:left;margin-left:15px;margin-bottom:15px;padding-bottom:20px;"><b>'.$title.'<br>'.$date_added.'</b>
 
<input id="'.$message.'" type="radio" name="admin[]" value="'.$id.'"  onclick="javascript:document.form22.car.value=this.id"
  >


</font></div>'."\n";
   
   

   

 
}



?>

I would use something like
Code: [Select]
if ($title = "apple") {

but this would obviously not work for new posts, unless I wanted to constantly update my code.

I am sure there is a simple way to achieve this.

Perhaps someone can tell me!?

Thanks in advance

Dan

G'day to all,

I need some help building a site that has the ability to accept members and deposit those members in a database.

When the members join they will have the ability to add features to their profile; like, skills and certificates. Upload pictures of certificates and a profile pic.

The admin area needs to have the ability to add membership types and give those members specific abilities.

The admin also needs to have the ability to create profile options, like additional skills.

Like a type 1 member has the ability search and see all type 2 members who have a particular skill set.

type 2 members just get to see their own profile and the tasks the type 1 members list.

 

This all might sound like a bunch of brambles but please let me know if you are interested and we can get more involved.

Thank you so very much for your help in advanced...

Juan

Hi    I am reading in data from a csv file

if (($handle = fopen("data.csv", "r")) !== FALSE) {

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

echo "User Name: $data[0]";
echo "Booking IDs: $data[1]";

}

echo "<hr>";

The problem is some of the usernames are duplicated so get outputted the same user name 20 times but with different IDs, obviously but I am unsure how to group them together.    I can't seem to figure out how to write if the next username is the same as current then just add another ID not go through the entire loop.    Any ideas?   Thanks  

im trying to pull the information for a post which is to include 3 db tables(posts, users, attachments)

Ive tried the following code:

Code: [Select]
$query = $link->query("SELECT
            p.*,
            u.u_username, u.u_posts, u.u_avatar,
            u.u_signature, u.u_avatar_cropped,
            group_concat(a.a_name, a.a_size, a.a_date_posted separator '<br />') attachments
            FROM ".TBL_PREFIX."posts as p
            JOIN ".TBL_PREFIX."users as u
            ON (u.u_username = p.p_poster)
            LEFT JOIN ".TBL_PREFIX."post_attachments as a
            USING (".TBL_PREFIX."posts.p_pid)
            WHERE p.p_tid = '$tid'
            GROUP BY p.p_pid
            ORDER BY p.p_time_posted ASC")or die(print_link_error());


but that generates the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.p_pid)
         WHERE p.p_tid = '158'
         GROUP BY p.p_pid
         ORDER BY p.p_time_pos' at line 10"

All i am trying to do is pull everything from posts, join users and select all attachments that belong to the post.
Here is my database setup(shortened)

asf_posts
p_pid | p_name | p_poster
  1            test    doddsey65

asf_users
u_uid | u_username
  1        doddsey65

asf_attachments
a_aid | a_pid | a_name
  1            1      name

can anyone help?

Hi,

Cut down code

SELECT count(number) FROM table GROUP BY title

echo $title .'<br>'. $number;



That outputs

Code: [Select]
Title1
5

Title2
9

Title3
2

But what do i do if i wanted to retain and loop through the "$number" actual values, not JUST count the total? I still need access to what "$number" is while looping. But as GROUP BY returns one result.. im stuck.

Ideally i want this as the resulting output

Code: [Select]
title1 // grouped title
3   // total number of $numbers per grouped title
1234
12345
123456  // The actual values of $number totalling to the above grouped number

title2 // grouped title
2   // total number of $numbers
1234
123456  // The actual values of $number totalling to the above grouped number

Hi

Just a quick one.

I want my output result to look like this. In its simplistic form I have 2 columns, NAME and NUMBER.

Adam
1
2
3

Becky
1
2
3

Charlie
1
2
3


So the output results are grouped by the "name" field, then ordered by the "number" field.

I thought it was GROUP BY, but that function is something else.

Iv tried searching around but im unsure what you call this form of grouping results.

Many Thanks!