PHP - Mysql Group By Is Pretty Awesome

I am getting Use of undefined constant id - assumed 'id' with this:


$query = "SELECT * FROM products WHERE id = ".mysql_real_escape_string($_POST[id])."";

That seems pretty bull shit to me. Is this a bug in PHP?

finder.com.au is making waves in the comparison space, and we are expanding our Design & Technology team massively this year. We are looking for several full-stack Senior PHP Developers who will be responsible for building a wide variety of web-based applications (using PHP and MySQL) in addition to maintaining existing PHP code, optimizing website and database performance and developing custom PHP solutions. This is a great opportunity for web people at heart to join a growing team delivering awesome products that get used by hundreds of thousands of users. As a developer at finder.com.au you will have the freedom, autonomy, and responsiblity to improve the code, tools, and architecture without having to cut through red tape.   Why finder.com.au?   We've just been named one of the top service companies in the Asia Pacific for 2014, taking silver in the Stevie Awards for Australian Service Company of the year. We’ve also been dubbed one of Anthill’s 50 coolest companies in Australia for 2013 thanks to our amazing team, wonderful users and cool company culture.  As a development team, we move quickly, releasing code several times per day.  You need to be willing to move fast, rely on automation, make decisions on less than perfect information, trust your teammates, and constantly strive to improve. A new Head of Design and Technology, with experience at two of local startup scene’s biggest success stories, recently joined us to lead the team on a mission to speed up the entire company to become the Australian brand for comparison. The team runs hackdays every other month that give all developers free time to show off their creativity and innovation.  You will have choice of OS, laptop or desktop, IDE and toolset, and up to 3 monitors. What’s more, you will experience an ultra modern office located in the heart of the Sydney CBD, catered lunches and breakfast supplied every day and all the snacks and drinks you want!   Who are  you?   You live and breathe development and your code has soul. You are a great PHP coder that wants to join a team where you can flex your skills and grow further. You are an expert who understands complex problems and enjoys deploying solutions to business problems. You are keen to get your projects live and solve business problems and improve the development environment and tooling. You also have experience in getting code live multiple times per day. You may be applying from overseas – We are happy to provide support with sponsored relocation services. We already have a number of people on our team from overseas so you will be joining a community.   Must have experience:   4+ years of PHP development experience for Senior Roles Experience with phpunit Knowledge of CI tools (Bamboo, Travis, CircleCI) Knowledge of CD tools (Beanstalk, Capistrano) Knowledge of Git Ability and desire to use and extend Git tools (GitHub, Bitbucket) to enhance workflow Ability to mentor junior developers Experience with Wordpress beneficial Knowledge of Front End technologies beneficial Knowledge of monitoring tools beneficial   Please send us a resume to iwantin@finder.com.au and optionally provide us with a link to your GitHub or Bitbucket profile and a personal note as to why you would want to work with us at finder.com.au.    

I would like to take the following xml file, and convert it to a nice array. Is there any functions to do this or any suggestions how to do this?

xml file:
Code: [Select]
<?xml version="1.0" encoding="UTF-8"?>
<users>
<instructions>
<phpfile>users.php</phpfile>
</instructions>
<users action="multiple">
<user>
<name>root</name>
<password>5f4dcc3b5aa765d61d8327deb882cf99</password>
<permissions>
<access>CREATE_USR</access>
</permissions>
</user>
<users>
</users>
Desired array:
Code: [Select]
Array (
[instructions] => Array (
[phpfile] => users.php
)
[users] => Array (
[0] => Array (
[name] => root
[password] => 5f4dcc3b5aa765d61d8327deb882cf99
[permissions] => Array(
[access] => CREATE_USR
)
)
)
)

you can see it at the link below on my site. The generated thumbnails look terrible but I don't know why.  Any help greatly appreciated. thank you . (code below of upload script and viewgallery script)

http://ealike.com/gallery/viewgallery.php?cid=1


upload script

Code: [Select]
<?php
include("config.inc.php");

// initialization
$result_final = "";
$counter = 0;

// List of our known photo types
$known_photo_types = array( 
'image/pjpeg' => 'jpg',
'image/jpeg' => 'jpg',
'image/gif' => 'gif',
'image/bmp' => 'bmp',
'image/x-png' => 'png'
);

// GD Function List
$gd_function_suffix = array( 
'image/pjpeg' => 'JPEG',
'image/jpeg' => 'JPEG',
'image/gif' => 'GIF',
'image/bmp' => 'WBMP',
'image/x-png' => 'PNG'
);

