charlie321

Sorry you feel that way. I guess he will think for himself. It's not that I am ignoring anything. What I have done so far is working although I'm sure it isn't proper. I guess I'm not a proper guy. What works for me works. I'm just learning how to do things. I don't know arrays at all and more or less I am fumbling through this. I don't know what question you are talking about but I'm only looking for the answers that will work for me the way I do things. And I do realize I might turn people off for this. But to be honest I find responses from people on this board in particular to be somewhat insulting and ignorant on their own. Not all. There are some cool people on here. But there are also some pompous type people here. There is no understanding that not everyone on this board has been doing this for a very long time. There are people who are trying their best to get through this. There are people who get overwhelmed by head banging problems and might miss some posts. There are people who probably would rather not come to this board and ask questions only to be ridiculed by php gods. The purpose of this board is to help others. You sound like a professor I had in college who was very intelligent, but he didn't understand that he had no clue how to help others. This I feel is a very unfriendly board with exceptions and to be honest I would rather not have to ask questions on it. As a matter of fact earlier today I asked on a non-php board a php question because I really like that board. The people are friendly and want to help others. They aren't as much concerned that someone asks questions in the proper way, but they try their best to help others. If I ask another question on this board, (and I probably will), you have absolutely no obligation to answer me just as anyone else doesn't. But to make a comment such as you did in your post is ridiculous. You sound like a person I'd rather not associate with. You probably don't even golf! But still I wish you well in anything you pursue. Just try to remember we are all human and not too many are perfect like you!

Figured this out. I placed the if statement just below the while and it works well....

Sorry about that. Here is the script with the sql..... $sql = "SELECT itemnum,title,close,quantity,bidquantity, " . " bidprice,buyitprice,buser,bidder,biditem " . " FROM Items LEFT OUTER JOIN Bids " . " ON Items.itemnum = Bids.biditem " . " WHERE buser != '' and close > '$time' and outbid = ' 0 ' ". #"GROUP BY buser " . #"HAVING bidder=$buser or buser=\'$busername\' ". " ORDER by buser ASC "; $result = mysqli_query($con,$sql); $num = mysqli_num_rows($result); echo $num; echo "<br>"; $i = 0; $prebidder = null; while($row = mysqli_fetch_array($result)) { $buser = $row['buser']; $itemno = $row['biditem']; $price = $row['bidprice']; $qty = $row['bidquantity']; $query = "INSERT INTO invoiceitems(invnumber,itemnum,price,quantity) VALUES('$ttinvno','$itemno','$price','$qty')";#,unix_timestamp())"; mysqli_query($con,$query); $prebidder = $buser; if ( $prebidder != $buser ) { $ttinvno= $ttinvno +1; } $prebidder = $buser; $i++; } ?>

