Jump to content

charlie321

Members
  • Content Count

    28
  • Joined

  • Last visited

Community Reputation

0 Neutral

About charlie321

  • Rank
    Member

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. 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++; } ?>
  2. 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!
  3. 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.....
  4. I just looked the field outbid and it is a smallint(1). So would that not be considered a number?
  5. 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!
  6. 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; } } ?>
  7. 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!
  8. 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!!
  9. 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++; } ?>
  10. 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......
  11. 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.
  12. 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!
  13. Yes I just figured that out. Thanks very much!!!!!
  14. 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....
×
×
  • 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.