// Fetch the photo array sent by preupload.php
$photos_uploaded = $_FILES['photo_filename'];

// Fetch the photo caption array
$photo_caption = $_POST['photo_caption'];

while( $counter <= count($photos_uploaded) )
{
if($photos_uploaded['size'][$counter] > 0)
{
if(!array_key_exists($photos_uploaded['type'][$counter], $known_photo_types))
{
$result_final .= "File ".($counter+1)." is not a photo<br />";
}
else
{
mysql_query( "INSERT INTO gallery_photos(`photo_filename`, `photo_caption`, `photo_category`) VALUES('0', '".addslashes($photo_caption[$counter])."', '".addslashes($_POST['category'])."')" );
$new_id = mysql_insert_id();
$filetype = $photos_uploaded['type'][$counter];
$extention = $known_photo_types[$filetype];
$filename = $new_id.".".$extention;

mysql_query( "UPDATE gallery_photos SET photo_filename='".addslashes($filename)."' WHERE photo_id='".addslashes($new_id)."'" );

// Store the orignal file
copy($photos_uploaded['tmp_name'][$counter], $images_dir."/".$filename);

// Let's get the Thumbnail size
$size = GetImageSize( $images_dir."/".$filename );
if($size[0] > $size[1])
{
$thumbnail_width = 100;
$thumbnail_height = (int)(100 * $size[1] / $size[0]);
}
else
{
$thumbnail_width = (int)(100 * $size[0] / $size[1]);
$thumbnail_height = 100;
}

// Build Thumbnail with GD 1.x.x, you can use the other described methods too
$function_suffix = $gd_function_suffix[$filetype];
$function_to_read = "ImageCreateFrom".$function_suffix;
$function_to_write = "Image".$function_suffix;

// Read the source file
$source_handle = $function_to_read ( $images_dir."/".$filename ); 

if($source_handle)
{
// Let's create an blank image for the thumbnail
      $destination_handle = ImageCreate ( $thumbnail_width, $thumbnail_height );

// Now we resize it
       ImageCopyResized( $destination_handle, $source_handle, 0, 0, 0, 0, $thumbnail_width, $thumbnail_height, $size[0], $size[1] );
}

// Let's save the thumbnail
$function_to_write( $destination_handle, $images_dir."/tb_".$filename );
ImageDestroy($destination_handle );
//

$result_final .= "<img src='".$images_dir. "/tb_".$filename."' /> File ".($counter+1)." Added<br />";
}
}
$counter++;
}

// Print Result
echo <<<__HTML_END

<html>
<head>
<title>Photos uploaded</title>
</head>
<body>
$result_final
</body>
</html>

__HTML_END;
?>

DISPLAY THUMBNAILS AND IMAGES SCRIPT

Code: [Select]
<?php
include("config.inc.php");

// initialization
$result_array = array();
$counter = 0;

$cid = (int)($_GET['cid']);
$pid = (int)($_GET['pid']);

// Category Listing

