Incorrect lines returned inside second while loop

Hello there!

I have table “logs” where i store item history (buy , sell, split etc.)
i suspect 3 rows returned but it returns 9

$query1 = mysqli_query($conn, "SELECT date, document, value FROM logs WHERE status='20'");
while($row1 = mysqli_fetch_array($query1)){

	// returns 3 corect rows in this loop 
	
	
	$query2 = mysqli_query($conn, "SELECT action_date, value FROM logs WHERE document='".$row1['document']."' AND status='40'");
	while($row2 = mysqli_fetch_array($query2)){
	
		// returns 9 rows but need to be 3 rows
		echo $row1['date'].' '.$row1['value'].' '.$row2['action_date'].' '.$row2['value']; 
	
	}
	
	

}

Please help me fix this!

Queries inside a loop are bad. Better use a JOIN (because that’s what should be used here), then one query will do (and even remove duplicates).

$query1 = mysqli_query($conn, "SELECT date, document, value FROM logs AND status='20'");

I’m a bit confused about the syntax here, in particular that “AND” clause.

1 Like

My typo there should be WHERE

Funny thing is that i made it as join also but it still gives me 9 rows :frowning:

Then you need to check if either your logic (where you assume 3 results) or the SQL logic (that gives 9 results) is wrong.

1 Like

Can you please take a look on this? Maybe you see whats wrong but i cant find it :frowning:

SELECT 
e.date AS date, e.document AS document, e.value AS value,
i.action_date AS action_date, i.value AS value



FROM logs AS e

LEFT JOIN header AS h
ON e.docNr=h.docNr

LEFT JOIN logs AS i
ON e.docNr=i.docNr 
AND (DATE_FORMAT(i.activityDate,'%Y-%m-%d') BETWEEN '2018-03-01' AND '2018-03-31') 
AND i.status='40' 

WHERE e.status='20'

Shouldn’t activityDate already be a DATE type? (if it’s DATETIME or TIMESTAMP, better use DATE() than DATE_FORMAT())

date is in this format 0000-00-00 00:00:00 (datatype is TIMESTAMP)

At start i tought that GROUP BY i.id did the trick but it appears that then e.value is not correct
so i tried GROUP BY e.id, i.id but that just gives all rows again :frowning:

Any suggestions?

Show some example data where we can see the problem.

Note: in an aggregated query (i.e. using GROUP BY) every item in the SELECT clause must be either grouped (unique with respect to the GROUP BY clause) or aggregated (the result in an aggregate function (SUM, AVG, etc.).

1 Like

And while a bit off topic, the United Nations has formally declared the use of single letter aliases as crimes against humanity. Is it really that hard to write:

LEFT JOIN header ON logs.docNr = header.docNr

And at least in the query from post #7, you seem to be trying to join logs to itself? There are valid use cases for doing this but I don’t think this is one of them.

1 Like

I have lines for same document: received and sent

i want to put received date and sent date in same html table row

Also why single letter alias is bad? Its less code.

Every so often I enjoy tilting at wind mills. I know single letter aliases date back to the dawn of time. In fact, an inscription on Pharaoh Ramesses II’s tomb contains, you guessed it, a sql query with single letter aliases.

But all seriousness aside, can you really describe the record definition for i without rechecking the query? Would calling it log_sent really be that much more code?

I do get amused at the inconsistency as well. Why not call the header table just h? Five characters of coding saved right there. And don’t get me started on column names. activityDate??? Just call it z and you would not only save an incredible amount of typing but think of the criminal waste of disk space that would be avoided.

And in case you are wondering, this post is indeed a poor attempt at humor.

4 Likes

If query is not that big how hard is to find out what “i” is?? I think this is nonsense. … I m not talking about file size but about code size. Also your comment about changing activityDate to z is ridiculous. …

It’s not hard to find out, the point is that you have to go looking. Not now, while you’re writing the code, but in five years when you’ve come back to it, or someone else is trying to maintain it.

2 Likes

@droopsnoot @ahundiak

Ok cool that is true! Never thinked of it that way.

Yes, using a few more characters when naming will have negligible effect on the script while greatly improving readability which will reduce developer time.

Not that you need something like

$current_datetime_value_from_users_table_in_Ymd_format_indicating_last_visit = $result['last_vist']; 

but a happy middle between that and something like $lv = $result['last_visit']; would be a good idea. eg. $user_last_visit = $result['last_visit'];

i.e. enough so that you know at a glance what it is without needing to “translate” it and can more easily trace it in your code if you need to.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.