SELECT rows WHERE values are the same?

[TimDOES]

I am looking to select rows from an earlier query's results where the values in one column are the same. I am completely stuck on this and I'm sure there is a simple solution that I just can't see.

[Zane]

SELECT targetColumn AS x, COUNT(x) AS y
FROM users
GROUP BY x
HAVING ( COUNT(x) > 1 )

[TimDOES]

What values are x and y? Will this work if the "same Values" are undefined.

 

i.e. I have selected all machines made by $brand and now need to select each group of repeating column values ( Machine Type ).

 

Thank you for taking the time to help.

[F1Fan]

List your two separate queries so we can show you how to join them together.

[TimDOES]

The main categories I will be using are machineMfctr, machineType, machineModel.

 

My first query selects all the products made by a certain manufacturer.

$query = "select * from ARL2008 where machineMfctr = '$brand'";

 

Now I have multiple products for some Models and would like to be able to select them so the model name is only listed once while with all products for the model listed under it. Should this be done with another query or is there a better way to do this?

[F1Fan]

Well, you only listed one of the queries, so I'm guessing here.

 

Right now are you getting query results from one SELECT, looping through the results and running another SELECT against another table based on the results of the first query?

 

If so, let's pretend that this is your table structures:

 

table1 has columns data, manufacturer, someotherdata, and somedate.

table2 has columns manufacturer, modelname, and price

 

and let's say you're doing it this way now:

 

SELECT * FROM table1 ORDER BY manufacturer;
while (results from table equals $row){
  SELECT * FROM table2 WHERE manufacturer = {$row['manufacturer']}
  // results outputted here...
}

 

If that's the case, then you're wanting to do this:

SELECT * FROM table1, table2 WHERE table1.manufacturer = table2.manufacturer ORDER BY table1.manufacturer, table2.modelname

[TimDOES]

No, actually there is only one table that I am pulling from. I just need to sub select the group of products that are made for a certain model of machine in order to not label the model above every product (only post model once and then post all products under it). I am trying to figure out how to do this so I don't know the second query yet.

 

Thank you for the help.

[F1Fan]

Is this wrapped in PHP code? Because I do this kinda thing all the time, and I just use an if statement and a variable, like this:

 

<?php
// your selects 'n stuff up here...

$manuf = "";
while ($row = mysql_fetch_array($result)){
  if ($row['manufacturer'] != $manuf){
    echo "some header with {$row['manufacturer']}";
    $manuf = $row['manufacturer'];
  }
  echo "product data...";
}
?>

 

Make sense?

[Zane]

where does

machineMfctr, machineType

come into play on your question.

 

