php categories and Items php query

[Nasser_Kamal]

i have two tables

1.moths (month_id, month_order, month_name)
1.prices (price_id, month_id, price_order, price_name, price_amount)

i want query to show at the page like this

month_name (order by month_order)

• price_name | price_amount (order By price_order)

• price_name | price_amount

• price_name | price_amount

month_name

• price_name | price_amount (order By price_order)

• price_name | price_amount

• price_name | price_amount

so i any one can write me the query and the while loop that would be great
thanks

[bradgrafelman]

Nasser Kamal;10996208 wrote:

so i any one can write me the query and the while loop that would be great

Just to make sure I understand... you're not asking for help, rather you're asking someone here to do the work for you and spoonfeed you the code (e.g. free labor)?

[Nasser_Kamal]

hi bradgrafelman yes i'm looking for some one who can help me to write the code for me as i tried to write it many times but it never works ,, i just didn't want to put my code here as i'm a beginner and i didn't want to confuse other .. any way here is my code ,, it works correctly but it don't sort correctly

<?
 $sqlMonth = "SELECT DISTINCT month_id 
 			  FROM prices WHERE 1 ";
 $resMonth = mysql_query($sqlMonth) or die("error in getting month list".mysql_error());
?>
<?
			 while($recMonth = mysql_fetch_array($resMonth))
			 {
			   $sqlMonthInfo = "SELECT * FROM months WHERE month_id = '".$recMonth['month_id']."'";
			   $recMonthInfo = mysql_query($sqlMonthInfo) or die("error in getting month info".mysql_error());
			   $resMonthInfo = mysql_fetch_array($recMonthInfo);
			?>
<?
				 $sqlBookingList = "SELECT *  FROM prices 
				 					WHERE 
									month_id = '".$recMonth['month_id']."'";
 				$resBookingList = mysql_query($sqlBookingList) or die("error in getting booking list".mysql_error());
				$cnt=1;
				while($recBookingList = mysql_fetch_array($resBookingList))
				{
				?>

[bradgrafelman]

Well your first problem would be the confusing manner in which you're trying to retrieve data from the DB caused by the lack of JOINs. Most instances of a SQL query being executed inside of a loop that's processing another SQL query should have instead been written with less SQL queries and using JOINs instead.

For example, I would expect a SQL query that JOINs your two tables to look something like:

SELECT
    month_name, price_name, price_amount
FROM
    months m
        LEFT JOIN prices p
            ON p.month_id = m.month_id
ORDER BY month_order, price_order

As for the display format, you'd want to use a temporary variable to store the previous row's month_name value; if the current row's month_name value does not match the previous' value, then you'll know that you're processing rows from the next month and that you'll need to output the new "month_name" header/separator/etc.

[Nasser_Kamal]

hi ,, bradgrafelman

thanks for your help ,, i have been trying to correct my code using your instructions, i got the first month displayed and the first tour price

February 2012

09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |

09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |
09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |

now i need to get the other months and tours prices for each month to display like example

Februar 2012

09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |

March 2012

09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |

Februar 2012

09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |

Februar 2012

09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |

[Nasser_Kamal]

sorry i posted the previous post by mistake of pressing Enter so i'll post it again

[Nasser_Kamal]

hi ,, bradgrafelman

thanks for your help ,, i have been trying to correct my code using your instructions, i got the first month displayed and the first tour price

Februar 2012
09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers | |

now i need to get the other months and tours prices for each month to display like example
February 2012
09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |
16.02.12 | 23.02.12 | My Mermaid | HRG | Brothers |
23.02.12 | 01.03.12 | My Mermaid | HRG | Brothers |

March 2012
09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |
09.02.12 | 16.02.12 | My Mermaid | HRG | Brothers |
16.02.12 | 23.02.12 | My Mermaid | HRG | Brothers |
23.02.12 | 01.03.12 | My Mermaid | HRG | Brothers |
etc..

here is my php i got so far
$query_recBookingList = "SELECT
month_name, month_name_en, date_from, date_to, boat, port_du, port_en, routing_du, routing_en, infos, infos_en, pax, booked, price_option, prices_amount, booknow_du,booknow_en, tour_id
FROM
months m
LEFT JOIN prices p
ON p.month_id = m.month_id
ORDER BY month_order, price_order";
$recBookingList = mysql_query($query_recBookingList, $mermaid) or die(mysql_error());
$row_recBookingList = mysql_fetch_assoc($recBookingList);
$totalRows_recBookingList = mysql_num_rows($recBookingList);

mysql_free_result($recBookingList);
?>

and here is the code i use to display the data

<body>
<? while($recBookingList = mysql_fetch_array($resBookingList))
{
?>
<ul>
<li><?php echo $row_recBookingList['month_name']; ?>
<ul>
<li><?php echo $row_recBookingList['date_from']; ?> | <?php echo $row_recBookingList['date_to']; ?> | <?php echo $row_recBookingList['boat']; ?> | <?php echo $row_recBookingList['port_du']; ?> | <?php echo $row_recBookingList['routing_du']; ?> | <?php echo $row_recBookingList['infos']; ?></li>

</ul>
</li>

</ul>
<?

			 }
			?>

</body>