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


#1

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. Thanks in advance

<?Php
	$connection = new mysqli('localhost', 'root', 'username', 'password');
	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', 'username', 'password');
	
	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', 'username', 'password');
	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>

#2

Why do you open a connection with mysqli

$connection = new mysqli('localhost', 'root', 'username', 'password');

and then use the no-longer-supported mysql functions to access it ?

$result1 = mysql_query($query1);
...
while ($row1 = mysql_fetch_assoc($result1)) {

If changing to mysqli doesn't help, can you expand on exactly what is going wrong, what it's supposed to be doing that is it not?


closed #3

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.