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

vanvoquan · February 11, 2018

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>

requinix · February 11, 2018

:psychic:

Get what working? What's broken?

CyberShot · February 11, 2018

should this line $result1 = mysql_query($query1);

be this $result1 = mysqli_query($query1);

?

vanvoquan · February 11, 2018

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()

Edited February 11, 2018 by vanvoquan

requinix · February 11, 2018

You can't just stick an 'i' in there and assume it will work.

Look at the documentation. Use Google. Understand what you're messing with before you mess with it any more.

Barand · February 11, 2018

$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

vanvoquan · February 11, 2018

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);
?>

AzeS · February 11, 2018

the issue is that you're not adding the mysqli connection. Change the result1 to:

$result1 = mysqli_query($connection, $query1);

Edited February 11, 2018 by AzeS

Barand · February 11, 2018

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.

ginerjm · February 11, 2018

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.

Edited February 11, 2018 by ginerjm

Barand · February 11, 2018

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.

ginerjm · February 11, 2018

Barand - good point (of course). I was just afraid that the OP had made it something outlandish rather than just a different form of a date value.

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information