PHP MySQL Query

[ChrisBish]

I have a query which when I run in phpmyadmin it returns the results I want.

 

When I put it into PHP I get no results can someone tell me what I'm doing wrong?

 

<?php include("config.php"); ?>
<?php
// sending query
$sql = mysql_query("SELECT dayname((date(FROM_UNIXTIME(dateline)))) as 'Day Of Week', date((date(FROM_UNIXTIME(dateline)))) as 'Date', count(*) as 'Number of Opened Tickets', ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.lastactivity)) = Date and isresolved=1 ) as 'Number of Closed Tickets' from swtickets where ((date(FROM_UNIXTIME(dateline)) between (DATE_SUB(CURDATE(), INTERVAL (IF(DAYOFWEEK(CURDATE())=1, 9, DAYOFWEEK(CURDATE()))) DAY)) and (DATE_ADD(CURDATE(), INTERVAL (6 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY)) )) group by date(FROM_UNIXTIME(dateline))");
?>

<?php echo $sql; ?>

 

All it returns is: Resource id #4

 

When in phpmyadmin I get:

 

 

 

[ManiacDan]

Why are you constantly opening and closing PHP tags like this? 

 

You're not using MySQL result sets properly.  The output you're getting is probably something like "Resource ID #15."  This is a very basic error that stems from you not reading ALL of the MySQL manual pages in the PHP manual.  Basic usage examples are here.

[ChrisBish]

Basically because I just need a quick webpage working and I'm not fused about it looking nice. Also I'm new to PHP.

 

I get Resource ID #4.

 

<?php echo mysql_free_result($sql); ?>

 

Just returns:  "1"

[The Little Guy]

mysql_query only returns a data set. you need to use mysql_fetch_array or mysql_fetch_assoc like this:

 

 

$sql = mysql_query("SELECT dayname((date(FROM_UNIXTIME(dateline)))) as 'Day Of Week', date((date(FROM_UNIXTIME(dateline)))) as 'Date', count(*) as 'Number of Opened Tickets', ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.lastactivity)) = Date and isresolved=1 ) as 'Number of Closed Tickets' from swtickets where ((date(FROM_UNIXTIME(dateline)) between (DATE_SUB(CURDATE(), INTERVAL (IF(DAYOFWEEK(CURDATE())=1, 9, DAYOFWEEK(CURDATE()))) DAY)) and (DATE_ADD(CURDATE(), INTERVAL (6 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY)) )) group by date(FROM_UNIXTIME(dateline))");

while($row = mysql_fetch_assoc($sql)){
echo "<p>{$row['Day Of Week']}</p>";
}

[ChrisBish]

Thanks The Little Guy.

 

 

One more question.

 

I have a Google Graph.

 

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Weekday');
        data.addColumn('number', 'Tickets Opened');
        data.addColumn('number', 'Tickets Closed');
        data.addRows(7);
        data.setValue(0, 0, 'Saturday');
        data.setValue(0, 1, 60);
        data.setValue(0, 2, 63);
        data.setValue(1, 0, 'Sunday');
        data.setValue(1, 1, 34);
        data.setValue(1, 2, 18);		
        data.setValue(2, 0, 'Monday');
        data.setValue(2, 1, 34);
        data.setValue(2, 2, 18);
        data.setValue(3, 0, 'Tuesday');
        data.setValue(3, 1, 24);
        data.setValue(3, 2, 12);
        data.setValue(4, 0, 'Wednesday');
        data.setValue(4, 1, 38);
        data.setValue(4, 2, 27);
        data.setValue(5, 0, 'Thursday');
        data.setValue(5, 1, 33);
        data.setValue(5, 2, 22);
        data.setValue(6, 0, 'Friday');
        data.setValue(6, 1, 12);
        data.setValue(6, 2, 6);

        var chart = new google.visualization.LineChart(document.getElementById('weekly_div'));
        chart.draw(data, {width: 1000, height: 200, colors: new Array("#236B8E","#78A489")});
      }
    </script>
<p id="weekly_div" style="margin-top:-30px;margin-bottom:-15px"></p>

 

Now How would I get it to correctly display the Day, Tickets Opened and Tickets Closed. :/

 

 

So far have:

 

$sql = mysql_query("SELECT dayname((date(FROM_UNIXTIME(dateline)))) as 'Day Of Week', date((date(FROM_UNIXTIME(dateline)))) as 'Date', count(*) as 'Number of Opened Tickets', ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.lastactivity)) = Date and isresolved=1 ) as 'Number of Closed Tickets' from swtickets where ((date(FROM_UNIXTIME(dateline)) between (DATE_SUB(CURDATE(), INTERVAL (IF(DAYOFWEEK(CURDATE())=1, 9, DAYOFWEEK(CURDATE()))) DAY)) and (DATE_ADD(CURDATE(), INTERVAL (6 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY)) )) group by date(FROM_UNIXTIME(dateline))");
while($row = mysql_fetch_assoc($sql)){
echo "<p>{$row['Day Of Week']}</p>";
echo "<p>{$row['Number of Opened Tickets']}</p>";
echo "<p>{$row['Number of Closed Tickets']}</p>";
}

[ManiacDan]

JavaScript and PHP are separate languages.

[mikesta707]

You can put PHP tags directly inside a script tag, and echo PHP variables (or string literals or whatever) into the script which will then be executed as javascript, since PHP is done server side, and the response is then run by the client.

 

So something like this perhaps

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Weekday');
        data.addColumn('number', 'Tickets Opened');
        data.addColumn('number', 'Tickets Closed');
        //data.addRows(7); base # of rows on SQL size, not a static number
	<?php
	$sql = mysql_query("your gigantic query");
	$count = mysql_num_rows($sql);//php function for getting # rows returned in a result resource

	echo "data.addRows($count);\n";//javascript. \n so its readable in source

	//now we loop to set the values
	//we have $count rows and 3 columns so 
	$i = 0;//start at 0th row
	while($row = mysql_fetch_array($sql)){
		//extract values to php variables
		$day = $row['Day Of Week'];//you should pick easier to type keys
		$openTickets = $row['Number of Opened Tickets'];
		$closedTickets = $row['Number of Closed Tickets'];

		//echo javascript
		echo "data.setValue($i, 0, $day);\n";
		echo "data.setValue($i, 1, $openTickets);\n";
		echo "data.setValue($i, 2, $closeTickets);\n";

		$i++;//increment the row

	?>

        var chart = new google.visualization.LineChart(document.getElementById('weekly_div'));
        chart.draw(data, {width: 1000, height: 200, colors: new Array("#236B8E","#78A489")});
      }
    </script>
<p id="weekly_div" style="margin-top:-30px;margin-bottom:-15px"></p>

 

note: this is untested.

[ChrisBish]

I get:

 

Parse error: syntax error, unexpected $end in C:\inetpub\wwwroot\Ticket\index.php on line 84

 

And line 84 is </html>

 

Other than that it looks like it should work.

[The Little Guy]

mikesta707 is missing a "}" at the end of the while loop, and it and you should be good.

[ChrisBish]

Works perfectly, many thanks guys.