SQL query working in phpmyadmin but not in php

Question

i have made a query to select only the last date for each user. It works in phpmyadmin but when i want to execute it in a mysqli_query() in PHP it doesnt give anything back, not even an error.

the code is:

select * from table t inner join ( select User_ID, max(Date) as MaxDate from table group by User_ID ) tm on t.User_ID = tm.User_ID and t.Date = tm.MaxDate

If you have any idea why please let me know :)

EDIT the PHP code is :

$id = $_SESSION["ID"];  
    $SqlQuery = "SELECT * from 'tablename' t inner join ( select 'User_ID', max('Date') as 'MaxDate' from 'tablename' group by 'User_ID' ) tm on 't.User_ID' = 'tm.User_ID' and 't.Date' = 'tm.MaxDate'";
    $Result = mysqli_query($link, $SqlQuery) or die ("not possible to execute query: $sql on $link");

    if ($Result->num_rows > 0) {
     while($row = $Result->fetch_assoc()) {
         echo "<br> id: ". $row['Sick_ID']. " - UserID: ". $row['User_ID']. "- Reason " . $row['Reason'] . "<br>";
     }
} else {
     echo "0 results";
}

Presumably `table` is not the real table name, or this would fail in both places. You should probably show the php code in the question; the problem probably lies there. — Gordon Linoff, Jan 17 '16 at 16:47
$query= "SELECT persons.lastname, persons.firstname, orders.orderno ". "FROM persons ". "INNER JOIN orders ". "ON orders.o_id = persons.p_id "; — varunkumar, Jan 17 '16 at 16:50
You should consider getting the sql error instead of just saying, the query couldn't be executed. http://php.net/mysqli_error — Charlotte Dunois, Jan 17 '16 at 16:57
Other sql query did work in the code (it was : SELECT* FROM tablename) — BaanAxe, Jan 17 '16 at 16:58
changed it so it would show the error but still nothing shows, not even an error — BaanAxe, Jan 17 '16 at 17:02

score 2 · Accepted Answer · answered Jan 17 '16 at 17:03

2

Add the schema owner prefix to the select query. It usually happens when executing mysql queries on PHP.

Select * from data.table_name as t1 inner join data.table_name_2 as t2 .....

Better if:

Select data.t1.id, data.t2.name from data_table_name as t1 .....

answered Jan 17 '16 at 17:03

digitai

1,870
2
20
37

1

@DubstepsantaHD - If this was the error, you shouldn't be guessing at all. The mysqli extension will tell you the exact problem if you ask. – Álvaro González Jan 18 '16 at 16:49

SQL query working in phpmyadmin but not in php

1 Answers1

Linked