you have a table of products correct.....and columns for machineMfctr, machineType, and "model"(I know it's called something else)

which one has the duplicates?

[TimDOES]

Correct, they all have duplicates. Here is the code I have so far.

$query = "select machineTyoe from ARL2008 where machineMfctr = '$brand'";

$result = mysql_query($query);
if (!$result) {
    die("Query to show fields from table failed");
}
$row = mysql_fetch_array($result) or die(mysql_error());


while($row = mysql_fetch_array($result)){
echo "<h3>".$row['MachineModel']."</h3>";
echo "<div class='threeCol emptyBlox'>".$row['Brand']."</div><a class='blox' style='padding:0px;' href='toner/".$Partno.".htm'>
        <div class='threeCol'>".$row['DESCR1']."</div></a><a class='blox' style='padding:0px;' href='toner/".$Partno.".htm'>
        <div class='threeCol'>$".$row['Retail']."</div></a>";
echo "<div class='clear'></div>";
}

 

Right now it displays:

 

Machine Model 1 Header

Part Brand - Product1 - Price

-------------------------------------------

Machine Model 1 Header

Part Brand - Product2 - Price

-------------------------------------------

Machine Model 2 Header

Part Brand - Product3 - Price

---------------------------------------------

 

and I need it to be:

 

Machine Model 1 Header

Part Brand - Product1 - Price

Part Brand - Product2 - Price

--------------------------------------------

Machine Model 2 Header

Part Brand - Product3 - Price

--------------------------------------------

 

 

[F1Fan]

That's what I thought. Try this:

 

<?php
$query = "select machineTyoe from ARL2008 where machineMfctr = '$brand'";

$result = mysql_query($query);
if (!$result) {
    die("Query to show fields from table failed");
}
$row = mysql_fetch_array($result) or die(mysql_error());

$MachineModel = ""
while($row = mysql_fetch_array($result)){
if ($row['MachineModel']!=$MachineModel){
	echo "<h3>".$row['MachineModel']."</h3>";
	$MachineModel = $row['MachineModel'];
}
echo "<div class='threeCol emptyBlox'>".$row['Brand']."</div><a class='blox' style='padding:0px;' href='toner/".$Partno.".htm'>
        <div class='threeCol'>".$row['DESCR1']."</div></a><a class='blox' style='padding:0px;' href='toner/".$Partno.".htm'>
        <div class='threeCol'>$".$row['Retail']."</div></a>";
echo "<div class='clear'></div>";
}
?>

 

Not sure where your "--------" line comes in, but that should give you the idea.

[TimDOES]

BAM!! You have no idea how long I have been messing with this. Thank you so much. It works perfectly now. I knew it would be something simple I just couldn't see.

[F1Fan]

Glad to be of service!

[TimDOES]

After further review, I can see that this script is leaving the first result out. Can you help me with a fix for this?

[corbin]

$row = mysql_fetch_array($result) or die(mysql_error());

 

$MachineModel = ""

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

 

 

 

The script fetches the first result set and then never uses it.  That's where your first row is going ;p.

[TimDOES]

I can get the first result with this code but it still doubles the header for the first model if there is more than one product for it.

 

<?php 
echo "<h3>".$row['MachineModel']."</h3>";
echo "<a class='blox' style='padding:0px;' href='toner/".$row['Partno'].".htm'>
<div class='partHREF'>
<div class='threeCol emptyBlox'>".$row['Brand']."</div>
<div class='threeCol'>".$row['DESCR1']."</div>
<div class='threeCol'>$".$row['Retail']."</div>
<div class='clear'></div></div></a>";

$MachineModel = "";
while($row = mysql_fetch_array($result)){
if ($row['MachineModel']!=$MachineModel){
	echo "<h3>".$row['MachineModel']."</h3>";
	$MachineModel = $row['MachineModel'];
}
echo "<a class='blox' style='padding:0px;' href='toner/".$row['Partno'].".htm'>
<div class='partHREF'>
<div class='threeCol emptyBlox'>".$row['Brand']."</div>
<div class='threeCol'>".$row['DESCR1']."</div>
<div class='threeCol'>$".$row['Retail']."</div>
<div class='clear'></div></div></a>";

}

?>

 

Attached is a photo of the result:

 

 

[attachment deleted by admin]

[TimDOES]

Does anyone know a better loop to draw the table data and avoid leaving out the first result? I am stuck on this.

[F1Fan]

You're echoing the header row at the top of your code, then echoing it again in the loop. Get rid of the first one:

 

<?php 
echo "<a class='blox' style='padding:0px;' href='toner/".$row['Partno'].".htm'>
<div class='partHREF'>
<div class='threeCol emptyBlox'>".$row['Brand']."</div>
<div class='threeCol'>".$row['DESCR1']."</div>
<div class='threeCol'>$".$row['Retail']."</div>
<div class='clear'></div></div></a>";

$MachineModel = "";
while($row = mysql_fetch_array($result)){
if ($row['MachineModel']!=$MachineModel){
	echo "<h3>".$row['MachineModel']."</h3>";
	$MachineModel = $row['MachineModel'];
}
echo "<a class='blox' style='padding:0px;' href='toner/".$row['Partno'].".htm'>
<div class='partHREF'>
<div class='threeCol emptyBlox'>".$row['Brand']."</div>
<div class='threeCol'>".$row['DESCR1']."</div>
<div class='threeCol'>$".$row['Retail']."</div>
<div class='clear'></div></div></a>";

}

?>

[TimDOES]

The previous posts recommendation displays the first result above the "Model" header as shown in the attached picture below. I am trying to place it below the header.

 

[attachment deleted by admin]