Jump to content

Coundn't get this query to output data in php, Please help !


vanvoquan

Recommended Posts

Hi every one, I have play with this code for a whole day and still can not get it working. Any helps will be appreciated and will give donation. Thanks in advance

<?Php
	$connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa');
	if ($connection->connect_errno > 0) {
		die ('Unable to connect to database [' . $connection->connect_error . ']');
	}	
	$query1 = "SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()";
$result1 = mysql_query($query1);
if (!$result1) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query1;
    die($message);
}
while ($row1 = mysql_fetch_assoc($result1)) {
    echo $row1['totals'];
}	
mysql_free_result($result);
?>

And below is the code of the whole php file

<HTML>
<HEAD>
<TITLE></TITLE>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="onpage_submit.js"></script>
<META name="description" content="">
<META name="keywords" content="">
<META name="generator" content="CuteHTML">

</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080">
<table width="800" align="center"><tr><td align="center">hahahha</td></tr><tr>
<?Php
	$connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa');
	
	if ($connection->connect_errno > 0) {
		die ('Unable to connect to database [' . $connection->connect_error . ']');
	}	
	$sql = "SELECT t.id, t.firstname, SUM(s.sale) as sales, SUM(s.tip) as tips, SUM(s.sale + s.tip) as totals FROM technictians t LEFT JOIN everyday_sale s ON s.technictian_id = t.id where DATE(s.reg_date) = CURDATE() GROUP BY t.id";
	if (!$result = $connection->query($sql)) {
    	die ('There was an error running query[' . $connection->error . ']');
	}	
?>

<?php
	$rows = $result->num_rows;    // Find total rows returned by database
	if($rows > 0) {
		$cols = 3;    // Define number of columns
		$counter = 1;     // Counter used to identify if we need to start or end a row
		$nbsp = $cols - ($rows % $cols);    // Calculate the number of blank columns
                echo '<table border="1" bgcolor="#E0F2F7" bordercolor="blue" width ="800" height ="700" align="center">';
		while ($row = $result->fetch_array()) {
			if(($counter % $cols) == 1) {    // Check if it's new row
				echo '<tr>';	
			}
     
			echo '<td><table align="center"><tr><td align="center"><font size="5" color="red"><b>'.$row['firstname'].'</b></font></td></tr><tr><td><hr></td></tr>
<tr><td align="center"><font sie="5" color="maroon"><b>Earning Totals:</b></font></td></tr>

<tr><td align="center"><font size="4" color="green"><b>Sales: $ '.$row['sales'].'</b></font></td></tr>
<tr><td align="center"><font size="4" color="green"><b>Tips: $ '.$row['tips'].'</b></font><hr></td></tr>
<tr><td align="center"><font size="4" color="red"><b>Total: $ '.$row['totals'].'</b></font></td></tr>
<tr><td><hr></td></tr>
</table></td>';
			if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero
				echo '</tr>';	
			}
			$counter++;    // Increase the counter
		}
		$result->free();
		if($nbsp > 0) { // Add unused column in last row
			for ($i = 0; $i < $nbsp; $i++)	{ 
				echo '<td>
<?Php
	$connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa');
	if ($connection->connect_errno > 0) {
		die ('Unable to connect to database [' . $connection->connect_error . ']');
	}	
	$query1 = "SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()";
$result1 = mysql_query($query1);
if (!$result1) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query1;
    die($message);
}
while ($row1 = mysql_fetch_assoc($result1)) {
    echo $row1['totals'];
}	
mysql_free_result($result);
?>
</td>';		
			}
			echo '</tr>';
		}
                echo '</table>';
	}
?>
</td></tr></table>
</BODY>
</HTML>
Link to comment
Share on other sites

Thanks everyone for the inputs, try to replace mysql_query with mysqli_query and still not working. I can not get the output data from the database. I also got this message:

Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\AppServ\www\usnailsandspa\today_earnings.php on line 68
Invalid query: Whole query: SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()
Link to comment
Share on other sites

 

