Help with confusing PHP/MySQL query

[jkaye]

I'm trying to write this query, but I don't know how to go about it.

Say you have a table with a bunch of listings, with multiple listings per company. You have a status field in the table that is either complete or active.

What I want to do is show ONLY the active companies in the top part of a list, and only the complete companies in the bottom part of the list.

Problem is, some companies have active AND complete listings. Is there a way to check against a previous query? My thought would be to format the code at such:

<?php 

//ACTIVE COMPANIES
$query = mysql_query("SELECT * FROM listings WHERE status='active' GROUP BY company");
echo'<ul>';
while($result = mysql_fetch_array($query)) {
echo'<li>'.$result['company'].'</li>';
}
echo'</ul>';

//COMPLETE COMPANIES
$query2 = mysql_query("SELECT * FROM listings WHERE status='complete' GROUP BY company");
echo'<ul>';
while($result2 = mysql_fetch_array($query2)) {
echo'<li>'.$result2['company'].'</li>';
}
echo'</ul>';
?>

I just need to make sure the "active" companies are not duplicated in the "complete" companies. Any help is appreciated! I can't wrap my head around it.

[ﻼim]

I'm trying to write this query, but I don't know how to go about it.

Say you have a table with a bunch of listings, with multiple listings per company. You have a status field in the table that is either complete or active.

What I want to do is show ONLY the active companies in the top part of a list, and only the complete companies in the bottom part of the list.

Problem is, some companies have active AND complete listings. Is there a way to check against a previous query? My thought would be to format the code at such:

<?php 

//ACTIVE COMPANIES
$query = mysql_query("SELECT * FROM listings WHERE status='active' GROUP BY company");
echo'<ul>';
while($result = mysql_fetch_array($query)) {
echo'<li>'.$result['company'].'</li>';
}
echo'</ul>';

//COMPLETE COMPANIES
$query2 = mysql_query("SELECT * FROM listings WHERE status='complete' GROUP BY company");
echo'<ul>';
while($result2 = mysql_fetch_array($query2)) {
echo'<li>'.$result2['company'].'</li>';
}
echo'</ul>';
?>

I just need to make sure the "active" companies are not duplicated in the "complete" companies. Any help is appreciated! I can't wrap my head around it.

i hope this helps :

//COMPLETE COMPANIES
$query2 = mysql_query("SELECT * FROM listings WHERE status='complete' AND status <> 'active'
GROUP BY company");
echo'<ul>';
while($result2 = mysql_fetch_array($query2)) {
echo'<li>'.$result2.'</li>';
}
echo'</ul>';

[jkaye]

It's not filtering out the companies that have entries that are active as well...

[ﻼim]

It's not filtering out the companies that have entries that are active as well...

try this :

//COMPLETE COMPANIES
$myquery=mysql_query("select distinct company from listings");

if(mysql_fetch_object($myquery) == null)
{
	echo "<center> No Record Found. </center>";
}
else
{
	$query2 = mysql_query("SELECT distinct company from listings 
		where status like 'complete' and status <> 'active'
		group by company");
	// you may also add	other column names you need to display

	echo "<ul>";
	while($result2 = mysql_fetch_object($query2)) {
		echo"<li>".$result2->company."</li>";
		// you may also add	other column names you need to display here
	}
	echo "</ul>"; 
}

hope it works! Ϋ

[MooGeek]

Good work! sent you a message btw :D

[ﻼim]

Good work! sent you a message btw :D

:) thanks.

done with the reply. Ϋ

[jkaye]

Yeah, it's still not working....That query still finds everything that's complete and not filtering it out if it's complete AND active. I think I may have a way of doing it. Maybe if I pull everything from the table that's active, put it into an array, and check against the array, that might work.

[jkaye]

I ended up doing this:

<?php
echo'<ul>';
$array1 = array();
								
$query1 = mysql_query("SELECT company FROM listings WHERE status='active' GROUP BY company ORDER BY company asc");

while ($row = mysql_fetch_array($query1)) {
echo'<li>'.$row['company'].'</li>';
array_push($array1, $row['company']);
}
echo'</ul>';

$array2 = array();
								
$query2 = mysql_query("SELECT company FROM listings WHERE status='complete' GROUP BY company ORDER BY company asc");

while ($row2 = mysql_fetch_array($query2)) {
array_push($array2, $row2['company']);
}

echo'<ul>';

$final = array_diff($array2, $array1);

foreach($final as $key => $value) {
echo'<li>'.$value.'</li>';
}
echo'</ul>';
?>

[MooGeek]

well, I'm glad you did it all by yourself. congratulation btw :3

[ﻼim]

I ended up doing this:

<?php
echo'<ul>';
$array1 = array();
								
$query1 = mysql_query("SELECT company FROM listings WHERE status='active' GROUP BY company ORDER BY company asc");

while ($row = mysql_fetch_array($query1)) {
echo'<li>'.$row['company'].'</li>';
array_push($array1, $row['company']);
}
echo'</ul>';

$array2 = array();
								
$query2 = mysql_query("SELECT company FROM listings WHERE status='complete' GROUP BY company ORDER BY company asc");

while ($row2 = mysql_fetch_array($query2)) {
array_push($array2, $row2['company']);
}

echo'<ul>';

$final = array_diff($array2, $array1);

foreach($final as $key => $value) {
echo'<li>'.$value.'</li>';
}
echo'</ul>';
?>

:D I'm confused with this.

shame on me.
i didn't get your point.

anyway, 'nice to solve it by yourself. Ϋ

[MooGeek]

It's ok. atleast you've tried :) nice job anyway :)

[ﻼim]

It's ok. atleast you've tried :) nice job anyway :)

thanks. Ϋ

[Pirion1]

I would like to point out that in the code provided, you said to pull company in list that have status X, but not status Y which is redundant and not actually what you meant for it to mean i'm guessing.

[ﻼim]

I would like to point out that in the code provided, you said to pull company in list that have status X, but not status Y which is redundant and not actually what you meant for it to mean i'm guessing.

hey pirion1! Ϋ

who are you regarding to?