I am trying to loop through a filtered table. This is the code of the loop: $result = mysqli_query($con,$sql); $num = mysqli_num_rows($result); $row = mysqli_fetch_array($result); $i = 0; $prebidder = $row['buser']; mysqli_data_seek($result,0); while($row = mysqli_fetch_array($result)) { $buser = $row['buser']; $itemno = $row['biditem']; $price = $row['bidprice']; $qty = $row['bidquantity']; $query = "INSERT INTO invoiceitems(invnumber,itemnum,price,quantity) VALUES('$ttinvno','$itemno','$price','$qty')";#,unix_timestamp())"; mysqli_query($con,$query); if ($prebidder != $buser) { $ttinvno= $ttinvno +1; } $prebidder = $buser; $i++; } ?> The filtered data has 55 rows of which 28 are unique. I am trying to loop through so that the names ($buser) match the invoice number ($ttinvno). I have the rows indexed on buser descending. But the rows don't seem to want to match correctly. The invoice numbers go in sequence correctly but for instance the first two rows have the same name. But no matter what I have done in this format either the invoice number changes after the first row, or it changes after the third row. I want it to change after the second row. I have tried multiple ways including prematurely defining row and then using mysqli_data_seek($result,0); to reset the result before the loop. Obviously there is a flaw in my code. Can anyone help? Thanks very much in advance!

Holy cow. Thanks so much!

OK so I took the 'die' out ad I am actually trying to work only with the Bids table. Outbid is a tiny integer (1) and out of curiosity I echoed $outbid = $row['outbid']; echo $outbid; the outbid and I get a zero. But if I put 'where outbid = 0', I get the error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '0'' at line 1 Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\Apache24\htdocs\choo\testphp06.php on line 13 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\Apache24\htdocs\choo\testphp06.php on line 14 This happens whether I use quotes or not. I tried 1 for true. I tried false, 'false'. Nothing seems to work. I don't understand this at all. Further this seems to work in the second query with the two tables. At least it appears that way. Can anyone explain this? I did read something that this error can be fickle and it is really driving me nuts.....

I just looked the field outbid and it is a smallint(1). So would that not be considered a number?

Thanks very much for that critique. Very much appreciated. One comment. You mentioned that I should not put quotes around numbers. And I tried that in reference to this line: "WHERE buser != '' and close > $time and outbid = 0" So when I take the single quotes away from the 0 I get this error: Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\Apache24\htdocs\choo\testphp05.php on line 18 ...... Which is referencing this line: $result = mysqli_query($con,$sql) or die ( mysqli_error()); which is 18. The only way I have been successful in running the second query is by using the quotes. Is there an explanation for that? I'm going to study this and use it as a guide going forward. I really appreciate it!

Sorry.. I had forgotten to paste my code in in my previous message...... <?php require('dbc.php'); $sql = mysqli_query($con,"SELECT * FROM invoice order by time desc"); $row = mysqli_fetch_array($sql); $invno = $row['invnumber']; $invno = $invno + 1; $ttinvno = $invno; $time = $row['time']; $sql = "SELECT itemnum, image1, title, close, quantity, bidquantity, ". "bidprice, buyitprice, seller, biditem, buser, bidder " . "FROM Items LEFT OUTER JOIN Bids " . "ON Items.itemnum = Bids.biditem " . "WHERE buser != '' and close > $time and outbid = '0'" . #"GROUP BY buser " . #"HAVING bidder=$buser or buser=\'$busername\' ". "ORDER by buser"; $result = mysqli_query($con,$sql) or die ( mysqli_error()); $i = 0; while($row = mysqli_fetch_array($result)) {; $seller = $row['seller']; $buyer = $row['bidder']; $price = $row['bidprice']; $sql = "INSERT INTO invoice(buyer,seller,subtotal) VALUES('$buyer','$seller','$price')";#,unix_timestamp())"; if(mysqli_query($con, $sql)){ #echo "Records inserted successfully."; } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($con); } $invno= $invno +1; $i++; } $sql = "SELECT bidnum, biditem,bidder,bidquantity,bidprice,outbid,bidtime,invnumber,time " . "FROM Bids LEFT OUTER JOIN invoice " . "ON Bids.bidder = invoice.buyer " . "WHERE time = '' and outbid = '0'" . #"GROUP BY bidder " . #"HAVING bidder=$buser or buser=\'$busername\' " . "ORDER by invnumber ASC"; $result = mysqli_query($con,$sql) or die ( mysqli_error()); #$num = mysqli_num_rows($sql); #echo $num; while($row = mysqli_fetch_array($result)) {; $ttinvno = $row['invoice.invnumber']; $bidder = $row['bidder']; $itemno = $row['biditem']; $price = $row['bidprice']; $qty = $row['bidquantity']; $ttime = $row['time']; $sql = "INSERT INTO invoice_items(invnumber,itemnum,price,quantity) VALUES('$ttinvno','$itemno','$price','$qty')";#,unix_timestamp())"; if(mysqli_query($con, $sql)){ #echo "Records inserted successfully."; } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($con); } #$ttinvno= $ttinvno +1; $i++; if ($bidder != $row['bidder']) { $ttinvno= $ttinvno +1; } } ?>

Hi... I am still having problems. The first and second querys work well. the invoice table is inserted perfectly. (BTW I found out that invnumber in this table is actually auto increment!) The third query seems to end up empty every time as it inserts nothing into invoice_items. I've tried multiple combinations but I'm guessing the query is empty as it produces nothing into the invoice_items table. First is there a way of testing the query to see exactly how many rows it is producing? I tried it with #$num = mysqli_num_rows($sql); #echo $num; But that fails. How can I check to see if there is actually something produced by the query? Second is there anything obvious in the third query that may be preventing this from working? Thanks for any suggestions!

Yes dbase is still around! I converted our scripts from dos to windows 5 years ago (They more or less run our business.) Wasn't easy. Actually I think this is easier than what I went through then. The project I am now working on is an old perl auction script that we have been running for years. I am trying to make some improvements using php instead of perl where possible. Thanks for the tip on the $invno. I am working localhost now but possibly it is already auto increment on the unix server. I'm not sure. Really good point though. Thanks!!! BTW I am doing these projects because I love coding. Not real good at it and more or less self taught, but I love it! Wish it was an option in my education when I was younger, but then PHP was not even thought of!!

Hi actually no that isn't the case at all. The data seems correct. I'm changing the rows within the loop. But I did change the script a little bit to below just because it is a little cleaner. This is what I am used to working in dbase, but I am aware there is probably a better way to do this in php. I am new at this and am interested in finding more information of what you mean by insert... select query. I know what it is but how would that look working with this data? This is how I changed the loop for now. (At work right now so I haven't had much time to clean this up.) <?php require('dbc.php'); $sql = mysqli_query($con,"SELECT * FROM invoice order by time desc"); $row = mysqli_fetch_array($sql); $invno = $row['invnumber']; $invno = $invno + 1; $time = $row['time']; $sql = "SELECT itemnum, image1, title, close, quantity, bidquantity, ". "bidprice, buyitprice, seller, biditem, buser, bidder " . "FROM Items LEFT OUTER JOIN Bids " . "ON Items.itemnum = Bids.biditem " . "WHERE buser != '' and close > $time and outbid = '0'". #"GROUP BY buser " . #"HAVING bidder=$buser or buser=\'$busername\' ". "ORDER by buser"; $result = mysqli_query($con,$sql) or die ( mysqli_error()); #$result = $con->query($sql); #$row = mysqli_fetch_assoc($result); $i = 0; while($row = mysqli_fetch_array($result)) {; $seller = $row['seller']; $buyer = $row['bidder']; $price = $row['bidprice']; $sql = "INSERT INTO invoice(invnumber,buyer,seller,subtotal) VALUES('$invno','$buyer','$seller','$price')";#,unix_timestamp())"; if(mysqli_query($con, $sql)){ echo "Records inserted successfully."; } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($con); } $invno= $invno +1; $i++; } ?>

OK I was able to insert data into the invoice table like this: <?php require('dbc.php'); $sql = mysqli_query($con,"SELECT * FROM invoice order by time desc"); $row = mysqli_fetch_array($sql); $invno = $row['invnumber']; $invno = $invno + 1; $time = $row['time']; $sql = "SELECT itemnum, image1, title, close, quantity, bidquantity, ". "bidprice, buyitprice, seller, biditem, buser, bidder " . "FROM Items LEFT OUTER JOIN Bids " . "ON Items.itemnum = Bids.biditem " . "WHERE buser != '' and close > $time and outbid = '0'". #"GROUP BY buser " . #"HAVING bidder=$buser or buser=\'$busername\' ". "ORDER by buser"; $result = mysqli_query($con,$sql) or die ( mysqli_error()); #$result = $con->query($sql); $row = mysqli_fetch_assoc($result); $seller = $row['seller']; $buyer = $row['bidder']; $itemnum = $row['biditem']; $price = $row['bidprice']; $quantity = $row['bidquantity']; $buser = $row['buser']; $gtotal = 0; #echo $seller; $i = 0; #while $row['buser'] = $buser; while($row = mysqli_fetch_array($result)) {; $sql = "INSERT INTO invoice(invnumber,buyer,seller,subtotal) VALUES('$invno','$buyer','$seller','$price')";#,unix_timestamp())"; if(mysqli_query($con, $sql)){ echo "Records inserted successfully."; } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($con); } $invno= $invno +1; $i++; $seller = $row['seller']; $buyer = $row['bidder']; $price = $row['bidprice']; } ?> This isn't finished yet but a good start. I did not have to fool with an array, thank goodness. I found I can't insert the timestamp yet because that nulls the query as it is. I'll do that later after I have inserted everything I need into the two tables. Any comments on this code to improve it I would appreciate. Or suggestions on how to move forward......

Hi Handball.. That is correct. I have joined the two tables and want to insert the joined table data into two other tables. (Basically I am trying to make invoice entries.) One of the insert tables is named invoice. I need to insert into it invnumber which is actually a variable that I made based on the last invnumber from that table. So it is invnumber + 1. In this case I made a variable. I need to also insert seller and buyer into invoice. Also I need to insert into buyer and seller which unlike invnumber are fields from the tables. The second insert table is named invoice_items. I need to insert invnumber (variable), item number (bid.biditem), price (bid.bidprice), and quantity (bid.bidquatity). The fields in parenthesis are from the joined tables. (BTW thanks for the comment on the numeric fields.... That will be helpful.) The number of records I am inserting can vary. There may be 20 or so invoice rows to be inserted and 60 or so invoice_items rows. It does make sense to insert into the two tables separately. My problem is I don't exactly know how to loop the fields into an array, then insert them into the two tables as this is a first for me. i have actually done this in dbase before which I am more familiar with, but not with arrays. Just not sure how to handle this as I am relatively new at php. I really appreciate the replies.

No idea what I am doing here. I have a joined table which is: $sql = "SELECT itemnum, image1, title, close, quantity, bidquantity, ". "bidprice, buyitprice, seller, biditem, buser, bidder " . "FROM Items LEFT OUTER JOIN Bids " . "ON Items.itemnum = Bids.biditem " . "WHERE buser != '' and close > '$time' and outbid = '0'". #"GROUP BY buser " . #"HAVING bidder=$buser or buser=\'$busername\' ". "ORDER by buser"; $result = mysqli_query($con, $sql) or die ( mysqli_error()); $row = mysqli_fetch_assoc($result); I need to loop through the table on buser and insert select data into two other tables. One of the tables is the control table which will only have one row per buser. The other table I guess you would call the data table of the two and may have more than one row depending. The catch is these will be linked together by an invoice number. Table A, the control table has a field for invoice number, table B does not. Of course once the data is inserted into the new tables they will be joined and from there on it should be easy. I just don't know now how to get there. I've thought about using an array to loop through, but I don't know enough about them to make any sense of this. Not even sure the group by may help me in doing this. Anyone have any ideas on this? I sure do need it and thanks in advance!

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

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

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>

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.

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?

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.