2. PHPRunner General questions
  3. [SOLVED]  Insert Multiple Records with new Database API
This topic is locked
[SOLVED]

 Insert Multiple Records with new Database API

 		12/15/2019 11:05:01 AM
PHPRunner General questions
lefty author

I have three tables . This is done all inline.

I am trying to use newer Database API.

I would like to insert a record from Table(1) into table(2) based on field that is unique to Table(3) and Table(1) and in which Table(3) is right outer joined by field called class_id joined with Table(2) . Table 3 has for example 3 records with matching field in one record in Table(1).
When I look at the list page on Table(2) I can see query works as there is a list of the three items ( not saved - except 1 record is saved in database - see below ) ( I am also using Excel like grid here ) , Since Table(1) only actually adds 1 record to database So How can I insert all 3 records to Table (2) for each record with matched Table (1) record ( class_id and flddate ) and matched records from table ( 3 ) - 3 records fields ( class_id and flddate) .
I have this for adding 1 record. Where can I use a parameter to add multiple records in new Database API?
After Add on Table(1)
//

$data = array();

$data["class_id"] =$values["class_id1"];

$data["flddate"]=$values["flddate"];

DB::Insert("Table2", $data );
I am thinking something like this
$rs = DB::Query("select * from table3 WHERE class_id=". $values["class_id1"]." AND flddate=" . $values["flddate"]." ");

while( $data = $rs->fetchAssoc() )

// Then insert to Table(2) all three records for example from matching fields from joined Table(3)
Then redirect to those 3 records in Table 2 from Table 1 after add instead of seeing whole list on list page inline.
Table (1) and Table (2) are auto_increment keys )
but can't seem to put together both ideas above. Cannot use Update joined fields as they are not saved yet. Went through whole manual head spinning. Any Help would be highly appreciated here?
Update : The issue seems to be auto_increment key after add in Table(1) to destination Table(2) , I am getting three results in the query designer with the same auto_increment key Table(2) for matching records on one record in database. How can I auto_increment for each row returned by query?



Testing with PHPRunner 10.3 34222
Admin 12/16/2019



I would like to insert a record from Table(1) into table(2) based on field that is unique to Table(3) and Table(1) and in which Table(3) is right outer joined by field called class_id joined with Table(2) . Table 3 has for example 3 records with matching field in one record in Table(1).


This just gives me vertigo.
You cannot insert multiple records with a single command. SQL's INSERT insert one record at the time unless you copy data as is from one table to another using INSERT INTO ... SELECT FROM syntax.
Try to narrow your question down to something more specific someone can understand and help.
lefty author 12/16/2019



This just gives me vertigo.
You cannot insert multiple records with a single command. SQL's INSERT insert one record at the time unless you copy data as is from one table to another using INSERT INTO ... SELECT FROM syntax.
Try to narrow your question down to something more specific someone can understand and help.


