Re: [PHP-DB] Multiple inserts into a database
Daniel Smith wrote:
select itemname from deals d, items i, deal_items di where
d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1";
Would I be correct in thinking that the above is in effect the same as:
select itemname from deals, items, deal_items
where deals.dealid=deal_items.dealid
and items.itemid = deal_items.itemid
and dealname="meal 1";?
I haven't seen the use of "deals d", I'm guessing this basically means
"the table called deals but from now on I shall call it d" Does this
offer are benefits over my version above? I currently use this rather
long winded query because I find it helps me to work out what I am
using. I suppose x years down the line when I'm better at constructing
my queries, I would use your shorter version.
Yep they are the same. I'm using a table alias. See
http://www.designmagick.com/article/32/page/4 (it's a postgres article
but works just the same in mysql).
The problem I am having is going from the kind of select query you
mentioned to then using the results to go into a kind of order_history
table.
Using your example of the query:
select itemname from deals d, items i, deal_items di where
d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1"
I think I'm going to get:
x--x
| itemname |
| burger 1 |
| chips|
| drink|
x--x
What I want to do is then take this data and insert it into an
order_history kind of table containing other fields, so ultimately I can
establish that customer x on 01/01/2001 at 12:00 ordered meal 1 which
consists of (burger 1, chips, drink), part of the purpose being so I can
individual items that can be ordered as part of a group as well as
individual items. The whole customer x part is stored elsewhere and I
am intending to tie the order information with something like:
x-x
| order_id | customer_id | timestamp |
| 1|32 |2006-11-30 18:01:07 |
| 2|13 |2006-11-30 19:01:07 |
| 3|46 |2006-11-30 20:01:07 |
x-x
xx
| order_history_id | order_id | itemname |
|1 |3 | burger 1 |
|2 |3 | chips|
|3 |3 | drink|
xx
When I get the results from your query, in order to get the above table,
would i be best to do something
foreach ($array as $value) {
INSERT blah, blah INTO blah blah, WHERE blah = $value
}
You might want to include the price in the order history as well -
otherwise as prices change, your old orders will be affected. Whether
that's a good or bad thing you have to decide but something to think
about anyway.
You can also just copy the data straight in using an insert into select
query:
http://dev.mysql.com/doc/refman/4.1/en/insert-select.html
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple inserts into a database
> select itemname from deals d, items i, deal_items di where
> d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1";
Would I be correct in thinking that the above is in effect the same as:
select itemname from deals, items, deal_items
where deals.dealid=deal_items.dealid
and items.itemid = deal_items.itemid
and dealname="meal 1";?
I haven't seen the use of "deals d", I'm guessing this basically means
"the table called deals but from now on I shall call it d" Does this
offer are benefits over my version above? I currently use this rather
long winded query because I find it helps me to work out what I am
using. I suppose x years down the line when I'm better at constructing
my queries, I would use your shorter version.
The problem I am having is going from the kind of select query you
mentioned to then using the results to go into a kind of order_history
table.
Using your example of the query:
select itemname from deals d, items i, deal_items di where
d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1"
I think I'm going to get:
x--x
| itemname |
| burger 1 |
| chips|
| drink|
x--x
What I want to do is then take this data and insert it into an
order_history kind of table containing other fields, so ultimately I can
establish that customer x on 01/01/2001 at 12:00 ordered meal 1 which
consists of (burger 1, chips, drink), part of the purpose being so I can
individual items that can be ordered as part of a group as well as
individual items. The whole customer x part is stored elsewhere and I
am intending to tie the order information with something like:
x-x
| order_id | customer_id | timestamp |
| 1|32 |2006-11-30 18:01:07 |
| 2|13 |2006-11-30 19:01:07 |
| 3|46 |2006-11-30 20:01:07 |
x-x
xx
| order_history_id | order_id | itemname |
|1 |3 | burger 1 |
|2 |3 | chips|
|3 |3 | drink|
xx
When I get the results from your query, in order to get the above table,
would i be best to do something
foreach ($array as $value) {
INSERT blah, blah INTO blah blah, WHERE blah = $value
}
Note, the above is only illustrative and not meant to be my attempt at a
working foreach statement.
Thanks for the help so far
Danny
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple inserts into a database
Daniel Smith wrote: The kind of thing I'm looking to do is to insert a variable number of records into a database as a consequence of a form entry. I am using PHP with a MySQL database to store information that is vaguely analogous to a fast food online ordering system. I have a table containing individual items with a short code for each item. A user would enter the short code and this gets stored. x---x | item_id | item_name | item_code | |1| Burger | bur | |2| Fries| fr| |3| Cola | co| x---x What I want to do is have a way of letting the customer order multiple items with one code, e.g. entering "meal" results in burger/fries/cola being entered in an order table. I realise i need in effect a translation table, that contains meal with references to the items burger/frires/cola, something like below so I could do something "SELECT item_id FROM deal WHERE deal_name = meal" to get the individual items for the "meal" x---x | deal_id | deal_name | item_id| |1| meal | 1 | |2| meal | 2 | |3| meal | 3 | x---x So far, I can understand what I need to do to achieve this. What I am having trouble trying to understand what to do and how best to do it, is to get the individual elements of the meal entered into an order table. Would a foreach statement that works through the array produced by the "meal" query, inserting each item in the meal be the best way forward? The "deals" on offer would vary in size e.g. burger+fries/burger+fries +cola+ice cream+toy so whatever solution is use, would have to cope with changing size. Note: As you might have noticed already, I'm not the world's best PHP/MySQL programmer but I'm ready and willing to try stuff. I'd do it a little differently. create table deals(dealid, dealname); create table items(itemid, itemname); create table deals_items(dealid, itemid); Then you have something like this: - | dealid | dealname | - | 1 | meal 1 | | 2 | meal 2 | . the items table contains - | itemid | itemname | - | 1 | burger 1 | | 2 | burger 2 | | 3 | chips| | 4 | drink| . then for the deal_items table it joins both together: --- | dealid | itemid | --- | 1 | 1 | | 1 | 3 | | 1 | 4 | To get everything for "meal 1" you can then do a simple join query: select itemname from deals d, items i, deal_items di where d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1"; You'll need an index on deal_items(dealid, itemid) and dealid & itemid in their own tables will be primary keys. This is just called a "many-to-many" relationship. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Inserts
Thanks for the suggestions. I have
$TimeSheetID=$_POST['TimeSheetID'];
at the start of the script. However, what I neglected to mention is that TimeSheetID
is auto_increment, in first table, tblTimesheet.
I will try the other suggestions, and respond with my results.
Thanks,
Declan.
---
Good point. Since it's form data, what about $_POST['TimesheetID'] ?
> Don't see anything obviously wrong with your query string. Are the inserts
> happening in the same block of code, i.e., are you sure that
> _$TimesheetID_ has a value in it when you're performing the second insert?
>
>
> -dave
>
>
>> I am using a form to Insert data into 2 tables in the same database.
>
>> $TimesheetID needs to be in each table. However, it is not being
> inserted
>> into the second table, "tblTimesheetDetails" . Any advise?
>
>> $result_timesheet=mysql_query("INSERT INTO tblTimesheet (TimesheetID,
>> WorkerID, ClientID, TimesheetDate, ProspectiveOrRetrospective) VALUES
>>
> ('$TimeSheetID','$WorkerID','$ClientID','$TimesheetDate','$ProspectiveOrRetr
>> ospective')")or die("Insert Error: ".mysql_error());
>
>> $result_timesheetdetails=mysql_query("INSERT INTO tblTimesheetDetails
>> (TimesheetID, ActivityTypeID, TimeSpentHours, TimeSpentMinutes) VALUES
>>
> ('$TimeSheetID','$ActivityTypeID','$TimeSpentHours','$TimeSpentMinutes')")or
>> die("Insert Error: ".mysql_error());
>
Re: [PHP-DB] Multiple Inserts
Good point. Since it's form data, what about $_POST['TimesheetID'] ?
> Don't see anything obviously wrong with your query string. Are the inserts
> happening in the same block of code, i.e., are you sure that
> _$TimesheetID_ has a value in it when you're performing the second insert?
>
>
> -dave
>
>
>> I am using a form to Insert data into 2 tables in the same database.
>
>> $TimesheetID needs to be in each table. However, it is not being
> inserted
>> into the second table, "tblTimesheetDetails" . Any advise?
>
>> $result_timesheet=mysql_query("INSERT INTO tblTimesheet (TimesheetID,
>> WorkerID, ClientID, TimesheetDate, ProspectiveOrRetrospective) VALUES
>>
> ('$TimeSheetID','$WorkerID','$ClientID','$TimesheetDate','$ProspectiveOrRetr
>> ospective')")or die("Insert Error: ".mysql_error());
>
>> $result_timesheetdetails=mysql_query("INSERT INTO tblTimesheetDetails
>> (TimesheetID, ActivityTypeID, TimeSpentHours, TimeSpentMinutes) VALUES
>>
> ('$TimeSheetID','$ActivityTypeID','$TimeSpentHours','$TimeSpentMinutes')")or
>> die("Insert Error: ".mysql_error());
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Inserts
Any errors? Is the all the other data inserting into the second table?
> Hello,
> I am using a form to Insert data into 2 tables in the same database.
>
> $TimesheetID needs to be in each table. However, it is not being inserted
> into the second table, "tblTimesheetDetails" . Any advise?
>
> $result_timesheet=mysql_query("INSERT INTO tblTimesheet (TimesheetID,
> WorkerID, ClientID, TimesheetDate, ProspectiveOrRetrospective) VALUES
> ('$TimeSheetID','$WorkerID','$ClientID','$TimesheetDate','$ProspectiveOrRetr
> ospective')")or die("Insert Error: ".mysql_error());
>
> $result_timesheetdetails=mysql_query("INSERT INTO tblTimesheetDetails
> (TimesheetID, ActivityTypeID, TimeSpentHours, TimeSpentMinutes) VALUES
> ('$TimeSheetID','$ActivityTypeID','$TimeSpentHours','$TimeSpentMinutes')")or
> die("Insert Error: ".mysql_error());
>
>
> Thanks,
> Declan.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Inserts
Don't see anything obviously wrong with your query string. Are the inserts
happening in the same block of code, i.e., are you sure that
_$TimesheetID_ has a value in it when you're performing the second insert?
-dave
> I am using a form to Insert data into 2 tables in the same database.
> $TimesheetID needs to be in each table. However, it is not being
inserted
> into the second table, "tblTimesheetDetails" . Any advise?
> $result_timesheet=mysql_query("INSERT INTO tblTimesheet (TimesheetID,
> WorkerID, ClientID, TimesheetDate, ProspectiveOrRetrospective) VALUES
>
('$TimeSheetID','$WorkerID','$ClientID','$TimesheetDate','$ProspectiveOrRetr
> ospective')")or die("Insert Error: ".mysql_error());
> $result_timesheetdetails=mysql_query("INSERT INTO tblTimesheetDetails
> (TimesheetID, ActivityTypeID, TimeSpentHours, TimeSpentMinutes) VALUES
>
('$TimeSheetID','$ActivityTypeID','$TimeSpentHours','$TimeSpentMinutes')")or
> die("Insert Error: ".mysql_error());
Re: [PHP-DB] Multiple inserts revisited
Yes, it does what I want, but perhaps not what I described. My goal was to find
a way to add values to the actual query, which this does nicely. Yours, thank
you very much, is even nicer. :)
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: "Ford, Mike [LSS]" <[EMAIL PROTECTED]>
To: "'Becoming Digital'" <[EMAIL PROTECTED]>; "PHP-DB"
<[EMAIL PROTECTED]>
Sent: Thursday, 29 May, 2003 08:00
Subject: RE: [PHP-DB] Multiple inserts revisited
> -Original Message-
> From: Becoming Digital [mailto:[EMAIL PROTECTED]
> Sent: 28 May 2003 23:38
>
> My other option, as I saw it, was to loop through the items,
> appending value
> data to the query text with each iteration. If that seems
> cryptic, here's a
> basic idea of what I mean.
>
> $query = "INSERT INTO specials VALUES ("
> foreach ( $specials as $item ) {
> $query = substr_replace( $query, $item.", ", strlen( $query ) );
> }
> $query = substr_replace( $query, ")", strlen( $query )-2 );
> ?>
>
> If $specials was an array with the values spec1, spec2, and
> spec3, the final
> value of $query would be:
> INSERT INTO specials VALUES (spec1, spec2, spec3)
I'm actually slightly dubious that this query *does* do what you want, but if
you've double-checked it and it does, then this is a somewhat more compact way
of producing it:
$query = "INSERT INTO specials VALUES (" . implode(',', $specials) . ')'
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Beckett Park, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multiple inserts revisited
> -Original Message-
> From: Becoming Digital [mailto:[EMAIL PROTECTED]
> Sent: 28 May 2003 23:38
>
> My other option, as I saw it, was to loop through the items,
> appending value
> data to the query text with each iteration. If that seems
> cryptic, here's a
> basic idea of what I mean.
>
> $query = "INSERT INTO specials VALUES ("
> foreach ( $specials as $item ) {
> $query = substr_replace( $query, $item.", ", strlen( $query ) );
> }
> $query = substr_replace( $query, ")", strlen( $query )-2 );
> ?>
>
> If $specials was an array with the values spec1, spec2, and
> spec3, the final
> value of $query would be:
> INSERT INTO specials VALUES (spec1, spec2, spec3)
I'm actually slightly dubious that this query *does* do what you want, but if you've
double-checked it and it does, then this is a somewhat more compact way of producing
it:
$query = "INSERT INTO specials VALUES (" . implode(',', $specials) . ')'
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Beckett Park, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Inserts with mySQL
10 Seconds searching in the MySQL Manual: http://www.mysql.com/doc/en/ANSI_diff_Transactions.html You have to use InnoDB tables, but this will work. On Thu, 2002-11-14 at 11:26, Anthony wrote: > hmm, I guess that would work. There is no way to have mySQL check for > integrity for me is there? I read in a SQL book a while ago about > wrapping multiple inserts in a select statement, that way if any part of > the statement failed the whole thing would be rolled back. From my > research though, it doesn't look like mySQL supports this. Am I right > here? So far it looks like your idea could be the most reliable > suggestions I've gotten, so I'll start working on that for now. Thanks. > > - Anthony > > Peter Beckman wrote: > > Try this: > > > > $stack is an array of hashes: > > > > $stack[0] = array(0=>tablename, 1=>insertid()); > > > > For each insert you do, push an anonymous array on $stack which includes > > the tablename and insertid of the insert. > > > > Then as you continue your inserts, if any of them fail, call a function > > which takes that array $stack, and iterate through it, deleting the rows > > you just inserted. (see mysql_insert_id() function to get the insert ID) > > > > If none of them do fail, then you are in the clear. Either unset $stack or > > ignore it. > > > > Peter > > > > On Wed, 13 Nov 2002, Anthony wrote: > > > > > >>I have to drop a lot of data into mySQL from PHP. It will go into quite > >>a few different tables. How can I maintain integrity of the entire > >>insert? I need to do about a dozen inserts on four tables and I need to > >>insure that all the inserts are successful or that none of them get > >>done. I'm sort of new at this, so please help me out. Thanks. > >> > >>- Anthony > >> > >> > >>-- > >>PHP Database Mailing List (http://www.php.net/) > >>To unsubscribe, visit: http://www.php.net/unsub.php > >> > > > > > > --- > > Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation > > [EMAIL PROTECTED] http://www.purplecow.com/ > > --- > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
Re: [PHP-DB] Multiple Inserts with mySQL
hmm, I guess that would work. There is no way to have mySQL check for integrity for me is there? I read in a SQL book a while ago about wrapping multiple inserts in a select statement, that way if any part of the statement failed the whole thing would be rolled back. From my research though, it doesn't look like mySQL supports this. Am I right here? So far it looks like your idea could be the most reliable suggestions I've gotten, so I'll start working on that for now. Thanks. - Anthony Peter Beckman wrote: Try this: $stack is an array of hashes: $stack[0] = array(0=>tablename, 1=>insertid()); For each insert you do, push an anonymous array on $stack which includes the tablename and insertid of the insert. Then as you continue your inserts, if any of them fail, call a function which takes that array $stack, and iterate through it, deleting the rows you just inserted. (see mysql_insert_id() function to get the insert ID) If none of them do fail, then you are in the clear. Either unset $stack or ignore it. Peter On Wed, 13 Nov 2002, Anthony wrote: I have to drop a lot of data into mySQL from PHP. It will go into quite a few different tables. How can I maintain integrity of the entire insert? I need to do about a dozen inserts on four tables and I need to insure that all the inserts are successful or that none of them get done. I'm sort of new at this, so please help me out. Thanks. - Anthony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Inserts with mySQL
hmm, I guess that would work. There is no way to have mySQL check for integrity for me is there? I read in a SQL book a while ago about wrapping multiple inserts in a select statement, that way if any part of the statement failed the whole thing would be rolled back. From my research though, it doesn't look like mySQL supports this. Am I right here? So far it looks like your idea could be the most reliable suggestions I've gotten, so I'll start working on that for now. Thanks. - Anthony Peter Beckman wrote: Try this: $stack is an array of hashes: $stack[0] = array(0=>tablename, 1=>insertid()); For each insert you do, push an anonymous array on $stack which includes the tablename and insertid of the insert. Then as you continue your inserts, if any of them fail, call a function which takes that array $stack, and iterate through it, deleting the rows you just inserted. (see mysql_insert_id() function to get the insert ID) If none of them do fail, then you are in the clear. Either unset $stack or ignore it. Peter On Wed, 13 Nov 2002, Anthony wrote: I have to drop a lot of data into mySQL from PHP. It will go into quite a few different tables. How can I maintain integrity of the entire insert? I need to do about a dozen inserts on four tables and I need to insure that all the inserts are successful or that none of them get done. I'm sort of new at this, so please help me out. Thanks. - Anthony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Inserts with mySQL
Try this: $stack is an array of hashes: $stack[0] = array(0=>tablename, 1=>insertid()); For each insert you do, push an anonymous array on $stack which includes the tablename and insertid of the insert. Then as you continue your inserts, if any of them fail, call a function which takes that array $stack, and iterate through it, deleting the rows you just inserted. (see mysql_insert_id() function to get the insert ID) If none of them do fail, then you are in the clear. Either unset $stack or ignore it. Peter On Wed, 13 Nov 2002, Anthony wrote: > I have to drop a lot of data into mySQL from PHP. It will go into quite > a few different tables. How can I maintain integrity of the entire > insert? I need to do about a dozen inserts on four tables and I need to > insure that all the inserts are successful or that none of them get > done. I'm sort of new at this, so please help me out. Thanks. > > - Anthony > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re:[PHP-DB] multiple inserts
"Barry Rumsey" <[EMAIL PROTECTED]> wrote on 4/21/2002 4:58:21 AM:
>
>I have the following insert :
>mysql_connect( "localhost", "", "" );
> mysql_select_db( "" );
>
> mysql_query("INSERT INTO music_album VALUES
>(NULL, '$artist_id' ,'$album' ,NULL ,NULL)");
>
> mysql_query("INSERT INTO music_songs VALUES
>(NULL ,NULL ,NULL ,NULL ,'$songname' ,'$lyrics')");
>
>When I run this, the first insert works alright but the second insert
>does nothing. What am I doing wrong ?
> How do I do a mysql_insert_id()?
if you'd think that your queries is correct, try use some sort of different identifier
for each
query. and also look at mysql_error, as this very useful to determine what's wrong.
$query1 = mysql_query("INSERT INTO music_album VALUES
(NULL, '$artist_id' ,'$album' ,NULL ,NULL)");
if(!$query1) {echo "error query 1 mysql_error()"; }
$query2 = mysql_query("INSERT INTO music_songs VALUES
(NULL ,NULL ,NULL ,NULL ,'$songname' ,'$lyrics')");
if(!$query2) {echo "error query 2 mysql_error()"; }
CMIIW
_
adi.baliroomfinder.net
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
