looping through a joined table then inserting into two tables

[charlie321]

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!

[Barand]

7 minutes ago, charlie321 said:

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.

Which is which?

Can you provide your table structures and, perhaps, some sample data. (SQL dump maybe)

[ginerjm]

OK - you have 2 tables - Items and Bids so far.  You are saying that you want to get some data items out of the query results and insert them into 2 other tables.  Is that correct?

Why not use your loop to create two arrays of the data items you want to add to each table.  That is, a separate array for each of the 2 new tables.  Then run an insert query on each one to insert those items, using the 'value arrays' to insert multiple rows in one query execution.  Does that sound like a plan?

BTW - in the query you posted - if close is a date or datetime or time field (by def.) you don't need quotes on the value since it is numeric.  Just be sure it is in the proper format.  And the same for the outbid value.   Obviously you will need to add a while loop around that fetch line you have above.  As for the group by, if has no use here since I believe all you are doing is collecting the joined sets of data in order to create your new input values for the other tables.

BTW #2 - if you already have these data items in your database, why do you need to copy them to new tables?

Edited July 15, 2019 by ginerjm

[benanamen]

11 minutes ago, ginerjm said:

BTW #2 - if you already have these data items in your database, why do you need to copy them to new tables?

Yeah, the question smells. OP tell us what the real problem is you are trying to solve instead of asking about your attempted solution to solving it.

[charlie321]

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.

 

[charlie321]

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

[Barand]

Have you checked what has been inserted into the invoice table?

It looks like you get values from the first row then loop through the rest of the rows in the results, outputting the same values from the first row into every new record in the invoice table.

All that processing could be accomplished with a single "INSERT ... SELECT ... " query.

Keep trying and good luck.

[charlie321]

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++;
}
?>

[ginerjm]

dBase??? I remember when that first came out.  And people are still using it?  Wow!

[mac_gyver]

20 minutes ago, charlie321 said:

$invno = $invno + 1;

the above won't work correctly if there are concurrent instances of your script running, unless you lock the table for the duration of this part of the process, which is undesirable. each occurrence of your script will get the same starting value, attempt to modify and use it, resulting in duplicate values, which should produce query errors, if your table is defined correctly with that column being a unique index, or will mess up your stored data if not.

what you should do is have the invoice number column be an auto-increment integer primary key. you would just insert a new row of data, then get the last insert id from that query to use when inserting the item data in the next table.

[Barand]

28 minutes ago, ginerjm said:

dBase??? I remember when that first came out.  And people are still using it?

Aah, memories! I cut my SQL teeth on dBase and FoxPro around 30+ years ago.

 

[charlie321]

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

[Barand]

20 minutes ago, charlie321 said:

Wish it was an option in my education when I was younger,

Same here. When I started work in IT there was a theory around that, one day, every company would have a computer!

[charlie321]

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!

[ginerjm]

#$num = mysqli_num_rows($sql);
#echo $num;
	

Your two comments "fail"?  Not sure how that can happen.

Try echoing out your query before you execute it.  Maybe just once (followed by an exit() ) to see if it is being built correctly.

Edited July 17, 2019 by ginerjm

[Barand]

num_rows() is for SELECT queries.

affected_rows() is for INSERTS, UPDATES, DELETES

[charlie321]

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;
  }
}

?>

[mac_gyver]

some implementation points for the code -

• you have semicolons ; on the end of your while(...) {; lines, so if your loops are not doing what you expect, this is the reason.
• 'require' isn't a function, so the () around the filename are not needed and are just cluttering up your code.
• you have inconsistent, nonworking, and nonexistent error handling for the database statements. you should also not unconditionally output database errors onto a live site (and you shouldn't spend your time editing code when moving it between development and a live site), as this will just give hackers useful information about your connection username and server path when they intentionally trigger errors. instead, use exceptions to handle database statement errors and in most cases let php catch and handle the exceptions, where it will use its error related settings (error_reporting, display_errors, and log_errors) to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.) this will let you remove all the error handling logic you have now, simplifying the code. to use exceptions for errors for the mysqli extension, add the following line of code before the point where you make the connection - 

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

• you have unused variables and unused columns being SEELCTed in the queries. you should only write code/query syntax that gets used. this is more important if someone other than you is expected to read and figure out what the code/queries are doing, such as forum members where you are asking for help.
• you have another race/timing problem in the code. by using the highest unixtime from the invoice table to detect new closed bids, you can miss bids if this code runs right before a new close time and takes longer than a second to run. the new unixtime that gets inserted into the invoice table can be greater than a close time that was never processed. (i know of some forum software (VB) that has/had a similar problem where remembering the last visited time and querying for records greater than that time and misses information that does exist but wasn't processed.) the correct way of handling this is to use the id of the highest bid that was processed. you would then query for bid ids that are greater than the highest bid id that was processed.
• you can put white-space (space, tab, new-lines) in an sql query statement to format it, so all the error-prone concatenation is not needed and is just more clutter in your code.
• copying variables to other variables is just more error-prone typing and clutter. just use the original variables.
• don't put quotes around values that are numbers.

for what you are doing, inserting invoice record(s) and corresponding item record(s), you should just SELECT the item and bid information (the first JOIN query in your code), fetch the data from that query into an array of sub-arrays of rows, indexed by a composite buyer/seller value for the main array index, and an array of rows for each buyer/seller, then loop over this array of data in the rest of the code.

for the rest of the code, all you will need is two nested foreach(){} loops. the first loop would get the composite buyer/seller value and the sub-array of corresponding rows. you would execute the insert query for the invoice table as part of this loop and get the last insert id from this query. the second loop would loop over the sub-array of rows and execute the insert query for the invoice_items table.

speaking of looping and executing queries. you should use prepped queries, with place-holders for each value, then supply the values when the query gets executed. this will provide a performance gain (about 5% for INSERT queries) and will also prevent sql injection (any bid or item information that came from an external source could contain sql special characters that will break the sql query syntax, which is how sql injection is accomplished.) you would prepare each query once, before the start of any looping, then just supply the data values when you execute the query inside of the loops. unfortunately, the mysqli extension is overly complicated and inconsistent when dealing with prepared queries, and you should switch to the much simpler and more consistent PDO extension.

Edited July 17, 2019 by mac_gyver

[charlie321]

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!

 

[charlie321]

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

[Barand]

As the error message says" ... expects 1 parameter".

You haven't provided any in ... or die( mysqli_error() );

RTFM

[charlie321]

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

[mac_gyver]

1 hour ago, charlie321 said:

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!

 

the php error you got is a result of the nonworking error handling, but is being caused by the error--prone concatenation used to build the sql query statement. you are missing needed white-space between the 0 and the following ORDER BY... term, that quoting the number satisfied.

[charlie321]

Holy cow.  Thanks so much!