Will try narrowing this a bit:
Table setup - There is one record for each class ( id ( Auto increment ) class_id1 , date , other fields .......

Table attendance id ( auto increment ) class_id , date , other fields for taking attendance. ( RIGHT OUTER JOIN students ON students.class_id = attendance.class_id )

Table students ( key is student_id then , class_id , other fields ) many students that belong to class_id
OBJECTIVE: Table setup / after adding record insert a record into attendance for each student that matches class_id from students table.

ISSUE : I see query in designer shows the correct rows except - auto increments are the same ID of course because we just added one record to attendance table.
I was thinking of trying this instead:
$sql="Insert into attendance(class_id, class_date , etc..........) Values (". $values["class_id1"]." , ". $values[class_date]." , etc........) WHERE ( Select Count(*) from students where class_id =". $values["class_id1"].")"........................;

// stuck here not sure if this is possible to add multiple records

DB::Query($sql)
Or do I need to do a cross join or Union which I don't believe I can do in PHPrunner.
jadachDevClub member 12/16/2019

In cases like this, I execute a stored procedure. I find it much easier.
lefty author 12/17/2019



In cases like this, I execute a stored procedure. I find it much easier.


Thanks, for reply . I was thinking a trigger or stored procedure. Unfortunately never had to use either before so I am new to those. Just can't understand why I can't use an insert into with a select statement in after add event I saw this somewhere done just can't find the post it was years ago. You would think you can add multiple rows. I can hand code it but don't know how many times to execute it as it's all based on the count of records with values of an id field in another table which has a relationship class_id as I stated above. Have the data in a join just can't use it. Auto_increment just repeats in query screen. example three different student_id's(students table) with the same class_id produces same primary key value in testing query.

no good. Not sure what I am doing wrong .
Admin 12/18/2019

It didn't make things any clearer.
I think you need to figure out for yourself if this is a SQL question or a PHPRunner question. If you can handle whatever you trying to achieve using plain SQL then you can just run the same query in PHPRunner using either of options available be that new Database API or old Database API. A SQL query is just a SQL query.
HJB 12/18/2019

@John - https://xlinesoft.com/phprunner/docs/db_insert.htm - manual content is very straight on the syntax to be used. If you are experimenting, if not to say, to provoke that you own a NEWER database API (with of course keeping us in the dark of what it is all about), I'm sorry to tell you this: You try to mix FIRE and WATER here to get a HOT solution for your Alumni client who not only stopped brainstorming decades ago (after Excel grids came alive), but as well totally REFUSE to go NEW paths which such university buddies had been trampling to be their very own eternal PATHS in regard to so called SPREADSHEET usage where students and classes and else are listed in rows and columns. My personal advice to you is this: Please try to shun to even listen to such clients at all as they are not only totally unwilling to go NEW paths, but, worst, are at least trying to somehow force you (monetarily speaking before their well known little budgets to go) to quickly please bring an "Excel like grid" solution at nearly ZERO cost to avoid "learning curves" and so, I fear, you are trying this forum to somehow create a WHITE ELEPHANT solution for such "antique" users who shall never ever change their data flow minds until the rest of their life. So, what you try is this: You got "mouse-trapped" by the requirements of the "antiques" and now you want to use this forum (which is by the way NOT a support forum for third party oriented NEW database API at all, not to talk even a support forum for PHPR at all) to somehow get you out of the jam. It's pity to see you as years old PHPR user to currently stay in a total "fall back" in the aim to somehow help out "WalkingDeads" on something called "spreadsheet= database" movie which even NETFLIX would be willing to buy "offhand" in regard to the public press- and movie-rights straightaway once the "mission impossible" bullet had been finally hitting the reality while PHPR usage and its overwhelming innovative upgrading schedule on averaged minimum twice a year (now on three months basis) is UNIQUE = too shiny for "antiques" anyway.
lefty author 12/18/2019



It didn't make things any clearer.
I think you need to figure out for yourself if this is a SQL question or a PHPRunner question. If you can handle whatever you trying to achieve using plain SQL then you can just run the same query in PHPRunner using either of options available be that new Database API or old Database API. A SQL query is just a SQL query.


Yes Sergey , This went from a PHPrunner question to a MySQL SQL question very quickly. Client wants data to be dynamically available when entering list page . Turns out it is a foreign key issue as well as the clients design issue who also uses PHPrunner. I either need to do a Stored Procedure like Jadach mentioned or design a query in PhpMyAdmin and use as a datasource in PHPrunner. Did not realize even though my join showed me for example three rows of records it was tied to one record in the database. ( My bad

) . Excuse me if there was confusing statements in my post as never had this request before going back to first versions of PHPrunner 2005 unrelated to this sql issue ( so used to using PHPrunner - which is a good thing

) . Will mark as solved and post accordingly.
Ended up using $_SESSIONS to get what I needed and then unset them when user hit complete button . Although could not load list dynamically without having to add / edit records my issue is solved. Also learned you can use Unions in PHPrunner , just can't use the query designer buttons after designing the query . Just have to back out and bypass it. Something learned new everyday.