[SOLVED] Really Basic PHP MYSQL Display question

[Aj-Wy]

I have not really used PHP at all, but I need to use a bit of script in one section of a site I am working on and I just can't get it to work, I woz wondering if anyone here could post some code I could use.

I have a MYSQL database with two fields, and I have got the page set up to output the entire list of both columns, but some of the entries have the same first column but a different second column, this probably doesnt sound very clear, so I'll show you, atm it displays like this:

 

Column 1:    Column 2:

Item 1          A

Item 1          B

Item 2          C

Item 3          D

 

But I want it not to duplocate the Item 1 and instead output so that A and B are listed beside the Item 1 heading just once, i.e:

 

Column 1:    Column 2:

Item 1          A

                    B

Item 2          C

Item 3          D

 

I know it probably not that hard but I'm really struggling, if anyone could help that would be great, thanks.

If this doesnt make sense please post nd tell me and I'll try and explain a bit better.

Cheers

[xyn]

I know what you mean, err I would probably suggest doing this...

<?php
function  displayItemOnce($Item) {
//include mysql connection
$sql = mysql_query("SELECT * 
FROM `table` 
WHERE `column_1`='$Item'");

while($data = mysql_fetch_array($sql))
{
   $items.=$data['column_2'];
}

return $items;
}

echo "Item 1: " . displayItemOnce("item 1");
echo "Item 2: " . displayItemOnce("item 2");
?>

[mpharo]

$select = mysql_query("SELECT * FROM table") or die(mysql_error());

while($sql=mysql_fetch_array($select)) {

If ($result==$sql[column1]){

echo "";
echo "$sql[column2]";

}else{

echo "$sql[column1]";
echo "$sql[column2]";

}
$result=$sql[column1];
}

 

Untested so im not sure if this will work or not.

[xyn]

$select = mysql_query("SELECT * FROM table") or die(mysql_error());

while($sql=mysql_fetch_array($select)) {

If ($result==$sql[column1]){

echo "";
echo "$sql[column2]";

}else{

echo "$sql[column1]";
echo "$sql[column2]";

}
$result=$sql[column1];
}

 

Untested so im not sure if this will work or not.

 

Though if it works, i would remove the or die(mysql_error()); bit

[Aj-Wy]

Err, I'm not sure I fitted it all around my current code correctly as it came up with a fatal error... I will show u my current code:

 

$i=0;
while ($i < $to) {

$id=mysql_result($result,$i,"id");
$offspring=mysql_result($result,$i,"offspring");
$country=mysql_result($result,$i,"country");
$race=mysql_result($result,$i,"race");
$prize=mysql_result($result,$i,"prize");

?> <tr><td><font size="-1"><b><?echo "$offspring";?></b></td>
<td><font size="-1"><?echo "$country";?></td>
<td><font size="-1"><?echo "$race";?></td>
<td width="100"><font size="-1"><?echo "$prize";?></td></tr> <?

$i++;
}


mysql_close();



php?>

 

Now I dont really know what much of that means as it is pieced together from various code banks etc. but what I need is for if the 'offspring' is the same in different entries, I need it to group them, displayig that offspring name only once, with the different races/prizes.

Agen if that makes no sense, I'm really sorry, but if u know how I could fit your ideas in around that it would be great

Thanks

[sasa]

change line

<?echo "$offspring";?>

to

<? if($offspring != $tmp) {$tmp = $offspring;echo "$offspring";} else echo ' ';?>

you must order your query by offspring

[Aj-Wy]

Oh brilliant thats perfect thanks!!!!!!!!!!

cheers, thanks very much. Problem solved

[Aj-Wy]

Hello

I am continuing to fill out this webpage with dynamic sections, and have encountered another problem that is probably equally as simple, but I still can't get round and I was wondering if anyone here could help,

I am trying to display the entries from the MYSQL database only that have their 'type' field set to 'Y', now I was able to achieve this with this messy code:

<tr><td><font size="-1"><b>
<? 
if ($type=="Y"){
echo "$horse";
}else{
echo ' ';
}
?>

</b></td><td><font size="-1"><? 
if ($type=="Y"){
echo "$pedigree";
}else{
echo '&nbsp';
}
?></td><td><font size="-1"><? 
if ($type=="Y"){
echo "$status";
}else{
echo '&nbsp';
}
?></td><td><font size="-1"><? 
if ($type=="Y"){
echo "$breeding history";
}else{
echo '&nbsp';
}
?></td><td><font size="-1"><? 
if ($type=="Y"){
echo "$Pedigree outline";
}else{
echo '&nbsp';
}
?></td></tr>

 

Now i know that looks pretty awful, and complicated, and although it works, its not very good, as when it finds an entry that doesnt fit the criteria, it does leave it out, but it also leaves a blank row where it should of been (as well as the fact that I have had to include the If function for every single one of the fields from an entry i want to display), is there a quicker way to do this code, where after testing the entry initially, it will either include all the fields I want from that entry, or move straight on to check the next entry without automatically creating a row?

If this doesnt make sense please ask me about it.

thanks all

[sasa]

in your sql use WHERE ... AND type ='Y' etc.

 

... mens what you have now in where part

etc means rest of your sql

[Aj-Wy]

So I should use:

 

<tr><td><font size="-1"><b>
<? 
where ($type=="Y"){
echo "$horse";
}
?>

</b></td>[/code
?
If that is completely not what you mean I apologise, I'm a little bit new when it comes to all this coding 

[sasa]

you pull data from database

 

you use SQL to pull out dat

 

SQL look something like 'SELECT something FROM table_name WHERE something etc.

 

i suggest that you add and type='Y' in your SQL

in this case you pull just data that have in field type value 'Y'

I think that you don't need to echoed empty rows 

[mpharo]

It will be a lot easier in your code if you just put all the data into an array instead of specifying each variable is a result. It will also speed up the page or script when it runs. It is also less code to use, try using something like this.

 

<?php

while($sql=mysql_fetch_array($select)) {
echo "ID: $sql[id] Offspring: $sql[offspring] "
. "Country: $sql[country] Race: $sql[race] Prize: $sql[prize]";

}
?>

[Aj-Wy]

Erm, sorry to continue to be so dense,

but I tried to put in what you suggested:

 

mysql_connect("mysql.hosts.co.uk",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM sales WHERE type='Y' ORDER BY horse ASC";
$result=mysql_query($query);

 

and that didnt really worked, as it didnt find any results (when there are definitely some Y value entries in there, have you any idea why that might be?

thanks for all your help

[sasa]

change

$result=mysql_query($query);

to

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

[Aj-Wy]

Excellent! thanks