Solved

Need to diplay query using PHP

Posted on 2009-07-08
6
243 Views
Last Modified: 2013-12-13
What I'm trying to do is display this query on a webpage using PHP. This works in sql but I am having troubles getting this to work like this.
<?php

mysql_connect("localhost", "user", "pass") or die("Count Not Connect: " . mysql_error());
 

mysql_select_db('database');
 
 

$data = mysql_query("SELECT 

                REASON,

    SUM(CASE ITEM_TYPE WHEN 'LCD 19' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LCD19,

    SUM(CASE ITEM_TYPE WHEN 'LCD 17' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LCD17,

    SUM(CASE ITEM_TYPE WHEN 'LCD Large' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LCDLarge,

    SUM(CASE ITEM_TYPE WHEN 'Small CRT' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS SmallCRT,

    SUM(CASE ITEM_TYPE WHEN 'Large CRT' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LargeCRT,

    SUM(CASE ITEM_TYPE WHEN 'Windows 2000' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Windows2000,

    SUM(CASE ITEM_TYPE WHEN 'Windows XP' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS WindowsXP,

    SUM(CASE ITEM_TYPE WHEN '4621SW' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Phone4621SW,

    SUM(CASE ITEM_TYPE WHEN '8410D' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Phone8410D,

    SUM(CASE ITEM_TYPE WHEN '8510B' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Phone8410B

FROM

    stock_inventory

GROUP BY REASON

ORDER BY REASON");
 

echo $data;
 

?>

Open in new window

0
Comment
Question by:arraysg2008
  • 3
6 Comments
 
LVL 31

Expert Comment

by:Frosty555
Comment Utility
$data is a PHP resource representing a recordset object. It does not actually contain any data.

You have to pass $data to a call to mysql_fetch_assoc(). mysql_fetch_assoc() will return an associative array containing one record from the query.

In order to retrieve all the records, you have to call the function over and over again until it returns false, displaying the items in the array however you see fit.

Here's an example:

http://www.phpf1.com/tutorial/php-mysql-tutorial.html?page=3
0
 

Author Comment

by:arraysg2008
Comment Utility
ok this is what I got so far, how do I put two cells in front?


$data = mysql_query("SELECT
                REASON,
    SUM(CASE ITEM_TYPE WHEN 'LCD 19' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LCD19,
    SUM(CASE ITEM_TYPE WHEN 'LCD 17' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LCD17,
    SUM(CASE ITEM_TYPE WHEN 'LCD Large' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LCDLarge,
    SUM(CASE ITEM_TYPE WHEN 'Small CRT' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS SmallCRT,
    SUM(CASE ITEM_TYPE WHEN 'Large CRT' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS LargeCRT,
    SUM(CASE ITEM_TYPE WHEN 'Windows 2000' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Windows2000,
    SUM(CASE ITEM_TYPE WHEN 'Windows XP' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS WindowsXP,
    SUM(CASE ITEM_TYPE WHEN '4621SW' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Phone4621SW,
    SUM(CASE ITEM_TYPE WHEN '8410D' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Phone8410D,
    SUM(CASE ITEM_TYPE WHEN '8510B' THEN IF(ACTION = 'Deploy', -QUANTITY, QUANTITY) ELSE 0 END) AS Phone8410B
FROM
    stock_inventory
GROUP BY REASON
ORDER BY REASON");
?>

<table border ="1" width="100">

<tr><th>LCD19<th>LCD17<th>LCDLarge<th>SmallCRT<th>LargeCRT<th>Windows2000<th>WindowsXP<th>Phone4621SW<th>Phone8410D<th>Phone8410B

<?php
while ($servdata = mysql_fetch_array($data, MYSQL_ASSOC))
{

$LCD19 = $servdata['LCD19'];
$LCD17 = $servdata['LCD17'];
$LCDLarge = $servdata['LCDLarge'];
$SmallCRT = $servdata['SmallCRT'];
$LargeCRT = $servdata['LargeCRT'];
$Windows2000 = $servdata['Windows2000'];
$WindowsXP = $servdata['WindowsXP'];
$Phone4621SW = $servdata['Phone4621SW'];
$Phone8410D = $servdata['Phone8410D'];
$Phone8410B = $servdata['Phone8410B'];
echo "<br />";

?>

<tr>


<td><?php echo $LCD19;  ?></td>
<td><?php echo $LCD17;  ?></td>
<td><?php echo $LCDLarge;  ?></td>
<td><?php echo $SmallCRT;  ?></td>
<td><?php echo $LargeCRT;  ?></td>
<td><?php echo $Windows2000;  ?></td>
<td><?php echo $WindowsXP;  ?></td>
<td><?php echo $Phone4621SW;  ?></td>
<td><?php echo $Phone8410D;  ?></td>
<td><?php echo $Phone8410B;  ?></td>

</tr>
<?php

}
?>

</table>
0
 
LVL 31

Expert Comment

by:Frosty555
Comment Utility
Looks good to me.

The rest of way it is displayed is just simple HTML. If you want to have cells to the left or right of other cells, rearrange their order in the HTML. If you want cells ontop of other cells, they have to be in their own row.

Easiest thing to do is to mock up a row in dreamweaver / your favorite HTML editing program. Then edit the html to put your PHP code in the right places.
0
 
LVL 31

Accepted Solution

by:
Frosty555 earned 125 total points
Comment Utility
The main problem was not understanding how to use the resource returned by mysql_query(). It was a resource that needed to be fed to various calls to mysql_fetch_assoc etc, and not something you can just echo out. That was answered in the first comment and the asker appeared to understand, from the followup comment they posted. As far as I can tell this question has been fully answered by the first 2 comments.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
Comment Utility
I agree. Full points to Frosty555
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now