Query problems with inner join

[charlie321]

Hi.. I think I have bad code in three different similar query's in my code.

The first query is:  $sql="SELECT * FROM invoice as d INNER JOIN members as c ON d.buyer=c.usernum order by " . $orderBy . " " . $order;

$result = $con->query($sql);

Actually in this one I didn't even know I had a problem other than it was slow working until I put this error trap in:

if (!$check1_res) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}

The other two pass $id from the previous script they are:

$sql=mysqli_query($con,"SELECT * FROM invoice_items as d inner JOIN items as c ON d.itemnum=c.itemnum where invnumber = '$id'");

$row = mysqli_fetch_array($sql);

and

$sql=mysqli_query($con,"SELECT * FROM invoice as d inner JOIN members as c ON d.buyer=c.usernum where invnumber = '$id'");

$row = mysqli_fetch_array($sql);

To be honest I didn't know I had a problem with the first two until The third would not return the correct data.  I only got partial or none of the invoice data I was expecting.  But I realized all three have problems when I used the error trap which come back with this:

Notice: Undefined variable: check1_res in C:\Apache24\htdocs\choo\tc_invoice.php on line 37
Error:
Notice: Undefined variable: check1_res in C:\Apache24\htdocs\choo\tc_invoice.php on line 83
Error:

The error I get is identical for the first query.

At first I thought it was the way that I was passing the variable, but $id is valid when echoed just below the query.

So I am hoping that this is going to be something I did wrong with the querys or possibly the fetch statement after the query is wrong???

Really appreciate any help you can give me.  If you need to see more of the code on either script please let me know.

[benanamen]

The first problem is that you are using variables in your query. You need to use Prepared Statements.

You have not shown us where $check1_res  comes from.

[ginerjm]

I do believe your first query has a syntax error in it also.  You end the query with a space char and then add $order to it.

[mac_gyver]

26 minutes ago, charlie321 said:

I put this error trap in

doing this was a waste of time, since you never defined/set the variable that causes it to 'function', and the undefined variable error you got from it has nothing to do with with any sql query problem.

your code needs to ALWAYS have error handling for statements that can fail. the easiest, simplest way of adding error handling for database statements is to use exceptions for errors and in most cases let php catch and handle the exception where it will use its error related settings (error_reporting, display_errors, log_errors) to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.) when learning, developing, and debugging code/queries, you should display all errors, which will now include database errors. when on a live/public server, you should log all errors, which will now include database errors. to use exceptions for errors with the mysqli extension, add the following line of code before the point where you make the connection, and then remove any error handling logic you have in your code now -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

the above will tell you if a query error is occurring, where in the code it occurred at, and what the error information is. if you are not getting any errors and you are not getting the expected result, you will need to troubleshoot your query/code to find out why.

[charlie321]

Thanks very much for the suggestion.  My connection is in a separate file.  This is what I did.  Is this correct?

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
    //echo 'This is a server using Windows!';
    $con = new mysqli("localhost","root","password","ding");
} else {
    //echo 'This is a server not using Windows!';
   $con = new mysqli("localhost","root","password","ding");
}

// Check connection
if ($con->connect_error)
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>

If it is correct, now I am getting no error messages but the third query is still not returning all the data.  Back to square one I guess.  Any other suggestions?

 

[charlie321]

OK I found more information.  The first record of any filtered table data is missing from the results of the query.  If there is one row that is supposed to show, nothing shows.  It is always the first row.  Does this ring a bell?  I'm not sure if indexing on the field that this is affected would help.  Anyone have a clue on what is happening?  Thanks for any help.

[mac_gyver]

that's usually because your code is fetching and discarding the first row. you would need to post your code to get specific help with what is wrong with it.

[charlie321]

Here is the code.  It is the second query that is not receiving the first row.  I hope this doesn't post twice.  I posted it half hour ago but it seems to have disappeared.

 

