DevTech Posted May 15, 2022 Share Posted May 15, 2022 I have a problem with a query that when I use it on MySQL it works well but when I insert it on my php code it no longer works this is my code $query=$bdd->prepare('SELECT t2.ui_company, t1.* FROM a2billing.nwc_refill_users t1 LEFT JOIN a2billing.nwc_anagrafica t2 ON t1.refag_richiedente = t2.ui_login WHERE t1.refag_paga_a='' AND t1.refag_importo > 0 AND t1.refag_dataora_validazione<>'' AND (DATE(t1.refag_dataora)>='2022-01-01' AND DATE(t1.refag_dataora)<='2022-05-15') '); $query->execute(); can u help me guy's Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/ Share on other sites More sharing options...
requinix Posted May 15, 2022 Share Posted May 15, 2022 I assume by "no longer works" you mean that you get parse errors from PHP? Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/#findComment-1596290 Share on other sites More sharing options...
Barand Posted May 15, 2022 Share Posted May 15, 2022 56 minutes ago, DevTech said: this is my code Is that all of your code? No attempt to fetch and display any results returned? Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/#findComment-1596295 Share on other sites More sharing options...
DevTech Posted May 16, 2022 Author Share Posted May 16, 2022 actually yes here is my whole code <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr" lang="fr"> <head> <title>Recharge</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <link rel="stylesheet" media="screen" type="text/css" title="Design" href="style.css" /> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> </head> <body> <div class="w3-container"> <h2>Refill Accounts</h2> <?php try { $bdd = new PDO('mysql:host=localhost;dbname=a2billing;charset=utf8', 'root', ''); } catch(Exception $e) { die('Erreur : '.$e->getMessage()); } $query=$bdd->prepare('SELECT t2.ui_company, t1.refag_importo FROM a2billing.nwc_refill_users t1 LEFT JOIN a2billing.nwc_anagrafica t2 ON t1.refag_richiedente = t2.ui_login WHERE t1.refag_paga_a="" AND t1.refag_importo AND t1.refag_dataora_validazione<>"" AND (DATE(t1.refag_dataora)>="2022-01-01" AND DATE(t1.refag_dataora)<="2022-05-15")'); $query->execute(); echo'<table class="w3-table-all"><tr><td>Company</td><td>Date</td><td>Total</td></tr></div>'; echo "" . date("Y/m/d") . "<br>"; while($data=$query->fetch()) { echo'<tr><td>'.$data['refag_richiedente'].'</td><td>'.$data['refag_richiedente'].'</td><td>'.$data['refag_dataora'].'</td><td>'.$data['Total'].'</td></tr>'; } echo'</table>'; ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/#findComment-1596306 Share on other sites More sharing options...
Barand Posted May 16, 2022 Share Posted May 16, 2022 You need to get PDO to report any errors that it encounters. Here's my connection code as an example const HOST = 'localhost'; const USERNAME = '????'; const PASSWORD = '????'; const DATABASE = 'test'; // default db function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } In particular, the second line of the function tells PDO to throw exceptions on error. Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/#findComment-1596307 Share on other sites More sharing options...
DevTech Posted May 16, 2022 Author Share Posted May 16, 2022 finally the table appear thank you for all Do you have an idea how I can display the ( refag_importo ) as a total?? I tried with sum(t1.refag_importo) AS Total but it doesn't work Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/#findComment-1596309 Share on other sites More sharing options...
Phi11W Posted May 16, 2022 Share Posted May 16, 2022 14 hours ago, DevTech said: $query=$bdd->prepare('SELECT t2.ui_company, t1.* FROM a2billing.nwc_refill_users t1 LEFT JOIN a2billing.nwc_anagrafica t2 ON t1.refag_richiedente = t2.ui_login WHERE t1.refag_paga_a='' AND t1.refag_importo > 0 AND t1.refag_dataora_validazione<>'' AND (DATE(t1.refag_dataora)>='2022-01-01' AND DATE(t1.refag_dataora)<='2022-05-15') '); Remember that you're building a PHP String that just happens to contain some text (SQL) that means something to your database. You have to build tat string according to PHP rules: $query=$bdd->prepare('SELECT t2.ui_company, t1.* FROM a2billing.nwc_refill_users t1 LEFT JOIN a2billing.nwc_anagrafica t2 ON t1.refag_richiedente = t2.ui_login WHERE t1.refag_paga_a=\'\' AND t1.refag_importo > 0 AND t1.refag_dataora_validazione<>\'\' AND (DATE(t1.refag_dataora)>=\'2022-01-01\' AND DATE(t1.refag_dataora)<=\'2022-05-15\')'); You might also consider using Parameters in place of the literals. A tiny bit more code but you'll avoid headaches like this. Regards, Phill W. Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/#findComment-1596311 Share on other sites More sharing options...
Solution Barand Posted May 16, 2022 Solution Share Posted May 16, 2022 1 hour ago, DevTech said: Do you have an idea how I can display the ( refag_importo ) as a total?? If you use sum(t1.refag_importo) AS Total without a GROUP BY clause it will give you a single row with the total for all the selected records. if, for example, you have SELECT t2.ui_company , sum(t1.refag_importo) AS Total FROM . . . GROUP BY ui.company then you get the total for each company Quote Link to comment https://forums.phpfreaks.com/topic/314796-problem-query/#findComment-1596312 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.