[SOLVED] DISTINCT Field vs Indistinct Fields

[EternalSorrow]

Currently I am trying to organize my designs by series where when the series letter is clicked, the visitor will view all the series under that series letter category with the newest design image and date updated also shown.

 

The problem I'm having is that I wish to have indistinct fields, such as the image and date, along with the DISTINCT series field.

 

Here's the live preview of my difficulty (the database is choosing a random image with it's respective date, and I am unable to figure out how it is choosing that specific image).

 

Here's the code for the PHP. I'd be grateful for any point in the right direction.

 

<?php

if (!is_numeric($_GET["series_letter"]) && !empty($_GET["series_letter"]) && $_GET["series_letter"]!="")
{
$series_letter = $_GET["series_letter"];
}

$query="SELECT DISTINCT series FROM `design` WHERE `series_letter` = '$series_letter' ORDER BY series asc ";
$result = mysql_query( $query ) or die(mysql_error());

$i = 1;

echo '<table align="center" width="80%">';

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

$select_series_count = mysql_query("SELECT * FROM `design` where `series`='$series' ORDER BY `series` ASC") or die (mysql_error());
$count_amount = mysql_num_rows($select_series_count);

$contents_here = '<table align="center" width="100%" style="text-align: center;">
<tr><td><a href="info.php?series='.$series.'"><img src="previews/'.$image.'" title="design" alt="design">
<br><b>'.$series.'</b></A>
<br>Designs: '.$count_amount.' ~ Updated: '.$date.'
</table>';

if ($i==1)
    {
    echo '<tr><td><div class="even">'.$contents_here.'</div>';
    }
   
    else if ($i==0)
    {
    echo '<td><div class="odd">'.$contents_here.'</div>';
    }

    $i++; $i=$i%2;

}
echo '</table>';

        ?>

[bobbinsbro]

hi again.

 

where do you set the value of $image and $date...? if they're supposed to get their values from the DB, that's your problem.

[EternalSorrow]

where do you set the value of $image and $date...? if they're supposed to get their values from the DB, that's your problem.

 

That would be the difficulty I'm having.  If I set the values of the $image and $date in this part of the code:

 

$query="SELECT DISTINCT series FROM `design` WHERE `series_letter` = '$series_letter' ORDER BY series asc ";

 

then all designs for the series would load because they each have a unique image.  If I remove the DISTINCT command then all designs for the series will again show.

 

So my difficulty is if I access the db with the DISTINCT command with the $image and $date fields, all designs for the series will show.  If I do not put the DISTINCT command with the $image and $date fields, all designs for the series will show. 

 

Is there any way to place the series as a DISTINCT field without the $image and $date fields being distinct?

[Andy-H]

You should be using a loop instead of an if statement I think.

[bobbinsbro]

@andy: there's a while loop around the if. i think the if is just for layout purposes.

 

@EternalSorrow:

i'm trying to understand the table structure in the DB. am i right in assuming that the reason you use distinct for getting the series names is because you have many images, each set on a different date, for each series? the table looks something like:

 

unique_ID - SERIAL, primary key

series - probably a varchar, not unique

image - a filepath? unique

date - the date in which the the image was added

 

and a section of the table would look like:

 

unique_ID            series                  image                    date

    12                    a                    file1.jpeg            01/01/2001

    13                    a                    file2.jpeg            02/02/2002

    14                    a                    file3.jpeg            03/03/2003

 

