Stuck with some queries

[alce]

I have been stuck for the past 2 days with some code and its driving me crazy.

I have a table with 6 fields and I need to display the results of certain queries on tables. I have done this in the past with no problems when the number of rows is variable but this time the number of colums needs to be variable too, futhermore, a simple

while ($rows=mysql_fetch_assoc($result))

will not do since I need to loop trough the records only to display certain fields.

It is really hard form me to explain what I need to accomplish without showing the contents of the database and I do not want to write an extremely long post (because maybe you will not want to even read it) but i will try to do my best.

If I perform a query, I can get, say 30 rows. The first field (code) is exactly the same for all of them. The second field (season) has only 2, 3 or 4 possibble values. The third field (category) has some 14 values that repeat themselves for each value in the season field. The fourth field (rate) is the only field that is different in every row.

What I want to do is to display a table with the values of the category field in the first column (obviously only once). Then use every distinct value of the season field as column headers and display their corresponding values from the rate field on the same column...

I tried just about everything, splitting the query into 3 different queries, hard coding the category field, using different resources from the same resultset and I can't figure it out.

I would appreciate if someone is willing to help me with this. I am sure that for someone with more experience with PHP/sql this is no problem at all.

[phparion]

your entire post is about problem in your query... well, where is your query? ......

post your queries and other related code here.

[alce]

Thnak you phpairon.

The query itself is very simple:

$rec4 = mysql_query ("SELECT cat, season, fare FROM pricing WHERE code='".$_GET['code']."'") ;

The resultset is not even that big either (around 35-50 rows depending on the valu of "code". What I am having problems with is displaying the information in a table like this:

Category low high
A 550 680
B 300 350
C 220 200

Depending on the value of "code", there could be 1, 2, 3 or 4 different season columns.

My table is something like this:

code season cat fare
3gr high A 680
3gr high B 350
3gr high C 200
3gr low A 550
3gr low B 300
3gr low C 220
4gr high z 900
4gr high w 850
4gr med z 600
4gr med w 550
4gr low z 315
4gr low w 210

As you can see from the table, if the variable "code" gets the value of 3gr, I would need to display a 4 row (including headers) and 3 column table. If the value of "code" is 4gr I need to display a 3 row 4 column table.

Originally, I had 3 tables and was using a join statment in the query but decided to include everything in just 1 table since its only 360 records long.

Thanks again.

[Steerpike]

I get the feeling a 'group by' query will solve your problem but I can't figure out the exact details of what it would look like.
*edit*
never mind, looks like I'm off base with a 'group by' suggestion. I'm not sure..sorry, maybe a 'disctict' query?

Steerpike

[tomda]

Still not very clear to me Alce.
Is your problem the query itself or how to display results of your query?

$rec4 = mysql_query ("SELECT cat, season, fare FROM pricing WHERE code='".$_GET['code']."'") ;

Not related to your problem, but using $_GET in your query is really not safe at all. Watch out for MySQL injection!

[alce]

Thanks guys.
Yes, my problem is how to display the results of my query.
When getting a result resource i usually do de following:

First, I hard code the column headers:

<table class="detalles">
<tr>
<td>Categor&iacute;a</td>
<td>Tarifa</td>
<td>Disponibilidad</td>
</tr>

Then I start the loop
<?php while ($row = mysql_fetch_assoc($resource3)) {?>
<tr>
<td><?php echo $row['category_cd']?></td>
<td><?php echo $row['fare_amt']?></td>
<td><?php echo $row['cabins']?></td>
</tr>
<?php }?>
</table>

This works fine for a simple table where I know how many columns I will be displaying. However, in this case, I need to display a table with variable columns and use some fields as column headers. I tried using 2 different queries, as steerpike suggested like this:

$cd=$_GET['code'] //Tomda, I hope this solves the problem. Tks.
$resource1 = mysql_query(SELECT DISTINCT season from pricing WHERE code=$cd)

This resource gives me the column headers that I can set dinamycally:

<table class="detalles">
<tr>
<th>Categor&iacute;a</th>
<?php while ($row = mysql_fetch_assoc($resource1)) {?>
<tr>
<th><?php echo $row['season']?></th>
</tr>
<?php }?>
</table>

The second query looks like this:

$resource2 = mysql_query(SELECT cat, fare from pricing WHERE code=$cd)

The values from 'cat' should go under the column 'categories' but displayed only once (since they repeat themselves for every different season) and the values of the field 'fare' should go under their corresponding season, like this:

Category High Low
A value1 value2
B value3 value4
Cvalue5 value6

So basically, I dont know what to do with the resoult resource.

Thanks again.

[alce]

I checked the SQL injection problem and there sould not be any.

The value of 'code' does not come from user imput. It is hardcoded and sent in a URL from another page:

<a href=" page.php?code=10gr">

Eventhough I am still stuck with the problem of displaying the data, I learnt something extremely useful. Not only about SQL injection but security in general. Thank you tomda.

[alce]

Ok, now this thread has completely changed topics but I think this ones is even more important.

So it turns out that even if the value of the variable is hard coded, well, a user can still send malicious code to my query simply by typing it in the URL himself. How to protect the database from that?

Any thoughts?

[tomda]

Yeah, anyone can type in the URL.

// CREATE A FUNCTION
// ALLOW A-Z, a-z, 0-9 and _
// **************************
function check_field1($var){if(!preg_match("/[^A-Za-z0-9_]/",$var)) {return TRUE;} else {return FALSE;}}

// IF CODE EXIST -> CODE=GET[CODE]
if(isset($_GET["code"])) {$code=$_GET["code"];}
// OTHERWISE GIVE DEFAULT VALUE TO CODE
else {$code="10gr";}
// CHECK IF CODE IS A-Z, a-z, 0-9 and _ only
// IF NOT GIVE DEFAULT VALUE
if(!check_field1($code)) {$code="10gr";}

For your display problem, I have tried to understand your last explanation but failed to do so, sorry...

[alce]

Thank you Tomda.

I was using addslashes() but your function is far more powerfull.

As for the display problem, I am going to start all over again from scratch. We wil see if I can get it right this time.

As you can see, my experience with PHP and SQL is rather limited plus I am not a native english speaking guy so it can get really hard for me to explain what I want to achieve.

I deeply appreciate you efforts tohough and thanks again for your suggestions.