if( empty($cid) && empty($pid) )
{
$number_of_categories_in_row = 4;

$result = mysql_query( "SELECT c.category_id,c.category_name,COUNT(photo_id)
FROM gallery_category as c
LEFT JOIN gallery_photos as p ON p.photo_category = c.category_id
GROUP BY c.category_id" );
while( $row = mysql_fetch_array( $result ) )
{
$result_array[] = "<a href='viewgallery.php?cid=".$row[0]."'>".$row[1]."</a> "."(".$row[2].")";
}
mysql_free_result( $result );

$result_final = "<tr>\n";

foreach($result_array as $category_link)
{
if($counter == $number_of_categories_in_row)
{
$counter = 1;
$result_final .= "\n</tr>\n<tr>\n";
}
else
$counter++;

$result_final .= "\t<td>".$category_link."</td>\n";
}

if($counter)
{
if($number_of_categories_in_row-$counter)
$result_final .= "\t<td colspan='".($number_of_categories_in_row-$counter)."'>&nbsp;</td>\n";

$result_final .= "</tr>";
}
}


// Thumbnail Listing

else if( $cid && empty( $pid ) )
{
$number_of_thumbs_in_row = 5;

$result = mysql_query( "SELECT photo_id,photo_caption,photo_filename FROM gallery_photos WHERE photo_category='".addslashes($cid)."'" );
$nr = mysql_num_rows( $result );

if( empty( $nr ) )
{
$result_final = "\t<tr><td>No Category found</td></tr>\n";
}
else
{
while( $row = mysql_fetch_array( $result ) )
{
$result_array[] = "<a href='viewgallery.php?cid=$cid&pid=".$row[0]."'><img src='".$images_dir."/tb_".$row[2]."' border='0' alt='".$row[1]."' /></a>";
}
mysql_free_result( $result );

$result_final = "<tr>\n";

foreach($result_array as $thumbnail_link)
{
if($counter == $number_of_thumbs_in_row)
{
$counter = 1;
$result_final .= "\n</tr>\n<tr>\n";
}
else
$counter++;

$result_final .= "\t<td>".$thumbnail_link."</td>\n";
}

if($counter)
{
if($number_of_photos_in_row-$counter)
$result_final .= "\t<td colspan='".($number_of_photos_in_row-$counter)."'>&nbsp;</td>\n";

$result_final .= "</tr>";
}
}
}

// Full Size View of Photo
else if( $pid )
{
$result = mysql_query( "SELECT photo_caption,photo_filename FROM gallery_photos WHERE photo_id='".addslashes($pid)."'" );
list($photo_caption, $photo_filename) = mysql_fetch_array( $result );
$nr = mysql_num_rows( $result );
mysql_free_result( $result );

if( empty( $nr ) )
{
$result_final = "\t<tr><td>No Photo found</td></tr>\n";
}
else
{
$result = mysql_query( "SELECT category_name FROM gallery_category WHERE category_id='".addslashes($cid)."'" );
list($category_name) = mysql_fetch_array( $result );
mysql_free_result( $result );

$result_final .= "<tr>\n\t<td>
<a href='viewgallery.php'>Categories</a> &gt; 
<a href='viewgallery.php?cid=$cid'>$category_name</a></td>\n</tr>\n";

$result_final .= "<tr>\n\t<td align='center'>
<br />
<img src='".$images_dir."/".$photo_filename."' border='0' alt='".$photo_caption."' />
<br />
$photo_caption
</td>
</tr>";
}
}

// Final Output
echo <<<__HTML_END

<html>
<head>
<title>Gallery View</title>
</head>
<body>
<table width='100%' border='0' align='center' style='width: 100%;'>
$result_final
</table>
</body>
</html>

__HTML_END;
?>

I'm in the process of building a network for investors and entrepreneurs. When a user logs in, they are directed to profile.php where they can have access to their profile information. If you go into the address bar and type in network.jasonbiondo.com/jasonbiondo you will not be able to view my profile, but if you type in http://network.jasonbiondo.com/profile. ... asonbiondo , you will be able to see my profile. I want to make it cleaner so that you can access profiles by going directly to network.jasonbiondo.com/jasonbiondo . I think my .htaccess file is causing the problem.

I have listed my code below.

.htaccess

Code: [Select]
  Options +FollowSymLinks
    RewriteEngine On
    RewriteCond %{SCRIPT_FILENAME} !-d
    RewriteCond %{SCRIPT_FILENAME} !-f
    RewriteRule ^(.*)$ ./profile.php?u=$1 [NC]

<?php
if (!isset($_GET['register'])) {
echo "register stuff here"; 
}
elseif (!isset($_GET['login'])) {
echo "login stuff here";
}
elseif (!isset($_GET['forgotpassword'])) {
echo "password recovery stuff here"; 
}
else {
echo "default page stuff here"; 
}
?>

you get what i'm going for, but it kinda messes up this way with different contents on different places? help

http://i.imgur.com/6olCy.png
The picture (in the imgur link) pretty much describes what I'm trying to do. As it stands, it would take too long to simply do everything manually with html and css (both of which I'm pretty good at I think), moving all img src and dates from one div tag to another whenever I wanna add something. So yeah, what's the most efficient way to simply submit a picture, and all my other pictures will move?

I know some of you guys will say to just learn php, but quite frankly, I don't have the time... and all the php books are ~1000pgs long which seems really daunting. I know, my problem's pretty basic and it'll probably be covered in the first 100pages or so, but still, like I said I don't have that much time for the moment.

When I do, however, can you guys recommend me the quickest guide/video series/book to learn practical php? Not the most comprehensive, as the stuff I'm going to be doing's not that complex.

One last thing, I've been hearing about CakePHP; what is it? I read that it's an "open source web application framework", but I still have little clue. I'm currently using dreamweaver for all my web stuff (no I do not use the WYSIWYG feature, I just grown accustom to using it). Should I "learn cakephp"?


