Solved

Need to diplay query using PHP

Posted on 2009-07-08
6
250 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
ID: 24809924
$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
ID: 24810017
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
ID: 24818366
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
ID: 25007614
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
ID: 25009040
I agree. Full points to Frosty555
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

832 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