SQL query working in phpmyadmin but not in php

Viewed 66 times

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";
}

Answers

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 .....

Friday, November 4, 2022

pan_thomakos

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

klas_lindbäck

I'm guessing that meetingid has no value.

Trapping on an error as suggested will not always work. If the query syntax is valid then no error is triggered, but you get no results.

Add an echo to your code:

$q1d = "SELECT max(meetingid) as newmeeting FROM meetings LIMIT 1)";
$r1d = mysqli_query ($dbc, $q1d);
echo mysqli_error() . "<br/>$q1d";

Then if necessary copy and paste the echoed query into the SQL query box in phpMyAdmin.

Wednesday, October 12, 2022

avianey

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc

Wednesday, August 17, 2022

thecontstruct

You have cyrillic characters in your query, so it may be necessary to set mySQL connection encoding. If you are using utf-8, insert following line after mysqli_connect:

mysqli_query($conn, "SET NAMES 'utf8'");

Or if your script is saved in windows-1251, use the following: mysqli_query($conn, "SET NAMES 'cp1251'");

For more information about connection character sets and encodings please see the manual

And why does the query work in phpMyAdmin? Because it probably sets encoding for you in the background.

Tuesday, August 9, 2022

yury_matatov

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

SQL query working in phpmyadmin but not in php

Answers

php,mysql