$sql = "SELECT t.id, t.firstname, SUM(s.sale) as sales, SUM(s.tip) as tips, SUM(s.sale + s.tip) as totals FROM technictians t LEFT JOIN everyday_sale s ON s.technictian_id = t.id where DATE(s.reg_date) = CURDATE() GROUP BY t.id";

When a table is LEFT JOINed (as everyday_sale is) you cannot apply a WHERE condition to records in that table. The condition should go in the join's ON clause.

SELECT t.id
     , t.firstname
     , SUM(s.sale) as sales
     , SUM(s.tip) as tips
     , SUM(s.sale + s.tip) as totals 
FROM technictians t 
    LEFT JOIN everyday_sale s 
        ON s.technictian_id = t.id 
           AND DATE(s.reg_date) = CURDATE() 
GROUP BY t.id
Link to comment
Share on other sites

Thanks Barand, the one you wrote above is what I have and i works perfectly. But i'm talking about another query because i also need SUM(sale+tip) for today of all the technictians. The one you wrote only give me the sum(sale+tip) for today only for one technictian, not all of them so that why I created another query but it's not working.

	$query1 = "SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()";
$result1 = mysqli_query($query1);

And got this message

Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\AppServ\www\usnailsandspa\today_earnings.php on line 67
Invalid query: Whole query: SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE() 

And below is the whole code:

<?Php
	$connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa');
	if ($connection->connect_errno > 0) {
		die ('Unable to connect to database [' . $connection->connect_error . ']');
	}	
	$query1 = "SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()";
$result1 = mysqli_query($query1);
if (!$result1) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query1;
    die($message);
}
while ($row1 = mysql_fetch_assoc($result1)) {
    echo $row1['totals'];
}	
mysql_free_result($result);
?>
Link to comment
Share on other sites

Thanks Barand, the one you wrote above is what I have and i works perfectly..... The one you wrote only give me the sum(sale+tip) for today only for one technictian, not all of them

 

The one I wrote is obviously not working perfectly because you have added that erroneous WHERE clause. Do as I said and put the condition in the ON clause and it should give all technicians, even those with zero sales. Then you won't even need that second query.

 

(Your WHERE clause is making it behave like an ordinary JOIN instead of a LEFT JOIN, so matching technician/sales only.)

 

You called that first query correctly with $connection->query(). So why the hell is it a problem for you with that second query? Do it the same way.

Link to comment
Share on other sites

Points to examine:

 

$query1 = "SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()";
Where is 's' defined? And why do you need to apply the DATE function to that, as yet, undefined value? Isn't it stored in the table as a date already? And if not, why isn't it?

 

$result1 = mysql_query($query1);
If you would only do as has been suggested and RTFM you would find the proper syntax for the MysqlI_query call is:

mixed mysqli_query ( mysqli $link , string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )
And next:

}
while ($row1 = mysql_fetch_assoc($result1)) {
    echo $row1['totals'];
}	
mysql_free_result($result);
You begin your script properly by using the mysqlI db interface. So why do you switch off to the OLD AND DEPRECATED MySQL* ones? All of your calls to MySQL* functions need to be researched and changed to use the appropriate mysqlI* functions. And as said before you can't just simply add an I all the time - RTFM

 

 

Lastly - are you actually learning how to write your own code? Or - are you just simply copying someone's old html code because you haven't bothered to learn anything newer than probably 2008 (I'm guessing here)? Your use of style attributes on your table tag along with most of your other tags is just short-sighted and lazy. You need to learn about CSS and at least make an attempt at using the abilities of it even if you don't learn how it cascades.

 

Copying other's code when you don't understand code in general is a rocky approach to actually learning how to program.

Link to comment
Share on other sites

 And why do you need to apply the DATE function to that, as yet, undefined value? Isn't it stored in the table as a date already? And if not, why isn't it?

 

 

 

If reg_date is defined as DATETIME or TIMESTAMP (perfectly valid) then the DATE() function is required when matching against CURDATE(), otherwise the time element of the value will interfere with the equality test.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.