使用GET进行MySQL查询

Question

Hi below is a sql query that does not function correctly. This query below should show 1 invoice of the current selected customer. But somehow I can not manage to make this query work like shown below.

<?php
$id = (ISSET($_GET['id'])) ? intval($_GET['id']):0;
$clientnumber= (ISSET($_GET['clientnumber'])) ? intval($_GET['clientnumber']):0;

$sql = "SELECT * 
        FROM customers AS a 
            inner join invoices AS b on a . '$clientnumber' = b . '$clientnumber'
                and a . '$id' != b . '$id'"; 

$result = $conn->query($sql) or die(mysql_error());
$query=getenv(QUERY_STRING);
parse_str($query);

?>

<?php while ($row = $result->fetch_assoc()) {?>

Echo sql results:

SELECT * 
FROM customers AS a 
    inner join invoices AS b on a.'0' = b.'0' 
        and a.'4' != b.'4'

Answer 1

Although you should use prepared statements, I think the problem is that you need to check the column in the customers table matches the columns in the invoices table and not the value your looking for. The names of the columns I'm not sure about, but your matching the invoice is for the client and then in the WHERE clause you check that the client number is the one your looking for...

 SELECT * 
    FROM customers AS a 
    inner join invoices AS b on a.clientnumber = b.clientnumber
            and b.invoiceid = $id
    where a.clientnumber = $clientnumber

To work without the ID, then try

 SELECT * 
    FROM customers AS a 
    inner join invoices AS b on a.clientnumber = b.clientnumber
    where a.clientnumber = $clientnumber

If your clientnumber column is a character field...

    where a.clientnumber = '$clientnumber'

If this still isn't returning rows, try changing the inner join to...

left join invoices AS b on a.clientnumber = b.clientnumber

This allows it to find customers where there isn't a matching invoice.