Why my query is showing all users amount insted of current user? any prob with sql?

[prohor]

Hi,
Thank you for your time spending to help me.

I am trying to display only the list of amounts in case of current id but it is showing all when I have used WHERE clause to set. Can you help me indicating if any mistake I have done in my Query?

$sql_inccur = "SELECT * FROM incomes LEFT JOIN inctype ON incomes.inctype_id = inctype.inctype_id LEFT JOIN customer ON incomes.cust_id WHERE incomes.cust_id = '$_SESSION[cust_id]' ORDER BY inc_date DESC";
	$query_inccur = mysqli_query($db_link, $sql_inccur);
	checkSQL($db_link, $query_inccur);

[phdr]

The main problem is the 2nd join condition - ON incomes.cust_id. That is joining every row that has a true incomes.cust_id with every row in the customer table, i.e. incomes.cust_id 1 with customer ids 1,2,3,…, incomes.cust_id 2 with customer ids 1,2,3,…, income.cust_id 3 with customer ids 1,2,3,…,… It is missing a relationship for the rows in the customer table.

Some suggestions -

1. Use table alias names to simplify the syntax, something like i for income, t for type, c for customer.
2. Specifically list the table_alias.column names you are SELECTing. This helps to write queries that retrieve only the data you want, and helps to document what you are doing.
3. Use LEFT JOINs when you want all the data from the left-hand side of the query regardless of any matching data on the right-hand side.

So, what exactly are you doing - selecting customer information and any income/income type information. You would have a LEFT JOIN between the customer table and the incomes table and income type table, since there may not be any incomes for a customer. Your query would end up looking like -

SELECT c.some_customer_column, ..., i.some_incomes_column, ..., t.some_inctype_column, ...
FROM customer c
LEFT JOIN incomes i ON c.cust_id = i.cust_id
LEFT JOIN inctype t ON i.inctype_id = t.inctype_id
WHERE c.cust_id = ?
ORDER BY i.inc_date DESC

Note: you should use a prepared query when supplying unknown data to an sql query statement, hence the ? place-holder in the above. Also, the table/alias isn’t required for uniquely named columns (the inc_date), but if you always include it, anyone (like help forum members) can read your query and know what columns are from which table without having to know or guess what your naming scheme is.

[prohor]

Thank you a lot. I think now I can solve my problem.