<html>
<link rel="stylesheet" href="css/style.css">
<?php
require('dbc.php');
$id = $_REQUEST['id'];
$sql=mysqli_query($con,"SELECT * FROM invoice as d inner JOIN members as c ON d.buyer=c.usernum where invnumber = '$id'");
$row = mysqli_fetch_array($sql);

#invoice
#$invno = $row['invnumber'];
$epoch = $row["time"];
$dt = new DateTime("@$epoch");
#$dt = $row['time'];
$subtotal = $row['subtotal'];
$shipping = $row['shipping'];
$tax = $row['tax'];
$gtotal = $row['gtotal'];
$message = $row['message'];

#member
$username = $row['username'];
$fullname = $row['fullname'];
$email = $row['email'];
$street = $row['street'];
$city = $row['city'];
$state = $row['state'];
$zip = $row['zip'];
$phone = $row['phone'];

//$sql = mysqli_query($con,"SELECT * FROM items where $itemnum = '$itemnum'");
//$row = mysqli_fetch_array($sql);

#items
#$title = $row['title'];

$sql=mysqli_query($con,"SELECT * FROM invoice_items as d inner JOIN items as c ON d.itemnum=c.itemnum where invnumber = '$id'");

#$sql = mysqli_query($con,"SELECT * FROM invoice_items where invnumber = '$id'");
$row = mysqli_fetch_array($sql);

#invoice_items
$invno = $row['invnumber'];
$itemnum = $row['itemnum'];
$price = $row['price'];
$quantity = $row['quantity'];

$i = 0;
// Establish the output variable
$dyn_table = '<table class = "stripe" border = "1" cellpadding="10">';
$dyn_table .= '<tr><td class = "gridtext">' ."Item Number" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."Description" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."Qty" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."Price" . '</td>';

while($row = mysqli_fetch_array($sql)){;
    $itemnum = $row['itemnum'];
    $title = $row['title'];
    $price = $row['price'];
    $quantity = $row['quantity'];
    
    
        $dyn_table .= '<tr><td>' . $itemnum . '</td>';
        $dyn_table .= '<td>' . $title . '</td>';
        $dyn_table .= '<td>' . $quantity . '</td>';
        $dyn_table .= '<td>' . $price . '</td>';
       
   $i++;
}
$dyn_table .= '</tr></table>';
?>

<?php
require('dbc.php');
$id = $_REQUEST['id'];
$sql=mysqli_query($con,"SELECT * FROM invoice as d inner JOIN members as c ON d.buyer=c.usernum where invnumber = $id");
#if (!$check1_res) {
#    printf("Error: %s\n", mysqli_error($con));
#    #exit();
#}
$row = mysqli_fetch_array($sql);

#invoice
#$invno = $row['invnumber'];
$epoch = $row["time"];
$dt = new DateTime("@$epoch");
#$dt = $row['time'];
$subtotal = $row['subtotal'];
$shipping = $row['shipping'];
$tax = $row['tax'];
$gtotal = $row['gtotal'];
$message = $row['message'];

#member
$username = $row['username'];
$fullname = $row['fullname'];
$email = $row['email'];
$street = $row['street'];
$city = $row['city'];
$state = $row['state'];
$zip = $row['zip'];
$phone = $row['phone'];

$sql=mysqli_query($con,"SELECT * FROM invoice_items as d inner JOIN items as c ON d.itemnum=c.itemnum where invnumber = $id");
#$sql = mysqli_query($con,"SELECT * FROM invoice_items where invnumber = '$id'");

$row = mysqli_fetch_array($sql);

#invoice_items
$invno = $row['invnumber'];
$itemnum = $row['itemnum'];
$price = $row['price'];
$quantity = $row['quantity'];

$i = 0;
// Establish the output variable
$dyn_table = '<table class = "stripe" border = "1" cellpadding="10">';
$dyn_table .= '<tr><td class = "gridtext">' ."<b>Item Number</b>" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."<b>Description</b>" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."<b>Qty</b>" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."<b>Price</b>" . '</td>';