Please answer any one of those questions (particularly the first one). Many thanks and I'll be sure to reply!

I found this online at http://php.net/manual/en/function.time.php:

<?php
 function rel_time($from, $to = null)
 {
  $to = (($to === null) ? (time()) : ($to));
  $to = ((is_int($to)) ? ($to) : (strtotime($to)));
  $from = ((is_int($from)) ? ($from) : (strtotime($from)));

  $units = array
  (
   "year"   => 31536000, // seconds in a year   (12 months)
   "month"  => 2628000,  // seconds in a month  (4 weeks)
   "week"   => 604800,   // seconds in a week   (7 days)
   "day"    => 86400,    // seconds in a day    (24 hours)
   "hour"   => 3600,     // seconds in an hour  (60 minutes)
   "minute" => 60,       // seconds in a minute (60 seconds)
   "second" => 1         // 1 second
  );

  $diff = abs($from - $to);
  $suffix = (($from > $to) ? ("from now") : ("ago"));

  foreach($units as $unit => $mult)
   if($diff >= $mult)
   {
    $and = (($mult != 1) ? ("") : ("and "));
    $output .= ", ".$and.intval($diff / $mult)." ".$unit.((intval($diff / $mult) == 1) ? ("") : ("s"));
    $diff -= intval($diff / $mult) * $mult;
   }
  $output .= " ".$suffix;
  $output = substr($output, strlen(", "));

  return $output;
 }
?>
=======================

Basically, if you type <?php echo rel_time("March 12, 2011 7:00 PM"); ?>, it will show you the time ago relative to the current time.

But there's two problems. 
1) The time it sends out is about 5hrs early (the actual time is 7:00pm, I set the rel time to 7:00pm, but the time it displays reads '5 hours ago, 1 minute, and 3 seconds ago'. I'm not much of a coder, but judging by the looks of it, it would appear correct... but apparently not.

2) My second gripe is that the code shows too many increments of date. What I mean is, if if I set the date to over a year ago, it would display the years,months,weeks,days,minutes,seconds. That is way too much increments. I just want it to display seconds, if I submitted it seconds ago, just display minutes if it was displayed minutes ago, and display days if it was submitted days ago. You get the idea.

Friends, family, fellow coders, and bored people- can you help a nooblet?

Thanks!

$username = $loggedInUser->username; // This is the logged in username
$time = time();
$makedir = $username.'_'.$time;

$var = getcwd();
$var = str_replace('\users', '\imageuploads', $var);
$dirlocation = $var."\\".test_directory($username, $mysqli);   function test_directory ($username, $mysqli) {
     $stmt = $mysqli->prepare("SELECT Temp_Directory FROM uc_users WHERE user_name LIKE ?");
    $stmt->bind_param("s", $username);
    $stmt->execute();
    $stmt->bind_result($Tempdir);
    while ($stmt->fetch()){
    return $Tempdir;
        }
    } if((!empty(test_directory($username, $mysqli))) && is_dir($dirlocation)){
//echo "this is it";
$thedirectory = $dirlocation;
}

if(empty(test_directory($username, $mysqli))){
//echo "it's not a directory";
$newdir = $var."\\".$makedir;
$query = mysqli_query($mysqli, "UPDATE uc_users SET Temp_Directory='$makedir' WHERE user_name='$username'");
if(!$query){
//echo mysqli_error($mysqli);
}
mkdir($newdir);
//security
chmod($newdir, 0644);
$thedirectory = $newdir;
}

if(!is_dir($dirlocation) && (!empty(test_directory($username, $mysqli)))){
//echo "third one";
mkdir($dirlocation);
chmod($dirlocation, 0644);
$thedirectory = $dirlocation;
}         Ok, so what I'm doing here is testing to see whether a a record exists of the user having a folder in the MySQL database. Then, if it does, make sure that a folder exists at that location. If there is no folder, we create one for the user. If there is already a folder, we leave it alone.   This is for image uploads, and $thedirectory, is where we upload images later on in the script.   Hope that makes sense.   The code seems to work. But how can I improve it and make it more robust? Or should I just leave it alone? Should I return FALSE from the function for better reliability over empty()?  

Hi all.

I was wondering if there was a way (other than retrieving db fields with a query) to show something like "customer_id" as "Customer ID".  Is there a functionality of this nature in either MySQL or PHP?  I'm aware of preg_replace and such but that would still require two queries; one for the field names and one for the data (right?)

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, 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

This topic has been moved to MySQL Help.

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

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