PHP MYSQL Query

[egmweb]

Hi Guys,

I wonder if you may help me querying my database for specific values that I need as follows:

db: db1
table: table1

I have the following fields in table1:
id, item_name, item_descrip, item_number, amount, active

my values for those fields are:
1, item1, something1, 1, 10, yes
2, item2, something2, 2, 20, yes
3, item3, something3, 3, 30, yes
4, item4, something4, 4, 40, yes
5, item5, something5, 5, 50, yes
6, item6, something6, 6, 60, yes

here is my php script:

$sql = mysql_query("SELECT * from table1 WHERE
active='yes' ORDER BY amount ASC Limit 2");

while($row = mysql_fetch_array($sql)) {
extract($row);

echo "<table width='100%' border='0' cellspacing='1' cellpadding='5' bgcolor='#CECFCE' >
<tr>
<td height='35' colspan='2' align='left' valign='middle'><span class='cat'><font class=text88><b>$item_name</b></font></span></td>
</tr>
<tr>
<td height='105' colspan='2' valign='top' bgcolor='#ffffff'>$item_descrip<br>";
if ($id==1){
echo "<img src='pic1.jpg' width='500' height='54' /></td></tr>";
}
else if ($id==2){
echo "<img src='pic2.jpg' width='500' height='76' /></td></tr>";
}
echo "<tr>
<td width='20%' height='25' align='left' valign='middle' bgcolor='#EDEDED'>item1:</td>
<td width='80%' align='left' valign='middle' bgcolor='#ffffff'>&nbsp;<span class='cat'><b>$amount</b></span></td>
</tr>
<tr>
<td height='25' align='left' valign='middle' bgcolor='#EDEDED'>item2:</td>
<td align='left' valign='middle' bgcolor='#ffffff'>&nbsp;<span class='cat'><b>$amount</b></span></td>
</tr>
<tr>
<td height='25' align='left' valign='middle' bgcolor='#EDEDED'>item3:</td>
<td align='left' valign='middle' bgcolor='#ffffff'>&nbsp;<span class='cat'><b>$amount</b></span></td>
</tr>
</table>
<br>";

I need just for the id==1 pic1.jpg and for id==2 pic2.jpg...

But, I wonder how can I get the amount values for ids: 1,2,3,4,5,6 item just for those id==1 and id==2.

I mean, let's say that I have 2 main options, which are id1 and id2: pro and lite.
and I'm having 6 items that can be either lite or pro.

So I'm querying the id1 and the id2, but I need the amount values for id1, id2, id3, id4, id5, id6.

Would you please help me with this?

Thanks in advance.

 

[southbeach]

You are not querying for id1 and id2, your are querying for all active rows. As far as I can see, you can just the same process the values field as you within the same condition.

Further, I am not clear about what exactly you are trying to accomplish or asking. Perhaps, if you rephrase your question one can provide you with suggestions.

Also, why limit your query? Are you employing a Next/Prev navigation ???

Try to re-work your question ... I humbly believe it is not clear.

 

[egmweb]

Hi southbeach,

Ok, I need to get the first 2 ids (id1 and id2) because they have the main information and description to construct a html table with the values for the amount fields for ids 1,2,3,4,5,6. does it make sense?

Thanks in advance.

 

[southbeach]

Does it matter if the amount is from id3, id4, id5 or id6? Are you simply looking for the SUM() of the value for those fields?

What about the values for id1 and id2?

I have used queries like this:

SELECT * ,
CASE WHEN id = 1 THEN SUM(amount) END AS amount1,
CASE WHEN id = 2 THEN SUM(amount) END AS amount2,
CASE WHEN id = 3 THEN SUM(amount) END AS amount3,
CASE WHEN id = 4 THEN SUM(amount) END AS amount4,
CASE WHEN id = 5 THEN SUM(amount) END AS amount5,
CASE WHEN id = 6 THEN SUM(amount) END AS amount6
FROM table1 WHERE active = "yes" GROUP BY id

Using above query, you should be able to reference $amount1 through $amount6 as the accumulated total for each ID.

I was, however, suggested to use something like:

$query = "
SELECT id, SUM(amount)
FROM table1
GROUP BY id";

$result = mysql_query($query) or die( mysql_error());

$data = array();
while ($rows = mysql_fetch_row($result)){
$id = $rows[0];
$amount = $rows[1];

$data[$id] = $amount;
}

Using the latter, you will then refer to the amount by pointing to the array association to the ID. In other words, $data[1] holds the total for ID1, $data[2] holds the total for ID2 ...

Hope this helps!

 

[egmweb]

Wow, thanks very much, you did it...

best regards.

Eduard.

 

[southbeach]

Glad to be of help!