while($row = mysqli_fetch_array($sql)){;
    $itemnum = $row['itemnum'];
    $title = $row['title'];
    $price = $row['price'];
    $quantity = $row['quantity'];
    
    
        $dyn_table .= '<tr><td>' . $itemnum . '</td>';
        $dyn_table .= '<td>' . $title . '</td>';
        $dyn_table .= '<td>' . $quantity . '</td>';
        $dyn_table .= '<td>' . $price . '</td>';
       
       
   $i++;
}
$dyn_table .= '</tr></table>';
?>
<body>
<table  class = "norm" background-color = #f2f2f2 align = "center" border = "1" rules = none cellpadding="3" width = "70%">
<th width = "40%"><?php echo $fullname ?></th><th width = "45%" align = "left">Train City, Inc.</th><th width = "15%""><?php echo $dt->format('M-d-Y'); ?></th><tr>

<td><?php echo $street ?></td><td>ChooChoo Auctions Invoice</td><td><?php echo "Invoice No: " . $invno; ?></td><tr>
<td><?php echo $city . ", " . $state . "  " . $zip ?></td><td>auctions@traincity.com</td><td></td><tr>
<td><?php echo $email ?></td><td>321-799-4005</td><td></td><tr>
<td><?php echo "Username: " . $username ?></td><td></td><td></td><tr>
<td><?php echo "Phone No: " . $phone ?></td><td></td><td></td><tr>

</table>
</html>

<?php
echo $dyn_table;

?>

<table  class = "footer" background-color = #f2f2f2 align = "center" border = "1" rules = none cellpadding="3" width = "70%">
<td width = "70%">Thanks very much for your order!!  Please note that all auction payments are due no later than 7 days<td width ="25%" align = "right"><?php echo "Sub-Total: " . $subtotal; ?></td><td width = "5%"</td><tr>
<td>after the end of the auction.  We can combine auctions for you, but not after seven days.</td><td align = "right"><?php echo "Shipping: " . $shipping; ?></td><td></td><tr>
<td>Please make payment on the internet by clicking <a href="https://www.paypal.com/webapps/shoppingcart?flowlogging_id=ff6917d36b2f3&mfid=1562617513545_96d73356b045a#/checkout/openButton" target = "_blank"> here</a>.  Or you can email or call us.</td><td align = "right"><?php echo "Sales Tax: " . $tax; ?></td><td></td><tr>
<td></td><td align = "right"><?php echo "<b>Total:  " . $gtotal; ?></b></td><td></td><tr>
</table>
</table>
</body>

</html>

[charlie321]

Somehow some of this quote got pasted in a duplication.  I fixed this and it does the same thing.  Not sure what happened with the duplicated code....

[kicken]

In this code:

$sql=mysqli_query($con,"SELECT * FROM invoice_items as d inner JOIN items as c ON d.itemnum=c.itemnum where invnumber = $id");

$row = mysqli_fetch_array($sql);

$invno = $row['invnumber'];
$itemnum = $row['itemnum'];
$price = $row['price'];
$quantity = $row['quantity'];

$i = 0;
// Establish the output variable
$dyn_table = '<table class = "stripe" border = "1" cellpadding="10">';
$dyn_table .= '<tr><td class = "gridtext">' ."<b>Item Number</b>" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."<b>Description</b>" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."<b>Qty</b>" . '</td>';
$dyn_table .= '<td class = "gridtext">' ."<b>Price</b>" . '</td>';


while($row = mysqli_fetch_array($sql)){;
    $itemnum = $row['itemnum'];
    $title = $row['title'];
    $price = $row['price'];
    $quantity = $row['quantity'];
    
    
        $dyn_table .= '<tr><td>' . $itemnum . '</td>';
        $dyn_table .= '<td>' . $title . '</td>';
        $dyn_table .= '<td>' . $quantity . '</td>';
        $dyn_table .= '<td>' . $price . '</td>';
       
       
   $i++;
}

You're fetching the first row on line 3, then your while loop later on fetches rows 2...n.

You probably want to just remove the fetch on line 3 and the variables you're trying to grab from it.

 

[charlie321]

Yes I just figured that out.  Thanks very much!!!!!