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'];
}
}
Can you please take a look on this? Maybe you see whats wrong but i cant find it
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'
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
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.).
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.
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.
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.
Yes, using a few more characters when naming will have negligible effect on the script while greatly improving readability which will reduce developer time.
