Jump to content

Problem Query


DevTech
 Share

Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

  • Solution
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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.