if the answer is 'yes', then you would only want 1 image for each series and the corresponding date (since it looks to me like you're only displaying 1 image per series). what i would do is combine both sql strings into 1:

 

$sql = "SELECT * FROM design where series_letter='".$series_letter"' ORDER BY series ASC";

 

then i would loop through the results, only using 1 entry per series by doing something like this:

 

<?php
$results = //multidimentional array: first level - numeric, holding the row number. second level - assoc, holding the column names
$lastSeries = //holds the name of the last series treated
$loopCount = //amount of rows in $result

for ($j = 0; $j < $loopCount; ++$j){

if ($lastSeries == $result[$i]['series']){
continue; //don't treat the same series twice
}
else{

$lastSeries = $result[$i]['series']; //so we know we treated this series already
$image = $result[$i]['image']; //get first image for this series that happens to be in the table
$date = $result[$i][date]; //get date of the image


//your layout code untouched
$contents_here = '<table align="center" width="100%" style="text-align: center;">
<tr><td><a href="info.php?series='.$series.'"><img src="previews/'.$image.'" title="design" alt="design">
<br><b>'.$series.'</b></A>
<br>Designs: '.$count_amount.' ~ Updated: '.$date.'
</table>';

if ($i==1)
    {
    echo '<tr><td><div class="even">'.$contents_here.'</div>';
    }
   
    else if ($i==0)
    {
    echo '<td><div class="odd">'.$contents_here.'</div>';
    }

    $i++; $i=$i%2;
}
?>

[Andy-H]

Ahh didnt see the loop, sorry.

[bobbinsbro]

a better idea than my previous post:

add a display_image boolean column to your table, set the rows with images you want displayed on this page to 1 and default the rest to 0, then select * from design where series_letter = $resier_letter AND display_image = 1, and loop through those.

[EternalSorrow]

@bobbinsbro: your assumption about my database structure is correct.

 

And the better idea would be simpler for the code, but if I added another design to the same series I would have to change the display_image field for the older design, so I'm willing to try the first, and more complicated PHP.

 

I inputted the information as it was written, but I appear to have left out some detail because my page receives an error message:

 

Parse error: syntax error, unexpected T_FOR in /home/animara/public_html/new/design/series.php

 

and the error is found on this line:

 

for ($j = 0; $j < $loopCount; ++$j){

 

Here is the current, changed code in its entirety:

 

<?php

if (!is_numeric($_GET["series_letter"]) && !empty($_GET["series_letter"]) && $_GET["series_letter"]!="")
{
$series_letter = $_GET["series_letter"];
}

$sql = "SELECT * FROM design where series_letter='.$series_letter' ORDER BY series ASC";

$results = //multidimentional array: first level - numeric, holding the row number. second level - assoc, holding the column names
$lastSeries = //holds the name of the last series treated
$loopCount = //amount of rows in $result

for ($j = 0; $j < $loopCount; ++$j){

if ($lastSeries == $result[$i]['series']){
continue; //don't treat the same series twice
}
else{

$lastSeries = $result[$i]['series']; //so we know we treated this series already
$image = $result[$i]['image']; //get first image for this series that happens to be in the table
$date = $result[$i][date]; //get date of the image


//your layout code untouched
$contents_here = '<table align="center" width="100%" style="text-align: center;">
<tr><td><a href="info.php?series='.$series.'"><img src="previews/'.$image.'" title="design" alt="design">
<br><b>'.$series.'</b></A>
<br>Designs: '.$count_amount.' ~ Updated: '.$date.'
</table>';

if ($i==1)
    {
    echo '<tr><td><div class="even">'.$contents_here.'</div>';
    }
   
    else if ($i==0)
    {
    echo '<td><div class="odd">'.$contents_here.'</div>';
    }

    $i++; $i=$i%2;
}

echo '</table>';

        ?>

[bobbinsbro]

lol. sorry. i didn't give you working code. only a base to build on. i think a working version should be something like this:

<?php

if (!is_numeric($_GET["series_letter"]) && !empty($_GET["series_letter"]) && $_GET["series_letter"]!="")
{
$series_letter = $_GET["series_letter"];
}

$sql = "SELECT * FROM design where series_letter='.$series_letter' ORDER BY series ASC";
$queryResult = mysql_query( $query ) or die(mysql_error());

$loopCount = mysql_num_rows($queryResult); //amount of rows in $result
$lastSeries = ""; //holds the name of the last series treated. initialize to empty.

for ($j = 0; $j < $loopCount; ++$j){

$result = mysql_fetch_array($queryResult);

if ($lastSeries == $result['series']){
continue; //don't treat the same series twice
}
else{

$lastSeries = $result['series']; //so we know we treated this series already
$image = $result['image']; //get first image for this series that happens to be in the table
$date = $result[date]; //get date of the image


//your layout code untouched
$contents_here = '<table align="center" width="100%" style="text-align: center;">
<tr><td><a href="info.php?series='.$series.'"><img src="previews/'.$image.'" title="design" alt="design">
<br><b>'.$series.'</b></A>
<br>Designs: '.$count_amount.' ~ Updated: '.$date.'
</table>';

if ($i==1)
    {
    echo '<tr><td><div class="even">'.$contents_here.'</div>';
    }
   
    else if ($i==0)
    {
    echo '<td><div class="odd">'.$contents_here.'</div>';
    }

    $i++; $i=$i%2;
}

echo '</table>';
?>

 

i'm not 100% sure my use of $result as containing mysql_fetch_array() is correct, as i use mysqli, and not mysql. post any errors, and i'll see if i can figure it out.

[EternalSorrow]

The code returns a blank page without any error message (rather unusual).

[bobbinsbro]

could you give me var_dump($queryResult) please?

the page is blank because the loop continue's every time, because $result['series'] actually does equal "" for some reason. that's why i want to see the contents of $queryResult, so i know which index to call for that if() condition.

[EternalSorrow]

The var_dump($queryResult); gives the following message:

 

resource(6) of type (mysql result)

 

 

[bobbinsbro]

damn. that didn't work.

 

i noticed there's a period (.) before $series_letter in the sql string. delete it. does that make any difference?

 

if not, put var_dump($result) after $result = mysql_fetch_array($queryResult); in the for() loop, and post the dump from 1 loop iteration please.

[EternalSorrow]

Deleting the period did nothing and the same message as before appears when I place the var_dump($result) in the for() loop.

[bobbinsbro]

can you detail all the column names (case sensitive) in the table your'e using here please

[EternalSorrow]

Sure, here are the relevant fields and column functions in their exact order:

 

id int(5)

title varchar(100)

url varchar(100)

image varchar(100)

series varchar(100)

series_letter varchar(5)

dl varchar(100)

count_display int(100)

preview_display varchar(5)

date varchar(50)

type varchar(25)

resource varchar(25)

 

 

 

[bobbinsbro]

ok, i've been very silly. i made a very few simple changes to you're original code (which was pretty much fine, i have no idea what i was thinking). try using this code as it is instead of the junk i've been giving you:

<?php

if (!is_numeric($_GET["series_letter"]) && !empty($_GET["series_letter"]) && $_GET["series_letter"]!="")
{
$series_letter = $_GET["series_letter"];
}

$query="SELECT DISTINCT series FROM `design` WHERE `series_letter` = '$series_letter' ORDER BY series asc ";
$result = mysql_query( $query ) or die(mysql_error());

$i = 1;

echo '<table align="center" width="80%">';

while ($row = mysql_fetch_array($result))
{
extract($row); //set $series = $row['series']

$select_series_count = mysql_query("SELECT image, date FROM `design` where `series`='$series' ORDER BY `series` ASC") or die (mysql_error());
$count_amount = mysql_num_rows($select_series_count);

$tableArray = mysql_fetch_array($select_series_count);

$contents_here = '<table align="center" width="100%" style="text-align: center;">
<tr><td><a href="info.php?series='.$series.'"><img src="previews/'.$tableArray['image'].'" title="design" alt="design">
<br><b>'.$series.'</b></A>
<br>Designs: '.$count_amount.' ~ Updated: '.$tableArray['date'].'
</table>';

if ($i==1)
    {
    echo '<tr><td><div class="even">'.$contents_here.'</div>';
    }
   
    else if ($i==0)
    {
    echo '<td><div class="odd">'.$contents_here.'</div>';
    }

    $i++; $i=$i%2;

}
echo '</table>';
?>

 

[EternalSorrow]

The code works perfectly now!    Thank you so much for the help!

 

And don't tear yourself down with that "junk code" nonsense!  I've learned a lot from the questions you were asking (had to look up a number of items to answer them) and I'm truly grateful for all the help you've given me.

 

Thanks for putting up with my annoying questions 

[bobbinsbro]

no problem. i'm glad i could help.

well, this experience proved to me that my friends have been right all along: i only think straight at night. lol.

if you need anything else, please don't hesitate to scream. 

[EternalSorrow]

if you need anything else, please don't hesitate to scream.