[SOLVED] query not returning correct number of records. please help

[debuitls]

Hi,

 

I'm developing a reverse auction style site.

 

However, I'm really struggling with the following.

 

$result = mysql_query("SELECT proposal.*,count(bid.proposalid) AS bids,SYSDATE(), TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
LEFT JOIN bid ON proposal.proposalid = bid.proposalid
WHERE SYSDATE() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid");

 

There are three records that this query should be returning. However when I run this query and attempt to echo to the screen it only prints one?

 

If I run this query it counts the correct number of records?

 

$sql = mysql_query("SELECT COUNT(proposalid) FROM proposal
WHERE SYSDATE() < tomorrowtime AND username = '$username'");

 

I know it's difficult without seeing the tables but just wondering if anybody can spot why this is happening?

 

Any suggestions would be much appreciated.

[kratsg]

This can also count the number of records returned:

mysql_num_rows($sql);

[debuitls]

Thanks kratsg for getting back to me!

 

I'm not sure im 100% clear on your suggestion.

 

Ill rephrase my question just to make sure I'm being clear about what the problem is.

 

$result = mysql_query("SELECT proposal.*,count(bid.proposalid) AS bids,SYSDATE(), TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
LEFT JOIN bid ON proposal.proposalid = bid.proposalid
WHERE SYSDATE() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid");

 

This is only returning one record to the screen. It should be returning 3.

 

I ws just wondering if anybody can see why it would return only one record?

 

Thanks

[Stuie_b]

How are you outputting the results to the screen?

 

are you simply echoing the results out or are you looping throught?

 

[debuitls]

Hi Stuie_b

 

I'm using a while loop

 

while($row = mysql_fetch_array($result))
{
echo "<tr onclick=\"window.location='results.php?id=" . $row['proposalid'] . "'\">";
			  echo "<td>" . $row['county'] . "</td>";
			  echo "<td>" . $row['checkin'] . "</td>";
			  echo "<td>" . $row['checkout'] . "</td>";
			  echo "<td>" . $row['budget'] . "</td>";
			  echo "<td>" . $row['adults'] . "</td>";
			  echo "<td>" . $row['children'] . "</td>";
			  echo "<td>" . $row['timeleft'] . "</td>";	
			  echo "<td>" . $row['bids'] . "</td>";
			  				  
			echo "</tr>";
			echo "</tbody>";
}
echo "</table>";

 

So I dont think thats the problem.

 

If I remove the

 

WHERE SYSDATE() < proposal.tomorrowtime 

 

it seems to print all the results.

 

Thanks

[debuitls]

Hi,

 

I just realised that the reason that only one row was returning and not three was because of the

 

count(bid.proposalid) AS bids

 

GROUP BY bid.proposalid

 

parts of the sql code.

 

When I removed these it returned all three rows.

 

So that great, but I'm now left with the problem of not having the number of bids made on each proposal in the table.

 

I was just wondering if anybody can think of another way of counting the number of bids made??

 

I think this is what kratsg was suggesting earlier in the thread?

 

This can also count the number of records returned:

mysql_num_rows($sql);

 

Can anyone explain what kratsg meant by this? I'm not so sure.

 

Alternatively if anyone else has any sugestions it would be great!

 

Cheers

[kratsg]

GROUP BY bid.proposalid

 

Add that line back in, and then do the following

 

GROUP_CONCAT(bid.proposalid) AS 'bids'

 

This will group your bids as a comma separated list

 

$num_bids = count(",",explode($row['bids']));

 

Then you can count the length of the array created by exploding into an array around the commas.

[debuitls]

Thanks very much for your suggestion kratsg!

 

$result = mysql_query("SELECT proposal.*,SYSDATE(),GROUP_CONCAT(bid.proposalid) AS 'bids', TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid

...

echo "<td>" . count(",",explode($row['bids'])). "</td>";

");

 

I gave it a try but I still seem to be runnin into the problem of sql only retrieving one record.

 

Whats strange is that if I make a bid on any of the proposals that arent showing then they seem to be retrieved and echo on screen.

 

Does anyone have any other suggestions?

 

[kratsg]

Then we'll add the LEFT OUTER JOIN statement. The main problem with the group by is that if the table with bids is empty, then it won't even return that record, even if that proposal exists.

 

I had a similar problem and was able to google and fix this like shown:

 

Your query:

SELECT proposal.*,SYSDATE(),GROUP_CONCAT(bid.proposalid) AS 'bids', TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid

 

FIXED Query:

SELECT proposal.*,SYSDATE(),GROUP_CONCAT(bid.proposalid) AS 'bids', TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
LEFT OUTER JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid

 

Let me know if this fixes it (you may want to add some code checking that if there are no bids, ie if $row['bids'] is empty, set it to 0 or something)

[debuitls]

Thanks again for your time on this kratsg.

 

I added the LEFT OUTER JOIN statement as you suggested but still only returining proposals which have bids made.

 

So I think I may need some code checking as you say. 

 

I'm sorry, only learning php, is there any way you could give me an example of how to structure the statement to check if there are no bids for a particular proposal.

[kratsg]

If you have phpMyAdmin, why not run that query in and see what comes out of it... :-D I don't particularly see anything wrong with it.

[debuitls]

When I run it there in phpmyadmin it only returns the one row when it should be retrieving 4.

 

SELECT proposal . * , SYSDATE( ) , GROUP_CONCAT( bid.proposalid ) AS 'bids', TIME_FORMAT( TIMEDIFF( tomorrowtime, SYSDATE( ) ) , '%H hours, %i minutes' ) AS timeleft
FROM proposal
LEFT OUTER JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW( ) < proposal.tomorrowtime
AND proposal.username = 'abrett'
GROUP BY bid.proposalid

 

Anyone have any more ideas?

 

Sorry for dragging it out on everyone!

 

[kratsg]

Ok, let's try it this way.

 

Give us two screenshots: one of the row in the 'proposal' table showing the username='abrett' and then the other being the rows in the 'bid' table where the ids match and we'll see what's going on.

 

Or maybe it's only showing one row because there's only one proposal with the username 'abrett'?

[mikesta707]

if the number of bids made is equal to the number of rows returned, than kats previous advice to use mysql_num_rows would work. I'm not entirely sure if this is true, if it is, then that would probably be the easiest solution if you are having so much trouble getting the query to return the count.

 

to use mysql_num_rows just stick in a line like that after your query

$query = mysql_query("MY QUERY");
//THE FOLLOWING LINE IS THE IMPORTANT ONE
$num = mysql_num_rows($query);

 

this is of course a very simplified version without any debug checks, but the basic logic is there.

[debuitls]

Ok so you'll see in proposal screenshot that user abrett has made three proposals, proposalid 103,104,105

 

You can see that only two of the proposals have bids on them in the bidtable screenshot, 103 and 104. There is no bid on 105!

 

However in the site screenshot all three proposals appear with one bid on them each.

 

If none of the proposals had a bid on them then only one proposal would print to screen. 

 

I hope this makes it less confusing!

 

Thanks so much for helpin me out!

 

 

[attachment deleted by admin]

[kratsg]

Alright, I'm 90% sure exactly where the error is. Let's see the code for displaying the table (first picture)

[debuitls]

Sounds promising

 

$result = mysql_query("SELECT proposal.*,SYSDATE(),GROUP_CONCAT(bid.proposalid) AS 'bids', TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
LEFT OUTER JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW() < proposal.tomorrowtime AND proposal.username = '$username'
GROUP BY bid.proposalid
");


echo "<table id='myTable' cellpadding='0'>
<thead>
<th axis='string'>Location</th>
<th axis='date'>Check-in</th>
<th axis='date'>Check-out</th>
<th axis='number'>Budget</th>
<th axis='number'>Adults</th>
<th axis='number'>Children</th>
<th axis='string'>Auction Time Left</th>
<th axis='number'>Bids</th>


</thead>";


while($row = mysql_fetch_array($result))
{
echo "<tr onclick=\"window.location='results.php?id=" . $row['proposalid'] . "'\">";
			  echo "<td>" . $row['county'] . "</td>";
			  echo "<td>" . $row['checkin'] . "</td>";
			  echo "<td>" . $row['checkout'] . "</td>";
			  echo "<td>" . $row['budget'] . "</td>";
			  echo "<td>" . $row['adults'] . "</td>";
			  echo "<td>" . $row['children'] . "</td>";
			  echo "<td>" . $row['timeleft'] . "</td>";	
			  echo "<td>" . count(",",explode($row['bids'])). "</td>";
			  				  
			echo "</tr>";
			echo "</tbody>";
}
echo "</table>";

[kratsg]

I thought it would've been a problem with $row['bids']

 

Can you run the query in your phpmyadmin/whatever database you've got?

 

This query, see what's returned.

SELECT proposal.*,SYSDATE(),GROUP_CONCAT(bid.proposalid) AS 'bids', TIME_FORMAT(TIMEDIFF(tomorrowtime, SYSDATE()), '%H hours, %i minutes') AS timeleft
FROM proposal
LEFT OUTER JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW() < proposal.tomorrowtime AND proposal.username = 'abrett'
GROUP BY bid.proposalid

 

Also, it's the first row that shouldn't have a bid (since it's antrim and not kilkenny [antrim=105])

[debuitls]

ah ok, my mistake.

 

That query returns the same three proposals. (See screenshot if that helps!)

 

[attachment deleted by admin]

[debuitls]

Oh wait sorry no it doesnt, it includes a new proposal I just made there 107.

 

 

[kratsg]

Well, this data is conflicting :-P

 

 

How does that match with this?

 

[debuitls]

Sorry kratsg for being confusing.

 

I just made a new proposal (ie 107) which is the new Kilkenny proposal.

 

I deleted it there.

 

So basically back to the way it was, ran the query you gave me. Screenshot is the result

 

[attachment deleted by admin]

[kratsg]

Can you show me the end of that last screenshot? IE: what does it display under the 'bids' column (bids will contain a comma separated list of the ids listed)

[debuitls]

here it is!

 

[attachment deleted by admin]

[kratsg]

Got it :-D

 

Replace the line (you'll know which line)

 

              echo "<td>" . count(",",explode($row['bids'])). "</td>";

 

WITH

 

              echo "<td>" . (!empty($row['bids']))?count(",",explode($row['bids'])):'0'. "</td>";